Н.А.Боярский, А.В.Шовкун

Статья посвящена практическим вопросам создания аналитической части корпоративной информационно-аналитической системы. Рассматриваются возможности Oracle OLAP Option для создания многомерных витрин данных и Oracle BI Beans для создания аналитических приложений, предоставляющих доступ к данным в OLAP Option. Подробно рассматривается пример создания аналитического приложения, в котором проектирование витрины данных ведется средствами Корпоративного Каталога показателей компании ЛАНИТ на основе Oracle OLAP Option.

Введение 

Типовая архитектура корпоративной информационно-аналитической системы (КИАС) включает в себя: буферную область (оперативный склад данных - ОСД), центральное хранилище данных (ЦХД), витрины данных (ВД) и аналитические приложения. Клиентское аналитическое приложение представляет собой набор инструментов, предназначенных для извлечения, анализа и представления информации из хранилища данных для нужд специалистов предприятия. Стандартом де-факто стало использование OLAP инструментов для анализа данных в КИАС. Аналитическая обработка информации (On-Line Analytic Processing) - деятельность, связанная с построением запросов к хранилищу данных и визуализацией текстовых и числовых данных, при которой используются многомерные модели данных. 

В качестве решения для построения многомерных хранилищ данных, витрин данных и аналитических (OLAP) приложений компания Oracle предлагает сервер многомерных баз данных "Oracle Database with OLAP Option" и интегрированные с ним аналитические компоненты BI Beans. OLAP Option является компонентом СУБД Oracle Database и поддерживает как реляционные (ROLAP), так и многомерные (MOLAP) структуры для хранения многомерных данных. Oracle BI Beans является набором Java-компонент для создания аналитических приложений в среде JDeveloper. Компоненты BI Beans используют метаданные из каталога OLAP Option для создания аналитических отчетов, графиков, а также для выполнения операций свертки, детализации и других стандартных OLAP операций. 

Архитектура и возможности OLAP Option и BI Beans 

Архитектура OLAP Option представлена на Рис. 1. Центральным звеном OLAP Option является OLAP Catalog (Каталог), который содержит описание многомерной модели витрины данных в терминах "измерение", "показатель", "куб". Физически OLAP каталог представляет собой набор таблиц схемы OLAPSYS, в которых хранятся все метаданные.

Рис. 1. Архитектура Oracle OLAP Option 

Метаданные каталога разделены на три части в зависимости от функциональных возможностей и используемых исходных данных: 

• CWM1 (ROLAP) - предоставляет возможность создания метаданных для простых многомерных моделей: 

  • Измерения могут иметь альтернативные иерархии, но у всех иерархий должен быть один общий нижний уровень. 

  • В кубе могут храниться только детальные данные, т.е. связанные с нижними уровнями измерений. 

  • Данные могут храниться только в одной таблице фактов. 

  • Идентификаторы уровней не должны содержать пустые значения (NULL). 

  • Все иерархии должны быть уровневыми. Иерархии вида "Parent-Child" не поддерживаются.

• CWM2 (ROLAP) - предоставляет возможность создания более сложных метаданных: 

  • Измерения могут иметь альтернативные иерархии, нижние уровни которых могут быть любыми. 

  • В кубе могут храниться и детальные и агрегированные данные. 

  • Данные могут храниться в нескольких таблицах фактов. Отдельная таблица фактов может быть определена для каждой комбинации альтернативных иерархий измерений. 

  • Идентификаторы уровней не должны содержать пустые значения (NULL).

  • Допускаются как уровневые, так и Parent-Child иерархии. Но Oracle Java OLAP API, который используется для доступа к данным через метаданные, описанные в Каталоге, не поддерживает работу с Parent-Child иерархиями.

• AW (MOLAP) - предоставляет возможность создания метаданных для многомерных кубов, содержащихся в Analytic Workspace (Аналитическом пространстве). Модуль Analytic Workspace полностью поддерживает функционал своего предшественника Express server, добавив к богатым "многомерным" возможностям стандартные возможности Oracle Database (управление безопасностью, масштабируемость, кластеризация и т.д.).

В таблице 1 приведены возможности по описанию витрин данных, предоставляемые различными механизмами OLAP Option. 

Возможности моделей метаданных	CWM1	CWM2	AW
Способ хранения данных	ROLAP		MOLAP
Альтернативные иерархии (с общим нижним уровнем)	Да	Да	Да
Альтернативные иерархии (с различными нижними уровнями)	Нет	Да	Да
Хранение фактов для промежуточных уровней иерархий	Нет	Да	Да
Количество таблиц фактов в кубе	1	>=1	Не применимо
Поддержка "Parent/Child" иерархий	Нет	Да	Да
Автоматический подсчет агрегатов	Нет	Да	Да
Графический интерфейс	OEM	Нет	AWM, OWB
API для описания витрины данных	PL/SQL	PL/SQL	DML
API для доступа к данным витрины	Java OLAP API

Таблица 1. Сравнение возможностей моделей метаданных OLAP Option

Из приведенного сравнения видно, что наибольший интерес для создания ROLAP витрин данных представляет CWM2, однако описание метаданных для этого механизма возможно только с помощью скриптов PL/SQL, т.к. компания Oracle не предлагает для этой задачи какого-либо инструмента с графическим интерфейсом. 

В настоящее время существует решение, которое позволяет создавать скрипты PL/SQL для описания метаданных CWM2 с использованием графического интерфейса пользователя. Этим решением является программный продукт "Корпоративный каталог показателей" компании ЛАНИТ. 

Процесс создания витрины данных и аналитического приложения для доступа к ней состоит из этапов, представленных в Таблице 2. 

Таблица 2. Процесс создания витрины данных в OLAP Option

 Рассмотрим более подробно процесс создания витрины данных и аналитического приложения с использованием Корпоративного каталога показателей, Oracle OLAP Option (метаданные CWM2) и BI Beans. Для этого мы пройдем полный цикл создания аналитического приложения для вымышленной торгово-производственной компании "BEER". 

Описание примера 

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

В центральном офисе продаж осуществляется планирование продаж продукции по регионам и региональным подразделениям. Утвержденный план доводится до региональных центров продаж. В настоящее время в региональных центрах собирается и накапливается информация о фактах продаж. Расчет себестоимости продукции выполняется в производственном подразделении компании. 

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

Особенностями рассматриваемой предметной области является то, что продажи продукции производятся как поштучно, так и мелким оптом (ящиками по 20 бутылок, упаковками по 6 банок и т.п.). А данные по региональным центрам продаж могут группироваться для анализа различными способами: в соответствии с территориально-административным делением (по федеральным округам) и по экономическим районам России.

Описание метаданных (проектирование системы) 

Создаем многомерную витрину данных с хранением детализированных данных в реляционной структуре (ROLAP). Как отмечалось выше, для этого необходимо:

• Определить многомерную модель данных (измерения, показатели и кубы); 

• Спроектировать реляционные таблицы для хранения элементов измерений и значений показателей (таблицы измерений и таблицы фактов); 

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

Для выполнения этих действий будет использован программный продукт компании ЛАНИТ "Корпоративный каталог показателей", который предоставляет графический интерфейс для управления метаданными, используемыми для создания хранилищ и витрин данных, а также аналитических приложений. Основные возможности Каталога показателей: 

• Графический интерфейс для описания многомерной модели данных (показатель, измерение, куб). 

• Описание реляционных структур хранения данных, генерация баз данных, реинжиниринг баз данных. 

• Описание связей между многомерной моделью данных и реляционной структурой хранения данных. 

• В Каталоге показателей также реализованы механизмы контроля версий метаданных, анализ зависимостей, поиск и формирование отчетов по метаданным.

В рассматриваемом примере мы используем Каталог показателей для описания многомерной модели данных, проектирования соответствующих реляционных таблиц и для экспорта метаданных в каталог OLAP Option. 

Описание многомерной модели данных обычно начинается с определения измерений, на которых определены показатели. В процессе описания предметной области мы выделяем четыре измерения (в скобках указаны способы реализации измерений): 

Время (Звезда)	Год Квартал Месяц День
Упаковки (Снежинка)	Иерархия "Упаковка до элемента" Все упаковки  Тип тары  Группа упаковок по количеству  Упаковки по литражу Иерархия "Упаковка до группы" Все упаковки Тип тары  Группа упаковок по количеству
Продукция (Звезда)	Вся продукция  Тип продукта  Продукт
Центры продаж (Снежинка)	Иерархия "По федеральным округам" Страна  Федеральный округ  Город Иерархия "По экономическим районам" Страна  Экономический район  Город
Проверенные данные (Вырожденное)	Признак достоверности (элементы измерения: "Проверено", "Не проверено")

Также выделяется шесть показателей, которые объединены в один многомерный куб: 

• Сумма продаж, 

• Количество продаж, 

• Уникальные единицы, 

• Планируемая сумма продаж, 

• Себестоимость, 

• Цена.

Рассмотрим подробнее описание измерения Центры продаж в Каталоге показателей. На Рис. 2 представлено описание уровней, атрибутов и иерархий этого измерения. Для каждого элемента можно привести подробное текстовое описание, поясняющее семантику измерения. 

Рис. 2. Описание уровней, атрибутов и иерархий измерения 

На Рис. 3 представлено общее описание показателя "Сумма продаж", а на Рис. 4 изображена зависимость показателя от измерений. 

Рис. 3. Описание показателя. Общие свойства. 

Рис. 4. Описание показателя. Зависимость от измерений. 

На Рис. 5 представлено описание куба "Данные по продажам". В куб включены все показатели и измерения из нашего примера. Также при описании характеристик куба имеется возможность задать параметры агрегации показателей куба по каждому из измерений. 

Рис. 5. Описание куба. Показатели.

Рис. 6. Описание куба. Измерения. 

Рис. 7. Описание куба. Агрегация. 

После завершения описания многомерной модели мы можем приступить к проектированию модели данных для нашего примера (см. Рис. 8). Для проектирования реляционных моделей данных используется Oracle Designer, интегрированный с Каталогом показателей. Следует отметить, что работу по описанию многомерной модели данных может выполнять бизнес-аналитик или консультант. Проектирование реляционной модели данных выполняется ИТ специалистом. Oracle OLAP Option позволяет использовать для реализации измерений следующие способы реализации: 

• "Звезда" - все уровни измерения хранятся в одной таблице, 

• "Снежинка" - каждый уровень измерения может храниться в отдельной таблице, 

• "Вырожденное" - элементы единственного уровня измерения хранятся в таблице фактов.

Рис. 8. Модель данных (Oracle Designer). 

Как видно из рисунка, для измерения Центры продаж мы используем три таблицы: 

• таблицу DIM_BEER_ORG_SC для уровня "Центр продаж", 

• таблицу DIM_BEER_ORG_FO для уровней "Федеральный округ" и "Страна", 

• таблицу DIM_BEER_ORG_ER для уровня "Экономический район" и "Страна",

причем для иерархии "По федеральным округам" используются таблицы DIM_BEER_ORG_SC и DIM_BEER_ORG_FO, а для иерархии "По экономическим районам" - DIM_BEER_ORG_SC и DIM_BEER_ORG_ER. Такая организация таблиц соответствует схеме "Снежинка". 

В соответствии с постановкой задачи нам необходимо хранить значения показателей (факты) не только по конкретным элементам упаковки (банка, бутылка), но и по группам (ящик, пластиковый контейнер). Это означает, что фактические данные могут быть заданы не только для самого нижнего уровня измерения "Упаковки", но и для более высоких уровней. Это требование является одной из причин, по которой мы не можем использовать CWM1, а должны использовать модель CWM2 OLAP Option. Для решения этой задачи необходимо создать в измерении "Упаковки" две альтернативные иерархии с разными нижними уровнями. При этом разные уровни измерения должны быть размещены в разных таблицах (Снежинка). Таким образом, мы используем для хранения уровней "Все упаковки", "Тип упаковки", "Группа упаковок по количеству" таблицу DIM_PACKAGE_GRP, а для уровня "Упаковки по литражу" - DIM_PACKAGE_ITEM.

После создания описания таблиц в базе данных необходимо произвести загрузку данных. Этот процесс может выполняться как с использованием Oracle Warehouse Builder, так и с использованием пользовательских PL/SQL-скриптов. Важным условием при загрузке данных является поддержание уникальности идентификаторов уровней внутри измерения, т.е. элементы измерения на разных уровнях должны иметь уникальные идентификаторы. Хорошей практикой является использование для идентификаторов суррогатных ключей типа NUMBER. 

После описания многомерной (бизнес) модели и реляционной модели для хранения данных нам необходимо определить соответствия (мэппинги) между элементами двух моделей. Для этого необходимо в Корпоративном каталоге показателей определить мэппинги измерений по схемам "Звезда", "Снежинка", которые используются в Oracle OLAP Option. Помимо этих схем Каталог показателей позволяет описать мэппинги по схемам "Parent-Child" и "Граф", которые не используются напрямую в OLAP Option. Также возможна схема реализации измерения "Вырожденное", которая в действительности соответствует схеме реализации "Звезда", но мэппинг должен производиться не на таблицу элементов измерения, а на таблицу фактов. 

Описание мэппига измерения по схемам "Звезда" и "Снежинка" состоит из следующих этапов: 

1. Выбор таблиц, в которых будут содержаться элементы уровней измерения (см. Рис. 9). 

2. Каждому атрибуту уровня ставится в соответствие столбец в таблице элементов измерения, в котором будут храниться значения этого атрибута (см.Рис. 10). 

3. Для каждого уровня определяются соответствия специальных параметров реализации (в терминах Каталога показателей - роли) столбцам в таблицах, на которые были отмаппированы уровни измерения (см. Рис. 11).

Рис. 9. Таблицы для реализации измерения 

Рис. 10. Мэппинг атрибутов измерения

Рис. 11. Мэппинг ролей измерения 

Для определения мэппинга куба необходимо выбрать таблицу фактов и столбцы, которые являются внешними ключами для идентификаторов измерений (см. Рис. 12). 

Рис. 12. Мэппинг куба 

Работы по созданию бизнес-метаданных могут (и должны) производиться бизнес-специалистами, которые знают предметную область. Работы по созданию структур данных и определению мэппингов должны производиться ИТ-специалистами. 

После определения многомерной (бизнес) модели, реляционной модели данных, связей между ними (меппингов) и загрузки данных в витрину необходимо описать наши метаданные в OLAP Catalog. 

Загрузка метаданных в OLAP Catalog 

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

Для экспорта метаданных к ККП используется мастер, в котором производится доопределение специфических для Oracle OLAP Option параметров, таких как имя пользователя, имеющего роли OLAP_USER и OLAP_DBA, а также название схемы, в которой хранятся данные для витрины. На Рис. 13 изображен один из этапов мастера экспорта, на котором производится определение специальных атрибутов времени, используемых в OLAP Option.

Рис. 13. Экспорт метаданных. Определение атрибутов измерения времени. 

В таблице 3 представлены соответствия метаданных в ККП элементам в Oracle OLAP Catalog. 

Таблица 3. Соответствие метаданных OLAP Option и Каталога показателей 

Созданный в процессе экспорта метаданных скрипт можно запустить в SQL*Plus или в любом другом SQL инструменте (например, TOAD или SQL Navigator).

Листинг скрипта описания метаданных приведен в Приложении 1. 

В результате запуска скрипта описания метаданных из нашего примера будут записаны в OLAP Catalog. Просмотреть получившиеся метаданные можно с помощью представлений (View) в схеме OLAPSYS, либо с помощью Analytic Workspace Manager, либо используя BI Beans.Например, для просмотра описаний измерений и кубов CWM2 из представлений в схеме OLAPSYS можно использовать команды: 

SELECT * from OLAPSYS.ALL$OLAP2_DIMENSIONS, 

SELECT * from OLAPSYS.ALL$OLAP2_CUBES. 

Полные описания всех метаданных CWM2 (уровни, атрибуты, иерархии, показатели, мэппинги) можно просмотреть в представлениях с префиксом ALL$OLAP2_(валидные метаданные) или DBA$OLAP2_ (ошибочные метаданные).

Возможности Oracle OLAP Option 

Oracle OLAP Option обладает богатыми функциональными возможностями по описанию метаданных CWM2. В рассмотренном примере мы использовали только некоторые из них: 

• мэппинг измерений по схеме "Звезда" и "Снежинка"; 

• использование альтернативных иерархий с одинаковым нижним уровнем в "Снежинке"; 

• мэппинг альтернативных иерархий по схеме "Снежинка" если иерархии имеют разные нижние уровни; при этом также появляется возможность связывать нижние уровни разных иерархий с одним кубом; 

• использование измерений без иерархий (вырожденные и одноуровневые измерения); в действительности, даже если измерение в OLAP Option не имеет иерархии, на этапе мэппинга такого измерения создается иерархия с именем "CWM2_OLAP_HIERARCHY_HIDDEN". И именно это имя иерархии необходимо в итоге использовать при описании мэппинга куба.

• использование только иерархий с типом UNSOLVED-LEVEL BASED; этот тип иерархии подразумевает связь с таблицей фактов по нижнему уровню одной из иерархий измерения; 

• мэппинг куба на одну таблицу фактов;

Oracle OLAP Option CWM2 предоставляет также и другие возможности для описания метаданных в витрине данных:

• мэппинг альтернативных иерархий по схеме "Звезда" внутри одной таблицы, если иерархии имеют одинаковый нижний уровень; 

• мэппинг альтернативных иерархий по схеме "Звезда" на несколько таблиц, если иерархии имеют разные нижние уровни; при этом появляется возможность связывать нижние уровни разных иерархий с одним кубом (и одной или несколькими таблицами фактов); 

• использование иерархий SOLVED LEVEL-BASED; этот тип иерархии подразумевает связь с таблицей фактов по всем уровням измерения; для использования этого типа иерархии должны быть специальным образом подготовлены данные в таблицах элементов измерения, а также посчитаны агрегатные данные в таблице фактов; 

• мэппинг куба на несколько таблиц фактов; можно делать мэппинг для каждой комбинации иерархий измерений, включенных в куб;

• использование в качестве функции агрегации не только SUM, но и еще 12 различных функций, например AVERAGE, MAX, MIN, FIRST, AND, OR;

• использование материализованных представления (Materialized View); материализованные представления позволяют существенно увеличить скорость выполнения запросов к таблице фактов с детальными данными; это осуществляется за счет того, что представления содержат агрегатные данные, скрипты для расчета которых формируются автоматизировано из OLAP PL/SQL.

Использование компонент Oracle BI Beans в аналитических приложениях 

Компоненты Oracle Business Intelligence Beans являются Java-компонентами, которые можно использовать при построении собственных аналитических приложений. Получать аналитические отчеты с использованием BI Beans можно также непосредственно из среды JDeveloper. BI Beans являются Java-компонентами, которые в свою очередь используют Oracle OLAP API, написанный также на Java. 

При построении отчетов BI Beans используют метаданные из OLAP Catalog, причем они могут работать со всеми типами метаданных - CWM1, CWM2, AW. OLAP Catalog скрывает от аналитического приложения физические аспекты реализации витрины данных. Для приложения нет разницы, какая модель использована при проектировании витрины данных. Разница существует только в том, насколько сложные многомерные модели данных могут быть описаны и в скорости выполнения аналитических запросов. 

Построение аналитических отчетов в среде JDeveloper с помощью мастеров, поддерживающих технологию быстрой разработки приложений RAD, - это очень простая и интуитивно понятная процедура. Первоначально необходимо выбрать тип отчета (Таблица, Кросс-таблица или График), а также выбрать показатели, которые должны присутствовать в отчете. Далее необходимо выбрать уровни и элементы измерений, от которых зависят показатели. На последнем этапе можно задать параметры форматирования отчетов (подсвечивание данных в зависимости от их значений, изменение шрифтов и т.п.). Наиболее информативным отчетом является Кросс-таблица (см. Рис. 14), в которой имеется возможность просмотра как данных от нижнего уровня к верхнему (Drill Up), так и от верхнего к нижнему (Drill Down). 

Рис. 14. Отчеты BI Beans. Кросс-таблица и график. 

Полученные отчеты можно сохранить как на локальном диске, так и в специальном репозитории (BI Beans Catalog), создаваемом в базе данных Oracle в отдельной схеме. 

Т.к. JDeveloper является довольно "громоздким" инструментов, предназначенным для разработчиков приложений, вызов мастеров построения отчетов BI Beans из его среды является не самым удобным решением задачи анализа. Для бизнес-специалистов лучшим решением является использование Java-приложения, использующее компоненты BI Beans. Компания ЛАНИТ предлагает помимо Корпоративного каталога показателей также и клиентское аналитическое приложение, использующее весь функционал, представленный в BI Beans. Здесь не приводится подробное описание данного продукта, т.к. это выходит за рамки этой статьи. 

Заключение 

В настоящее время компания Oracle предлагает полную линейку продуктов, предназначенных для анализа данных. Для построения хранилищ и витрин данных, а также аналитических систем Oracle предлагает использовать OLAP Option и BI Beans, которые являются комплексным и простым в использовании решением. Тандем из этих продуктов позволяет в сжатые сроки создавать полнофункциональные аналитические системы. Единственным этапом в создании аналитической системы, который из-за трудоемкости и отсутствия поддержки в каких-либо продуктах Oracle замедляет скорость разработки, является создания метаданных CWM2 в OLAP Catalog. 

Компания ЛАНИТ предлагает в качестве дополняющего решения программный продукт "Корпоративный каталог показателей", который позволяет максимально быстро и качественно создавать метаданные CWM2 в OLAP Catalog для их дальнейшего использования в аналитической системе. Вторым решением компании ЛАНИТ для витрин данных является аналитическое Java-приложение, основанное на компонентах BI Beans. 

• Обратиться в "Интерфейс" за дополнительной информацией/по вопросу приобретения продуктов
• Подробнее о продуктах Oracle
• Приобрести продукты Oracle в ITShop.ru
• Курсы обучения по продуктам компании Oracle
• Подписаться на рассылку "Информационные технологии: CASE, RAD, ERP, OLAP"