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

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

Oracle OLAP
Oracle Data Mining
Oracle Partitioning
Oracle
Warehouse Builder
ХРАНИЛИЩА И ВИТРИНЫ ДАННЫХ
Oracle BI Suite EE PLUS
ИНСТРУМЕНТЫ БИЗНЕС-АНАЛИЗА
Oracle BI Suite SE ONE
Oracle BI SE
АНАЛИТИЧЕСКИЕ ПРИЛОЖЕНИЯ
Hyperion Planing
Hyperion Financial Mng
Strategic Finance
Аналитическая платформа Oracle (2010)
Oracle
Data Integrator
HYPERION
ESSBASE
ORACLE DATABASE
УПРАВЛЕНИЕ ЭФФЕКТИВНОСТЬЮ
ДЕЯТЕЛЬНОСТИ
BI APPLICATIONS
Marketing
Sales
Contact Center
Finance
Supply Chain
HR
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
----- ---------- --------- --------- ----------
7369 SMITH 800,00 800
7499 ALLEN 1600,00 300,00 1900
7521 WARD 1250,00 500,00 1750
7566 JONES 2975,00 2975
7654 MARTIN 1250,00 1400,00 2650
7698 BLAKE 2850,00 2850
7782 CLARK 2450,00 2450
7788 SCOTT 3000,00 3000
7839 KING 5000,00 5000
7844 TURNER 1500,00 0,00 1500
7876 ADAMS 1100,00 1100

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

Структура типичной 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 100000
Найти товары, по которым за период Октябрь 2009 были продажи на сумму более 100 000 в регионе Северо-Запад
Задача
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 > 100000
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])

Повышение производительности

Три (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.
1979 – Выпущена Oracle Version 2 (RDBMS)
1983 – MDS начинает переписывать Express на C с AED.
1985 – 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
1998-2000 – Oracle продолжает развитие Express и в конце 90х начинается работа по интеграции Express в ядро Oracle
2002 – Oracle 9.2.0 – Express интегрирован в ядро Oracle и назван OLAP option
2004 – 10.1.0 – OLAP добавлены возможности доступа по SQL и секционирование
2004 – 10.1.0 – В OLAP option появляется новая запатентованная технологния агрегирования и управления разреженными данными
2005 – 10.2.0 – первое терабайтное Аналитическое пространство
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
VP
Admin
Analyst
Senior Director
VP
President
День
Месяц
Квартал
Год
Уровневые
Родитель-потомок
(parent-child)
Director
Admin

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

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

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

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

Агрегации
Агрегации вычисляют значения на верхних уровнях на основе данных нижних уровней

Агрегации
Пример – Вычисление значения на конец периода

Иерархические распределения
Распределения вычиcляют значения нижних уровней на основе значений верхних и весов

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

Вычисляемые показатели
Вычисляемый показатель, демонстрирующий на верхних уровнях наличие проблемы на нижних
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
DOEND
RETURN _alert

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

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

JAN04
FEB04
MAR04
APR04
MAY04
JUN04
JUL04
AUG04
SEP04
OCT04
NOV04
DEC04
JAN05
FEB05
MAR05
APR05
MAY05
JUN05
JUL05
AUG05
SEP05
OCT05
NOV05
DEC05
Q1-04
Q2-04
Q3-04
Q4-04
Q1-05
Q2-05
Q3-05
Q4-05
Q1-04
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.

JAN04
FEB04
MAR04
APR04
MAY04
JUN04
JUL04
AUG04
SEP04
OCT04
NOV04
DEC04
JAN05
FEB05
MAR05
APR05
MAY05
JUN05
JUL05
AUG05
SEP05
OCT05
NOV05
DEC05
Q1-04
Q2-04
Q3-04
Q4-04
Q1-05
Q2-05
Q3-05
Q4-05
Q1-04
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 функций

Доступ с помощью любых инструментов
Реляционные View
Другие
Business
Objects
Cognos
Oracle 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 4000968
CY1999 TOTAL TOTAL TOTAL 330425
CY2003 TOTAL TOTAL TOTAL 534069
CY1998 TOTAL TOTAL TOTAL 253816
CY2005 TOTAL TOTAL TOTAL 565718
CY2006 TOTAL TOTAL TOTAL 584929
CY2004 TOTAL TOTAL TOTAL 587419
CY2000 TOTAL TOTAL TOTAL 364233
CY2002 TOTAL TOTAL TOTAL 364965
CY2001 TOTAL TOTAL TOTAL 415394
CY2000.Q1 TOTAL TOTAL TOTAL 88484
CY2001.Q2 TOTAL TOTAL TOTAL 97346
CY2001.Q3 TOTAL TOTAL TOTAL 105704
CY2005.Q3 TOTAL TOTAL TOTAL 138953
Агрегаты по годам
Агрегаты по кварталам
Итого по продуктам и др.
измерениям

Запросы к 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;

Материализованные представления
Хранение суммарных данных
Использование материализованных представлений при автоматической оптимизации запросов
Средства модификации запросов
Различные методы обновления (полное, частичное и отложенное)

Materialized Views
Создание MV при нерегламентированных запросах - проблема
Пользователи хотят ровый отклик
Появление большого количества MV
EXPENSE_DETAIL
day_id
item_id
emp_id
expense_amount
potential_fraud_cost

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

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

Oracle OLAP MDX Driver (http://simba.com)

Работа Simba MDX-OLAP Adapter

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

OLAP 11g и BI EE

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

Дополнительная информация
http://oracle.com
Email:
[email protected]
Блог:
http://www.oraclebi.ru/