Новые возможности Oracle OLAP 11g Андрей Пивоваров Старший менеджер по технологическому консалтингу Oracle CIS

Oracle OLAP Oracle Data Mining Oracle Partitioning Oracle Warehouse Builder ХРАНИЛИЩА И ВИТРИНЫ ДАННЫХ Oracle BI Suite EE PLUS ИНСТРУМЕНТЫ БИЗНЕС-АНАЛИЗА Oracle BI Suite SE ONEOracle BI SE АНАЛИТИЧЕСКИЕ ПРИЛОЖЕНИЯ Hyperion Planing Hyperion Financial Mng Strategic Finance Аналитическая платформа Oracle (2010) Oracle Data Integrator HYPERION ESSBASE ORACLE DATABASE УПРАВЛЕНИЕ ЭФФЕКТИВНОСТЬЮ ДЕЯТЕЛЬНОСТИ BI APPLICATIONS MarketingSalesContact Center FinanceSupply ChainHR Scorecard GoldenGate

Почему популярен Excel?

Вычисления внутри строки на SQL SQL> select t.empno,t.ename,t.sal,t.comm, sal+nvl(comm,0) summ from emp t; EMPNO ENAME SAL COMM SUMM SMITH 800, ALLEN 1600,00 300, WARD 1250,00 500, JONES 2975, MARTIN 1250, , BLAKE 2850, CLARK 2450, SCOTT 3000, KING 5000, TURNER 1500,00 0, ADAMS 1100,

Вычисления между строк на SQL select (select sal from emp where empno=7499) - (select sal from emp where empno=7521) diff from dual; DIFF

Структура типичной OLTP системы

Запрос над OLTP

Куб OLAP Время Продукты Регионы Прибыль Агрегация Распределение Прогноз Рост прибыли Прибыль за год Прибыль Доля прибыли Единица Тип продукта Всего по продуктам МесяцКварталГод

Вычисления на Oracle OLAP DML RPR hr_cube_sal(emp_id '7499')- hr_cube_sal(emp_id '7521) (В Excel-е B10=A5-A6 ) TIME SAL JAN10 350,00 FEB10 350,00 MAR10 450,00

Сравнение OLAP DML и SQL limit geography to NW' limit time to 'OCT2009' limit product to dollars gt Найти товары, по которым за период Октябрь 2009 были продажи на сумму более в регионе Северо-Запад Задача OLAP DML Select p.prod_name, g.geog_name, t.time_name, f.sales from fact f, proddim p, geogdim g, timedim t where f.prod_id = p.prod_id and f.geog_id = g.geog_id and t.time_id = f.time_id and g.geog_id = NW' and t.time_id = 'OCT2009' and sales > SQL

SQL Model Выборка рассматривается в виде куба Введен оператор MODEL С помощью модели куб, т.е. выборка, может изменяться и расширяться Мощнейшие возможности по формированию сложных вычислений SELECT * FROM sales s MODEL DIMENSION BY (Product,Version) MEASURES (units,price,sale) RULES UPSERT (sale['Total','Total'] = SUM(sale)[ANY,ANY])

C OLAP Без OLAP Больше времени Меньше времени Предсказуемые Простые вычисления Непредсказуемые Сложные вычисления Время отработки запроса Повышение производительности

Три (M)OLAP сервера Oracle Oracle Express Server не развивается, используется, например, с OFA Oracle OLAP option опция СУБД Oracle, наследница Express Server Oracle Hyperion Essbase появился с приобретеним Hyperion

Краткая история Oracle OLAP (1/2) 1967 – Leonard M. Lodish и John D.C. Little основали Management Decision Systems (MDS) 1977 – Software Development Laboratories (SDL), предшественник Oracle. Основатели Larry Ellison, Bob Miner и Ed Oates – Выпущена Oracle Version 2 (RDBMS) 1983 – MDS начинает переписывать Express на C с AED – Information Resources, Inc. (IRI) приобретает MDS. Express полностью переписан на C 1993 – E.F. Codd придумал термин OLAP и E.F. Codd, S.B. Codd и C.T. Salley опубликовали работу Providing OLAP to User-Analysts: An IT Mandate где сформулировали 12 принципов OLAP

Краткая история Oracle OLAP (2/2) 1995 – Oracle приобретает Express у IRI – Oracle продолжает развитие Express и в конце 90х начинается работа по интеграции Express в ядро Oracle 2002 – Oracle – Express интегрирован в ядро Oracle и назван OLAP option 2004 – – OLAP добавлены возможности доступа по SQL и секционирование 2004 – – В OLAP option появляется новая запатентованная технологния агрегирования и управления разреженными данными 2005 – – первое терабайтное Аналитическое пространство 2007 – Выпущена Oracle Database 11g 2007 – Oracle покупает Hyperion 2007 – 11.1 – OLAP option интегрирована с механизмом query rewrite для использования кубов в качестве материализованных представлений.

Что такое Oracle OLAP? Многомерная СУБД, встроенная в СУБД Oracle (MOLAP) Основное назначение – анализ данных Построена на основе Oracle Express Server Данные хранятся в т.н. Аналитических пространствах (Analytical Workspaces)

Oracle OLAP и СУБД Oracle Реляционые таблицы Аналитические пространства Oracle Database 11g

Отличительные особенности OLAP Работа не только с детальными данными, но и с агрегатами При этом разработчик приложений не думает о том как рассчитывать агрегаты Мощные аналитические возможности Работа с множеством иерархий, в том числе и Parent- Child Возможность записывать факт в произвольное место куба Часто используется в финансовых системах, когда агрегат не равен сумме детальных данных

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

Типы иерархий Director VPAdmin Analyst Senior Director VP President День Месяц Квартал Год УровневыеРодитель-потомок (parent-child) Director Admin

Analytic Workspace Manager Enterprise Manager для OLAP Некоторые вещи без AWM сделать крайне сложно

Возможности Oracle OLAP 23 Прогнозы Вычисляемые показатли Присвоения Куб Распределения Агрегации Данные и правила вычисление интегрированы Большая библиотека для вычислений Многомерные вычисления

Вычисления Вычисления определяются один раз Работают по всем измерениям и уровням Инструменты просто извлекают результаты 24

Вычисления могут использовать данные в других кубах 25 Факт Время Организация Регион План Организация Время budget_cube.variance = actual_cube.actual[geography TOTAL] – budget_cube.budget Вычисления между кубами

Агрегации Агрегации вычисляют значения на верхних уровнях на основе данных нижних уровней 26 Company 75 Brand 40 Item 10 Item 30 Brand 35 Item 20 Item 15

Агрегации Пример – Вычисление значения на конец периода Q1 165 Jan 125 Feb 140 Mar 165 Q2 145 Apr 135 May 120 Jun 145 Q3 110 Jul 130 Aug 155 Sep 110 Q4 175 Oct 120 Nov 165 Dec 175

Иерархические распределения Распределения вычиcляют значения нижних уровней на основе значений верхних и весов 28 Company 1,000 emp. $1,200,000 East 750 emp. $900,000 Southeast 200 emp. $240,000 Mid-Atlantic 150 emp. $180,000 Northeast 400 emp. $480,000 West 250 emp. $300,000 Midwest 75 emp. $90,000 West 175 emp. $210,000

Вычисляемые показатели 29 net_income = operating_income – taxes operating_income = gross_margin - (marketing + selling_exp + r_d) gross_margin = revenue - cogs net_income = operating_income – taxes operating_income = gross_margin - (marketing + selling_exp + r_d) gross_margin = revenue - cogs

Вычисляемые показатели 30 Вычисляемый показатель, демонстрирующий на верхних уровнях наличие проблемы на нижних VRB _alert NUMBER VRB _product NUMBER _product = product + 0 TEMPSTAT product time DO LIMIT product TO DESCENDANTS USING product_parentrel _product IF STATLEN(product) EQ 0 THEN _alert = na ELSE DO LIMIT product KEEP sales_cube_sales_ytd_pr_yr_pct LT 0 AND sales_cube_sales_share_tot_pro GT.25 IF STATLEN(product) GT 0 THEN _alert = -1 ELSE _alert = na DOEND RETURN _alert VRB _alert NUMBER VRB _product NUMBER _product = product + 0 TEMPSTAT product time DO LIMIT product TO DESCENDANTS USING product_parentrel _product IF STATLEN(product) EQ 0 THEN _alert = na ELSE DO LIMIT product KEEP sales_cube_sales_ytd_pr_yr_pct LT 0 AND sales_cube_sales_share_tot_pro GT.25 IF STATLEN(product) GT 0 THEN _alert = -1 ELSE _alert = na DOEND RETURN _alert

Вычисляемые показатели Большое количсетво шаблонов вычислений Можно расширять набор шаблонов 31

Вычисляемые показатели Шаблоны помогают создать аналитическое выражение 32 Percent Change in YTD Sales from Year Ago Тип вычислений Описание Автоматически генерируемое выражение

JAN04FEB04MAR04APR04MAY04JUN04JUL04AUG04SEP04OCT04NOV04DEC04JAN05FEB05MAR05APR05MAY05JUN05JUL05AUG05SEP05OCT05NOV05DEC05 Q1-04Q2-04Q3-04Q4-04Q1-05Q2-05Q3-05Q4-05 Q1-04 SUM(sales) OVER HIERARCHY (global.time.calendar_year BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL global.time.calendar_year) Cumulative Total (or AVG, MIN, MAX) Cumulative total (sum) of Sales in the Time dimension and Calendar Year hierarchy within ancestor at level calendar year. Total from beginning member to current member.

JAN04FEB04MAR04APR04MAY04JUN04JUL04AUG04SEP04OCT04NOV04DEC04JAN05FEB05MAR05APR05MAY05JUN05JUL05AUG05SEP05OCT05NOV05DEC05 Q1-04Q2-04Q3-04Q4-04Q1-05Q2-05Q3-05Q4-05 Q1-04 LAG_VARIANCE_PERCENT(global.units_cube.sales,1) OVER HIERARCHY (global.time.calendar_year BY ANCESTOR AT LEVEL global.time.year POSITION FROM BEGINNING) Percent Difference From Parallel Period Percent difference from parallel period for Sales in the Time dimension and Calendar Year hierarchy 1 Year ago based on position from the beginning of Year.

Присвоения Присвоение записывает данные в ячейки куба Можно использовать для Что-Если анализа С учетом иерархий и уровней. Пользователь может определять свои правила вычислений Фильры, ранжирования, временные ряды, более 450 функций 35

Доступ с помощью любых инструментов Реляционая таблицаМногомерный куб Oracle Database 11g Реляционные View Другие Business Objects CognosOracle BI EE Ускорение для реляционных приложений и отчетных средств

11g Cube Fact View DESC units_cube_view; Name Null Type TIME VARCHAR2(100) CHANNEL VARCHAR2(100) CUSTOMER VARCHAR2(100) PRODUCT VARCHAR2(100) UNITS NUMBER SALES NUMBER COST NUMBER SALES_PRIOR_PERIOD NUMBER SALES_DIFF_PRIOR_PERIOD NUMBER SALES_PCT_DIFF_PRIOR_PERIOD NUMBER Keys Stored facts Calculated facts

11g Cube Fact View SELECT time, product, customer, channel, units FROM units_cube_view WHERE rownum < 15; TIME PRODUCT CUSTOMER CHANNEL UNITS TOTAL TOTAL TOTAL TOTAL CY1999 TOTAL TOTAL TOTAL CY2003 TOTAL TOTAL TOTAL CY1998 TOTAL TOTAL TOTAL CY2005 TOTAL TOTAL TOTAL CY2006 TOTAL TOTAL TOTAL CY2004 TOTAL TOTAL TOTAL CY2000 TOTAL TOTAL TOTAL CY2002 TOTAL TOTAL TOTAL CY2001 TOTAL TOTAL TOTAL CY2000.Q1 TOTAL TOTAL TOTAL CY2001.Q2 TOTAL TOTAL TOTAL CY2001.Q3 TOTAL TOTAL TOTAL CY2005.Q3 TOTAL TOTAL TOTAL Агрегаты по годам Агрегаты по кварталам Итого по продуктам и др. измерениям

Запросы к Cube Views Explain Plan SELECT t.long_description time, g.long_description geography, c.long_description channel, f.sales sales FROM time_calendar_view t, product_standard_view p, geography_regional_view g, channel_sales_channel_view c, sales_cube_view f WHERE t.dim_key IN('CY2007','Q1CY2007', 'FEB2007') AND p.dim_key = 'ALL_PRODUCTS' AND g.level_name = 'REGION' AND c.dim_key = 'INTERNET' AND t.dim_key = f.TIME AND p.dim_key = f.product AND g.dim_key = f.geography AND c.dim_key = f.channel ORDER BY t.end_date;

Материализованные представления Объемы продаж по Западному и Восточному регионам за последние 3 месяца ? x (30 GB) Продажи(детальныеданные)Продажи(детальныеданные) Хранение суммарных данных Использование материализованных представлений при автоматической оптимизации запросов Средства модификации запросов Различные методы обновления (полное, частичное и отложенное) (2 GB) Продажи по регионам по месяцам

Materialized Views Создание MV при нерегламентированных запросах - проблема Пользователи хотят ровый отклик Появление большого количества MV EXPENSE_DETAIL day_id item_id emp_id expense_amount potential_fraud_cost EXPENSE_YDT year_id district_id expense_amount potential_fraud_cost Year, Region EXPENSE_MDT year_id district_id expense_amount potential_fraud_cost Month, District EXPENSE_MEC month_id category_id emp_id expense_amount potential_fraud_cost Month, Employee, Category EXPENSE_YEC year_id category_id emp_id expense_amount potential_fraud_cost Year, Employee, Category EXPENSE_YRC year_id category_id region_id expense_amount potential_fraud_cost Year, Region, Category EXPENSE_QDI qtr_id item_id district_id expense_amount potential_fraud_cost Qtr, District, Item EXPENSE_XXX XXX_id XXX_id XXX_id expense_amount potential_fraud_cost Org Lvl, Time Lvl, Cat Lvl EXPENSE_XXX XXX_id XXX_id XXX_id expense_amount potential_fraud_cost EXPENSE_XXX XXX_id XXX_id XXX_id expense_amount potential_fraud_cost EXPENSE_XXX XXX_id XXX_id XXX_id expense_amount potential_fraud_cost EXPENSE_YRT year_id total_category_id region_id expense_amount potential_fraud_cost Year, District

11g Cube-Organized Materialized Views Cube MV Cube Organized MV показывает агрегированные данные Tables Детальные данные в обычных таблицах SQL запрос Агрегированные данные в кубе СУБД обновляет данные QueryRewrite Детальные данные в реляционных таблицах Приложение генерирует SQL запрос Агрегированные данные в OLAP кубе Доступ к агрегированным данным в кубе – автоматический, через query rewrite Приложение не меняется, но запросы работают быстрее

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

Oracle OLAP MDX Driver (

Работа Simba MDX-OLAP Adapter MS EXCEL Pivot Table Simba MDX Adapter MDX запрос Данные Возврат данных Oracle OLAP Выполнение запроса Фильтрация, агрегация,вычисления Oracle Client SQL Данные (записи)

Для чего можно использовать OLAP 11g Как MOLAP сервер Как вычислительное ядро для приложений на Oracle Как новый тип MV Как ускоритель запросов по агрегированным данным

OLAP 11g и BI EE

О чем помнить при работе с OLAP Oracle OLAP – другая СУБД, со своими принципами и подходами к оптимизации. Частая жалоба – люди, не имеющие опыта с OLAP сталкиваются с тем, что все не работает «быстро» и сразу Существуют миф, что если запросы на огромном объеме данных работают медленно – данные нужно переложить в OLAP и все будет работать быстро. OLAP опция динамично развивается, поэтому лучше использовать как можно более позднюю версию и патчи. Olap 10g сильно отличается от OLAP 9i, а OLAP 11g отличается от OLAP 10g

Дополнительная информация Блог:

A Q &