Виды моделей хранилища данных
Лабораторная работа №4
ПРОЕКТИРОВАНИЕ СТРУКТУРЫ ХРАНИЛИЩА ДАННЫХ
1.
2.
Определение и типовые архитектуры хранилищ данных
Определение понятия «хранилище данных» первым дал Уильям Инмон в своей монографии – это «предметно-ориентированная, интегрированная, содержащая исторические данные, неразрушаемая совокупность данных, предназначенная для поддержки принятия управленческих решений».
Данные из различных источников помещаются в хранилище, а их описания — в репозиторий метаданных. Конечный пользователь, используя различные инструменты (средства визуализации, построения отчетов, статистической обработки и т.д.) и содержимое репозитория анализирует данные в хранилище. Результатом является информация в виде готовых отчетов, найденных скрытых закономерностей, каких-либо прогнозов. Так как средства работы конечного пользователя с хранилищем данных могут быть самыми разнообразными, то теоретически их выбор не должен влиять на структуру хранилища и функции его поддержания в актуальном состоянии. Физическая реализация данной концептуальной схемы может быть самой разнообразной.
Двухуровневая архитектура хранилища данных подразумевает построение витрин данных (data mart) без создания центрального хранилища, при этом информация поступает из регистрирующих систем и ограничена конкретной предметной областью. При построении витрин используются основные принципы построения хранилищ данных, поэтому их можно считать хранилищами данных в миниатюре. Плюсы: простота и малая стоимость реализации; высокая производительность за счет физического разделения регистрирующих и аналитических систем, выделения загрузки и трансформации данных в отдельный процесс, оптимизированной под анализ структурой хранения данных; поддержка истории; возможность добавления метаданных.
Построение полноценного корпоративного хранилища данных обычно выполняется в трехуровневой архитектуре. На первом уровне расположены разнообразные источники данных — внутренние регистрирующие системы, справочные системы, внешние источники (данные информационных агентств, макроэкономические показатели). Второй уровень содержит центральное хранилище, куда стекается информация от всех источников с первого уровня, и, возможно, оперативный склад данных, который не содержит исторических данных и выполняет две основные функции. Во-первых, он является источником аналитической информации для оперативного управления и, во-вторых, здесь подготавливаются данные для последующей загрузки в центральное хранилище. Под подготовкой данных понимают их преобразование и проведение определенных проверок. Наличие оперативного склада данных просто необходимо при различном регламенте поступления информации из источников. Третий уровень представляет собой набор предметно-ориентированных витрин данных, источником информации для которых является центральное хранилище данных. Именно с витринами данных и работает большинство конечных пользователей.
Виды моделей хранилища данных
Хранилища строятся на основе многомерной модели данных, подразумевающей выделение отдельных измерений (время, география, клиент, счет) и фактов (объем продаж, доход, количество товара) с их анализом по выбранным измерениям. Многомерная модель данных физически может быть реализована как в многомерных, так и в реляционных СУБД. В последнем случае она выполняется по схеме «звезда» или «снежинка». Каждая таблица фактов содержит детальные данные и внешние ключи на таблицы измерений.
Теория построения многомерной модели данных и ее воплощение в реляционной структуре известна, однако информации по проблеме представлению иерархий очень мало. В качестве примера измерения, широко применяющегося при анализе деятельности предприятия и имеющего иерархическую структуру, можно привести справочник статей затрат (рис. 1).
Рисунок 1 – Модель иерархического справочника
Классически проблема представления иерархий решается с помощью рекурсивной связи, что позволяет помещать в одной таблице дерево любой глубины и размерности. В нашем случае рассматриваемые данные представлены в виде таблицы 1.
Таблица 1 – Представление иерархий с помощью рекурсивной связи
Однако в простоте этого решения скрывается и основной его недостаток: стандарт SQL не поддерживает рекурсивные указатели, поэтому для представления деревьев в хранилище данных используют другие методы.
Метод, предложенный Джо Селко, основан на теории множеств — все узлы дерева проходятся в прямом порядке и для каждого узла заполняются два значения (рис. 3).
Рисунок 2 – Нумерация левой и правой границ узлов дерева
Сначала заполняется левая граница и лишь затем правая — при движении обратно от потомков к родителям. При такой нумерации узлов каждый родитель содержит потомков, левая и правая граница которых лежит в интервале между левой и правой границей родителя. Аналогично все родители потомка имеют левую границу, которая меньше левой границы потомка и правую, большую правой границы потомка. Следовательно, сумму затрат для конкретного места возникновения затрат и всех его составляющих можно получить одним запросом. Например, для получения затрат по инфраструктуре можно выполнить следующий SQL-запрос:
select sum(fact_table.cost)
from fact_table, dimension_table D1, dimension_table D2
where fact_table.dimension_id = D2.id
and D2.left >= D1.left
and D2.right <= D1.right
and D1.name = «Инфраструктура»
Для простоты работы с таким справочником кроме полей left, right стоит добавить еще два поля: «Level» — уровень узла в дереве, «Is_leaf» — флаг, показывающий является ли узел листом в дереве или нет. Таким образом, мы получаем таблицу «dimension_table» (таблица 2), которая позволяет хранить дерево любой глубины вложенности и размерности и выбирать потомков и родителей с помощью одного запроса.
Таблица 2 – Представление иерархий с помощью левой и правой границ
Другой способ, описанный Ральфом Кимбаллом, основан на введении вспомогательной таблицы («helper-table»), через которую осуществляется связь таблицы фактов с таблицей измерения. Эта вспомогательная таблица отражает иерархическую структуру измерения и подчиняется следующему закону: вспомогательная таблица содержит весь набор пар «родитель-потомок», причем потомок может не быть непосредственным потомком родителя. Структура такой таблицы и ее содержимое показано в таблице 3.
Таблица 3 – Структура и содержание вспомогательной таблицы
Связывая таблицу фактов (рис. 4) с идентификатором потомка во вспомогательной таблице, а таблицу измерений с идентификатором родителя, мы можем вычислять сумму затрат для каждого места возникновения затрат и всех его составляющих одним запросом, как и в предыдущем случае. При этом, добавляя ограничения на поля «Distance» и «Is Leaf», мы можем легко считать затраты для любого уровня в иерархии.
Рисунок 4 – Модель иерархического справочника с вспомогательной таблицей
Например, для того, чтобы посчитать сумму затрат, возникающих в местах, находящихся по иерархии на один уровень ниже «Инфраструктуры», необходимо выполнить следующий SQL-запрос:
select sum(fact_table.cost)
from fact_table, dimension_table, helper_table
where fact_table.dimension_id = helper_table.child_id
and dimension_table.dimension_id = helper_table.parent_id
and dimension_table.name = «Инфраструктура»
and helper_table.distance = 1
Проблема проектирования иерархических справочников еще более усложняется когда измерение может иметь несколько альтернативных иерархий и становится совсем трудноразрешимой при необходимости поддерживать историю изменения таблицы измерения.
Важный момент, с которым часто приходится сталкиваться разработчику хранилища данных, связан с агрегатными значениями. Этот класс задач условно можно разделить на два подкласса. Первый рассматривает задачи создания и поддержания агрегатов по имеющимся детальным данным и широко освещен в литературе. Второй связан с тем, что источники данных для хранилища предоставляют собой не детальные значения, а уже некоторый набор агрегированных данных. Такая ситуация типична при создании хранилищ для управляющих компаний и государственных контролирующих органов, собирающих множество отчетных форм.
Крайним случаем такого подхода является модель, которую условно можно назвать «показатель-значение». Суть ее состоит в том, что собирается большой набор показателей, характеризующих финансово-хозяйственную деятельность предприятия. Эти показатели могут быть как связанными между собой функционально, так и нет, могут отражать одни и те же величины, но с разной степенью детализации и т.д. При попытке представить такие данные в виде многомерной модели разработчик сталкивается со значительными проблемами и очень часто идет по пути создания не хранилища данных, а хранилища форм. Типичное хранилище форм строится на основе трех измерений — экономические показатели, время, отчетные формы; таблицы фактов — значения экономических показателей и вспомогательных таблиц, описывающих, как показатели и их значения расположены в отчетных формах. При анализе таких данных аналитик будет испытывать значительные трудности, связанные главным образом с тем, что показатели различных форм нельзя сравнивать между собой. Единственное, что ему остается — это отслеживание изменений показателей одной формы во времени.