Olap запросы. OLAP в финансовом управлении

В 1993 году основоположник реляционного подхода к построению баз данных Эдгар Кодд с партнерами (Edgar Codd, математик и стипендиат IBM), опубликовали статью, инициированную компанией "Arbor Software" (сегодня это известнейшая компания "Hyperion Solutions"), озаглавленную "Обеспечение OLAP (оперативной аналитической обработки) для пользователей-аналитиков", в которой сформулированы 12 особенностей технологии OLAP , которые впоследствии были дополнены еще шестью. Эти положения стали основным содержанием новой и очень перспективной технологии.

Основные особенности технологии OLAP (Basic):

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

Специальные особенности ( Special ):

• обработка неформализованных данных;
• сохранение результатов OLAP : хранение их отдельно от исходных данных;
• исключение отсутствующих значений;
• обработка отсутствующих значений.

Особенности представления отчетов ( Report ):

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

Управление измерениями ( Dimension ):

• универсальность измерений;
• неограниченное число измерений и уровней агрегации ;
• неограниченное число операций между размерностями.

Исторически сложилось так, что сегодня термин " OLAP " подразумевает не только многомерный взгляд на данные со стороны конечного пользователя, но и многомерное представление данных в целевой БД. Именно с этим связано появление в качестве самостоятельных терминов "Реляционный OLAP" ( ROLAP ) и "Многомерный OLAP" ( MOLAP ).

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

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

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

Рис. 6.14.

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

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

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

В настоящее время быстрое развитие получило направление, называемое динамическим моделированием (Dynamic Simulation ), в полной мере реализующее указанный выше принцип FASMI.

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

В таблице 6.3 приведены сравнительные характеристики статического и динамического анализа.

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

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

Ключевые вопросы "Сколько продано?", "На какую сумму продано?" расширяются по мере усложнения бизнеса и накопления исторических данных до некоторого множества факторов, или разрезов: "..в Санкт-Петербурге, в Москве, на Урале, в Сибири…", "..в прошлом квартале, по сравнению с нынешним", "..от поставщика А по сравнению с поставщиком Б…" и т. д.

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

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

Время . Как правило, это несколько периодов: Год, Квартал, Месяц, Декада, Неделя, День. Многие OLAP -инструменты автоматически вычисляют старшие периоды из даты и вычисляют итоги по ним.

Категория товара . Категорий может быть несколько, они отличаются для каждого вида бизнеса: Сорт, Модель, Вид упаковки и пр. Если продается только один товар или ассортимент очень невелик, то категория не нужна.

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

Регион . В зависимости от глобальности бизнеса можно иметь в виду Континент, Группа стран, Страна, Территория, Город, Район, Улица, Часть улицы. Конечно, если есть только одна торговая точка, то это измерение отсутствует.

Продавец . Это измерение тоже зависит от структуры и масштабов бизнеса. Здесь может быть: Филиал, Магазин, Дилер, Менеджер по продажам. В некоторых случаях измерение отсутствует, например, когда продавец не влияет на объемы сбыта, магазин только один и так далее.

Покупатель . В некоторых случаях, например, в розничной торговле , покупатель обезличен и измерение отсутствует, в других случаях информация о покупателе есть, и она важна для продаж. Это измерение может содержать название фирмы-покупателя или множество группировок и характеристик клиентов: Отрасль, Группа предприятий, Владелец и так далее.. Анализ структуры продаж для выявления важнейших составляющих в интересующем разрезе. Для этого удобно использовать, например, диаграмму типа "Пирог" в сложных случаях, когда исследуется сразу 3 измерения - "Столбцы". Например, в магазине "Компьютерная техника" за квартал продажи компьютеров составили $100000, фототехники -$10000, расходных материалов - $4500. Вывод: оборот магазина зависит в большой степени от продажи компьютеров (на самом деле, быть может, расходные материалы необходимы для продажи компьютеров, но это уже анализ внутренних зависимостей).

Анализ динамики ( регрессионный анализ - выявление трендов ). Выявление тенденций, сезонных колебаний. Наглядно динамику отображает график типа "Линия". Например, объемы продаж продуктов компании Intel в течение года падали, а объемы продаж Microsoft росли. Возможно, улучшилось благосостояние среднего покупателя, или изменился имидж магазина, а с ним и состав покупателей. Требуется провести корректировку ассортимента. Другой пример: в течение 3 лет зимой снижается объем продаж видеокамер.

Анализ зависимостей (корреляционный анализ). Сравнение объемов продаж разных товаров во времени для выявления необходимого ассортимента - "корзины". Для этого также удобно использовать график типа "Линия". Например, при удалении из ассортимента принтеров в течение первых двух месяцев обнаружилось падение продаж картриджей с порошком.

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

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

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

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

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

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

OLAP-технология относится к виду интеллектуального анализа и предполагает 12 принципов:

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

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

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

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

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

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

7. Динамическое управление разреженными матрицами . Физическая схема OLAP-инструмента должна полностью адаптироваться к специфической аналитической модели для оптимального управления разреженными матрицами. Разреженность (измеряется в процентном отношении пустых ячеек ко всем возможным) – это одна из характеристик распространения данных.

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

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

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

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

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

В качестве специализированных OLAP-систем, предлагаемых в настоящее время на рынке, можно указать CalliGraph, Business Intelligence.

Для решения простых задач анализа данных возможно использовать бюджетное решение – офисные приложения Excel и Access компании Microsoft, которые содержат элементарные средства OLAP-технологии, позволяющие создавать сводные таблицы и строить на их основе различные отчеты.

Концепция технологии OLAP была сформулирована Эдгаром Коддом в 1993 г.

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

Основные требования, предъявляемые к приложениям для многомерного анализа:

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

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

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

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

В рамках OLAP-технологий на основе того, что многомерное представление данных может быть организовано как средствами реляционных СУБД, так многомерных специализированных средств, различают три типа многомерных OLAP-систем:

• - многомерный (Multidimensional) OLAP-MOLAP;
• - реляционный (Relation) OLAP-ROLAP;
• - смешанный или гибридный (Hibrid) OLAP-HOLAP.

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

Достоинствами MOLAP являются:

• - более быстрое, чем при ROLAP получение ответов на запросы - затрачиваемое время на один-два порядка меньше;
• - из-за ограничений SQL затрудняется реализация многих встроенных функций.

К ограничениям MOLAP относятся:

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

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

Достоинствами ROLAP-систем являются:

• - возможность оперативного анализа непосредственно содержащихся в хранилище данных, т.к. большинство исходных баз данных - реляционного типа;
• - при переменной размерности задачи выигрывают RO- LAP, т.к. не требуется физическая реорганизация базы данных;
• - ROLAP-системы могут использовать менее мощные клиентские станции и серверы, причем на серверы ложится основная нагрузка по обработке сложных SQL-запросов;
• - уровень защиты информации и разграничения прав доступа в реляционных СУБД несравненно выше чем в многомерных.

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

Выполнение же этих условий позволяет при использовании ROLAP-систем добиться схожих с MOLAP-системами показателей в отношении времени доступа, а также превзойти в экономии памяти.

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

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

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

Режим выявления закономерностей основан на интеллектуальной обработке данных. Главной задачей здесь является выявление закономерностей в исследуемых процессах, взаимосвязей и взаимовлияния различных факторов, поиск крупных «непривычных» отклонений, прогноз хода различных существенных процессов. Эта область относится к интеллектуальному анализу (Data mining).

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

Уровень транзакционных систем

Уровень хранилищ данных

Уровень витрин данных

Уровень OLAP – систем

Уровень аналитических приложений

OLAP - системы - (OnLine Analytical Processing, аналитическая обработка в настоящем времени) - представляют собой технологию комплексного многомерного анализа данных. OLAP - системы применимы там, где есть задача анализа многофакторных данных. Являют собой эффективное средство анализа и генерации отчетов. Рассмотренные выше хранилища данных, витрины данных и OLAP - системы относятся к системам бизнес - интеллекта (Business Intelligence, BI).

Очень часто информационно-аналитические системы, создаваемые в расчете на непосредственное использование лицами, принимающими решения, оказываются чрезвычайно просты в применении, но жестко ограничены в функциональности. Такие статические системы называются в литературе Информационными системами руководителя (ИСР), или Executive Information Systems (EIS) . Они содержат в себе предопределенные множества запросов и, будучи достаточными для повседневного обзора, неспособны ответить на все вопросы к имеющимся данным, которые могут возникнуть при принятии решений. Результатом работы такой системы, как правило, являются многостраничные отчеты, после тщательного изучения которых у аналитика появляется новая серия вопросов. Однако каждый новый запрос, непредусмотренный при проектировании такой системы, должен быть сначала формально описан, закодирован программистом и только затем выполнен. Время ожидания в таком случае может составлять часы и дни, что не всегда приемлемо. Таким образом, внешняя простота статических СППР, за которую активно борется большинство заказчиков информационно-аналитических систем, оборачивается катастрофической потерей гибкости.

Динамические СППР, напротив, ориентированы на обработку нерегламентированных (ad hoc) запросов аналитиков к данным. Наиболее глубоко требования к таким системам рассмотрел E. F. Codd в статье , положившей начало концепции OLAP. Работа аналитиков с этими системами заключается в интерактивной последовательности формирования запросов и изучения их результатов.

Но динамические СППР могут действовать не только в области оперативной аналитической обработки (OLAP); поддержка принятия управленческих решений на основе накопленных данных может выполняться в трех базовых сферах .

Сфера детализированных данных. Это область действия большинства систем, нацеленных на поиск информации. В большинстве случаев реляционные СУБД отлично справляются с возникающими здесь задачами. Общепризнанным стандартом языка манипулирования реляционными данными является SQL. Информационно-поисковые системы, обеспечивающие интерфейс конечного пользователя в задачах поиска детализированной информации, могут использоваться в качестве надстроек как над отдельными базами данных транзакционных систем, так и над общим хранилищем данных.

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

Сфера закономерностей. Интеллектуальная обработка производится методами интеллектуального анализа данных (ИАД, Data Mining) , главными задачами которых являются поиск функциональных и логических закономерностей в накопленной информации, построение моделей и правил, которые объясняют найденные аномалии и/или прогнозируют развитие некоторых процессов.

Оперативная аналитическая обработка данных

В основе концепции OLAP лежит принцип многомерного представления данных. В 1993 году в статье E. F. Codd рассмотрел недостатки реляционной модели, в первую очередь указав на невозможность "объединять, просматривать и анализировать данные с точки зрения множественности измерений, то есть самым понятным для корпоративных аналитиков способом", и определил общие требования к системам OLAP, расширяющим функциональность реляционных СУБД и включающим многомерный анализ как одну из своих характеристик.

Классификация продуктов OLAP по способу представления данных.

В настоящее время на рынке присутствует большое количество продуктов, которые в той или иной степени обеспечивают функциональность OLAP. Около 30 наиболее известных перечислены в списке обзорного Web-сервера http://www.olapreport.com/. Обеспечивая многомерное концептуальное представление со стороны пользовательского интерфейса к исходной базе данных, все продукты OLAP делятся на три класса по типу исходной БД.

Самые первые системы оперативной аналитической обработки (например, Essbase компании Arbor Software , Oracle Express Server компании Oracle ) относились к классу MOLAP, то есть могли работать только со своими собственными многомерными базами данных. Они основываются на патентованных технологиях для многомерных СУБД и являются наиболее дорогими. Эти системы обеспечивают полный цикл OLAP-обработки. Они либо включают в себя, помимо серверного компонента, собственный интегрированный клиентский интерфейс, либо используют для связи с пользователем внешние программы работы с электронными таблицами. Для обслуживания таких систем требуется специальный штат сотрудников, занимающихся установкой, сопровождением системы, формированием представлений данных для конечных пользователей.

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

Наконец, гибридные системы (Hybrid OLAP, HOLAP) разработаны с целью совмещения достоинств и минимизации недостатков, присущих предыдущим классам. К этому классу относится Media/MR компании Speedware . По утверждению разработчиков, он объединяет аналитическую гибкость и скорость ответа MOLAP с постоянным доступом к реальным данным, свойственным ROLAP.

Многомерный OLAP (MOLAP)

В специализированных СУБД, основанных на многомерном представлении данных, данные организованы не в форме реляционных таблиц, а в виде упорядоченных многомерных массивов:

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

2) поликубов (каждая переменная хранится с собственным набором измерений, и все связанные с этим сложности обработки перекладываются на внутренние механизмы системы).

Использование многомерных БД в системах оперативной аналитической обработки имеет следующие достоинства.

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

Многомерные СУБД легко справляются с задачами включения в информационную модель разнообразных встроенных функций, тогда как объективно существующие ограничения языка SQL делают выполнение этих задач на основе реляционных СУБД достаточно сложным, а иногда и невозможным.

С другой стороны, имеются существенные ограничения.

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

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

Следовательно, использование многомерных СУБД оправдано только при следующих условиях.

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

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

Время ответа системы на нерегламентированные запросы является наиболее критичным параметром.

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

Реляционный OLAP (ROLAP)

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

В большинстве случаев корпоративные хранилища данных реализуются средствами реляционных СУБД, и инструменты ROLAP позволяют производить анализ непосредственно над ними. При этом размер хранилища не является таким критичным параметром, как в случае MOLAP.

В случае переменной размерности задачи, когда изменения в структуру измерений приходится вносить достаточно часто, ROLAP системы с динамическим представлением размерности являются оптимальным решением, так как в них такие модификации не требуют физической реорганизации БД.

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

Главный недостаток ROLAP по сравнению с многомерными СУБД - меньшая производительность. Для обеспечения производительности, сравнимой с MOLAP, реляционные системы требуют тщательной проработки схемы базы данных и настройки индексов, то есть больших усилий со стороны администраторов БД. Только при использовании звездообразных схем производительность хорошо настроенных реляционных систем может быть приближена к производительности систем на основе многомерных баз данных.

В цикле статей «Введение в базы данных», публиковавшемся в последнее время (см. КомпьютерПресс №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’ или на название иной страны, можно снова выполнить этот запрос и получить другое число. Выполнив эту процедуру со всеми странами, мы получим следующий набор данных (ниже показан фрагмент):

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

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

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

Такой набор данных называется сводной таблицей (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 Отметим, однако, что результатом этого запроса будет не сама сводная таблица, а лишь набор агрегатных данных для ее построения (ниже показан фрагмент):

Третий из рассмотренных выше запросов имеет уже три параметра в условии 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