OLAP-анализ данных: решаемые задачи, технологии, актуальные проблемы Кудрявцев Юрий, ВМиК МГУ 22 февраля ACM SIGMOD Москва

Организационное 1,5 часа Вашего времени Вопросы приветствуются

План Задачи анализа, определение OLAP OLAP-расширения SQL MOLAP продукты Вопросы, перспективные технологии

Появление термина OLAP Статья Кодда Providing OLAP for End-User Analysis отосланная в IEEE Computer – 12 признаков OLAP-системы

12 признаков OLAP. 1.Многомерная концепция данных. OLAP оперирует CUBE данными, которые являются многомерными массивами данных. Число измерений OLAP кубов не ограничено. 2.Прозрачность. OLAP системы должны опираться на открытые системы, поддерживающие гетерогенные источники данных. 3.Доступность. OLAP системы должны представлять пользователю единую логическую схему данных. 4.Постоянная скорость выполнения запросов. Производительность не должна падать при росте числа измерений. 5.Клиент\сервер архитектура. Системы должны базироваться на открытых, модульных системах. 6.Различное число измерений. Системы не должны ограничиваться 3хмерной моделью представления данных. Причем измерения должны быть эквивалентны по применению любых функций.

12 признаков OLAP. 7.Динамическое представление разреженных матриц. Идея относится к «нулям» в реляционных базах данных и сжатию больших файлов, «разреженная матрица» - матрица, не каждая ячейка которой содержит данные. OLAP системы должны содержать средства хранении и обработки больших объемов данных. 8.Многопользовательская поддержка. OLAP системы должны поддерживать многопользовательский режим работы. 9.Неограниченные многомерные операции. Аналогично, требованию о различном числе измерений : все измерения считаются равными и многомерные операции не должны накладывать ограничений на отношения между ячейками. 10.Интуитивно понятные инструменты манипулирование данными. В идеале, пользователи не должны пользоваться различными усложненными меню и прочим, чтобы сформулировать многоуровневые запросы. 11.Гибкая настройка конечных отчетов. Пользователи должны иметь возможность видеть только то, что им необходимо, причем все изменения данных должны немедленно отображаться в отчетах. 12.Отсутствие ограничений на количество измерений и уровней агрегации данных

НО Последние 4 страницы статьи посвящены Essbase – проверка соответствия OLAP критериям Жена Кодда в это время работает в Arbor Software (разработчик Essbase) Arbor Software спонсировало написание статьи

Результат Журнал Computer после публикации официально изымает статью Кодда из своих архивов

Простое определение OLAP Nigel Pendse -- olapreport.com FASMI –FAST –Analysis –Shared –Multidimensional

Для задач анализа мы вводим «многомерность» данных В SQL измерения – обычно аргументы запроса с Group By Drill-up\down, slice&dice 2 задачи для примера: Кросс-таблица Нарастающий итог за квартал

OLAP-расширения SQL Группировка данных –Grouping Set –Rollup –Cube Row_Number(), Rank Window By SQL-1999

На чем запускать запросы MySQL Microsoft SQL Server Oracle IBM DB2 Postgres

Нужен ORACLE OLAP Option (EE) Таблицы не создаются – достаточно прав на TEMP

Grouping Set (grouping_sets.sql) EMPIDDEPTJOB_TITLE 1hrmanager 2itsysadmin 3itdba 4hrclerk 5itnetworkadmin 6hrclerk 7itnetworkadmin 8itclerk DEPTJOB_TITLE STAFF_QUANTITY hr3 it5 networkadmin2 dba1 clerk3 sysadmin1 manager1 select dept,job_title, count(*) as staff_quantity from emp_data group by grouping sets (dept,job_title)

Rollup (rollup.sql) EMPIDDEPTJOB_TITLE 1hrmanager 2itsysadmin 3itdba 4hrclerk 5itnetworkadmin 6hrclerk 7itnetworkadmin 8itclerk DEPTJOB_TITLESTAFF_QUANTITY hrclerk2 hrmanager1 hr3 itdba1 itclerk1 itsysadmin1 itnetworkadmin2 it5 8 select dept,job_title, count(*) as staff_quantity from emp_data group by rollup (dept,job_title) Group By Rollup (a,b,c) == Group by grouping sets (a,b,c)(a,b)(a)() EMP_Data

Cube (cube.sql) EMPIDDEPTJOB_TITLE 1hrmanager 2itsysadmin 3itdba 4hrclerk 5itnetworkadmin 6hrclerk 7itnetworkadmin 8itclerk DEPTJOB_TITLESTAFF_QUANTITY 8 dba1 clerk3 manager1 sysadmin1 networkadmin2 hr3 hrclerk2 hrmanager1 it5 itdba1 itclerk1 itsysadmin1 itnetworkadmin2 select dept,job_title, count(*) as staff_quantity from emp_data group by cube (dept,job_title) ; Group By Cube == Group by grouping sets (a,b,c)(a,b)(b,a)(b,c)(a)(b)(c)() EMP_Data

Row_Number Возвращает номер кортежа Варианты определения: –IDENTITY (MicroSoft) – колонка в таблице –ROWID (ORACLE) – физический номер в сегменте –ROW_NUMBER() – функция (Sybase WatCom SQL)

Row_Number (rownum.sql) select dept,job_title, row_number() over (order by empid) as row_num from emp_data; DEPTJOB_TITLEROW_NUM hrmanager1 itsysadmin2 itdba3 hrclerk4 itnetworkadmin5 hrclerk6 itnetworkadmin7 itclerk8

Ранжирование RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST -- разные типы ранжирования по значению мерыRANK DENSE_RANK PERCENT_RANK CUME_DIST

Window By (window_by.sql) REGIONMONTHSALES south120 south230 south320 south440 south550 south660 north15 north27 north310 north420 north55 north610 REGIONMONTHSALESMOVING_AVERAGE north155 north2712 north31022 north42037 north5535 north61035 south12020 south23050 south32070 south44090 south south select region,month, sales, sum(sales) over (partition by region order by month asc rows 2 preceding) as moving_average from sales_data Moving_Total – нарастающий итог за квартал

Oracle Model By MODEL [main] [reference models] [PARTITION BY ( )] DIMENSION BY ( ) MEASURES ( ) [IGNORE NAV] | [KEEP NAV] [RULES [UPSERT | UPDATE] [AUTOMATIC ORDER | SEQUENTIAL ORDER] [ITERATE (n) [UNTIL ] ] ( =... )

REGIONMONTHSALES south120 south230 south320 south440 south550 south660 north15 north27 north310 north420 north55 north610 REGIONMONTHSALES north15 north27 north310 north420 north55 north610 north77,5 south120 south230 south320 south440 south550 south660 south755 select region,month, sales from sales_data model partition by (region) dimension by (month) measures (sales) rules (sales[7] = (sales[5]+sales[6])/2) order by region, month; Oracle Model By (model_by_simple.sql) Считаем продажи в 7ом месяце

Oracle Model By (model_by_running_total.sql) select region,month, sales,sales_rt from sales_data model partition by (region) dimension by (month) measures (sales,0 sales_rt) rules (sales_rt[any] = case when cv(month) = 1 then (sales[cv(month)]) else (sales_rt[cv(month)-1] + sales[cv(month)]) end ) order by region, month; Running_Total (Sales_RT) – накопленный итог продаж

REGIONMONTH SALES south120 south230 south320 south440 south550 south660 north15 north27 north310 north420 north55 north610 REGIONMONTHSALESSALES_RT north155 north2712 north31022 north42042 north5547 north61057 south12020 south23050 south32070 south south south Oracle Model By (model_by_running_total.sql) Running_Total (Sales_RT) – накопленный итог продаж

Oracle Model By (model_by_iterate.sql) select region,month, sales, sales_forecast from sales_data model return updated rows partition by (region) dimension by (month) measures (sales, 0 sales_forecast) rules ITERATE(100) UNTIL (ABS((PREVIOUS(sales_forecast[6]) - sales_forecast[6]) ) < ) ( sales_forecast[any] = case when sales_forecast[cv(month)-1] > 0 then (sales[cv(month) -1] + sales_forecast [cv(month)-1])/2 - - (sales_forecast[cv(month)] + else (1.5 * sales[cv(month)-1]) end ) order by region, month Прогноз продаж считаем на базе предыдущего прогноза и факта

Oracle Model By (model_by_iterate.sql) REGIONMONTHSALES south120 south230 south320 south440 south550 south660 north15 north27 north310 north420 north55 north610 REGIONMONTHSALESSALES_FORECAST north15 north277,5 north3107,25 north4208,625 north5514,3125 north6109,65625 south120 south23030 south32030 south44025 south55032,5 south66041,25 Прогноз продаж считаем на базе предыдущего прогноза и факта

Проблемы ROLAP Хранение агрегатов (материализация) или вычисление на лету Моделирование измерений и вычислений Схемы хранения «снежинка» и «звезда» (Кимбалл и Инмон)

Достаточно ли подобных расширений SQL?

4 типа OLAP систем по Кодду Categorical – простые запросы Exegetical – многомерный анализ, drill- up\down Contemplative – изменение расчетных результатов, при изменении входных параметров Formulaic – задание правил поведения системы и цель, сценарное моделирование

Задачи 3его, 4го типа Goal-Seeking, BackSolving – многомерные обратные расчеты уравнений Вводим данные в ячейку, являющуюся пересечением формул по двум измерениям (например, Продажи по всем продуктам (сумма), в Год (сумма)) Пересчет по профилям

Многомерные Базы Данных Статисческие базы данных (SBD) Модель данных изначально включающая измерения (с иерархиями) как объекты Особая роль измерения Время Использование многомерных формул

MOLAP-продукты Essbase Express Ms Analysis Services

Hyperion Essbase Extended Spread Sheet database Arbor Software Роберт Эйрль – column-based storage

Oracle Express MIT (1975) ->…->IRI Software->Oracle (1995) Express 4GL Схема работы Model By

MicroSoft Analysis Services – HOLAP движок Новая разработка Panorama -> Microsoft MDX – новый язык запросов к многмерным данным (поддерживается Hyperion)

MDX (пример синтаксиса) SELECT { [Measures].[Dollar Sales], [Measures].[Unit Sales] } on columns, { [Time].[Q1, 2005], [Time].[Q2, 2005] } on rows FROM [Sales] WHERE ([Customer].[MA])

А что же IBM? Перепродавали Essbase, как IBM OLAP Server, прекратили в 2006 Сделали IBM Cube Views – не продавался

Open-Source Mondrian – ROLAP, поддерживает MDX PALO – memory-based MOLAP, новая разработка

Стандарты OLAP OLAP Council. –JOLAP – поддерживался Hyperion и Mondrian => мертв –APB-1 Benchmark – набор тестов для определения производительности OLAP- движка XML\A – стандарт взаимодействия с MS Analysis Services (описание сервиса). Использует MDX.

Выводы и замечания Что такое OLAP? Нет стандартов, ни в модели данных, ни в языках Статистические пакеты сближаются с OLAP-приложениями Сервера отчетности не используют возможности SQL

Новые решения Языки векторного программирования (APL, K) In-memory базы данных (TimesTen, Applix, KX) как буфера для хранения агрегатов в СУРБД. Streaming OLAP.

Рекомендуемая Литература Codd E.F. Providing OLAP for end-user analysys: An IT mandate. Thomsen E. OLAP Solutions: Building Multidimensional Information Systems. Second Edition. Wiley, Rafanelli M. Multidimensional Databases Problems and Solutions. Idea Grouping Publ., Hershey, London, Melbourne, Singapore, Beijing, Celko J. Analytics and OLAP in SQL. Morgan Kaufmann, 2006