CC BY
23
ПРОЕКТИРОВАНИЕ УНИВЕРСАЛЬНОЙ СТРУКТУРЫ РЕЛЯЦИОННОГО
ХРАНИЛИЩА ДАННЫХ
П.В. Сараев
Липецкий государственный технический университет Кафедра прикладной математики E-mail: scorp@adm. les.lipetsk.ru
Введение
Успешная деятельность коммерческих предприятий и организаций тесно связана с объективностью, полнотой и удобством представления аналитической информации. Это, в свою очередь, требует эффективного хранения и обработки данных, представляющих собой ценность для процесса принятия управленческих решений. В настоящее время наилучшим способом решения указанной проблемы считается формирование хранилищ данных (ХД, Data Warehouse), содержащих аналитическую информацию, получаемую на основе данных оперативных систем, участвующих в технологическом процессе (OLTP-систем) [1]. К отличительным особенностям ХД относятся:
1) редкое добавление новой информации (один раз в день или реже);
2) огромное количество запросов данных;
3) невозможность удаления данных за предыдущие периоды (хранение всей исторической информации);
4) единые параметры хранения значений показателей (единицы измерения, точность и т.д.).
Также подразумевается, что каждое ХД является предмет-ориентированной базой данных (БД), т.е. моделью некоторой предметной области. Именно данный аспект является основной причиной трудоемкости работы по проектированию БД и основанных на этой БД информационно-аналитических систем. Имеется несколько причин:
1) моделирование деятельности даже небольших организаций и предприятий связано с необходимостью рассмотрения многих сторон его функционирования: технической, финансовой, кадровой, плановой, что приводит к сильному разрастанию количества сущностей и связей между ним;
2) технологические (в широком смысле) процессы каждой организации во многом индивидуальны, что требует и соответствующего индивидуального подхода в каждом конкретном случае;
3) неудачная реализация структуры ХД приводит к большим трудностям при его использовании во время эксплуатации (из-за невозможности изменить идеи, заложенные на стадии проектирования).
Общая характеристика поставленной задачи
В связи с вышесказанным была поставлена задача: спроектировать универсальную относительно моделируемой области, эффективную с точки зрения скорости обработки информации, удобную для разработки надстроечных программных продуктов (отчетных и информационно-аналитических систем) структуру ХД, а также процедуры для выполнения базовых операций над данными. В качестве основы была выбрана реляционная модель БД как наиболее широко используемая.
Для разработки структуры ХД был выделен следующий набор основных блоков ХД (совокупностей сущностей с определенной функциональной нагрузкой):
1) ядро;
2) блок отчетности (описание структур отчетных форм);
3) блок для идентификации, регламентации и аудита действий пользователей.
Самым важным является первый блок - ядро структуры ХД. Именно он содержит информацию о фактах (показателях), измерениях (параметрах детализации показателей) и собственно данные, необходимые для аналитики. Данные о показателях и измерениях предлагается хранить в виде дерева для возможности их структурированного учета и использования.
Блок отчетности предназначен для определения структуры и описаний вида отчетов. Отчетные формы несут двоякую нагрузку: с одной стороны, на их основе пользователям представляется выборка данных из хранилища, а с другой, они используются для ручного внесения данных, которые невозможно импортировать в систему автоматически из транзакционных и прочих систем, а также для их корректировки. Кроме того, структура должна позволять легко и удобно администрировать отчетные формы (целью является создание программного комплекса для визуального составления отчетов конечными пользователями наподобие системы Business Intelligence).
На базе блока для идентификации пользователей осуществляется авторизация и аутентификация пользователей, полномочия их доступа к конкретным показателям и формам, а также контроль их действий по изменению тех или иных данных.
Для снижения вероятности сознательной порчи или неумышленного изменения и удаления данных целесообразным является разработка определенных процедур доступа и обработки информации, контролирующих достоверность имеющейся информации. Такие процедуры собираются в пакеты, и изменение информации в ХД разрешается только на их основе.
Описание ядра хранилища данных На рис. 1 приведена концептуальная схема ядра ХД, предлагаемая в качестве базы для реализации универсальной структуры. Использованные обозначения стандартны для диаграмм типа "сущность-связь". На рис.2 показана физическая модель ХД в виде набора таблиц, которая во многих случаях более удобная для понимания. Рассмотрим далее назначение используемых сущностей.
Единица измере ния
ИД единицы измерения N <М>
Наименование единицы УД50 <М>
Аббревиатура УД10 <М>
Множитель N <М>
Тип показателя
ИД типа показателя N <М>
Комментарий УД200
Определение показателя
ИД показателя N <М>
Наименование УД200 <М>
Аббревиатура УД50
Точность N
Параметры детализации (измерения)
ИД параметра N <М>
Порядковый номер N <М>
Наименование УД200 <М>
Аббревиатура УД50
Тип суммируемости УД1
Начало действия
Окончание действия
Конкретный показатель
6 6 6 6
Возможные измерения
Данные
Дата актуальности <М>
Значение N <М>
Н»
Строки параметров (точки) ИД строки параметров N <М>
Отчетный период
Отчетный период <М>
Дата начала <М>
Дата окончания <М>
Открыт для заполнения УД1 <М>
Рис. 1. Концептуальная модель ядра хранилища данных
Объекты сущности "Определение показателя" представляет собой описание экономических, технических и прочих показателей, которые необходимы для проведения аналитической работы. К ним относятся, например, "Доходы", "Объем предоставляемых услуг", "Количество работников". Отношение иерархичности, показанное в схеме на рис.1 связью сущности с самой собой, позволяет организовать некоторую структуру
показателей. На верхнем уровне могут находиться блоки показателей (блок финансовых показателей, технических, натуральных и т.д.), которые далее детализируются. Иерархия предназначена для устранения хаоса введения и изменения имеющихся показателей и по возможности должна опираться на определенную классификацию, существующую в данной области деятельности предприятия.
ITEMTYPE
ITEMTYP NUMBER <pk>
COMM VARCHAR2(200)
MEASTYPE
MEASTYP NUMBER <pk>
P MEASTYP NUMBER <fk>
NAME VARCHAR2(50)
ABBR VARCHAR2(10)
FACTOR NUMBER
t
DEFINITION
DEFID NUMBER <pk>
P DEFID NUMBER <fk1>
MEASTYP NUMBER <fk2>
NAME VARCHAR2(200)
ABBR VARCHAR2(50)
PREC NUMBER
DEFID
ITEMTYP
ITEM
NUMBER <pk,fk2> NUMBER <pk,fk1 >
VAL
DEFID NUMBER <pk,fk1 >
ITEMTYP NUMBER <pk,fk1 >
PARSETID NUMBER <pk,fk2>
REPDAT DATE <pk,fk3>
ACTTUALDAT DATE <pk>
VAL NUMBER
PARAM
PARID NUMBER <pk>
P PARID NUMBER <fk>
SERNO NUMBER
NAME VARCHAR2(200)
ABBR VARCHAR2(50)
ADDTYP VARCHAR2(1)
BDAT DATE
EDAT DATE
DEFDETAIL
DEFID NUMBER <pk,fk1>
PARSETID NUMBER <pk,fk2>
PARAMSET
PARSETID NUM BER <pk>
PARID1 NUM BER <fk1>
PARID2 NUM BER <fk2>
PARID3 NUM BER <fk3>
PARID4 NUM BER <fk4>
PARID5 NUM BER <fk5>
REPDATE
REPDAT DATE <pk>
BREPDAT DATE
EREPDAT DATE
OPENED VARCHAR2(1)
Рис.2. Физическая модель ядра хранилища данных
Так как все показатели должны храниться в единой форме, сущность "Определение показателя" ссылается на сущность "Единица измерения", которая в иерархическом виде содержит информацию об используемых измерениях. В качестве родителя выступает базовая единица (рубль, метр), а в качестве дочерних - производные единицы с соответствующими коэффициентами-множителями (поле FACTOR) по отношению к базовой единице.
Как известно, показатели делятся на два типа - интервальные (значение за некоторый период времени) и моментные (значение на определенную дату). Например, доход за месяц -интервальная величина, а количество телефонных аппаратов на некоторую дату - моментная. Интервальная величина может иметь значение за различные периоды времени (за месяц, квартал, год и т.п.; с начала года). Кроме того, некоторые показатели могут иметь и интервальное, и моментное значение. Скажем, количество установленных телефонных аппаратов может иметь интервальное значение за месяц и моментное значение на некоторую дату. Для того, чтобы не приходилось каждый раз заводить отдельные показатель, предлагается создать сущность "Тип показателя", который и будет являться кодификатором "интервальности/моментности" показателей.
Конкретные показатели (например, "Доход от предоставления услуг междугородной связи") определяются сущностью "Конкретный показатель" (таблицы ITEM), которая состоит из ссылок на идентификаторы сущностей "Определение показателей" и "Тип показателей". Именно объекты, определяемые этой сущностью, используются при проведении информационно-аналитической работы.
Сущность "Параметры детализации (измерения)" содержат данные, характеризующие возможные способы разбиения - срезов - тех или иных показателей. Сюда могут включаться такие срезы, как "Подразделения предприятия" (с дочерними значениями "Филиал 1", "Филиал 2" и т.д.), "Категория клиента" (со значениями "Население", "Бюджетные предприятия", "Хозрасчетные предприятия"), "Тип предоставляемой услуги" (со значениями "Традиционные", "Новые", которые в свою очередь также детализируются - см. табл.1). Атрибут "Тип суммируемости" показывает, можно ли суммировать значения, детализированные по данному срезу (детализация может быть не полной, а справочной, например). Атрибуты "Начало действия" и "Конец действия" задают период, в котором актуально данное разбиение (например, детализация по какой-то группе услуг, предоставляемых лишь в определенный период). Атрибут "Порядковый номер" полезен при сортировке срезов в порядке, отличном от естественного.
Таблица 1. Пример иерархии типов услуг в таблице "PARAM"
PARID P_PARID NAME
1 Услуги электросвязи
2 1 Традиционные услуги
3 2 Документальная электросвязь
4 2 Услуги местной связи
5 2 Услуги междугородной связи
6 2 Услуги международной связи
7 1 Новые услуги
8 7 Предоставление доступа к Интернет
9 7 1Р-телефония
Сущность "Строки параметров (точки)" содержит возможные комбинации имеющихся в сущности "Параметры детализации (измерения)" параметров. Данная сущность имеет цель снизить объем используемого пространства для хранения данных, так как при хранении конкретных значений показателей позволяет хранить не пять, а одно числовое поле. Приведенное на диаграмме количество связей с сущностью "Параметры детализации (измерения)", равное пяти, является в данном случае условным - при необходимости это количество может быть расширено путем добавления новой связи (добавление поля в таблице не вызывает потери имеющихся в ней данных). Конечно, таблица РЛБАМБЕТ будет содержать большое количество комбинаций (декартово произведение значений соответствующих параметров), но все равно их придется где-нибудь перечислять. Лучше сделать это один раз. Это, к тому же, повысит и управляемость системы.
Сущность "Возможные измерения" рассчитана на хранение данных о возможных комбинациях параметров детализации для показателей. Эта сущность полезна при составлении и контроле различных отчетных форм.
Дата отчета также является одним из измерений, но очень специфичным, что требует выделения в отдельную сущность "Отчетный период". Специфичность состоит в том, что количество делений этого измерения постоянно вырастает (с появлением каждого нового отчетного периода), а также в необходимости наличия дополнительных атрибутов. Обычно под отчетным периодом понимается месяц, однако, возможны различные варианты, для отражения чего и включены атрибуты "Начало периода" и "Конец периода". Атрибут "Открыт для заполнения" предназначен для контроля защиты информации. Значения показателей, относящихся к закрытому для заполнения периоду, не могут быть изменены в системе.
Наконец, сущность "Данные" содержит информацию о фактах - значениях показателей в срезе по каждому необходимому измерению. В данную сущность введен дополнительный атрибут "Дата актуальности", что позволяет при необходимости иметь информацию о значениях определенных показателей в более детальном плане, чем по отчетному периоду (например, за каждую неделю).
Аспекты работы с физической моделью хранилища
При создании ХД в рамках конкретной СУБД следует учесть специфику содержащейся информации. Наибольшего внимания требует таблица с фактами VAL, на которую приходится основная нагрузка при работе. Использование таблицы для хранения комбинации параметров, как отмечалось выше, позволяет существенно снизить объем занимаемого дискового пространства, но таблица все равно остается громадной. Ориентируясь на Oracle как наиболее распространенную СУБД, следует отметить, что в ней существует механизм, позволяющей достаточно эффективно организовать работу с данной таблицей. Этот механизм, появившийся с 8-й версии Oracle, но реально работающий с Oracle8i, называется секционированием или партиционированием (от англ. partitioning). В этом случае таблица VAL разбивается на секции по ключу REPDAT следующим образом: CREATE TABLE val (
defid NUMBER NOT NULL,
) PARTITION BY RANGE (repdat) PARTITION val200001 VALUES LESS THAN (TO
TABLESPACE tb200001, PARTITION val200001 VALUES LESS THAN (TO TABLESPACE tb200002
PARTITION val200310 VALUES LESS THAN (TO_DATE('01.11.2003','dd.mm.yyyy')) TABLESPACE tb200310;
Для увеличения эффективности доступа к этой таблице индекс лучше создать локальным (с модификатором LOCAL), что позволит без ощутимой потери производительности осуществлять доступ к данным и обеспечивать эффективную архивацию данных (для чего каждая секция размещается в отдельном табличном пространстве).
В связи с важностью данных для повышения надежности и контроля ошибок содержащихся в таблице значений показателей можно рекомендовать написание триггера на операции вставки (INSERT), обновления (UPDATE) и удаления (DELETE) данных из этой таблице, в котором:
- запретить модификацию данных, относящихся к закрытому периоду;
- запретить возможность прямого изменения данных в таблицах, разрешив производить эти действия только процедурам определенных пакетов.
Следует с особой тщательностью подойти к вопросу создания индексов. Также необходимо решить вопрос о целесообразности хранения агрегированных значений
DATE('01.02.2000','dd.mm.yyyy')) DATE('01.03.2000','dd.mm.yyyy'))
показателей или их оперативного вычисления, что следует рассматривать в контексте имеющихся возможностей аппаратной части ХД.
Заключение
В настоящее время структура ХД еще находится в стадии разработки. Представленная в данной работе структура не является окончательной - необходимость ее изменения может появиться даже на этапе разработки клиентских приложений для работы и анализа данных. Тем не менее, как думается, представленная в работе структура может претендовать на звание универсальной структуры хранилища данных.
Список литературы
1. Корнеев В.В., Гарев А.Ф., Васютин С.В., Райх В.В. Базы данных. Интеллектуальная обработка информации.- М.: "Нолидж", 2000.- 352с.
2. Хрусталев Е.М. Агрегация данных в OLAP-кубах.- http://www.olap.ru/home/mut.asp
