Компьютерные технологии интеллектуальной поддержки управленческих решений
Оперативная аналитическая обработка информации (OLAP)
1. Общие принципы построения и обработки многомерных массивов данных.
2. Средства On-Line Analytical Processing (оперативной аналитической обработки, OLAP) в MS Office.
1. Общие принципы построения и обработки многомерных массивов данных
Сегодня все большее внимание уделяется средствам реализации и концепциям построения информационных систем, ориентированных на аналитическую обработку данных. И в первую очередь это касается систем управления базами данных, основанными на многомерном подходе – МСУБД.
Следует заметить, что МСУБД не являются изобретением девяностых годов, а сам многомерный подход возник практически одновременно и параллельно с реляционным. Однако, только начиная с середины девяностых годов интерес к МСУБД начал приобретать всеобщий характер.
Многомерная модель данных
"Многомерный взгляд на данные наиболее характерен для пользователя, занимающегося анализом данных" – это утверждение является исходной посылкой в разработке систем аналитической обработки данных. Однако, что такое многомерное представление, откуда появляется многомерность в трехмерном мире, чем оно отличается и чем оно лучше ставшего уже привычным реляционного представления? Именно эти вопросы возникают практически у любого, впервые прочитавшего это утверждение.
Реляционная модель
Модель |
Месяц |
Объем |
"Жигули" |
Июнь |
12 |
"Жигули" |
Июль |
24 |
"Жигули" |
Август |
5 |
"Москвич" |
Июнь |
2 |
"Москвич" |
Июль |
18 |
"Волга" |
Июль |
19 |
Представим, что имеется не три модели автомобилей, а 30 и не три, а 12 различных месяцев. В случае построчного (реляционного) представления отчет будет составлять 360 строк (30х12), который займет не менее 5-6 страниц.
Достаточно очевидно, что даже при небольших объемах данных отчет, представленный в виде двухмерной таблицы (Модели автомобиля по оси Y и Время по оси X), нагляднее и информативнее отчета с реляционной построчной формой организации.
В случае же многомерного (в нашем случае двухмерного) представления получится компактная таблица 12´30, которая вполне уместится на одной странице и которую, даже при таком объеме данных, можно реально оценивать и анализировать.
Многомерная модель
|
Июнь |
Июль |
Август |
"Жигули" |
12 |
24 |
5 |
"Москвич" |
2 |
18 |
No |
"Волга" |
No |
19 |
No |
Когда говорится о многомерной организации данных, вовсе не подразумевается то, что данные представляются конечному пользователю (визуализируются) в виде четырех или пятимерных гиперкубов. Это невозможно, да и пользователю более привычно и комфортно иметь дело с двухмерным табличным представлением и двухмерной бизнес-графикой.
Закономерен вопрос: "Где же здесь многомерность, откуда она берется и куда исчезает?" Ответ прост. Когда говорится о многомерности, имеется в виду не многомерность визуализации, а многомерное представление при описании структур данных и поддержка многомерности в языках манипулирования данными.
Многомерное представление при описании структур данных
Основными понятиями, с которыми оперирует пользователь и проектировщик в многомерной модели данных, являются:
измерение (Dimension);
ячейка (Cell).
Иногда вместо термина "Ячейка" используется термин "Показатель" (Measure).
Измерение – это множество однотипных данных, образующих одну из граней гиперкуба. Например, Дни, Месяцы, Кварталы, Годы – это наиболее часто используемые в анализе временные Измерения. Примерами географических измерений являются: Города, Районы, Регионы, Страны и т.д.
В многомерной модели данных Измерения играют роль индексов, используемых для идентификации конкретных значений (Показателей), находящихся в Ячейках гиперкуба.
В свою очередь, Показатель – это поле (обычно цифровое), значения которого однозначно определяются фиксированным набором Измерений. В зависимости от того, как формируются его значения, Показатель может быть определен, как:
переменная (Variable) – значения таких Показателей один раз вводятся из какого-либо внешнего источника или формируются программно и затем в явном виде хранятся в многомерной базе данных (МБД);
формула (Formula) - значения таких Показателей вычисляются по некоторой заранее специфицированной формуле. То есть для Показателя, имеющего тип Формула, в БД хранится не его значения, а формула, по которой эти значения могут быть вычислены.
Причем это различие существует только на этапе проектирования и полностью скрыто от конечных пользователей.
В рассмотренном примере каждое значение поля Объем продаж однозначно определяется комбинацией полей:
Модель автомобиля;
Месяц продаж.
Но в реальной ситуации для однозначной идентификации значения Показателя, скорее всего, потребуется большее число измерений, например:
Модель автомобиля;
Менеджер;
Время (например, Год).
Измерения:
Время (Год) - 1994, 1995, 1996
Менеджер - Петров, Смирнов, Яковлев
Показатель:
Объем Продаж
И в терминах многомерной модели речь будет идти уже не о двухмерной таблице, а о трехмерном гиперкубе:
первое Измерение – Модель автомобиля;
второе Измерение – Менеджер, продавший автомобиль;
третье Измерение – Время (Год), – на пересечении граней которого находятся значения Показателя Объем продаж.
При этом в отличие от Измерений, не все значения Показателей должны иметь и имеют реальные значения. Например, Менеджер Петров в 1994 г. мог еще не работать в фирме, и в этом случае все значения Показателя Объем продаж за этот год будут иметь неопределенные значения.
Гиперкубические и поликубические модели данных
В различных МСУБД используются два основных варианта организации данных:
гиперкубическая модель;
поликубическая модель.
Системы, поддерживающие Поликубическую модель (например, Oracle Express Server), предполагают, что в МБД может быть определено несколько гиперкубов с различной размерностью и с различными Измерениями в качестве их граней. Например, значение Показателя Рабочее Время Менеджера, скорее всего, не зависит от Измерения Модель Автомобиля и однозначно определяется двумя Измерениями: День и Менеджер. В Поликубической модели в этом случае может быть объявлено два различных гиперкуба:
двухмерный – для Показателя Рабочее Время Менеджера;
трехмерный – для Показателя Объем Продаж.
В случае же Гиперкубической модели предполагается, что все Показатели должны определяться одним и тем же набором Измерений. То есть только из-за того, что Объем Продаж определяется тремя Измерениями, при описании Показателя Рабочее Время Менеджера придется также использовать три Измерения и вводить избыточное для этого Показателя Измерение Модель Автомобиля.
Операции манипулирования Измерениями
Формирование "Среза". Пользователя редко интересуют все потенциально возможные комбинации значений Измерений. Более того, он практически никогда не работает одновременно сразу со всем гиперкубом данных. Подмножество гиперкуба, получившееся в результате фиксации значения одного или более Измерений, называется Срезом (Slice). Например, если ограничить значение Измерения Модель Автомобиля = "ВАЗ2108", то получится подмножество гиперкуба (в данном случае – двухмерную таблицу), содержащее информацию об истории продаж этой модели различными менеджерами в различные годы.
Операция "Вращение". Изменение порядка представления (визуализации) Измерений (обычно применяется при двухмерном представлении данных) называется Вращением (Rotate). Эта операция обеспечивает возможность визуализации данных в форме, наиболее комфортной для их восприятия. Например, если менеджер первоначально вывел отчет, в котором Модели автомобилей были перечислены по оси X, а Менеджеры по оси Y, он может решить, что такое представление мало наглядно, и поменять местами координаты (выполнить Вращение на 90 градусов).
Отношения и Иерархические Отношения. В нашем примере значения Показателей определяются только тремя измерениями. На самом деле их может быть гораздо больше и между их значениями обычно существуют множество различных Отношений (Relation) типа "один ко многим".
Операция Агрегации. С точки зрения пользователя, Подразделение, Регион, Фирма, Страна являются точно такими же Измерениями, как и Менеджер. Но каждое из них соответствует новому, более высокому уровню агрегации значений Показателя Объем продаж. В процессе анализа пользователь не только работает с различными Срезами данных и выполняет их Вращение, но и переходит от детализированных данных к агрегированным, т.е. производит операцию Агрегации (Drill Up). Например, посмотрев, насколько успешно в 1995 г. Петров продавал модели "Жигули" и "Волга", управляющий может захотеть узнать, как выглядит соотношение продаж этих моделей на уровне Подразделения, где Петров работает. А затем получить аналогичную справку по Региону или Фирме.
Операция Детализации. Переход от более агрегированных к более детализированным данным называется операцией Детализации (Drill Down). Например, начав анализ на уровне Региона, пользователь может захотеть получить более точную информацию о работе конкретного Подразделения или Менеджера.
Таким образом, основное назначение МСУБД – реализация систем, ориентированных на динамический, многомерный анализ исторических и текущих данных, анализ тенденций, моделирование и прогнозирование будущего. Причем такие системы в большой степени ориентированы на обработку произвольных, заранее не регламентированных запросов.
МСУБД является основной частью информационно-аналитической системы (ИАС), структура которой показана на рис. 1. основу такой системы составляет хранилище данных.
Рис. 1. Структура корпоративной информационно-аналитической
системы
Одно из центральных мест в ИАС занимает система оперативной аналитической обработки данных (OLAP).
2. Средства OLAP в MS Office
В основе концепции OLAP (рис. 2) лежит принцип многомерного представления данных, т.к. одним из недостатков реляционной модели является невозможность объединять, просматривать и анализировать данные с точки зрения множественности измерений, то есть самым понятным для корпоративных аналитиков способом.
Рис. 2 Архитектура системы многомерного интеллектуального
анализа данных
Многомерное концептуальное представление (multi-dimensional conceptual view) представляет собой множественную перспективу, состоящую из нескольких независимых измерений, вдоль которых могут быть проанализированы определенные совокупности данных. Одновременный анализ по нескольким измерениям определяется как многомерный анализ. Каждое измерение включает направления консолидации данных, состоящие из серии последовательных уровней обобщения, где каждый вышестоящий уровень соответствует большей степени агрегации данных по соответствующему измерению. Так, измерение Исполнитель (рис. 3) может определяться направлением консолидации, состоящим из уровней обобщения "предприятие - подразделение - отдел - служащий". Измерение Время может даже включать два направления консолидации - "год - квартал - месяц - день" и "неделя - день", поскольку счет времени по месяцам и по неделям несовместим. В этом случае становится возможным произвольный выбор желаемого уровня детализации информации по каждому из измерений. Операция спуска (drilling down) соответствует движению от высших ступеней консолидации к низшим; напротив, операция подъема (rolling up) означает движение от низших уровней к высшим.
Аналитику нужно много данных, эти данные являются выборочными, а также носят характер "набор атрибутов « число". Последнее означает, что аналитик работает с таблицами следующего типа (рис. 4).
Рис. 3 Измерения и направления консолидации данных
Здесь "Страна", "Товар", "Год" являются атрибутами, а "Объем продаж" – тем самым числовым значением. Данную таблицу, как можно заметить, легко можно перевести в три измерения: по одной из осей отложить страны, по другой – товары, по третьей – годы. А значениями в этом трехмерном массиве будут соответствующие объемы продаж (рис. 5).
страна |
товар |
год |
объем продаж |
Аргентина |
бытовая электроника |
2000 |
142 |
Аргентина |
бытовая электроника |
2001 |
168 |
Аргентина |
бытовая электроника |
2002 |
174 |
Аргентина |
метизы |
2001 |
13 |
Аргентина |
метизы |
2002 |
14 |
Бразилия |
бытовая электроника |
2000 |
134 |
Бразилия |
бытовая электроника |
2001 |
345 |
Бразилия |
бытовая электроника |
2002 |
454 |
Бразилия |
метизы |
2000 |
23 |
Бразилия |
метизы |
2001 |
53 |
Бразилия |
метизы |
2002 |
65 |
Венесуэла |
бытовая электроника |
2000 |
546 |
Венесуэла |
бытовая электроника |
2001 |
675 |
Венесуэла |
бытовая электроника |
2002 |
543 |
Венесуэла |
метизы |
2000 |
23 |
Венесуэла |
метизы |
2001 |
45 |
Венесуэла |
метизы |
2002 |
56 |
Рис. 4 Реляционное представление отчета о продажах
Представленный на рисунке 5 трехмерный массив в терминах OLAP называется кубом. На самом деле, с точки зрения строгой математики кубом такой массив будет далеко не всегда: у настоящего куба количество элементов во всех измерениях должно быть одинаковым, а у кубов OLAP такого ограничения нет. Тем не менее, несмотря на эти детали, термин "кубы OLAP" ввиду своей краткости и образности стал общепринятым. Куб OLAP совсем не обязательно должен быть трехмерным. Он может быть и двух-, и многомерным – в зависимости от решаемой задачи.
Рис. 5 Куб OLAP
Терминология кубов OLAP приведена в табл. 1.
Таблица 1.
Термин |
Описание |
Ось |
Координата гиперкуба |
Измерение |
Свойство данных, которое откладывается по оси |
Уровень |
(иерархическое)подмножество измерения |
Член |
Элемент данных в измерении |
Мера |
Исходные данные для куба |
Слой |
Измерение или мера, являющиеся постоянными в пределах отображения |
Исходные данные для OLAP-куба могут храниться либо в реляционных, либо в многомерных структурах. В связи с этим в настоящее время применяются три способа хранения данных:
MOLAP (Multidimensional OLAP) – исходные и агрегатные данные хранятся в многомерной базе данных. Хранение данных в многомерных структурах позволяет манипулировать данными как многомерным массивом, благодаря чему скорость вычисления агрегатных значений одинакова для любого из измерений. Однако в этом случае многомерная база данных оказывается избыточной, так как многомерные данные полностью содержат исходные реляционные данные.
ROLAP (Relational OLAP) – исходные данные остаются в той же реляционной базе данных, где они изначально и находились. Агрегатные же данные помещают в специально созданные для их хранения служебные таблицы в той же базе данных.
HOLAP (Hybrid OLAP) – исходные данные остаются в той же реляционной базе данных, где они изначально находились, а агрегатные данные хранятся в многомерной базе данных.
В сущности, вопрос заключается в следующем: что позволит достичь максимальной производительности – внедрение средств OLAP в реляционные СУБД, использование специализированных автономных средств или и то и другое одновременно?
Для ROLAP (реляционной OLAP) могут использоваться СУБД с расширенными возможностями, включающими предварительную обработку определенных запросов и некоторые другие функции, которые могут служить вполне адекватными средствами хранения данных OLAP. В MOLAP (многомерной OLAP) применяются СУБД обработки транзакций, выполнения запросов и создания отчетов. При этом требования OLAP к обработке данных настолько специфичны, что ни одна СУБД не может достичь в этой сфере приемлемой производительности. В HOLAP (гибридной OLAP) СУБД и специализированные OLAP-процессоры успешно используются для достижения обоих целей, и каждая из составляющих имеет свою роль.
Microsoft использует эти термины более узко в контексте средств оперативной аналитической обработки СУБД SQL Server. Для Microsoft термин ROLAP означает, что хранение исходных данных и заранее вычисленных агрегаций данных происходит в базе данных SQL Server. При MOLAP данные, структура куба и заранее вычисленные агрегации данных хранятся в специализированной многомерной структуре данных. При HOLAP данные остаются в реляционной базе данных, но агрегации данных хранятся в многомерной структуре.
MOLAP дает максимальную производительность, но и требует больше всего памяти. ROLAP требует меньше памяти, но работает медленней. ROLAP предназначена для больших баз данных, информация из которых запрашивается редко. В способе HOLAP высокоуровневая обработка дает большую производительность, но при исследовании мелких деталей быстродействие снижается.
В специализированных СУБД, основанных на многомерном представлении данных, данные организованы не в форме реляционных таблиц, а в виде упорядоченных многомерных массивов: гиперкубов (все хранимые в БД ячейки должны иметь одинаковую мерность, то есть находиться в максимально полном базисе измерений), или поликубов (каждая переменная хранится с собственным набором измерений, и все связанные с этим сложности обработки перекладываются на внутренние механизмы системы).
Использование многомерных СУБД оправдано при следующих условиях:
объем исходных данных для анализа не слишком велик (не более нескольких гигабайт), то есть уровень агрегации данных достаточно высок;
набор информационных измерений стабилен (поскольку любое изменение в их структуре почти всегда требует полной перестройки гиперкуба);
время ответа системы на нерегламентированные запросы является наиболее критичным параметром;
требуется широкое использование сложных встроенных функций для выполнения кроссмерных вычислений над ячейками гиперкуба, в том числе возможность написания пользовательских функций.
Структурно OLAP-системы строятся по двум схемам: «звезда» и «снежинка»
Идея схемы «звезда» заключается в том, что имеются таблицы для каждого измерения, а все факты помещаются в одну таблицу, индексируемую множественным ключом, составленным из ключей отдельных измерений (рис. 6). Каждый луч схемы звезды задает направление консолидации данных по соответствующему измерению.
Рис. 6. Пример схемы «звезды»
В сложных задачах с многоуровневыми измерениями используется схема снежинки (snowflake schema) (рис.7).
В этом случае отдельные таблицы фактов создаются для возможных сочетаний уровней обобщения различных измерений. Это позволяет добиться лучшей производительности, но часто приводит к избыточности данных и к значительным усложнениям в структуре базы данных, в которой оказывается огромное количество таблиц фактов.
Рис. 7. Пример схемы "снежинки" (фрагмент для одного измерения)
Создание кубов OLAP
Средства MS Excel позволяют создавать сводные отчеты по различным источникам данных. Процедура создания такого отчета предполагает выполнение следующих операций.
1. Выбрать источник данных (базы данных).
2. Выбор таблицы или запроса, выступающего в качестве источника информации.
3. Создание OLAP-куба.
4. Проектирование макета куба и выбор места размещение куба на листе MS Excel.
5. Сохранение результатов для последующего анализа.