7.1. Хранилища данных

Хранилище данных (ХД) на практике реализуется как БД специальной структуры. Однако сама идея вынести ХД в отдельный самостоятельный класс связана с исследованием и анализом накопленных организацией данных в цифровом виде с целью получить конкурентные преимущества.

Информационная технология складирования данных (data warehousing) родилась в недрах компании IBM и была окончательно сформулирована Б. Инмоном и Р. Кимбаллом в 90-х прошлого столетия, как метод решения информационно-аналитических задач в области принятия и поддержки решений. Возникнув на стыке технологии БД, систем поддержки принятия решений (СППР - DSS) и компьютерного анализа данных, в дальнейшем концепция складирования данных претерпела эволюцию, поскольку оказалась пригодной для широкого круга приложений в бизнесе, науке и технологии.

Концепция ХД была предложена как основа методологии организации данных в системах поддержки и принятия решений. Согласно классическому определению Б. Инмона, хранилище данных есть предметно-ориентированная, интегрированная, неизменяемая и поддерживающая хронологию электронная коллекция данных для обеспечения процесса принятия решений.

Предметная ориентированность. Информация в ХД организована в соответствии с основными аспектами деятельности предприятия (заказчики, продажи, склад и т.п.), т.е. бизнес - процессами. Это принципиальное отличие ХД от оперативной БД, где данные организованы в соответствии с процессами (выписка счетов, отгрузка товара и т.п.), т.е. бизнес - операциями. Предметная организация данных в ХД способствует как значительному упрощению анализа, так и повышению скорости выполнения аналитических запросов. Выражается она, в частности, в использовании иных, чем в оперативных системах, схемах организации данных.

Интегрированность. Исходные данные извлекаются из оперативных БД, проверяются, очищаются, приводятся к единому виду, в нужной степени агрегируются (то есть вычисляются суммарные показатели) и загружаются в ХД. Такие интегрированные данные намного проще анализировать.

Привязка ко времени. Данные в хранилище всегда напрямую связаны с определенным периодом времени. Данные, выбранные их оперативных БД, накапливаются в хранилище в виде «исторических слоев», каждый из которых относится к конкретному периоду времени. Это позволяет анализировать тенденции в развитии бизнеса.

Неизменяемость. Попав в определенный «исторический слой» ХД, данные уже никогда не будут изменены. Это также отличает ХД от оперативной БД, в которой данные все время меняются, и один и тот же запрос, выполненный дважды с интервалом в 10 минут, может дать разные результаты. Стабильность данных также облегчает их анализ.

ХД оказались удобной электронной коллекцией для решения задач анализа данных не только в бизнесе, но и в науке и технологии. Следует отметить, что в определении соединены две различные функции: 1) сбор, организация и подготовка данных для анализа в виде постоянно наращиваемого набора данных; 2) собственно анализ как элемент подготовки и принятия решений.

Использование термина «поддержка и принятие решений» в качестве сферы применения ХД существенно сужает как определение, так и возможность применения концепции в других сферах. Если в определении в качестве области применения оставить лишь анализ и воспроизводство новых данных (как элемент обработки информации в научных, технологических и экологических системах), круг использования данной концепции может быть значительно расширен. Таким образом, можно дать и такое определение: ХД - есть организация и поддержка предметно-ориентированной, интегрированной, слабо изменяемой по внутренней структуре и поддерживающей хронологию электронной коллекции данных для обработки с целью извлечения новых данных или обобщения имеющихся.

Очень важен основной принцип действия ХД: единожды занесенные в него данные затем многократно извлекаются из него и используются для анализа. Отсюда вытекает одно из основных преимуществ использования этой технологии, — контроль информации, полученной из различных источников, предварительно согласованной и размещенной в ХД. Отметим, что отсюда следует и наиболее уязвимое место ХД, - корректность его данных, полученных из разных источников. Данные перед загрузкой должны быть либо «очищены от шума», либо обработаны методами нечеткой логики, допускающей наличие противоречивых фактов, чтобы противоречия в данных были по возможности устранены. Заметим также, что интеграция в определении ХД понимается не только как интеграция информации по всем источникам, но в смысле согласованного представления данных из разных источников по их типу, размерности и содержательному описанию.

С точки зрения применения концепции в бизнесе, производстве и технологиях следует придерживаться следующего определения: ХД - структурно расширяемая вычислительная среда, спроектированная для анализа неизменяемых во времени данных, логически и физически преобразованных из различных источников, соответствующая направлениям бизнеса, обновляемая и поддерживаемая длительный период времени, выраженная в простых бизнес -терминах и обобщенная (суммированная) для быстрого анализа.

Напомним, что целью настоящей лекции является изучение использование команды SECECT в ХД. Далее по ХД приводятся только основные сведения.

7.2. Основные модели данных в хранилищах данных

Поскольку ХД в первую очередь предназначено для исследования и анализа данных. Его предметную область естественно рассматривать как многомерное пространство лингвистических и числовых параметров, которые так или иначе связаны с осью времени (изменяются, медленно меняются или остаются постоянными с течением времени). Поэтому основной метод проектирования ХД называется многомерным моделированием данных, а модель данных называется многомерной.

Многомерную модель можно представить в виде куба (или гиперкуба) в силу конечности области изменение параметров. Для визуализации данных, которые имеют несколько измерений, аналитики используют аналогию с кубом данных (data cube), т.е. часть многомерного пространства, в котором факты сохраняются на пересечении n-измерений. Например, куб может хранить данные о продажах, организованные в трех измерениях «Товар», «Рынок сбыта» и «Время».

Основными понятиями многомерной модели данных являются: факты, атрибуты, измерения, метрики, иерархии и гранулированность.

Факт (fact) - это набор связанных элементов данных, содержащих метрики и описательные данные. Каждый факт обычно представляет элемент данных, численно описывающий деятельность организации, бизнес-операцию или событие, которое может быть использовано для анализа деятельности организации или бизнес - процессов. В ХД факты сохраняются в базовых таблицах реляционной БД. Например, стоимость товара, количество единиц товара и т.д.

Атрибут (Attribute) - это описание характеристики реального объекта предметной области. Как правило, атрибут содержит заранее известное значение, характеризующее факт. Обычно атрибуты представляются тестовыми полями с дискретными значениями. Например, габариты упаковки товара, запах товара.

Измерение (dimension) - это интерпретация факта с некоторой точки зрения в реальном мире. Измерения, подобно атрибутам, содержат текстовые значения, которые сильно связаны по смыслу между собой. Обычно измерения представляются как оси многомерного пространства, точками которого являются связанные с ними факты. В многомерной модели, каждый факт связан с одной или несколькими осями. Измерения обычно представляют нечисловые, лингвистические переменные, такие, как филиалы организации, сотрудники организации, покупатели и т.д. Например, при анализе продаж продукции, производимой или продаваемой организацией, такими измерениями обычно вступают время, покупатели, продавцы, место продажи или складирования товара.

Измерения задаются перечислением своих элементов (members). Элемент измерения (dimensional member) - уникальное имя или идентификатор (лингвистическая переменная), используемая для определения позиции элемента. Например, измерение время может содержать следующие элементы: все месяцы, кварталы, годы.

Часто элементы измерения находятся в отношении часть-целое или родитель - потомок, что позволяет ввести на измерении одну или несколько иерархий. Каждая иерархия может иметь несколько уровней иерархии (hierarchy levels). Каждый элемент измерения должен принадлежать только одному уровню иерархии, порождая, таким образом, разбиение на непересекающиеся подмножества. Примером может служить иерархия на измерении «Время»: год, полугодия, кварталы, месяцы и дни. Элемент измерения неделя может принадлежать двум месяцам, поэтому для некого следует определить другую иерархию.

Метрика или показатель (measure) - это числовая характеристика факта, который определяет эффективность деятельности или бизнес - действия организации с точки зрения измерения. Как правило, метрика содержит заранее неизвестное значение характеристики факта. Конкретные значения метрики описываются с помощью переменных. Например, пусть метрикой является численное выражение продаж товара в деньгах, количество проданных единиц товара и т.д. Метрика определяется с помощью комбинации элементов измерения и, таким образом, представляет факт.

Гранулированность (Granularity) - это уровень детализации данных, сохраняемых в ХД. Например, ежедневные объемы продаж, ежемесячные объемы продаж.

Существуют несколько схем для многомерного моделирования данных. Две из них считаются основными: схема «звезда» (star schema) и схема «снежинка» (snowflake schema). В более сложных случаях используются так называемые «многозвездочные» схемы или схема «созвездие фактов» с несколькими таблицами фактов (Fact Constellation Schema)..

Схема «звезда» имеет одну таблицу фактов и несколько таблиц измерений. Таблицы измерений являются денормализованными.

Схема «снежинка» имеет одну таблицу фактов и несколько нормализованных таблиц измерений.

На Рис. 7.1 приведен пример схемы «звезда», созданной для учета продажи бакалейных товаров. Таблица фактов «Продажи бакалеи» (учет операций продажи бакалейных товаров торговой компании) имеет один первичный ключ «Номер счета», четыре внешних ключа (по числу измерений) и два параметра «Количество» проданного бакалейного товара и «Стоимость товара».

Измерение «Время» является одним из критических элементов модели ХД. Если данные в OLTP системах запросы фокусируются на текущем моменте времени, то в системах поддержки принятия решений (DSS системах), для которых проектируются и создаются ХД, запросы фокусируются на задачах анализа данных, а именно, как данные изменялись в различные периоды времени. Например, каков был объем продаж торговой компании за последний квартал, месяц или каковы тенденции в покупках товаров в течение последнего квартала.

Например. Измерение «Магазин» позволяет сгруппировать операции продаж по магазинам с учетом их географического положения. Измерение «Товары» анализировать типовые схемы закупок товаров и отвечать на вопрос, какие товары, как правило, покупаются одновременно покупателями. Измерение «Покупатели» позволяет анализировать покупки с учетом их частоты, географического положения и количества.

Таблицы измерений являются своеобразным путеводителем при выборке строк из таблицы фактов.

Таблицы фактов имеют большое количество строк. Таблицы измерений, как правило, имею гораздо меньше число строк. Одним из главных преимуществ использования такой схемы является то, производительность операции СУБД соединения таблицы фактов и таких таблиц соединений будет близка к оптимальной.

Схема «снежинка» (Рис. 7.2) добавляет иерархию в таблицы измерений. Например, измерение «Регион» группирует магазины по географическим регионам, а измерение «Категория товара» группирует товары по категориям, измерение «Категория покупателей» группирует покупателей по категориям, измерение «Период продаж» группирует продажи по периодам времени. Таким образом, использование иерархий превращает схему «звезда» в схему «снежинка».

На Рис. 7.3 приведен пример схемы с несколькими таблицами фактов - схемы «созвездие фактов» (Fact Constellation Schema). На схеме есть три таблицы фактов: «Продажи бакалеи», «Учет_товара» и «Покупка_товара», у которых имеются, как общие измерения («Товары»), так и эксклюзивные (например, измерение «Склады» для таблицы фактов «Учет_товара»).

7.3. Многомерная схема хранилища данных для примеров

Рассмотрим схему «звезда» ХД, предназначенного для анализа сбыта продукции торговой организации. Фрагменты схем примеров построены на основе учебной схемы HR Oracle 11g. Она включает таблицы изменений «Товар» (Product), «Время» (Time), «Покупатель» (Customer), «Регион» (Region) и таблицу фактов «Данные по продажам» (Sales).

Таким образом, имеем одну таблицу фактов и четыре таблицы измерений. Рассмотрим, как конструируется запрос на SQL к схеме типа «звезда». В типовом запросе к такой схеме указываются детальные сведения о «точках в информационном пространстве» этой схемы (т.е. задаются измерения), а затем данные, соответствующие этим «точкам», обобщаются.

Рассмотрим пример запроса к таблице фактов «Данные по продажам». Пусть требуется просмотреть данные о продажах товара с идентификационным номером 33 за месяцы с мая по август текущего года по региону «Москва» с идентификационным номером 81. Тогда запрос может выглядеть следующим образом:

SELECT SUM(sales_dollar_amount* sales_quantity_sold), time_month, region_name
FROM Sales, Time, Region
WHERE Sales.region_id = Region.region_id
   AND Sales.time_id = Time_time_id
   AND Sales.product_id = 33
   AND Sales.region_id = 81
   AND Time.time_month    BETWEEN ‘Май’ AND ‘Август’
   AND Time.time_year = 2009  
GROUP BY time_month, region_name;

Изменяя данные о регионе, месяцах и товаре, при помощи выше приведенного запроса можно выявить тенденции изменения данных о продажах. Для схемы «звезда» характерно использование односторонних эквисоединений таблиц измерений и таблицы фактов. Таблицы измерений не соединяются друг с другом.

Метрика «Объем продаж», рассмотренная в предыдущем примере, является аддитивным фактом. Факт называется аддитивным, если его имеет смысл использовать с любыми измерениями для выполнения операций суммирования с целью получения какого-либо значимого результата.

Рассмотрим теперь, как конструировать запросы для полуаддитивных фактов. Факт называется полуаддитивным, если его имеет смысл использовать совместно с некоторыми измерениями для выполнения операций суммирования с целью получения какого-либо значимого результата.

Метрика «Остаток на складе» является типичным примером полуаддитивного факта. Допустим, что в магазине на складе в конце каждого месяца рассчитывается остаток по каждому товару. Рассмотрим схему типа «звезда» для ХД, которое предназначено для анализа движения товаров через магазин.

В схеме имеется три таблицы измерений «Месяц» (Data_month), «Магазин» (Store), «Товары» (Products) и таблица фактов «Остаток на складе» (Quantity_on_hand_fact). Метрика «Остаток на складе» является аддитивной по измерениям «Товары» (Products) и «Магазин» (Store), но не является аддитивной по измерению «Месяц» (Data_month). Попробуем с помощью запроса получить итоговое количество товаров на сладе в магазине в любой момент времени, используя измерения, для которых полуаддитивный факт является аддитивным.

Пусть необходимо просуммировать остатки товара «Подушка» на складе магазинов за январь 2019 года с учетом месторасположения последних, т.е. определить, сколько нереализованных подушек было в сети магазинов торговой организации в январе 2019 года. Сделаем это за счет соединения таблицы фактов с измерением «Магазин», как показано ниже.

<captionОписание полей таблиц схемы «звезда» для анализа движения товаров>

SELECT Store.store_location, SUM(Quantity_on_hand_fact.Quantity_on_hand)
FROM Store, Quantity_on_hand_fact, Products, Data_month
WHERE Store.store_id = Quantity_on_hand_fact.store_id
AND Quantity_on_hand_fact.month_id = Data_month.month_id
AND Products.product_id = Quantity_on_hand_fact.product_id
AND Data_month.data_month = ‘Январь’
AND Data_month.data_year = 2019
AND Products.product_name =’Подушка’
GROUP BY Store.store_location

Аналогично, можно суммировать метрику «Остаток на складе» по измерению «Товары», чтобы получать количество нереализованных товаров, сгруппированных по категориям товара.

В примерах, приведенных выше, использовалась агрегатная функция SUM() для суммирования и предложение GROUP BY для построения заданного разбиения результирующего множества. В ХД, как правило, отношение имеет внутреннюю структуру и при его обработке требуется проводить разбиение отношения на подмножества, обладающие тем или иным значением определенного атрибута. Например, в достаточно общей постановке вопрос можно сформулировать так: протабулировать значение некоторой функции на каждом из этих подмножеств в соответствии с общим значением атрибута. Предложение GROUP BY определяет, каким образом строить разбиение исходного множества на подмножества в соответствии с заданным критерием. Полученное разбиение представляет исходное отношение как объединение конечного числа непересекающихся подмножеств. Агрегатные функции применяются последовательно к каждому подмножеству в отдельности, а результат функции выводится в результирующем отношении.

Можно задавать условия выборки на результаты выполнения предложения GROUP BY для того, чтобы исключить определенные кортежи из построенного разбиения. Для этого предназначено специальное предложение HAVING, которое задает условие выборки к атрибутам перегруппированного, а не исходного отношения. Основное отличие в действии условия выборки предложения HAVING от аналогичного условия выборки предложения WHERE состоит в том, что первый выбирает подмножества из разбиения целиком в зависимости от его агрегируемых свойств, в то время как последний просматривает содержимое каждого из этих подмножеств построчно, не учитывая полученное разбиение. Иногда наблюдается более быстрое выполнение команды SELECT с использованием предложения HAVING, чем с предложением WHERE.