Успешно изучив материал, Вы будете знать :
понятие и основное назначение OLTP-систем;
понятие и основное назначение OLAP-систем;
задачи, решаемые OLTP- и OLAP-системами.
После изучения данной темы Вы будете уметь :
отличать задачи, решаемые OLTP- и OLAP-системами;
ориентироваться в классах OLAP-систем.
После изучения материала Вы будете обладать навыками использования OLTP- и OLAP-системам в работе менеджера.
OLTP-система 
OLAP-система
Data Warehousing — «хранилища (склады) данных»
В области ИТ управления существуют два взаимно дополняющих друг друга направления:
технологии, ориентированные на оперативную (транзакционную) обработку данных. Эти технологии лежат в основе КИСУ, предназначенных для оперативной обработки данных. Называются подобные системы — OLTP ( online transaction processing ) системы ;
технологии, ориентированные на анализ данных и принятие решений. Эти технологии лежат в основе КИСУ, предназначенных для анализа накопленных данных. Называются подобные системы — OLAP ( online analytical processing ) системы .
Основное назначение OLAP-систем : динамический многомерный анализ исторических и текущих данных, стабильных во времени; анализ тенденций; моделирование и прогнозирование будущего. Такие системы, как правило, ориентированы на обработку произвольных, заранее не регламентированных запросов. В качестве основных характеристик этих систем можно отметить следующие :
поддержка многомерного представления данных, равноправие всех измерений, независимость производительности от количества измерений;
прозрачность для пользователя структуры, способов хранения и обработки данных;
автоматическое отображение логической структуры данных во внешние системы;
динамическая обработка разряженных матриц эффективным способом.
Термин OLAP часто отождествляют с системами поддержки принятия решений DSS (Decision Support Systems). А в качестве синонима термина «решения» используют Data Warehousing — «хранилища (склады) данных» . Под этим понимается набор организационных решений, программных и аппаратных средств для обеспечения аналитиков информацией на основе данных из систем обработки транзакций нижнего уровня и других источников.
«Склады данных» позволяют обрабатывать данные, накопленные за длительные периоды времени. Эти данные являются разнородными (и не обязательно структурированными). Для «складов данных» присущ многомерный характер запросов. Огромные объемы данных, сложность структуры как данных, так и запросов — все это требует использования специальных методов доступа к информации.
В других источниках понятие Системы Поддержки Принятия Решений (СППР) считается более широким. Хранилища данных и средства оперативной аналитической обработки могут служить одними из компонентов архитектуры СППР.
OLAP всегда включает в себя интерактивную обработку запросов и последующий многопроходный анализ информации, который позволяет выявить разнообразные, не всегда очевидные тенденции, наблюдающиеся в предметной области.
Иногда различают OLAP в узком смысле — как системы, которые обеспечивают только выборку данных в различных разрезах, и OLAP в широком смысле, или просто OLAP, включающие в себя:
поддержку нескольких пользователей, редактирующих БД.
функции моделирования, в том числе вычислительные механизмы получения производных результатов, а также агрегирования и объединения данных;
прогнозирование, выявление тенденций и статистический анализ.
Каждый из этих типов систем требует специфической организации данных, а также специальных программных средств, обеспечивающих эффективное выполнение стоящих задач.
OLAP-средства обеспечивают проведение анализа деловой информации по множеству параметров, таких как вид товара, географическое положение покупателя, время оформления сделки и продавец, каждый из которых допускает создание иерархии представлений. Так, для времени можно пользоваться годовыми, квартальными, месячными и даже недельными и дневными промежутками; географическое разбиение может проводиться по городам, штатам, регионам, странам или, если потребуется, по целым полушариям.
OLAP-системы можно разбить на три класса.
1 класс. Наиболее сложными и дорогими из них являются основанные на патентованных технологиях серверы многомерных БД . Эти системы обеспечивают полный цикл OLAP-обработки и либо включают в себя, помимо серверного компонента, собственный интегрированный клиентский интерфейс, либо используют для анализа данных внешние программы работы с электронными таблицами. Продукты этого класса в наибольшей степени соответствуют условиям применения в рамках крупных информационных хранилищ. Для их обслуживания требуется целый штат сотрудников, занимающихся как установкой и сопровождением системы, так и формированием представлений данных для конечных пользователей. Обычно подобные пакеты довольно дороги. В качестве примеров продуктов этого класса можно привести систему Essbase корпорации Arbor Software, Express фирмы IRI (входящей теперь в состав Oracle), Lightship производства компании Pilot Software и др.
2 класс OLAP-систем — реляционные OLAP-системы (ROLAP). Здесь для хранения данных используются старые реляционные СУБД, а между БД и клиентским интерфейсом организуется определяемый администратором системы слой метаданных. Через этот промежуточный слой клиентский компонент может взаимодействовать с реляционной БД как с многомерной. Подобно средствам первого класса, ROLAP-системы хорошо приспособлены для работы с крупными информационными хранилищами, требуют значительных затрат на обслуживание специалистами информационных подразделений и предусматривают работу в многопользовательском режиме. Среди продуктов этого типа — IQ/Vision корпорации IQ Software, DSS/Server и DSS/Agent фирмы MicroStrategy и DecisionSuite компании Information Advantage.
ROLAP-средства реализуют функции поддержки принятия решений в надстройке над реляционным процессором БД.
Такие программные продукты должны отвечать ряду требований , в частности:
иметь мощный оптимизированный для OLAP генератор SQL-выражений, позволяющий применять многопроходные SQL-операторы SELECT и/или коррелированные подзапросы;
обладать достаточно развитыми средствами для проведения нетривиальной обработки, обеспечивающей ранжирование, сравнительный анализ и вычисление процентных соотношений в рамках класса;
генерировать SQL-выражения, оптимизированные для целевой реляционной СУБД, включая поддержку доступных в ней расширений этого языка;
предоставлять механизмы описания модели данных с помощью метаданных и давать возможность использовать эти метаданные для построения запросов в реальном масштабе времени;
включать в себя механизм, позволяющий оценивать качество построения сводных таблиц с точки зрения скорости вычисления, желательно с накоплением статистики по их использованию.
3 класс OLAP-систем — инструменты генерации запросов и отчетов для настольных ПК , дополненные OLAP-функциями или интегрированные с внешними средствами, выполняющими такие функции. Эти весьма развитые системы осуществляют выборку данных из исходных источников, преобразуют их и помещают в динамическую многомерную БД, функционирующую на ПК конечного пользователя. Указанный подход, позволяющий обойтись как без дорогостоящего сервера многомерной БД, так и без сложного промежуточного слоя метаданных, необходимого для ROLAP-средств, обеспечивает в то же время достаточную эффективность анализа. Эти средства для настольных ПК лучше всего подходят для работы с небольшими, просто организованными БД. Потребность в квалифицированном обслуживании для них ниже, чем для других OLAP-систем, и примерно соответствует уровню обычных сред обработки запросов. В числе основных участников этого сектора рынка — компания Brio Technology со своей системой Brio Query Enterprise, Business Objects с одноименным продуктом и Cognos с PowerPlay.
OLTP-системы , являясь высокоэффективным средством реализации оперативной обработки, оказались малопригодны для задач аналитической обработки. Это вызвано следующим.
Средствами традиционных OLTP-систем можно построить аналитический отчет и даже прогноз любой сложности, но заранее регламентированный. Любой шаг в сторону, любое нерегламентированное требование конечного пользователя, как правило, требует знаний о структуре данных и достаточно высокой квалификации программиста;
Многие необходимые для оперативных систем функциональные возможности являются избыточными для аналитических задач и в то же время могут не отражать предметной области. Для решения большинства аналитических задач требуется использование внешних специализированных инструментальных средств для анализа, прогнозирования и моделирования. Жесткая же структура баз не позволяет достичь приемлемой производительности в случае сложных выборок и сортировок и, следовательно, требует больших временных затрат для организации шлюзов.
В отличие от транзакционных, в аналитических системах не требуются и, соответственно, не предусматриваются развитые средства обеспечения целостности данных, их резервирования и восстановления. Это позволяет не только упростить сами средства реализации, но и снизить внутренние накладные расходы и, следовательно, повысить производительность при выборке данных.
Задачи, эффективно решаемые каждой из систем, определим на основе сравнительных характеристик OLTP- и OLAP-систем (табл. 7.1, 7.2).
Таблица 7.1.
Задачи, решаемые OLTP- и OLAP-системами
Характеристика
OLTP
OLAP
Частота обновления данных
Высокая частота, небольшие «порции»
Малая частота, большие «порции»
В основном внутренние
По отношению к аналитической системе, в основном внешние
Текущие (несколько месяцев)
Исторически (за годы) и прогнозируемые
Уровень агрегации данных
В основном агрегированные данные
Возможности аналитических операций
Последовательность интерактивных отчетов, динамическое изменение уровней агрегаций и срезов данных
Фиксация, оперативный поиск и обработка данных, регламентированная аналитическая обработка
Работа с историческими данными, аналитическая обработка, прогнозирование, моделирование
Таблица 7.2.
Сравнение OLTP и OLAP
1. Какое из утверждений Вы считаете правильным:
В результате воздействия на экономическую систему внешней среды могут изменится выполняемые ею (экономической системой) функции;
На экономическую систему внешняя среда влияния не оказывает;
2. OLAP в узком смысле слова трактуется как:
система, которая поддерживает несколько пользователей;
система, которая обеспечивает только выборку данных в различных разрезах; +
система, которая осуществляет прогнозирование, выявление тенденций и статистический анализ;
3. На каком уровне управления предприятием отсутствуют такие функции управления, как учет и контроль:
На стратегическом;
На тактическом;
На оперативном;
4. Основным назначением ERP систем является:
Автоматизация управленческого учета на предприятии;
Автоматизация процессов анализа деятельности предприятия;
Автоматизация процессов планирования, учета и управления по основным направлениям деятельности предприятия;
5. Основное назначение системы OLAP заключается в следующем:
аналитическая работа;
работа с историческими данными;
фиксация, оперативный поиск и обработка данных, регламентированная аналитическая обработка;
6. Набор приемов взаимодействия с компьютером, который реализуется операционной системой или его надстройкой называется:
системный интерфейс;
командный интерфейс;
прикладной интерфейс;
7. Модификация обеспечивающей ИТ в функциональную зависит от:
от квалификации пользователя;
от сложности необходимой модификации;
нет правильного ответа;
от квалификации пользователя и от сложности необходимой модификации; +
8. Основными характеристиками OLAP-систем является:
динамическая обработка разряженных матриц эффективным способом;
поддержка многомерного представления данных, равноправие всех измерений;
зависимость производительности от количества измерений;
9. OLTP-системы оказались мало пригодны по следующим причинам:
квалификации программиста;
необходимые для оперативных систем функциональные возможности являются избыточными для аналитических задач;
не предусматриваются развитые средства обеспечения целостности данных, их резервирование и восстановление;
любой шаг в сторону не требует знаний о структуре данных и достаточно высокой;
10. На состав АРМ, входящих в ЭИС и совокупность включаемых в них информационных технологий влияют:
Распределение обязанностей и целей между сотрудниками;
Квалификация пользователей;
Технологии предметных областей;
11. Системы поддержки принятия решений (Decision Support Systems — DSS):
Позволяют автоматизировать процесс обучения и консультирования;
Обеспечивают менеджерам возможности анализа и моделирования ситуации;
12. Программные продукты ROLAP должны отвечать следующим требованиям:
не предоставлять механизмы описания модели данных;
иметь мощный оптимизированный генератор SQL-выражений;
обладать достаточно развитыми средствами для проведения нетривиальной
обработки, обеспечивающей ранжирование, сравнительный анализ и вычисление процентных соотношений в рамках класса;
13. В ближайшем будущем развитие информационных технологий ожидается в направлении:
увеличения программных средств, для производства и финансовых учреждений;
развития ряда систем автоматизированного проектирования в промышленности, создание автоматизированных и роботизированных систем на производстве и строительстве, а так же в различных областях экономики и управления финансами; +
создания нового поколения провайдеров с использованием существующих местных телефонных линий передачи данных;
14. Целенаправленное управление экономической системой можно осуществлять более эффективно, если:
Экономическая система имеет линейную структуру;
Экономическая система имеет иерархическую структуру;
Аппаратные (технические) средства предназначены для:
организации процесса обработки анимации, видеоизображения, звука;
организации процесса переработки данных, связи и передачи данных;
организации процесса обработки информации;
15. MRP является стандартом построения автоматизированных систем управления деятельностью предприятия:
Нет — это не так;
Да — это верное утверждение;
16. Интерфейс, который обеспечивает выдачу на экран системного приглашения для ввода команды называется:
командный интерфейс;
прикладной интерфейс;
системный интерфейс;
17. Предметная технология и информационная технология:
Не влияют друг на друга;
Влияют друг на друга; +
18. Подсистема технического обеспечения включает:
Мainframe-компьютер, поддерживающий информационное обеспечение для принятия решений;
Функциональные и обеспечивающие информационные технологии;
Компьютеры, обеспечивающие работу ЭИС;
19. На высшем уровне управления решаются:
Задачи по выработке внешней политики деятельности предприятия;
Задачи по составлению тактических планов;
Задачи по управлению цехами, отделами, участками;
Задачи по составлению отчетов о ходе деятельности предприятия;
Задачи по разработке долгосрочных планов;
20. В области информационных технологий существуют:
системы, ориентированные на хранение данных;
системы, ориентированные на анализ данных и принятие решений;
системы, ориентированные на оперативную обработку данных;
21. Экономическая система:
Может менять свои свойства весьма динамично;
Не меняет своих свойств;
22. Информационное обеспечение это:
Совокупность норм, выраженных в нормативных актах, устанавливающих и закрепляющих организацию ЭИС, их цели, задачи, структуру, функции и правовой статус; +
Организация информационного окружения пользователя в виде комфортной среды, способствующей выполнению поставленных перед ним целей;
Совокупность языков общения, правил их формализации, терминов, используемых в ЭИС;
Совокупность внешней и внутренней информации, используемой в ЭИС;
23. Исполнительские информационные системы (Executive Information System — EIS):
Предназначены для предоставления руководству информации о текущей ситуации в компании и на рынке;
Предназначены для принятия стратегических решений;
24. Выделяют следующие классы OLAP:
серверы одномерных БД;
серверы многомерных БД;
реляционные OLAP-системы;
25. Под информационной технологией понимается:
система информационного обеспечения всех потребителей информации в стране, которая предоставляет им возможность использования новых информационных технологий на базе широкого применения информационно-вычислительных ресурсов и автоматизированной системы связи;
комплекс мер, направленных на обеспечение полного и своевременного использования достоверной информации и знаний во всех социально значимых видах человеческой деятельности;
система методов и способов сбора, накопления, хранения, поиска, обработки, анализа, выдачи данных, информации и знаний на основе применения аппаратных и программных средств и в соответствии с требованиями, предъявленными пользователями;
26. Экспертные системы предназначены для:
обработки текстовой информации;
обработки данных;
обработки знаний;
обработки графики;
27. Выберите правильную цепочку действий, наиболее точно отражающую процесс управления предприятием:
Целеполагание — анализ — контроль — формирование управленческого воздействия — корректировка — планирование — исполнение;
Целеполагание — планирование — исполнение — контроль — корректировка — анализ — формирование управленческого воздействия;
Целеполагание — планирование — исполнение — контроль -анализ — формирование управленческого воздействия — корректировка;
28. Базовые информационные технологии оказывают значительное влияние на предметную технологию:
Нет, утверждение ошибочно;
Это верное утверждение; +
29. Основным назначением MPS является:
Определение количественных показателей для оптимизации производственной технологии;
Определение количественных показателей каждого выпускаемого изделия в привязке к временным промежуткам планирования (неделя, месяц) в пределах горизонта планирования;
Планирование загрузки кадров;
Некоторое время назад мне довелось организовывать новую группу разработки, которая должна была заняться развитием OLAP и BI продуктов в дружеской софтверной компании. А так как группа была собрана из свежих выпускников ВУЗов, то мне пришлось написать «краткий курс молодого бойца» для того чтобы максимально доступно дать начальные понятия об OLAP людям, которые ни разу с ним не сталкивались, но уже имели опыт программирования и работы с БД.
Выкладываю теперь это Введение в Общественное Достояние.
В статье несколько смешиваются понятия OLAP, Business Intelligence, и Data Warehouse, но и в жизни часто сложно понять, где проходит граница. А уж в реальных проектах, так и подавно, все они ходят рядом. Поэтому прошу не судить строго.
Информационные системы серьезного предприятия, как правило, содержат приложения, предназначенные для комплексного анализа данных, их динамики, тенденций и т.п. Соответственно, основными потребителями результатов анализа становится топ-менеджмент. Такой анализ, в конечном итоге, призван содействовать принятию решений. А чтобы принять любое управленческое решение необходимо обладать необходимой для этого информацией, обычно количественной. Для этого необходимо эти данные собрать из всех информационных систем предприятия, привести к общему формату и уже потом анализировать. Для этого создают хранилища данных (Data Warehouses).
Обычно — место сбора всей информации, представляющей аналитическую ценность. Требования для таких хранилищ соответствуют классическому определению OLAP, будут объяснены ниже.
Иногда Хранилище имеет еще одну цель – интеграция всех данных предприятия, для поддержания целостности и актуальности информации в рамках всех информационных систем. Т.о. хранилище накапливает не только аналитическую, а почти всю информацию, и может ее выдавать в виде справочников обратно остальным системам.
Типичное хранилище данных, как правило, отличается от обычной реляционной базы данных. Во-первых, обычные базы данных предназначены для того, чтобы помочь пользователям выполнять повседневную работу, тогда как хранилища данных предназначены для принятия решений. Например, продажа товара и выписка счета производятся с использованием базы данных, предназначенной для обработки транзакций, а анализ динамики продаж за несколько лет, позволяющий спланировать работу с поставщиками, — с помощью хранилища данных.
Во-вторых, обычные базы данных подвержены постоянным изменениям в процессе работы пользователей, а хранилище данных относительно стабильно: данные в нем обычно обновляются согласно расписанию (например, еженедельно, ежедневно или ежечасно — в зависимости от потребностей). В идеале процесс пополнения представляет собой просто добавление новых данных за определенный период времени без изменения прежней информации, уже находящейся в хранилище.
И, в-третьих, обычные базы данных чаще всего являются источником данных, попадающих в хранилище. Кроме того, хранилище может пополняться за счет внешних источников, например статистических отчетов.
ETL – базовое понятие: Extraction, Transformation, Loading. Три этапа:
- Извлечение – извлечение данных из внешних источников в понятном формате;
- Преобразование – преобразование структуры исходных данных в структуры, удобные для построения аналитической системы;
- Загрузка – загрузка данных в хранилище.
Добавим еще один этап – очистка данных (Cleaning) – процесс отсеивания несущественных или исправления ошибочных данных на основании статистических или экспертных методов. Чтобы не формировать потом отчеты типа «Продажи за 20011 год».
Вернемся к анализу.
Анализ – исследование данных с целью принятия решений. Аналитические системы так и называют — системы поддержки принятия решений (СППР).
Здесь стоит указать на отличие работы с СППР от простого набора регламентированных и нерегламентированных отчетов. Анализ в СППР практически всегда интерактивен и итеративен. Т.е. аналитик копается в данных, составляя и корректируя аналитические запросы, и получает отчеты, структура которых заранее может быть неизвестна. Более подробно к этому мы вернемся ниже, когда будем обсуждать язык запросов MDX.
Системы поддержки принятия решений обычно обладают средствами предоставления пользователю агрегатных данных для различных выборок из исходного набора в удобном для восприятия и анализа виде (таблицы, диаграммы и т.п.). Традиционный подход сегментирования исходных данных использует выделение из исходных данных одного или нескольких многомерных наборов данных (нередко называемый гиперкубом или метакубом), оси которых содержат атрибуты, а ячейки – агрегируемые количественные данные. (Причем храниться такие данные могут и в реляционных таблицах, но в данном случае мы говорим о логической организации данных, а не о физической реализации их хранения.) Вдоль каждой оси атрибуты могут быть организованы в виде иерархий, представляющих различные уровни их детализации. Благодаря такой модели данных пользователи могут формулировать сложные запросы, генерировать отчеты, получать подмножества данных.
Технология комплексного многомерного анализа данных получила название 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). Для района данные агрегируются по всем городам, которые в него входят. Для области по всем районам, которые содержат все города и т.п. Зачем нужно несколько иерархий? Например, по оси с датой заказа мы можем хотеть группировать точки (т.е. дни) по иерархии Год-Месяц-День или по Год-Неделя-День: в обоих случаях по три уровня. Очевидно, что Неделя и Месяц по-разному группируют дни. Бывают также иерархии, количество уровней в которых не детерминировано и зависит от данных. Например, папки на компьютерном диске.
Агрегация данных может происходить с использованием нескольких стандартных функций: сумма, минимум, максимум, среднее, количество.
Перейдем к языку запросов в многомерных данных.
Язык SQL изначально был спроектирован не для программистов, а для аналитиков (и поэтому имеет синтаксис, напоминающий естественный язык). Но он со временем все больше усложнялся и теперь мало кто из аналитиков хорошо умеет им пользоваться, если умеет вообще. Он стал инструментом программистов. Язык запросов MDX, разработанный по слухам нашим бывшим соотечественником Мойшей (или Мошей) Посуманским (Mosha Pasumansky) в дебрях корпорации Майкрософт, тоже изначально должен был ориентирован на аналитиков, но его концепции и синтаксис (который отдаленно напоминает SQL, причем совершенно зря, т.к. это только путает), еще сложнее чем SQL. Тем не менее его основы все же понять несложно.
Мы рассмотрим его подробно потому что это единственный язык, который получил статус стандартного в рамках общего стандарта протокола XMLA, а во вторых потому что существует его open-source реализация в виде проекта Mondrian от компании Pentaho. Другие системы OLAP-анализа (например, Oracle OLAP Option) обычно используют свои расширения синтаксиса языка SQL, впрочем, декларируют поддержку и MDX.
Работа с аналитическими массивами данных подразумевает только их чтение и не подразумевает запись. Т.о. в языке MDX нет предложений для изменения данных, а есть только одно предложение выборки — select.
В OLAP из многомерных кубов можно делать срезы – т.е. когда данные фильтруются по одной или нескольким осям, или проекции – когда по одному или нескольким осям куб «схлопывается», агрегируя данные. Например, наш первый пример с суммой заказов из стран – есть проекция куба на ось Страны. MDX запрос для этого случая будет выглядеть следующим образом:
Select – ключевое слово и в синтаксис входит исключительно для красоты.
[Territory] – это название оси. Все имена собственные в MDX пишутся в квадратных скобках.
[Cities by Countries] – это название иерархии. В нашем случае – это иерархия Страна-Город
[All] – это название члена оси на первом уровне иерархии (т.е. страны) All – это мета-член, объединяющий все члены оси. Такой мета-член есть в каждой оси. Например в оси годов есть «Все года» и т.п.
Children – это функция члена. У каждого члена есть несколько доступных функций. Таких как Parent. Level, Hierarchy, возвращающие соответственно предка, уровень в иерархии и саму иерархию, к которой относится в данном случае член. Children – возвращает набор членов-потомков данного члена. Т.е. в нашем случае – страны.
on rows – Указывает как расположить эти данные в итоговой таблице. В данном случае – в заголовке строк. Возможные значении здесь: on columns, on pages, on paragraphs и т.п. Возможно так же указание просто по индексам, начиная с 0.
from [invoices1] – это указание куба, из которого производится выборка.
Что если нам не нужны все страны, а нужно только пара конкретных? Для этого можно в запросе указать явно те страны которые нам нужны, а не выбирать все функцией Children.
Фигурные скобки в данном случае – обявление набора (Set). Набор – это список, перечисление членов из одной оси.
Теперь напишем запрос для нашего второго примера – вывод в разрезе доставщика:
Здесь добавилось:
[Shipper] – ось;
.Members – функция оси, которая возвращает все члены на ней. Такая же функция есть и у иерархии и у уровня. Т.к. в данной оси иерархия одна, то ее указание можно опустить, т.к. уровень и иерархии тоже один, то можно выводить все члены одним списком.
Думаю, уже очевидно, как можно продолжить это на наш третий пример с детализацией по годам. Но давайте лучше не детализировать по годам, а фильтровать – т.е. строить срез. Для этого напишем следующий запрос:
А где же тут фильтрация?
where – ключевое слово
[2007] – это один член иерархии [Date]. Полное имя с учетом всех терминов было бы таким: [Date.By months].[All dates].[2007], но т.к. имя этого члена в рамках оси уникально, то все промежуточные уточнения имени можно опустить.
Почему член даты в скобках? Круглые скобки – это кортеж (tuple). Кортеж – это один или несколько координат по различным осям. Например для фильтрации сразу по двум осям в круглых скобках мы перечислим два члена из разных измерений через запятую. Т. е. кортеж определяет «срез» куба (или «фильтрацию», если такая терминология ближе).
Кортеж используется не только для фильтрации. Кортежи могут быть и в заголовках строк/колонок/страниц и т.п.
Это нужно, например, для того чтобы вывести в двумерную таблицу результат трехмерного запроса.
Crossjoin – это функция. Она возвращает набор (set) кортежей (да, набор может содержать кортежи!), полученный в результате декартового произведения двух наборов. Т.е. результирующий набор будет содержать все возможные сочетания Стран и Годов. Заголовки строк, таким образом, будут содержать пару значений: Страна-Год.
Вопрос, а где же указание какие числовые характеристики надо выводить? В данном случае используется мера по умолчанию, заданная для этого куба, т.е. Сумма заказа. Если мы хотим выводить другую меру, то мы вспоминаем, что меры – это члены измерения Measures. И действуем точно так же как и с остальными осями. Т.е. фильтрации запроса по одной из мер будет выводить именно эту меру в ячейках.
Вопрос: чем отличается фильтрация в where от фильтрации путем указания членов осей в on rows. Ответ: практически ничем. Просто в where указывается срез для тех осей, которые не участвуют в формировании заголовков. Т.е. одна и та же ось не может одновременно присутствовать и в on rows, и в where.
Вычисляемые члены
Для более сложных запросов можно объявлять вычисляемые члены. Члены как осей атрибутов, так и оси мер. Т.е. Можно объявить, например, новую меру, которая будет отображать вклад каждой страны в общую сумму заказов:
Вычисление происходит в контексте ячейки, у которой известные все ее атрибуты-координаты. Соответствующие координаты (члены) могут быть получены функцией CurrentMember у каждой из осей куба. Здесь надо понимать, что выражение [Territory].CurrentMember / [Territory].[Cities by Countries].[All]’ не делит один член на другой, а делит соответствующие агрегированный данные срезов куба! Т.е. срез по текущей территории разделится на срез по всем территориям, т.е. суммарное значение всех заказов. FORMAT_STRING – задает формат вывода значений, т.е. %.
Другой пример вычисляемого члена, но уже по оси годов:
Очевидно, что в отчете будет не единица, а разность соответствующих срезов, т.е. разность суммы заказов в эти два года.
Системы 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 самое время приступать к чтению серьезной литературы.