Автор: Андрей Пивоваров
Источник: Блог Андрея Пивоварова
Когда Дмитрий Волков предложил мне выступить на семинаре Database Options Details с рассказом про OLAP опцию в 11g, я сначала подумал - да о чем тут рассказывать? Между девятой и десятой версией было много различий. А между 10 и 11 вроде ничего особо не было. Ну, кроме Cube-Organized Materialized Views. Потом решил, что на семинаре будет масса людей, которые вообще с OLAP не работали, ни с какой версией, поэтому им, возможно, будет интересно узнать об OLAP вообще. Заодно при подготовке и посмотрю внимательнее, что там изменилось. Но оказалось, что изменений неожиданно много.
Для экспериментов я использовал Oracle 11.1.0.6 c OLAP Patch A (#6459753) на VMware, для который был выделен 1Gb RAM.
В этой статье я ориентируюсь на людей, которые уже знакомы с Oracle Express или Oracle OLAP, ну или хотя бы в общих чертах представляет, что это. Для начального ознакомления предлагаю почитать мои статьи "Что такое OLAP" (Часть 1 Часть 2)
Интеграция метаданных с Oracle Database
Первое, что бросилось в глаза, это то, что действительно через любой SQL инструмент теперь стало очень удобно смотреть на данные, лежащие в OLAP кубах. Причем, как только вы создаете какой-то объект с помощью Analytic Workspace Manager (AWM) (показатель, измерение и т.д.) он тут же становится виден из SQL. Для этого автоматически создается обвязка из VIEW, каждый из которых содержит вызов CUBE_TABLE. Выглядит это примерно так:
1. CREATE OR REPLACE VIEW UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_VIEW AS
2. SELECT
3. "UNITS",
4. "SALES",
5. "COST",
6. "TIME",
7. "CHANNEL",
8. "CUSTOMER",
9. "PRODUCT"
10. FROM TABLE(<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_TABLE('GLOBAL.UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>') );
GLOBAL.UNITS_CUBE - это указание на то, что данные лежат в кубе UNITS_CUBE, который создавался в AWM. А CUBE_TABLE - табличная функция, такой универсальный вызов данных из объектов, лежащих в аналитическом прострастве.
В 10й версии тоже была возможность увидеть данные через SQL. Для этого была табличная функция OLAP_TABLE. (Собственно, она и сейчас есть, но смысла ее использовать, наверное, не очень много) Но чтобы заставить ее работать, нужно было предварительно создать кучу разных абстрактных типов данных, описания LIMITMAP и проч. Причем можно было ошибиться на любом шаге. В общем, для начинающих совершенно не подходило. Даже был такой плагин к AWM, который эти типы мог создать сам, что, в общем, частично проблему решало. Сейчас ничего делать не надо. Для каждого измерения и куба есть соответствующая вьюшка. Вы можете легко делать запросы к этим VIEW для того, чтобы например, фильтровать измерения по атрибутам. То есть, в вашем SQL запросе будут объединяться вьюшки измерений и куба, а оптимизатор сам знает как весь этот запрос протолкнуть через CUBE_TABLE внутрь OLAP движка, где он и будет отработан. Более того, никто не запрещает объединять данные из OLAP с данными из реляционки в любом запросе. Например, для того чтобы соединить агрегированные данные с детальными.
Кроме того, в 10g вызовы OLAP_TABLE работали довольно медленно. В 11g разница в скорости заметна невооруженным глазом. Причем, встроенная смотрелка AWM работает достаточно медленно, но если такой же запрос выполнить из SQL, он работает гораздо быстрее. Что говорит скорее всего о том, что смотрелка AWM генерирует запросы не так, или медленно обрабатывает результаты. Вообще, при желании это можно оттрассировать, но мне пока не хватило времени.
Увеличение скорости работы SQL связано с тем, что теперь обработка запросов оптимизатором сделана более умно и фильтрация значений по измерениям происходит внутри аналитического пространства, то есть в движке OLAP, а не в самом Oracle, как это было в 10g. Если сравнить запрос SQL и аналогичный ему DML запрос выполнять из OLAP Worksheet, то разница в скорости не заметна. То есть, SQL отрабатыват примерно также как и запрос непосредственно к движку.
Можно сделать вывод, что связка SQL - CUBE_TABLE - стала вполне пригодной для того, чтобы пользоваться ей для доступа к OLAP.
Как следствие, к данным OLAP можно обращаться любым ROLAP инструментом, например Oracle Business Intelligence Enterprise Edition, который работает с базой Oracle через SQL. Другие API не обязательны.
При этом, любые манипуляции с движком OLAP, если это необходимо, можно делать используя пакет DBMS_AW.
Упрощение стандартной формы
Когда я запустил новый AWM, оказалось, что в нем исчезла возможность смотреть структуру AW в режиме Object View.
Тут надо сделать небольшое отступление.
В Oracle Express были пользовательские объекты. Были и внутренние объекты, хранящие метаданные, которые физически были реализованы объектами Express, такими как измерения, отношения, переменные и и.д. Но внутренние были в основном скрыты от конечного пользователя. Да и большой небходимости в них залезать не было. В 9й версии, когда OLAP стал частью СУБД Oracle, сначала все оставалось, как было в Express, но потом, в районе 9.2.0.4 (точно не помню) была придумана так называемая стандартная форма (Standard Form, SF)
Это специальная обвязка метаданных, нужная для интеграции с СУБД Oracle. Но проблема в том, что те объекты, которые создавались в AWM и которые пользователь считал измерениями, показателями и т.д., на самом деле физически лежат совершенно в других объектах, а добраться до них можно было лишь расшифорвав сложный слой метаданных SF. А SF сама по себе менялась с каждым патчсетом. Express всегда славился своим очень гибким языком, который сейчас называется OLAP DML. На нем можно было написать очень сложные расчетные формулы и программы, для работы с многомерными объектами. Но сложность SF по сути убивала эту возможность, так как было довольно сложно расшифровывать метаданные, к тому же, велика вероятность, что в следующем релизе что-то поменяется и ваша программа перестанет работать. А SF считается объектом внутренним и поддержка прошлых версий SF не гарантируется.
Поэтому, в AWM прошлых версий было два вида представления объектов OLAP - Model View и Object View. Model View показывал как объекты выглядят через призму SF, Object View - как они хранятся на самом деле. Что и говорить, найти соответствия между двумя предствлениями было очень сложно.
В 11 версии все стало гораздо проще. Создаем мы из AWM куб UNITS_CUBE, а в нем показатель SALES. Теперь в OLAP DML мы можем увидеть объект UNITS_CUBE_SALES, название которого составлено из названия куба и названия показателя. Это же распространяется и на вычисляемые показатели. У нас теперь опять есть простой путь использования наших показателей в формулах и программах. И не важно, что на самом деле, это не куб, а формула, которая смотрит на другой объект. Для нас уже не важно знать, как именно он хранится.
Хотя, если кому интересно, можно и посмотреть:
- ->dsc UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/i
mm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_SALES - DEFINE UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-
glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>
_SALES FORMULA LOCKDFN NUMBER WITH NULLTRACKING - <TIME CHANNEL CUSTOMER PRODUCT>
- EQ this_aw!UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-
glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_STORED
(this_aw!UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_MEASURE_DIM 'SALES') - ->dsc units_<A class=C title=cube href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==
/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">cube</A>_stored - DEFINE UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_STORED VARIABLE
READONLY LOCKDFN NUMBER - WITH NULLTRACKING WITH AGGCOUNT CHANGETRACKING
- <UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_MEASURE_DIM
TIME UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_COMPOSITE - <CHANNEL CUSTOMER PRODUCT>>
- ->dsc units_<A class=C title=cube href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">cube</A>_composite
- DEFINE UNITS_<A class=C title=CUBE href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">CUBE</A>_COMPOSITE
COMPOSITE READONLY LOCKDFN - <CHANNEL CUSTOMER PRODUCT> COMPRESSED
Напомню, что через SQL тот же куб можно увидеть через вьюшку UNITS_CUBE_VIEW, а показатель SALES соответственно будет UNITS_CUBE_VIEW.SALES
SELECT time,
product,
customer,
channel,
sales
FROM units_<A class=C title=cube href="http://www.infology.ru/gloss/imm-glossary/Title/Y3ViZQ==/Referer/LzIwMDkvMDQvMzAvODE4Lw==/">cube</A>_view
WHERE rownum < 15;
TIME PRODUCT CUSTOMER CHANNEL SALES
--------- ------- -------- ------- ----------
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
И вот тут видно основное отличие данных, которые достаются из OLAP от данных, которые берутся из обычных таблиц Oracle. Обратите внимание на первую строчку, где четыре слова "TOTAL". В этой строке - агрегат (сумма) по продажам по всем измерениям. Если бы мы хотели достать сумму по всем измерениям из обычной таблицы, нам нужно было написать что-то вроде
select sum(sales) from units_fact
А OLAP уже выдает все возможные агрегаты, поэтому вместо суммирования, нам нужно в условиях SQL запроса WHERE описать фильтр этой строки. Само суммирование делать не надо. Сумму уже посчитал OLAP сервер.
Это естественно накладывает некоторые условия на программирование SQL над OLAP. Например, в том же BI EE нужно специальным образом описать правила обсчета уровней иерархий, что бы не пошло суммирование по уже агрегированным данным.
Вторая тонкость в том, что если я напишу запрос по данным о продаже определенного продукта, и не укажу условия по другим измерениям, то из таблицы фактов мне выпадут все продажи этого продкута. Но если тоже самое сделать над OLAP, то кроме фактов детального уровня выйдут и все возможные комбинации агрегатов по разным измерениям, что в зависимости от структуры куба может быть объемом в десятки раз превышающим количество детальных фактов.
Иными словами, когда вы пишете запрос к OLAP нужно всегда ограничивать все измерения.
Продолжение следует.