OLAP(Online Analytical Processing) — оперативная аналитическая обработка

OLAP(Online Analytical Processing) - оперативная аналитическая обработка данных Исполнители: магистранты 1 курса Гребенкина Т. С. , Туманова Е. Г. Нижний Новгород, 2012 год

OLAP (Online Analytical Processing) — это совокупность концепций, принципов и требований, лежащих в основе программных продуктов, обеспечивающих сбор, хранение, манипулирование и оперативный анализ многомерных данных в режиме реального времени. Термин OLAP был предложен Е. Ф. Коддом в статье "OLAP для пользователей-аналитиков". Эта статья была опубликована в 1993 году и спонсировалась корпорацией Arbor Software (создателем и распространителем первого OLAP-продукта Essbase). Основное назначение OLAP-систем — поддержка оперативной аналитической деятельности, а также произвольных (ad-hoc) запросов пользователей-аналитиков. Цель OLAP-анализа — проверка возникающих гипотез и нахождение закономерностей в больших массивах данных для получения выводов о текущем состоянии бизнеса.

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

В 1993 г. Э. Кодд изложил основы концепции оперативной аналитической обработки и определил 12 требований, которым должны удовлетворять продукты, позволяющие требований выполнять оперативную аналитическую обработку: 1. Средства должны поддерживать многомерный взгляд на данные (гиперкубы данных). 2. Прозрачность - пользователь не должен знать о том, какие конкретные средства используются для хранения и обработки данных, как данные организованы и откуда они берутся. 3. Доступность - для обеспечения единого, согласованного и целостного взгляда пользователя на информацию, инструментарий OLAP должен накладывать свою логическую схему на физические массивы данных. 4. Устойчивая производительность - производительность не должна зависеть от количества измерений. 5. Поддержка архитектуры клиент-сервер. 6. Равноправность всех измерений. 7. Динамическая обработка разреженных матриц - неопределенные значения должны храниться и обрабатываться наиболее эффективным способом. Скорость доступа должна сохраняться вне зависимости от расположения ячеек данных. 8. Поддержка многопользовательского режима работы с данными.

9. Поддержка операций на основе различных измерений - все многомерные операции (например, агрегация) должны применяться к любому числу любых измерений. 10. Простота манипулирования данными - максимально удобный, естественный и комфортный пользовательский интерфейс для анализа. 11. Развитые средства представления данных - средства должны поддерживать различные способы визуализации (представления) данных. Гибкий механизм предоставления отчетов в любой форме. 12. Неограниченное число измерений и уровней агрегации данных - не должно быть ограничений на число поддерживаемых измерений. В 1995 году был сформулирован тест FASMI (быстрый анализ разделяемой многомерной информации): • предоставление пользователю результатов анализа за приемлемое время, • возможность осуществления логического и статистического анализа , и его сохранения в доступном для конечного пользователя виде, • многопользовательский доступ к данным с поддержкой механизмов защиты для исключения взаимных помех, несанкционированного доступа, • многомерное представление данных, включая полную поддержку иерархий и множественных иерархий (ключевое требование OLAP), • возможность обращаться к любой нужной информации независимо от ее объема и места хранения.

Применения OLAP: • Анализ данных. Возможность анализировать значительные объёмы данных и быстрый отклик на запросы делают подобные системы незаменимыми для анализа продаж, маркетинговых мероприятий и других задач с большим объёмом данных. Microsoft Excel Pivot Tables, Microsoft Analysis Services, SAP BW, Oracle Essbase, Oracle OLAP, Cognos Power Play, Micro Strategy, Business Objects. • Финансовое планирование бюджетирование. Проигрывание различных вариантов событий при планировании. Расчет требуемых изменений детальных ячеек при изменении агрегированного значения. Это инструмент для анализа «что - если» (what - if). Microsoft Performance Pint, Oracle EPB, Oracle OFA, Oracle Hyperion Planning, SAP SEM, Cognos Enterprise Planning. • Финансовая консолидация. Согласно международным стандартам учёта предъявляются высокие требования к консолидации и выходу компаний на IPO (Initial Public Offering). OLAP-технологии позволяют ускорить расчёт консолидированных отчётов и повысить прозрачность всего процесса. Oracle FCH, Oracle Hyperion FM, Cognos Controller.

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

Многомерный подход к данным в OLAP-анализе Многомерный куб представлен набором измерений, соответствующим граням куба. Измерения используются для агрегирования данных. Они организуются в иерархию, состоящую из нескольких уровней: (Страна Регион Область Город Магазин) Единицей анализируемой информации считается произошедший факт.

Типы запросов к многомерной базе данных: 1. Запросы вида slice и dice (срезы куба) — формирование подмножества многомерного массива данных, соответствующего единственному значению одного или нескольких элементов измерений. Slice - двумерная проекция куба. Срез dice - это трёх и более мерная проекция куба.

2. Запросы вида drill-down (детализация) и roll-up (обобщение) — взаимообратные операции, которые используют иерархию измерений и меры для агрегирования. Направление детализации/обобщения может быть задано как по иерархии отдельных измерений, так и согласно прочим отношениям, установленным в рамках измерений или между измерениями. Консолидация Детализация

3. Создание кросс-таблиц — то есть совмещение данных из разных таблиц по заданным признакам; например, создается отчет, в котором сводятся данные об издержках и выручке по странам и временным периодам; Январь Февраль США Мексика Россия Продано штук 7000 600 950 12000 500 900 Сумма продажи 105000 9000 15000 12000 7600 13000 Расходы магазина 150000 15000 60000 3500 4500

4. Запросы вида drill-across комбинируют кубы, которые имеют одно или несколько общих измерений. С точки зрения реляционной алгебры такая операция выполняет слияние (join). 5. Построение трендов — зависимость числовых или качественных значений показателя от тех или иных параметров, например, времени, технологии и т. д. 6. Запросы вида ranking возвращают только те ячейки, которые появляются в верхней или нижней части упорядоченного определенным образом списка.

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

Подходы к реализации многомерной модели данных 1. Схема звезды - является проекцией куба на «реляционную плоскость» и состоит из одной таблицы фактов (fact table) и нескольких таблиц измерений (dimension table).

Схема звезды

2. Схема снежинки - часть таблиц измерений разбита на несколько связанных таблиц.

Схема снежинки

3. Схема созвездия - группа таблиц фактов (или кубов), построенная на общих измерениях.

Пример: клиент, работник, заказ, упаковщик, детали заказа, продукт, поставщик, категория продукта.

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

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

Сравнительный анализ некоторых OLAP-серверов Microsoft Analysis Services, Oracle Essbase, IBM Cognos TM 1, Pentaho Mondrian , Jedox Palo Критерий/OLAP-сервер MSAS Essbase TM 1 Mondrian Palo Поддерживаемые формы хранения данных (MOLAP/ROLAP/HOLAP) (+/+/+) (+/-/-) (-/+/-) (+/-/-) Создание вычисляемых меток + + + Создание пользовательских агрегирующих функций + + - Оптимизация схемы агрегирования + + - Масштабируемость + + + Удобные средства создания кубов и администрирования + + + Решение проблемы «взрыва данных» + + + Поддержка XMLA и MDX + + + Поддержка Unix-подобных операционных систем - + + Подробное документирование на русском языке + + - Цена продукта на минимальную конфигурацию 4000$ 40000$ 50000$ беспл.

Технология и примеры реализации (на примере Oracle Express )

Реляционная модель Многомерная модель Модель Месяц Объем Июнь Июль Август "Жигули" Июнь 12 "Жигули" 12 24 5 "Жигули" Июль 24 "Москвич" 2 18 No "Жигули" Август 5 "Волга" No 19 No "Москвич" Июнь 2 "Москвич" Июль 18 "Волга" Июль 19 Многомерное представление при описании структур данных Основными понятия: Измерение (Dimension); - это множество однотипных данных, образующих одну из граней гиперкуба. Имерения играют роль индексов, используемых для идентификации конкретных значений (Показателей), находящихся в Ячейках гиперкуба. -Время (Год) - 1994, 1995 -Менеджер - Петров, Смирнов, Яковлев Показатель (Measure)(ячейка (Cell)). - это поле (обычно цифровое), значения которого однозначно определяются фиксированным набором Измерений. В Oracle Express Server, Показатель может быть определен, как: • Переменная (Variable); • Формула (Formula); -Объем Продаж

первое Измерение - Модель автомобиля; второе Измерение - Менеджер, продавший автомобиль; третье Измерение - Время (Год); Используются два основных варианта организации данных: Гиперкубическая модель - все Показатели должны определяться одним и тем же набором Измерений. То есть только из-за того, что Объем Продаж определяется тремя Измерениями, при описании Показателя Рабочее Время Менеджера придется также использовать три Измерения и вводить избыточное для этого Показателя Измерение Модель Автомобиля; Поликубическая модель ( модели Oracle Express Server) - может быть определено несколько гиперкубов с различной размерностью и с различными Измерениями в качестве их граней. Двухмерный куб - для Показателя Рабочее Время Менеджера; Трехмерный куб - для Показателя Объем Продаж.

Операции манипулирования Измерениями (примеры) Формирование "Среза" (Slice) Модель Автомобиля = "ВАЗ 2108" - получим подмножество гиперкуба, содержащее информацию об истории продаж этой модели различными менеджерами в различные годы. Операция "Вращение"(Rotate). Модели автомобилей были перечислены по оси X, а Менеджеры по оси Y, можно поменять местами координаты (выполнить Вращение на 90 градусов). Отношения и Иерархические Отношения : Менеджер ->Подразделение; Модель Автомобиля ->Фирма-Производитель. Иерархические Отношения (Hierarchical Relationships): День -> Месяц -> Квартал -> Год; Менеджер -> Подразделение -> Регион -> Фирма -> Страна; Модель Автомобиля -> Завод-Производитель -> Страна.

Операция Агрегации (Drill Up) Объем продаж по подразделени, по региону или по стране. Операция Детализации (Drill Down) Переход от Региона к более точной информациио работе конкретного Подразделения или Менеджера.

Варианты пользовательских запросов Подразделение Менеджер Временной Вопрос интервал Отдел Петров 3 года На сколько процентов увеличились продажи "Жигулей" в Западном регионе после январской рекламной кампании в еженедельнике "Западный Вестник"? Финансовый Смирнов 5 лет Какие региональные подразделения превысили в отдел третьем квартале запланированные расходы на командировки и как это соотносится с ростом их прибыли (в абсолютных и относительных величинах)? Коммерческий Левшин 10 лет Какие два варианта скидок наиболее эффективны отдел в Западном регионе в летний период при продаже автомобилей "Жигули", на основе данных за последние 10 лет? Отдел развития Васильева 5 лет Как повлияло на объемы продаж открытие двух бизнеса новых отделений в Южном регионе и на какой процент могут увеличиться продажи в Северном регионе, если в этом году там будет открыто 3 новых офиса?

Встроенные в Oracle Express функции Функция Описание CUMSUM Вычисляет нарастающий итог LAG Вычисляет значения за предшествующий временной период LAGABSPCT Вычисляет процентную разницу между текущим и предыдущим значением и индикацию направления изменения LAGDIF Вычисляет разность между текущим и предшествующим значением LAGPST Вычисляет процентную разницу между текущим и предыдущим значением LEAD Вычисляет значения за последующий временной период MOVINGAVERAGE Вычисляет скользящее среднее MOVINGMAX Возвращает серию максимальных значений MOVINGMIN Возвращает серию минимальных значений MOVINGTOTAL Возвращает серию итогов Функции агрегации AVERAGE Возвращает среднее значение AVV Проверяет, есть ли значения, соответствующие заданному критерию COUNT Подсчитывает количество значений, соответствующих заданному критерию EVERY Проверяет все значения на соответствие заданному критерию LARGEST Вычисляет наибольшее значение NONE Проверяет на отсутствие значений, соответствующих заданному критерию SMALLEST Вычисляет наименьшее значение STDEV Вычисляет стандартное отклонение ROLLUP Вычисляет сумму значений на основе Иерархических Отношений TOTAL Вычисляет сумму значений Финансовые функции DEPRDECL, DEPRSL, Вычисляют амортизационные отчисления DEPSOYD Вычисляют расписание выплаты процентов по займам FINTSCHED, FPMTSCHED Вычисляет внутреннюю процентную ставку IRR Прочие функции REGRESS Вычисляет линейную регрессию FORECAST TREND Вычисляет линейный прогноз FORECAST EXPONENTIAL Вычисляет экспоненциальный прогноз FORECAST WINTERS Вычисляет прогноз на основе алгоритма Holt-Winters

Загрузка данных в Oracle Express Загрузка данных может производиться практически из внешнего источника данных, включая: • различные РСУБД ; • плоские файлы с фиксированной структурой записей; • электронные таблицы (Lotus 1 -2 -3, Ecxell и т. д. ); • в интерактивном режиме через специально написанные пользовательские приложения или непосредственно Oracle Express EDDi. E.

Программа для считывания данных из реляционной таблицы TIME_TABLE (R_MANAGER, R_DAY, R_WORK_TIME) и загрузки их в Показатель WORK_TIME: " Установить сеанс связи с Oracle 7 RDBMS sql. dbms = "odbc" odbc. source = "Sun_Oracle 7" sql connect "SCOTT" identified by "TIGER" " Определить курсор CURS_1 для чтения данных из таблицы TIME_TABLE и " определить имена колонок, из которых будут выбираться данные sql declare CURS_1 cursor for SELECT R_MANAGER, R_DAY, R_WORK_TIME FROM TIME_TABLE " Открыть курсор sql open CURS_1 В цикле считывать данные из реляционной " таблицы в соответствующие поля МБД sql fetch CURS_1 into : Manager, : Day, : Work_Time while sqlcode eq 0 do sql fetch CURS_1 into : . Manager, : . Day, : . Work_Time Doend " По достижению конца таблицы или при возникновении ошибок закончить сеанс sql close CURS_1 sql disconnect return

Визуальное представление отчетов в Microsoft Office Performance. Point Server 2007 и Microsoft Office Sharepoint Server 2010

Пример получения детализированной информации из OLAP-отчета

Пример использования дерева декомпозиции из OLAP-отчета

Пример навигации в OLAP-отчете

План-Факт по статьям бюджета

Пример зоны покрытия компании

Пример индикаторной панели

Пример OLAP-отчета, опубликованного в Excel

Сравнительный анализ OLAP и BPM технологий

История программных продуктов 1. Автоматизированные Рабочие Места (АРМ). 2. ERP (Enterprise Resource Planning) - в 90 -х годах - представление о ресурсах предприятия: материальные потоки, денежные средства, труд рабочих. - внедрение ERP-приложений занимает очень много времени; - одно эксплуатируемое ERP-приложение в большинстве случаев не охватывает полностью все участки предприятия; -имеющихся в ERP-приложениях аналитических средств не достаточно для того, чтобы обрабатывать накапливаемую информацию. 3. OLAP-системы ( On-Line Analytical Processing ) - приложения, предназначенные для анализа и обработки информации в реальном режиме времени - обладают гибкостью представления и обработки данных -структура таких приложений наиболее полно соответствала представлению человека о природе информации. 4. BPM (Business Performance Management, Управление Эффективностью Бизнеса).

Международная компания IDC объединила возможности OLAP и ERP приложений в новое семейство – BPM (Business Performance Management, Управление Эффективностью Бизнеса). BPM-системы позволяют связывать воедино такие понятия как миссия компании, стратегия развития, цели, долгосрочные планы, среднесрочные перспективы и конкретные бюджеты на ближайший период. На основе планов поставок оцениваются возможности по объемам производства и т. п. Скорректированные и дополненные на нижнем уровне цифры агрегируются вновь до общекорпоративного уровня. Процесс “двунаправленного” бюджетирования итеративно повторяется до тех пор, пока не будет составлен общесогласованный бюджет. Построение такой системы основано на мощнейшем механизме консолидации финансовых и количественных данных. Консолидация в огромной разветвленной структуре – очень трудно контролируемый процесс.

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

Литература: 1. Архипенков С. Oracle Express OLAP/ Москва, Диалог МИФИ, 2000. 2. Архипенков С. Хранилища данных. / Д. Голубев, О. Максименко – М. : Диалог- МИФИ, 2002. 3. Белов В. С. Информационно-аналитические системы. Основы проектирования и применения: учебное пособие, руководство, практикум / Московский государственный университет экономики, статистики и информатики. — М. , 2005. — 111 с. 4. Методы и модели анализа данных: OLAP и Data Mining. / А. Барсегян [и др. ]– СПб. : БХВ-Петербург, 2004. 5. http: //ru. wikipedia. org/wiki - свободная Интернет-энциклопедия. 6. http: //www. fpm. com/refer/codd. html- статья Е. Ф. Кодда, С. Б. Кодда и С. Т. Солли "OLAP для пользователей-аналитиков: информационно- технологический мандат". 7. http: //www. olap. ru – сайт о бизнес-аналитике. 8. http: //www. basegroup. ru/ - сайт аналитической платформы Deductor. 9. http: //www. bi-grouplabs. ru/Technology/t/OLAP/ - Применение OLAP технологий при извлечении данных. 10. http: //www. bi-grouplabs. ru/Rech/opttrade/analysis_client_base. html -Deductor: CRM - анализ клиентской базы

Благодарим за внимание!