Главная › Тарифы › Введение в OLAP и многомерные базы данных. Olap в узком смысле слова трактуется как: олап кубы

Введение в OLAP и многомерные базы данных. Olap в узком смысле слова трактуется как: олап кубы

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

Что такое хранилище данных?

Обычно - место сбора всей информации, представляющей аналитическую ценность. Требования для таких хранилищ соответствуют классическому определению OLAP, будут объяснены ниже.

Иногда Хранилище имеет еще одну цель – интеграция всех данных предприятия, для поддержания целостности и актуальности информации в рамках всех информационных систем. Т.о. хранилище накапливает не только аналитическую, а почти всю информацию, и может ее выдавать в виде справочников обратно остальным системам.

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

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

И, в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.

Как строят хранилище?

ETL – базовое понятие: Три этапа:

Извлечение – извлечение данных из внешних источников в понятном формате;
Преобразование – преобразование структуры исходных данных в структуры, удобные для построения аналитической системы;

Добавим еще один этап – очистка данных (Cleaning ) – процесс отсеивания несущественных или исправления ошибочных данных на основании статистических или экспертных методов. Чтобы не формировать потом отчеты типа «Продажи за 20011 год».

Вернемся к анализу.

Что такое анализ и для чего он нужен?

Анализ – исследование данных с целью принятия решений. Аналитические системы так и называют - системы поддержки принятия решений (СППР ).

Здесь стоит указать на отличие работы с СППР от простого набора регламентированных и нерегламентированных отчетов. Анализ в СППР практически всегда интерактивен и итеративен. Т.е. аналитик копается в данных, составляя и корректируя аналитические запросы, и получает отчеты, структура которых заранее может быть неизвестна. Более подробно к этому мы вернемся ниже, когда будем обсуждать язык запросов MDX .

OLAP

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

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP - это ключевой компонент организации традиционных хранилищ данных. Концепция OLAP была описана в 1993 году Эдгаром Коддом , известным исследователем баз данных и автором реляционной модели данных. В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information - быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:

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

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

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

Многомерные понятия

Мы будем использовать для иллюстрации принципов OLAP базу данных Northwind, входящую в комплекты поставки Microsoft SQL Server и представляющую собой типичную базу данных, хранящую сведения о торговых операциях компании, занимающейся оптовыми поставками продовольствия. К таким данным относятся сведения о поставщиках, клиентах, список поставляемых товаров и их категорий, данные о заказах и заказанных товарах, список сотрудников компании.

Куб

Возьмем для примера таблицу Invoices1, которая содержит заказы фирмы. Поля в данной таблице будут следующие:

Дата Заказа
Страна
Город
Название заказчика
Компания-доставщик
Название товара
Количество товара
Сумма заказа

Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:

Какова суммарная стоимость заказов, сделанных клиентами из определенной страны?
Какова суммарная стоимость заказов, сделанных клиентами из определенной страны и доставленных определенной компанией?
Какова суммарная стоимость заказов, сделанных клиентами из определенной страны в заданном году и доставленных определенной компанией?

Все эти данные можно получить из этой таблицы вполне очевидными SQL-запросами с группировкой.

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

Представим себе, что нам надо получить информацию по суммарной стоимости заказов из всех стран и их распределение по компаниям доставщиков – мы получим уже таблицу (матрицу) из чисел, где в заголовках колонок будут перечислены доставщики, в заголовках строк – страны, а в ячейках будет сумма заказов. Это – двумерный массив данных. Такой набор данных называется сводной таблицей (pivot table ) или кросс-таблицей.

Если же нам захочется получить те же данные, но еще в разрезе годов, тогда появится еще одно изменение, т.е. набор данных станет трехмерным (условным тензором 3-го порядка или 3-х мерным «кубом»).

Очевидно, что максимальное количество измерений – это количество всех атрибутов (Дата, Страна, Заказчик и т.д.), описывающих наши агрегируемые данные (сумму заказов, количество товаров и т.п).

Так мы приходим к понятию многомерности и его воплощению – многомерному кубу . Такая таблица будет у нас называться «таблицей фактов ». Измерения или Оси куба (dimensions ) – это атрибуты, координаты которых – выражаются индивидуальными значениями этих атрибутов, присутствующих в таблице фактов. Т.е. например, если информация о заказах велась в системе с 2003 по 2010 год, то эта ось годов будет состоять из 8 соответствующих точек. Если заказы приходят из трех стран, то ось стран будет содержать 3 точки и т.д. Независимо от того, сколько стран заложено в справочнике Стран. Точки на оси называются ее «членами» (Members ).

Сами агрегируемые данные в данном случае буду назваться «мерами» (Measure ). Чтобы избежать путаницы с «измерениями», последние предпочтительней называть «осями». Набор мер образует еще одну ось «Меры» (Measures ). В ней столько членов (точек), сколько мер (агрегируемых столбцов) в таблице фактов.

Члены измерений или осей могут быть объединены одной или несколькими иерархиями (hierarchy ). Что такое иерархия, поясним на примере: города из заказов могут быть объединены в районы, районы в области, области страны, страны в континенты или другие образования. Т.е. налицо иерархическая структура – континент-страна-область-район-город – 5 уровней (Level ). Для района данные агрегируются по всем городам, которые в него входят. Для области по всем районам, которые содержат все города и т.п. Зачем нужно несколько иерархий? Например, по оси с датой заказа мы можем хотеть группировать точки (т.е. дни) по иерархии Год-Месяц-День или по Год-Неделя-День : в обоих случаях по три уровня. Очевидно, что Неделя и Месяц по-разному группируют дни. Бывают также иерархии, количество уровней в которых не детерминировано и зависит от данных. Например, папки на компьютерном диске.

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

MDX

Перейдем к языку запросов в многомерных данных.
Язык SQL изначально был спроектирован не для программистов, а для аналитиков (и поэтому имеет синтаксис, напоминающий естественный язык). Но он со временем все больше усложнялся и теперь мало кто из аналитиков хорошо умеет им пользоваться, если умеет вообще. Он стал инструментом программистов. Язык запросов MDX, разработанный по слухам нашим бывшим соотечественником Мойшей (или Мошей) Посуманским (Mosha Pasumansky) в дебрях корпорации Майкрософт, тоже изначально должен был ориентирован на аналитиков, но его концепции и синтаксис (который отдаленно напоминает SQL, причем совершенно зря, т.к. это только путает), еще сложнее чем SQL. Тем не менее его основы все же понять несложно.

Мы рассмотрим его подробно потому что это единственный язык, который получил статус стандартного в рамках общего стандарта протокола XMLA , а во вторых потому что существует его open-source реализация в виде проекта Mondrian от компании Pentaho . Другие системы OLAP-анализа (например, Oracle OLAP Option) обычно используют свои расширения синтаксиса языка SQL, впрочем, декларируют поддержку и MDX.

Работа с аналитическими массивами данных подразумевает только их чтение и не подразумевает запись. Т.о. в языке MDX нет предложений для изменения данных, а есть только одно предложение выборки - select.

В OLAP из многомерных кубов можно делать срезы – т.е. когда данные фильтруются по одной или нескольким осям, или проекции – когда по одному или нескольким осям куб «схлопывается», агрегируя данные. Например, наш первый пример с суммой заказов из стран – есть проекция куба на ось Страны. MDX запрос для этого случая будет выглядеть следующим образом:

Select ...Children on rows from
Что здесь что?

Select – ключевое слово и в синтаксис входит исключительно для красоты.
– это название оси. Все имена собственные в MDX пишутся в квадратных скобках.
– это название иерархии. В нашем случае – это иерархия Страна-Город
– это название члена оси на первом уровне иерархии (т.е. страны) All – это мета-член, объединяющий все члены оси. Такой мета-член есть в каждой оси. Например в оси годов есть «Все года» и т.п.
Children – это функция члена. У каждого члена есть несколько доступных функций. Таких как Parent. Level, Hierarchy, возвращающие соответственно предка, уровень в иерархии и саму иерархию, к которой относится в данном случае член. Children – возвращает набор членов-потомков данного члена. Т.е. в нашем случае – страны.
on rows – Указывает как расположить эти данные в итоговой таблице. В данном случае – в заголовке строк. Возможные значении здесь: on columns, on pages, on paragraphs и т.п. Возможно так же указание просто по индексам, начиная с 0.
from – это указание куба, из которого производится выборка.

Что если нам не нужны все страны, а нужно только пара конкретных? Для этого можно в запросе указать явно те страны которые нам нужны, а не выбирать все функцией Children.

Select { ..., ... } on rows from
Фигурные скобки в данном случае – обявление набора (Set ). Набор – это список, перечисление членов из одной оси .

Теперь напишем запрос для нашего второго примера – вывод в разрезе доставщика:

Select ...Children on rows .Members on columns from
Здесь добавилось:
– ось;
.Members – функция оси, которая возвращает все члены на ней. Такая же функция есть и у иерархии и у уровня. Т.к. в данной оси иерархия одна, то ее указание можно опустить, т.к. уровень и иерархии тоже один, то можно выводить все члены одним списком.

Думаю, уже очевидно, как можно продолжить это на наш третий пример с детализацией по годам. Но давайте лучше не детализировать по годам, а фильтровать – т.е. строить срез. Для этого напишем следующий запрос:

Select ..Children on rows .Members on columns from where (.)
А где же тут фильтрация?

where – ключевое слово
– это один член иерархии . Полное имя с учетом всех терминов было бы таким: .. , но т.к. имя этого члена в рамках оси уникально, то все промежуточные уточнения имени можно опустить.

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

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

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

Select crossjoin(...Children, ..Children) on rows .Members on columns from where (.)
Crossjoin – это функция. Она возвращает набор (set) кортежей (да, набор может содержать кортежи!), полученный в результате декартового произведения двух наборов. Т.е. результирующий набор будет содержать все возможные сочетания Стран и Годов. Заголовки строк, таким образом, будут содержать пару значений: Страна-Год .

Вопрос, а где же указание какие числовые характеристики надо выводить? В данном случае используется мера по умолчанию, заданная для этого куба, т.е. Сумма заказа. Если мы хотим выводить другую меру, то мы вспоминаем, что меры – это члены измерения Measures . И действуем точно так же как и с остальными осями. Т.е. фильтрации запроса по одной из мер будет выводить именно эту меру в ячейках.

Вопрос: чем отличается фильтрация в where от фильтрации путем указания членов осей в on rows. Ответ: практически ничем. Просто в where указывается срез для тех осей, которые не участвуют в формировании заголовков. Т.е. одна и та же ось не может одновременно присутствовать и в on rows , и в where .

Вычисляемые члены

Для более сложных запросов можно объявлять вычисляемые члены. Члены как осей атрибутов, так и оси мер. Т.е. Можно объявить, например, новую меру, которая будет отображать вклад каждой страны в общую сумму заказов:

With member . as ‘.CurrentMember / ..’, FORMAT_STRING=‘0.00%’ select ...Children on rows from where .
Вычисление происходит в контексте ячейки, у которой известные все ее атрибуты-координаты. Соответствующие координаты (члены) могут быть получены функцией CurrentMember у каждой из осей куба. Здесь надо понимать, что выражение .CurrentMember / .. ’ не делит один член на другой, а делит соответствующие агрегированный данные срезов куба! Т.е. срез по текущей территории разделится на срез по всем территориям, т.е. суммарное значение всех заказов. FORMAT_STRING – задает формат вывода значений, т.е. %.

Другой пример вычисляемого члена, но уже по оси годов:

With member . as ‘. - .’
Очевидно, что в отчете будет не единица, а разность соответствующих срезов, т.е. разность суммы заказов в эти два года.

Отображение в ROLAP

Системы OLAP так или иначе базируются на какой-нибудь системе хранения и организации данных. Когда речь идет о РСУБД, то говорят о ROLAP (MOLAP и HOLAP оставим для самостоятельного изучения). ROLAP – OLAP на реляционной БД, т.е. описанная в виде обычных двумерных таблиц. Системы ROLAP преобразуют MDX запросы в SQL. Основная вычислительная проблема для БД – быстрая агрегация. Чтобы быстрее агрегировать, данные в БД как правило сильно денормализованы, т.е. хранятся не очень эффективно с точки зрения занимаемого места на диске и контроля целостности БД. Плюс дополнительно содержат вспомогательные таблицы, хранящие частично агрегированные данные. Поэтому для OLAP обычно создается отдельная схема БД, которая лишь частично повторяет структуру исходных транзакционных БД в части справочников.

Многие системы OLAP предлагают инструментарий интерактивной навигации по уже сформированному запросу (и соответственно выбранным данным). При этом используется так называемое «сверление» или «бурение» (drill). Более адекватным переводом на русский было бы слово «углубление». Но это дело вкуса., в некоторых средах закрепилось слово «дриллинг».

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

drill-down – фильтрация по одной из исходных осей отчета с выводом детальной информации по потомкам в рамках иерархии выбранного фильтрующего члена. Например, если имеется отчет по распределению заказов в разрезе Стран и Годов, то при щелчке на 2007-м году выведется отчет в разрезе тех же Стран и месяцев 2007 года.
drill-aside – фильтрация под одной или нескольким выбранным осям и снятие агрегации по одной или нескольким другим осям. Например, если имеется отчет по распределению заказов в разрезе Стран и Годов, то при щелчке на 2007-м году выведется другой отчет в разрезе, например, Стран и Поставщиков с фильтрацией по 2007 году.
drill-trough – снятие агрегации по всем осям и одновременная фильтрация по ним же – позволяет увидеть исходные данные из таблицы фактов, из которых получено значение в отчете. Т.е. при щелчке по значению ячейки выводится отчет со всеми заказами, которые дали эту сумму. Эдакое мгновенное бурение в самые «недра» куба.

На этом все. Теперь, если вы решили посвятить себя Business Intelligence и OLAP самое время приступать к чтению серьезной литературы.

Теги: Добавить метки

Аннотация: В настоящей лекции рассматриваются основы проектирования кубов данных для OLAP-хранилищ данных. На примере показана методика построения куба данных с помощью CASE-инструмента.

Цель лекции

Изучив материал настоящей лекции, вы будете знать:

что такое куб данных в OLAP-хранилище данных ;
как проектировать куб данных для OLAP-хранилищ данных ;
что такое измерение куба данных ;
как факт связан с кубом данных ;
что такое атрибуты измерения ;
что такое иерархия ;
что такое метрика куба данных ;

и научитесь:

строить многомерные диаграммы ;
проектировать простые многомерные диаграммы .

Введение

Технология OLAP - это не отдельно взятый программный продукт , не язык программирования . Если постараться охватить OLAP во всех его проявлениях, то это совокупность концепций, принципов и требований, лежащих в основе программных продуктов, облегчающих аналитикам доступ к данным.

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

Централизация и удобное структурирование - это далеко не все, что нужно аналитику. Ему требуется инструмент для просмотра, визуализации информации. Традиционные отчеты, даже построенные на основе единого ХД, лишены, однако, определенной гибкости. Их нельзя "покрутить", "развернуть" или "свернуть", чтобы получить необходимое представление данных. Чем больше "срезов" и "разрезов" данных аналитик может исследовать, тем больше у него идей, которые, в свою очередь , для проверки требуют все новых и новых "срезов". В качестве такого инструмента для исследования данных аналитиком выступает OLAP .

Хотя OLAP и не представляет собой необходимый атрибут ХД, он все чаще и чаще применяется для анализа накопленных в этом ХД сведений.

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

Таким образом, OLAP можно определить как совокупность средств многомерного анализа данных, накопленных в ХД . Теоретически средства OLAP можно применять и непосредственно к оперативным данным или их точным копиям. Однако при этом существует риск подвергнуть анализу данные, которые для этого анализа не пригодны.

OLAP на клиенте и на сервере

В основе OLAP лежит многомерный анализ данных . Он может быть произведен с помощью различных средств, которые условно можно разделить на клиентские и серверные OLAP -средства.

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

Если исходные данные содержатся в настольной СУБД , вычисление агрегатных данных производится самим OLAP -средством. Если же источник исходных данных - серверная СУБД , многие из клиентских OLAP -средств посылают на сервер SQL -запросы, содержащие оператор GROUP BY , и в результате получают агрегатные данные, вычисленные на сервере.

Как правило, OLAP -функциональность реализована в средствах статистической обработки данных (из продуктов этого класса на российском рынке широко распространены продукты компаний Stat Soft и SPSS) и в некоторых электронных таблицах. В частности, неплохими средствами многомерного анализа обладает Microsoft Excel 2000. С помощью этого продукта можно создать и сохранить в виде файла небольшой локальный многомерный OLAP -куб и отобразить его двух- или трехмерные сечения.

Многие средства разработки содержат библиотеки классов или компонентов, позволяющие создавать приложения, реализующие простейшую OLAP -функциональность (такие, например, как компоненты Decision Cube в Borland Delphi и Borland C++Builder). Помимо этого многие компании предлагают элементы управления ActiveX и другие библиотеки, реализующие подобную функциональность.

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

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

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

Преимущества применения серверных OLAP -средств по сравнению с клиентскими OLAP -средствами сходны с преимуществами применения серверных СУБД по сравнению с настольными: в случае применения серверных средств вычисление и хранение агрегатных данных происходит на сервере, а клиентское приложение получает лишь результаты запросов к ним, что позволяет в общем случае снизить сетевой трафик, время выполнения запросов и требования к ресурсам, потребляемым клиентским приложением. Отметим, что средства анализа и обработка данных масштаба предприятия, как правило, базируются именно на серверных OLAP -средствах, например, таких как Oracle Express Server , Microsoft SQL Server 2000 Analysis Services, Hyperion Essbase, продуктах компаний Crystal Decisions, Business Objects, Cognos, SAS Institute. Поскольку все ведущие производители серверных СУБД производят (либо лицензировали у других компаний) те или иные серверные OLAP -средства, выбор их достаточно широк, и почти во всех случаях можно приобрести OLAP - сервер того же производителя, что и у самого сервера баз данных.

Отметим, что многие клиентские OLAP -средства (в частности, Microsoft Excel 2003, Seagate Analysis и др.) позволяют обращаться к серверным OLAP-хранилищам , выступая в этом случае в роли клиентских приложений, выполняющих подобные запросы. Помимо этого имеется немало продуктов, представляющих собой клиентские приложения к OLAP -средствам различных производителей.

Технические аспекты многомерного хранения данных

В многомерных ХД содержатся агрегатные данные различной степени подробности, например, объемы продаж по дням, месяцам, годам, по категориям товаров и т.п. Цель хранения агрегатных данных - сократить время выполнения запросов, поскольку в большинстве случаев для анализа и прогнозов интересны не детальные, а суммарные данные. Поэтому при создании многомерной базы данных всегда вычисляются и сохраняются некоторые агрегатные данные.

Отметим, что сохранение всех агрегатных данных не всегда оправданно. Дело в том, что при добавлении новых измерений объем данных, составляющих куб, растет экспоненциально (иногда говорят о "взрывном росте" объема данных). Если говорить более точно, степень роста объема агрегатных данных зависит от количества измерений куба и членов измерений на различных уровнях иерархий этих измерений . Для решения проблемы "взрывного роста" применяются разнообразные схемы, позволяющие при вычислении далеко не всех возможных агрегатных данных достичь приемлемой скорости выполнения запросов.

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

MOLAP ( Multidimensional OLAP) - исходные и агрегатные данные хранятся в многомерной базе данных. Хранение данных в многомерных структурах позволяет манипулировать данными как многомерным массивом, благодаря чему скорость вычисления агрегатных значений одинакова для любого из измерений . Однако в этом случае многомерная база данных оказывается избыточной, так как многомерные данные полностью содержат исходные реляционные данные.
ROLAP (Relational OLAP) - исходные данные остаются в той же реляционной базе данных, где они изначально и находились. Агрегатные же данные помещают в специально созданные для их хранения служебные таблицы в той же базе данных.
HOLAP ( Hybrid OLAP) - исходные данные остаются в той же реляционной базе данных, где они изначально находились, а агрегатные данные хранятся в многомерной базе данных.

Некоторые OLAP -средства поддерживают хранение данных только в реляционных структурах, некоторые - только в многомерных. Однако большинство современных серверных OLAP -средств поддерживают все три способа хранения данных. Выбор способа хранения зависит от объема и структуры исходных данных, требований к скорости выполнения запросов и частоты обновления OLAP -кубов.

Отметим также, что подавляющее большинство современных OLAP -средств не хранит "пустых" значений (примером "пустого" значения может быть отсутствие продаж сезонного товара вне сезона).

Основные понятия OLAP

Тест FAMSI

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP - это ключевой компонент организации ХД. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных. В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information) - быстрый анализ разделяемой многомерной информации, включающий следующие требования к приложениям для многомерного анализа :

Fast (Быстрый) - предоставление пользователю результатов анализа за приемлемое время (обычно не более 5 с), пусть даже ценой менее детального анализа;
Analysis (Анализ) - возможность осуществления любого логического и статистического анализа, характерного для данного приложения, и его сохранения в доступном для конечного пользователя виде;
Shared (Разделяемый) - многопользовательский доступ к данным с поддержкой соответствующих механизмов блокировок и средств авторизованного доступа;
Multidimensional (Многомерный) - многомерное концептуальное представление данных, включая полную поддержку для иерархий и множественных иерархий (это ключевое требование OLAP);
Information (Информация) - приложение должно иметь возможность обращаться к любой нужной информации, независимо от ее объема и места хранения.

Многомерное представление информации

Кубы

OLAP предоставляет удобные быстродействующие средства доступа, просмотра и анализа деловой информации. Пользователь получает естественную, интуитивно понятную модель данных, организуя их в виде многомерных кубов (Cubes) . Осями многомерной системы координат служат основные атрибуты анализируемого бизнес-процесса. Например, для продаж это могут быть товар, регион, тип покупателя. В качестве одного из измерений используется время. На пересечениях осей измерений (Dimensions) находятся данные, количественно характеризующие процесс - меры (Measures). Это могут быть объемы продаж в штуках или в денежном выражении, остатки на складе, издержки и т. п. Пользователь, анализирующий информацию, может "разрезать" куб по разным направлениям, получать сводные (например, по годам) или, наоборот, детальные (по неделям) сведения и осуществлять прочие манипуляции, которые ему придут в голову в процессе анализа.

В качестве мер в трехмерном кубе, изображенном на рис. 26.1 , использованы суммы продаж, а в качестве измерений - время, товар и магазин. Измерения представлены на определенных уровнях группировки: товары группируются по категориям, магазины - по странам, а данные о времени совершения операций - по месяцам. Чуть позже мы рассмотрим уровни группировки (иерархии ) подробнее.

Рис. 26.1.

"Разрезание" куба

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

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

(levels). Например, метки, представленная на поддерживаются далеко не всеми OLAP-средствами. Например, в Microsoft Analysis Services 2000 поддерживаются оба типа иерархии , а в Microsoft OLAP Services 7.0 - только сбалансированные. Различными в разных OLAP-средствах могут быть и число уровней иерархии , и максимально допустимое число членов одного уровня, и максимально возможное число самих измерений .

Архитектура OLAP-приложений

Все, что говорилось выше про OLAP, по сути, относилось к многомерному представлению данных. То, как данные хранятся, грубо говоря, не волнует ни конечного пользователя, ни разработчиков инструмента, которым клиент пользуется.

Многомерность в OLAP-приложениях может быть разделена на три уровня.

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

Первые два уровня в обязательном порядке присутствуют во всех OLAP-средствах. Третий уровень, хотя и является широко распространенным, не обязателен, так как данные для многомерного представления могут извлекаться и из обычных реляционных структур; процессор многомерных запросов в этом случае транслирует многомерные запросы в SQL-запросы, которые выполняются реляционной СУБД.

Конкретные OLAP-продукты, как правило, представляют собой либо средство многомерного представления данных (OLAP-клиент - например, Pivot Tables в Excel 2000 фирмы Microsoft или ProClarity фирмы Knosys), либо многомерную серверную СУБД (OLAP-сервер - например, Oracle Express Server или Microsoft OLAP Services).

Слой многомерной обработки обычно бывает встроен в OLAP-клиент и/или в OLAP-сервер, но может быть выделен в чистом виде, как, например, компонент Pivot Table Service фирмы Microsoft.

В цикле статей «Введение в базы данных», публиковавшемся в последнее время (см. КомпьютерПресс №3’2000 - 3’2001), мы обсуждали различные технологии и программные средства, применяемые при создании информационных систем - настольные и серверные СУБД, средства проектирования данных, средства разработки приложений, а также Business Intelligence - средства анализа и обработки данных масштаба предприятия, которые в настоящее время становятся все более популярными в мире, в том числе и в нашей стране. Отметим, однако, что вопросы применения средств Business Intelligence и технологии, используемые при создании приложений такого класса, в отечественной литературе пока еще освещены недостаточно. В новом цикле статей мы попробуем восполнить этот пробел и рассказать о том, что представляют собой технологии, лежащие в основе подобных приложений. В качестве примеров реализации мы будем использовать в основном OLAP-технологии фирмы Microsoft (главным образом Analysis Services в Microsoft SQL Server 2000), но надеемся, что основная часть материала будет полезна и пользователям других средств.

Первая статья в данном цикле посвящена основам OLAP (On-Line Analytical Processing) - технологии многомерного анализа данных. В ней мы рассмотрим концепции хранилищ данных и OLAP, требования к хранилищам данных и OLAP-средствам, логическую организацию OLAP-данных, а также основные термины и понятия, применяемые при обсуждении многомерного анализа.

Что такое хранилище данных

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

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

Ральф Кимбалл (Ralph Kimball), один из авторов концепции хранилищ данных, описывал хранилище данных как «место, где люди могут получить доступ к своим данным» (см., например, Ralph Kimball, «The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses», John Wiley & Sons, 1996 и «The Data Webhouse Toolkit: Building the Web-Enabled Data Warehouse», John Wiley & Sons, 2000). Он же сформулировал и основные требования к хранилищам данных:

поддержка высокой скорости получения данных из хранилища;
поддержка внутренней непротиворечивости данных;
возможность получения и сравнения так называемых срезов данных (slice and dice);
наличие удобных утилит просмотра данных в хранилище;
полнота и достоверность хранимых данных;
поддержка качественного процесса пополнения данных.

Удовлетворять всем перечисленным требованиям в рамках одного и того же продукта зачастую не удается. Поэтому для реализации хранилищ данных обычно используется несколько продуктов, одни их которых представляют собой собственно средства хранения данных, другие - средства их извлечения и просмотра, третьи - средства их пополнения и т.д.

И в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.

Что такое OLAP

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

Технология комплексного многомерного анализа данных получила название OLAP (On-Line Analytical Processing). OLAP - это ключевой компонент организации хранилищ данных. Концепция OLAP была описана в 1993 году Эдгаром Коддом, известным исследователем баз данных и автором реляционной модели данных (см. E.F. Codd, S.B. Codd, and C.T.Salley, Providing OLAP (on-line analytical processing) to user-analysts: An IT mandate. Technical report, 1993). В 1995 году на основе требований, изложенных Коддом, был сформулирован так называемый тест FASMI (Fast Analysis of Shared Multidimensional Information - быстрый анализ разделяемой многомерной информации), включающий следующие требования к приложениям для многомерного анализа:

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

Следует отметить, что OLAP-функциональность может быть реализована различными способами, начиная с простейших средств анализа данных в офисных приложениях и заканчивая распределенными аналитическими системами, основанными на серверных продуктах. Но прежде чем говорить о различных реализациях этой функциональности, давайте рассмотрим, что же представляют собой кубы OLAP с логической точки зрения.

Многомерные кубы

В данном разделе мы более подробно рассмотрим концепцию OLAP и многомерных кубов. В качестве примера реляционной базы данных, который мы будем использовать для иллюстрации принципов OLAP, воспользуемся базой данных Northwind, входящей в комплекты поставки Microsoft SQL Server или Microsoft Access и представляющей собой типичную базу данных, хранящую сведения о торговых операциях компании, занимающейся оптовыми поставками продовольствия. К таким данным относятся сведения о поставщиках, клиентах, компаниях, осуществляющих доставку, список поставляемых товаров и их категорий, данные о заказах и заказанных товарах, список сотрудников компании. Подробное описание базы данных Northwind можно найти в справочных системах Microsoft SQL Server или Microsoft Access - здесь за недостатком места мы его не приводим.

Для рассмотрения концепции OLAP воспользуемся представлением Invoices и таблицами Products и Categories из базы данных Northwind, создав запрос, в результате которого получим подробные сведения о всех заказанных товарах и выписанных счетах:

SELECT dbo.Invoices.Country, dbo.Invoices.City, dbo.Invoices.CustomerName, dbo.Invoices.Salesperson, dbo.Invoices.OrderDate, dbo.Categories.CategoryName, dbo.Invoices.ProductName, dbo.Invoices.ShipperName, dbo.Invoices.ExtendedPrice FROM dbo.Products INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN dbo.Invoices ON dbo.Products.ProductID = dbo.Invoices.ProductID

В Access 2000 аналогичный запрос имеет вид:

SELECT Invoices.Country, Invoices.City, Invoices.Customers.CompanyName AS CustomerName, Invoices.Salesperson, Invoices.OrderDate, Categories.CategoryName, Invoices.ProductName, Invoices.Shippers.CompanyName AS ShipperName, Invoices.ExtendedPrice FROM Categories INNER JOIN (Invoices INNER JOIN Products ON Invoices.ProductID = Products.ProductID) ON Categories.CategoryID = Products.CategoryID;

Этот запрос обращается к представлению Invoices, содержащему сведения обо всех выписанных счетах, а также к таблицам Categories и Products, содержащим сведения о категориях продуктов, которые заказывались, и о самих продуктах соответственно. В результате этого запроса мы получим набор данных о заказах, включающий категорию и наименование заказанного товара, дату размещения заказа, имя сотрудника, выписавшего счет, город, страну и название компании-заказчика, а также наименование компании, отвечающей за доставку.

Для удобства сохраним этот запрос в виде представления, назвав его Invoices1. Результат обращения к этому представлению приведен на рис. 1 .

Какие агрегатные данные мы можем получить на основе этого представления? Обычно это ответы на вопросы типа:

Какова суммарная стоимость заказов, сделанных клиентами из Франции?
Какова суммарная стоимость заказов, сделанных клиентами из Франции и доставленных компанией Speedy Express?
Какова суммарная стоимость заказов, сделанных клиентами из Франции в 1997 году и доставленных компанией Speedy Express?

Переведем эти вопросы в запросы на языке SQL (табл. 1).

Результатом любого из перечисленных выше запросов является число. Если в первом из запросов заменить параметр ‘France’ на ‘Austria’ или на название иной страны, можно снова выполнить этот запрос и получить другое число. Выполнив эту процедуру со всеми странами, мы получим следующий набор данных (ниже показан фрагмент):

Country	SUM (ExtendedPrice)
Argentina	7327.3
Austria	110788.4
Belgium	28491.65
Brazil	97407.74
Canada	46190.1
Denmark	28392.32
Finland	15296.35
France	69185.48
Germany	209373.6
…	…

Полученный набор агрегатных значений (в данном случае - сумм) может быть интерпретирован как одномерный набор данных. Этот же набор данных можно получить и в результате запроса с предложением GROUP BY следующего вида:

SELECT Country, SUM (ExtendedPrice) FROM invoices1 GROUP BY Country

Теперь обратимся ко второму из приведенных выше запросов, который содержит два условия в предложении WHERE. Если выполнять этот запрос, подставляя в него все возможные значения параметров Country и ShipperName, мы получим двухмерный набор данных следующего вида (ниже показан фрагмент):

	ShipperName
Country	Federal Shipping	Speedy Express	United Package
Argentina	1 210.30	1 816.20	5 092.60
Austria	40 870.77	41 004.13	46 128.93
Belgium	11 393.30	4 717.56	17 713.99
Brazil	16 514.56	35 398.14	55 013.08
Canada	19 598.78	5 440.42	25 157.08
Denmark	18 295.30	6 573.97	7 791.74
Finland	4 889.84	5 966.21	7 954.00
France	28 737.23	21 140.18	31 480.90
Germany	53 474.88	94 847.12	81 962.58
…	…	…	…

Такой набор данных называется сводной таблицей (pivot table) или кросс-таблицей (cross table, crosstab). Создавать подобные таблицы позволяют многие электронные таблицы и настольные СУБД - от Paradox для DOS до Microsoft Excel 2000. Вот так, например, выглядит подобный запрос в Microsoft Access 2000:

TRANSFORM Sum(Invoices1.ExtendedPrice) AS SumOfExtendedPrice SELECT Invoices1.Country FROM Invoices1 GROUP BY Invoices1.Country PIVOT Invoices1.ShipperName;

Агрегатные данные для подобной сводной таблицы можно получить и с помощью обычного запроса GROUP BY:

SELECT Country,ShipperName, SUM (ExtendedPrice) FROM invoices1 GROUP BY COUNTRY,ShipperName Отметим, однако, что результатом этого запроса будет не сама сводная таблица, а лишь набор агрегатных данных для ее построения (ниже показан фрагмент):

Country	ShipperName	SUM (ExtendedPrice)
Argentina	Federal Shipping	845.5
Austria	Federal Shipping	35696.78
Belgium	Federal Shipping	8747.3
Brazil	Federal Shipping	13998.26
…	…	…

Третий из рассмотренных выше запросов имеет уже три параметра в условии WHERE. Варьируя их, мы получим трехмерный набор данных (рис. 2).

Ячейки куба, показанного на рис. 2 , содержат агрегатные данные, соответствующие находящимся на осях куба значениям параметров запроса в предложении WHERE.

Можно получить набор двухмерных таблиц с помощью сечения куба плоскостями, параллельными его граням (для их обозначения используют термины cross-sections и slices).

Очевидно, что данные, содержащиеся в ячейках куба, можно получить и с помощью соответствующего запроса с предложением GROUP BY. Кроме того, некоторые электронные таблицы (в частности, Microsoft Excel 2000) также позволяют построить трехмерный набор данных и просматривать различные сечения куба, параллельные его грани, изображенной на листе рабочей книги (workbook).

Если в предложении WHERE содержится четыре или более параметров, результирующий набор значений (также называемый OLAP-кубом) может быть 4-мерным, 5-мерным и т.д.

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

Некоторые термины и понятия

Наряду с суммами в ячейках OLAP-куба могут содержаться результаты выполнения иных агрегатных функций языка SQL, таких как MIN, MAX, AVG, COUNT, а в некоторых случаях - и других (дисперсии, среднеквадратичного отклонения и т.д.). Для описания значений данных в ячейках используется термин summary (в общем случае в одном кубе их может быть несколько), для обозначения исходных данных, на основе которых они вычисляются, - термин measure, а для обозначения параметров запросов - термин dimension (переводимый на русский язык обычно как «измерение», когда речь идет об OLAP-кубах, и как «размерность», когда речь идет о хранилищах данных). Значения, откладываемые на осях, называются членами измерений (members).

Говоря об измерениях, следует упомянуть о том, что значения, наносимые на оси, могут иметь различные уровни детализации. Например, нас может интересовать суммарная стоимость заказов, сделанных клиентами в разных странах, либо суммарная стоимость заказов, сделанных иногородними клиентами или даже отдельными клиентами. Естественно, результирующий набор агрегатных данных во втором и третьем случаях будет более детальным, чем в первом. Заметим, что возможность получения агрегатных данных с различной степенью детализации соответствует одному из требований, предъявляемых к хранилищам данных, - требованию доступности различных срезов данных для сравнения и анализа.

Поскольку в рассмотренном примере в общем случае в каждой стране может быть несколько городов, а в городе - несколько клиентов, можно говорить об иерархиях значений в измерениях. В этом случае на первом уровне иерархии располагаются страны, на втором - города, а на третьем - клиенты (рис. 3).

Отметим, что иерархии могут быть сбалансированными (balanced), как, например, иерархия, представленная на рис. 3 , а также иерархии, основанные на данных типа «дата-время», и несбалансированными (unbalanced). Типичный пример несбалансированной иерархии - иерархия типа «начальник-подчиненный» (ее можно построить, например, используя значения поля Salesperson исходного набора данных из рассмотренного выше примера), представлен на рис. 4 .

Иногда для таких иерархий используется термин Parent-child hierarchy.

Существуют также иерархии, занимающие промежуточное положение между сбалансированными и несбалансированными (они обозначаются термином ragged - «неровный»). Обычно они содержат такие члены, логические «родители» которых находятся не на непосредственно вышестоящем уровне (например, в географической иерархии есть уровни Country, City и State, но при этом в наборе данных имеются страны, не имеющие штатов или регионов между уровнями Country и City; рис. 5).

Отметим, что несбалансированные и «неровные» иерархии поддерживаются далеко не всеми OLAP-средствами. Например, в Microsoft Analysis Services 2000 поддерживаются оба типа иерархии, а в Microsoft OLAP Services 7.0 - только сбалансированные. Различным в разных OLAP-средствах может быть и число уровней иерархии, и максимально допустимое число членов одного уровня, и максимально возможное число самих измерений.

Заключение

В данной статье мы ознакомились с основами OLAP. Мы узнали следующее:

Назначение хранилищ данных - предоставление пользователям информации для статистического анализа и принятия управленческих решений.
Хранилища данных должны обеспечивать высокую скорость получения данных, возможность получения и сравнения так называемых срезов данных, а также непротиворечивость, полноту и достоверность данных.
OLAP (On-Line Analytical Processing) является ключевым компонентом построения и применения хранилищ данных. Эта технология основана на построении многомерных наборов данных - OLAP-кубов, оси которого содержат параметры, а ячейки - зависящие от них агрегатные данные.
Приложения с OLAP-функциональностью должны предоставлять пользователю результаты анализа за приемлемое время, осуществлять логический и статистический анализ, поддерживать многопользовательский доступ к данным, осуществлять многомерное концептуальное представление данных и иметь возможность обращаться к любой нужной информации.

Кроме того, мы рассмотрели основные принципы логической организации OLAP-кубов, а также узнали основные термины и понятия, применяемые при многомерном анализе. И наконец, мы выяснили, что представляют собой различные типы иерархий в измерениях OLAP-кубов.

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

КомпьютерПресс 4"2001

Возможно, для кого-то использование OLAP-технологии (On-line Analytic Processing) при построении отчетности покажется какой-то экзотикой, поэтому применение OLAP-КУБа для них вовсе не является одним из важнейших требований при автоматизации бюджетирования и управленческого учета .

На самом деле очень удобно пользоваться многомерным КУБом при работе с управленческой отчетностью. При разработке форматов бюджетов можно столкнуться с проблемой многовариантности форм (подробнее об этом можно прочитать в Книге 8 "Технология постановки бюджетирования в компании" и в книге "Постановка и автоматизация управленческого учета").

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

Естественно, это приводит к тому, что руководители хотят получать отчетность во всех интересующих их аналитических срезах. А это значит, что отчеты нужно как-то заставить «дышать». Иными словами можно сказать, что в данном случае речь идет о том, что по смыслу один и тот же отчет должен предоставлять информацию в различных аналитических разрезах. Поэтому статичные отчеты уже не устраивают многих современных руководителей. Им нужна динамика, которую может дать многомерный КУБ.

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

Причем нужно уметь отличать настоящие КУБы от имитации. Одной из таких имитаций являются сводные таблицы в MS Excel. Да, этот инструмент похож на КУБ, но на самом деле таковым не является, поскольку это статические, а не динамические таблицы. Кроме того, в них гораздо хуже реализована возможность построения отчетов, использующих элементы из иерархических справочников.

Для подтверждения актуальности использования КУБа при построении управленческой отчетности можно привести простейший пример с бюджетом продаж. В рассматриваемом примере для компании актуальными являются следующие аналитические срезы: продукты, филиалы и каналы сбыта. Если для компании важны эти три аналитики, то бюджет (или отчет) продаж можно выводить в нескольких вариантах.

Следует отметить, что если создавать строки бюджетов на основе трех аналитических срезов (как в рассматриваемом примере), это позволяет создавать достаточно сложные бюджетные модели и составлять детализированные отчеты с использованием КУБа.

Например, бюджет продаж можно составлять с использованием только одной аналитики (справочника). Пример бюджета продаж, построенного на основе одной аналитики "Продукты" представлен на рисунке 1 .

Рис. 1. Пример бюджета продаж, построенного на основе одной аналитики "Продукты" в OLAP-КУБе

Этот же бюджет продаж можно составлять с использованием двух аналитик (справочников). Пример бюджета продаж, построенного на основе двух аналитик "Продукты" и "Филиалы" представлен на рисунке 2 .

Рис. 2. Пример бюджета продаж, построенного на основе двух аналитик "Продукты" и "Филиалы" в OLAP-КУБе программного комплекса "ИНТЕГРАЛ"

Если есть необходимость строить более детальные отчеты, то можно тот же бюджет продаж составлять с использованием трех аналитик (справочников). Пример бюджета продаж, построенного на основе трех аналитик "Продукты", "Филиалы" и "Каналы сбыта" представлен на рисунке 3 .

Рис. 3. Пример бюджета продаж, построенного на основе трех аналитик "Продукты", "Филиалы" и "Каналы сбыта" в OLAP-КУБе программного комплекса "ИНТЕГРАЛ"

Нужно напомнить о том, что КУБ, используемый для формирования отчетов, позволяет выводить данные в различной последовательности. На рисунке 3 бюджет продаж сначала "разворачивается" по продуктам, затем по филиалам, а потом по каналам сбыта.

Те же самые данные можно представить в другой последовательности. На рисунке 4 тот же самый бюджет продаж "разворачивается" сначала по продуктам, затем по каналам сбыта, а потом по филиалам.

Рис. 4. Пример бюджета продаж, построенного на основе трех аналитик "Продукты", "Каналы сбыта" и "Филиалы" в OLAP-КУБе программного комплекса "ИНТЕГРАЛ"

На рисунке 5 тот же самый бюджет продаж "разворачивается" сначала по филиалам, затем по продуктам, а потом по каналам сбыта.

Рис. 5. Пример бюджета продаж, построенного на основе трех аналитик "Филиалы", "Продукты" и "Каналы сбыта" в OLAP-КУБепрограммного комплекса "ИНТЕГРАЛ"

На самом деле это не все возможные варианты вывода бюджета продаж.

Кроме того, нужно обратить внимание на то, что КУБ позволяет работать с иерархической структурой справочников. В представленных примерах иерархическими справочниками являются "Продукты" и "Каналы сбыта".

С точки зрения пользователя он в данном примере получает несколько управленческих отчетов (см. Рис. 1-5 ), а с точки зрения настроек в программном продукте – это один отчет. Просто с помощью КУБа его можно просматривать несколькими способами.

Естественно, что на практике возможно очень большое количество вариантов вывода различных управленческих отчетов, если их статьи строятся на одной или нескольких аналитиках. А уж сам набор аналитик зависит от потребности пользователей в детализации. Правда, при этом не следует забывать, что, с одной стороны, чем больше аналитик, тем более детализированные отчеты можно строить. Но, с другой стороны, значит, и финансовая модель бюджетирования будет более сложной. В любом случае при наличии КУБа компания будет иметь возможность просмотра необходимой отчетности в различных вариантах, в соответствии с интересующими аналитическими разрезами.

Необходимо упомянуть еще о нескольких возможностях OLAP-КУБа.

В многомерном иерархическом OLAP-КУБе есть несколько измерений: тип строки, дата, строки, справочник 1, справочник 2 и справочник 3 (см. Рис. 6 ). Естественно, в отчет выводится столько кнопок со справочниками, сколько есть в строке бюджета, содержащей максимальное количество справочников. Если ни в одной строке бюджета нет ни одного справочника, то в отчете не будет ни одной кнопки со справочниками.

Изначально OLAP-КУБ строится по всем измерениям. По умолчанию при первоначальном построении отчета измерения расположены именно в тех областях, как показано на рисунке 6 . То есть такое измерение, как «Дата», располагается в области вертикальных измерений (измерения в области столбцов), измерения «Строки», «Справочник 1», «Справочник 2» и «Справочник 3» – в области горизонтальных измерений (измерения в области строк), а измерение «Тип строки» – в области «нераскрываемых» измерений (измерения в страничной области). Если измерение находится в последней области, то данные в отчете не будут «раскрываться» по этому измерению.

Каждое из этих измерений можно поместить в любую из трех областей. После переноса измерений отчет мгновенно перестраивается в соответствии с новой конфигурацией измерений. Например, можно поменять местами дату и строки со справочниками. Или можно в вертикальную область измерений перенести один из справочников (см. Рис. 7 ). Иными словами, отчет в OLAP-КУБе можно «крутить» и выбирать тот вариант вывода отчета, который является наиболее удобным для пользователя.

Рис. 7. Пример перестройки отчета после изменения конфигурации измерений программного комплекса "ИНТЕГРАЛ"

Конфигурацию измерений можно менять либо в основной форме КУБа, либо в редакторе карты изменений (см. Рис. 8 ). В этом редакторе также можно мышкой перетаскивать измерения из одной области в другую. Помимо этого, можно менять местами измерения в одной области.

Кроме того, в этой же форме можно настраивать некоторые параметры измерений. По каждому измерению можно настраивать расположение итогов, порядок сортировки элементов и названия элементов (см. Рис. 8 ). Также можно задавать, какое название элементов выводить в отчет: сокращенное (Name) или полное (FullName).

Рис. 8. Редактор карты измерений программного комплекса "ИНТЕГРАЛ"

Редактировать параметры измерений можно непосредственно в каждом из них (см. Рис. 9 ). Для этого нужно нажать на пиктограмму, расположенную на кнопке рядом с названием измерения.

Рис. 9. Пример редактирования справочника 1 Продукты и услуги в

С помощью этого редактора можно выбирать элементы, которые нужно показывать в отчете. По умолчанию в отчет выводятся все элементы, но при необходимости часть элементов или папок можно не показывать. Например, если нужно выводить в отчет только одну продуктовую группу, то у всех остальных необходимо убрать галочки в редакторе измерений. После чего в отчете будет только одна продуктовая группа (см. Рис. 10 ).

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

Рис. 10. Пример вывода в отчете только одной продуктовой группы (папки) в программном комплексе "ИНТЕГРАЛ"

В редакторе измерения можно оперативно создавать свои группы, перетаскивать туда элементы из справочников и т.д. По умолчанию автоматически создается только группа «Прочие», но можно создавать и другие группы. Таким образом, с помощью редактора измерений можно настраивать, какие элементы справочников и в каком порядке нужно выводить в отчет.

Следует отметить, что все такие перегруппировки не записываются. То есть после закрытия отчета или после его перерасчета в отчет будут выводиться все справочники в соответствии с настроенной методикой.

На самом деле все такие изменения можно было сделать изначально при настройке строк.

Например, с помощью ограничений также можно задавать, какие элементы или группы справочников нужно выводить в отчет, а какие – нет.

Примечание : более подробно тема данной статьи рассматривается на семинарах-практикумах "Бюджетное управление предприятием" и "Постановка и автоматизация управленческого учета" , которые проводит автор данной статьи - Александр Карпов .

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

Механизм OLAP является на сегодня одним из популярных методов анализа данных. Есть два основных подхода к решению этой задачи. Первый из них называется Multidimensional OLAP (MOLAP) – реализация механизма при помощи многомерной базы данных на стороне сервера, а второй Relational OLAP (ROLAP) – построение кубов "на лету" на основе SQL запросов к реляционной СУБД. Каждый из этих подходов имеет свои плюсы и минусы. Их сравнительный анализ выходит за рамки этой статьи. Мы же опишем нашу реализацию ядра настольного ROLAP модуля.

Такая задача возникла после применения ROLAP системы, построенной на основе компонентов Decision Cube, входящих в состав Borland Delphi. К сожалению, использование этого набора компонент показало низкую производительность на больших объемах данных. Остроту этой проблемы можно снизить, стараясь отсечь как можно больше данных перед подачей их для построения кубов. Но этого не всегда бывает достаточно.

В Интернете и прессе можно найти много информации об OLAP системах, но практически нигде не сказано о том, как это устроено внутри. Поэтому решение большинства проблем нам давалось методом проб и ошибок.

Схема работы

Общую схему работы настольной OLAP системы можно представить следующим образом:

Алгоритм работы следующий:

Получение данных в виде плоской таблицы или результата выполнения SQL запроса.
Кэширование данных и преобразование их к многомерному кубу.
Отображение построенного куба при помощи кросс-таблицы или диаграммы и т.п. В общем случае, к одному кубу может быть подключено произвольное количество отображений.

Рассмотрим как подобная система может быть устроена внутри. Начнем мы это с той стороны, которую можно посмотреть и пощупать, то есть с отображений.

Отображения, используемые в OLAP системах, чаще всего бывают двух видов – кросс-таблицы и диаграммы. Рассмотрим кросс-таблицу, которая является основным и наиболее распространенным способом отображения куба.

Кросс-таблица

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

Таким образом, таблицу можно разделить на следующие элементы, с которыми мы и будем работать в дальнейшем:

Заполняя матрицу с фактами, мы должны действовать следующим образом:

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

При этом нужно отметить то, что полученная матрица будет сильно разреженной, почему ее организация в виде двумерного массива (вариант, лежащий на поверхности) не только нерациональна, но, скорее всего, и невозможна в связи с большой размерностью этой матрицы, для хранения которой не хватит никакого объема оперативной памяти. Например, если наш куб содержит информацию о продажах за один год, и если в нем будет всего 3 измерения – Клиенты (250), Продукты (500) и Дата (365), то мы получим матрицу фактов следующих размеров:

Кол-во элементов = 250 х 500 х 365 = 45 625 000

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

Поэтому, для работы с этой матрицей нужно применить специальные механизмы работы с разреженными матрицами. Возможны различные варианты организации разреженной матрицы. Они довольно хорошо описаны в литературе по программированию, например, в первом томе классической книги "Искусство программирования" Дональда Кнута.

Рассмотрим теперь, как можно определить координаты факта, зная соответствующие ему измерения. Для этого рассмотрим подробнее структуру заголовка:

При этом можно легко найти способ определения номеров соответствующей ячейки и итогов, в которые она попадает. Здесь можно предложить несколько подходов. Один из них – это использование дерева для поиска соответствующих ячеек. Это дерево может быть построено при проходе по выборке. Кроме того, можно легко определить аналитическую рекуррентную формулу для вычисления требуемой координаты.

Подготовка данных

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

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

Библиотека компонентов CubeBase

Описанные выше идеи были положены в основу при создании библиотеки компонентов CubeBase.

TСubeSource осуществляет кэширование и преобразование данных во внутренний формат, а также предварительное агрегирование данных. Компонент TСubeEngine осуществляет вычисление гиперкуба и операции с ним. Фактически, он является OLAP-машиной, осуществляющей преобразование плоской таблицы в многомерный набор данных. Компонент TCubeGrid выполняет вывод на экран кросс-таблицы и управление отображением гиперкуба. TСubeChart позволяет увидеть гиперкуб в виде графиков, а компонент TСubePivote управляет работой ядра куба.

Сравнение производительности

Данный набор компонент показал намного более высокое быстродействие, чем Decision Cube. Так на наборе из 45 тыс. записей компоненты Decision Cube потребовали 8 мин. на построение сводной таблицы. CubeBase осуществил загрузку данных за 7сек. и построение сводной таблицы за 4 сек. При тестировании на 700 тыс. записей Decision Cube мы не дождались отклика в течение 30 минут, после чего сняли задачу. CubeBase осуществил загрузку данных за 45 сек. и построение куба за 15 сек.

На объемах данных в тысячи записей CubeBase отрабатывал в десятки раз быстрее Decision Cube. На таблицах в сотни тысяч записей – в сотни раз быстрее. А высокая производительность – один из самых важных показателей OLAP систем.