Пользователи Oracle при слове "OLAP" чаще всего вспоминают Oracle Express. И не все знают, что для OLAP-работы вполне хватает и функциональности, присутствующей в Oracle8i. О чем конкретно идет речь, и как упомянутая функциональность соотносится с возможностями Oracle Express, говорится ниже.
Туман вокруг OLAP
Разработчики OLAP-продуктов изрядно потрудились не только над самими продуктами, но и над созданием терминологического тумана вокруг продуктов. Видимо ( sic ! ), в тумане легче продавать. В этой заметке не ставится попыткой разгребание авгиевых конюшен терминологии, но некоторые понятия нужно уточнить.
С OLAP в сегодняшней торгово-популярной литературе связывается (по преимуществу) визуальный анализ данных, производимый в результате манипуляций над их базовым многомерно организованным представлением (над "многомерной БД"). Очевидно, что многомерный "гиперкуб" есть попытка реализации математического отношения, заданного на множестве имеющихся данных. Аналогичной попыткой реализации в реляционной теории является таблица. Реляционной таблице всегда однозначно соответствует гиперкуб, а гиперкубу - реляционная таблица. Для современных СУБД аналогичное утверждение верно только с оговорками ввиду невыполнения во всех из них некоторых важных требований реляционной теории. И, тем не менее, оно - ключ к пониманию общего и разного у "OLAP-систем" и "реляционных систем". Если мы хотим анализировать данные, получая "срезы" гиперкуба и свертки, то не обязательно прибегать к услугам многомерной БД. Функционально все то же самое можно делать и в более знакомой "реляционной" БД. Разница только в хранении данных и … в удобстве готовых для такой работы средств. Но последним в целом ряде случаев можно и слегка пожертвовать не конфликтуя с требованиями, предъявляемыми прикладной системе.
В частности, если говорить об уровне работы с данными (и не касаться вопросов графического представления), OLAP-операции можно выполнять и в обычной БД под Oracle. В сервере 8i, однако, для этих целей добавлены некоторые расширения SQL, специально предназначенные для повышения удобства выполнения OLAP-операций, разработанные в соответствии с существующими международными предложениями по SQL (… и отдаляющими диалект SQL в Oracle еще дальше от принятых стандартов и от реляционной теории!)
Демонстрационной пример
Рассмотрим следующий пример базы данных. Пусть в прикладной системе собирается информация о посещениях web-узла. Регистрируется число посещений за декаду с указанием имени домена посетителя и вида браузера. Данные попадают в следующую таблицу:
CREATE TABLE visits
(time DATE, idomain VARCHAR2(15), browser VARCHAR2(15), hits NUMBER);
Заполним ее данными:
INSERT INTO visits VALUES ('10-sep-2000','com','Netscape', 3792);
INSERT INTO visits VALUES ('10-sep-2000','ru','Netscape', NULL);
INSERT INTO visits VALUES ('10-sep-2000','com','IE', 9567);
INSERT INTO visits VALUES ('10-sep-2000','ru','IE', 3095);
INSERT INTO visits VALUES ('20-sep-2000','com','Netscape', 2642);
INSERT INTO visits VALUES ('20-sep-2000','ru','Netscape', 1639);
INSERT INTO visits VALUES ('20-sep-2000','com','IE', 8045);
INSERT INTO visits VALUES ('20-sep-2000','ru','IE', 3085);
(Значение NULL во второй строке оставлено специально чтобы показать, что рассматриваемые ниже операции "правильно" обрабатывают NULL-значения без необходимости специальных указаний).
Это "гиперкуб" с тремя измерениями и полем hits в качестве "факта".
Указания ROLLUP и CUBE
Данные можно просмотреть обычным предложением SELECT, но это не будет "аналитической обработкой". Для нее нужно уметь удобно выдавать итоговые результаты (свертки) по разным позициям. В дальнейшем тексте будет фигурировать итоговая сумма, однако синтаксически все то же самое можно делать с использованием других функций, допускаемых для применения с GROUP BY, например COUNT, AVG, MIN или VARIANCE.
Вот как для этого можно использовать указание GROUP BY ROLLUP:
SELECT time, idomain, browser, SUM(hits) AS total
FROM visits
GROUP BY ROLLUP(time, idomain, browser);
TIME IDOMAIN BROWSER TOTAL
--------- ---------- ---------- ---------
10-SEP-00 com IE 9567
10-SEP-00 com Netscape 3792
10-SEP-00 com 13359
10-SEP-00 ru IE 3095
10-SEP-00 ru Netscape
10-SEP-00 ru 3095
10-SEP-00 16454
20-SEP-00 com IE 8045
20-SEP-00 com Netscape 2642
20-SEP-00 com 10687
20-SEP-00 ru IE 3085
20-SEP-00 ru Netscape 1639
20-SEP-00 ru 4724
20-SEP-00 15411
31865
15 rows selected.
Примерно понятно, о чем в этом результате речь (в первых трех столбцах, там где значение отсутствует - итог), но есть одно неудобство: чтобы посмотреть итоговые суммы по другим критериям, нам необходимо выдать аналогичный запрос с перестановкой имен полей во фразе GROUP BY ROLLUP. Есть возможность справиться с этим в рамках одного запроса, указав вместо ROLLUP слово CUBE (то есть выдать все возможные свертки по гиперкубу):
SELECT time, idomain, browser, SUM(hits) AS total
FROM visits
GROUP BY CUBE(time, idomain, browser)
TIME IDOMAIN BROWSER TOTAL
--------- ---------- ---------- ---------
10-SEP-00 com IE 9567
10-SEP-00 com Netscape 3792
10-SEP-00 com 13359
10-SEP-00 ru IE 3095
10-SEP-00 ru Netscape
10-SEP-00 ru 3095
10-SEP-00 IE 12662
10-SEP-00 Netscape 3792
10-SEP-00 16454
20-SEP-00 com IE 8045
20-SEP-00 com Netscape 2642
20-SEP-00 com 10687
20-SEP-00 ru IE 3085
20-SEP-00 ru Netscape 1639
20-SEP-00 ru 4724
20-SEP-00 IE 11130
20-SEP-00 Netscape 4281
20-SEP-00 15411
com IE 17612
com Netscape 6434
com 24046
ru IE 6180
ru Netscape 1639
ru 7819
IE 23792
Netscape 8073
31865
27 rows selected.
Это более общий результат, из которого будут браться все прочие результаты, получаемые с помощью ROLLUP.
Как выбрать нужную итоговую сумму
Оба результата выше грешат тем, что выдают много разных итоговых сумм одновременно и не позволяют одним предложением отобрать сразу конкретный перечень нужных сверток. Для того, чтобы это сделать, в Oracle 8i добавлена возможность указания GROUPING:
SELECT time, idomain, browser, SUM(hits) AS total,
GROUPING (time) AS t,
GROUPING (idomain) AS d,
GROUPING (browser) AS b
FROM visits
GROUP BY ROLLUP (time, idomain, browser);
TIME IDOMAIN BROWSER TOTAL T D B
--------- ---------- ---------- --------- --------- --------- ---------
10-SEP-00 com IE 9567 0 0 0
10-SEP-00 com Netscape 3792 0 0 0
10-SEP-00 com 13359 0 0 1
10-SEP-00 ru IE 3095 0 0 0
10-SEP-00 ru Netscape 0 0 0
10-SEP-00 ru 3095 0 0 1
10-SEP-00 16454 0 1 1
20-SEP-00 com IE 8045 0 0 0
20-SEP-00 com Netscape 2642 0 0 0
20-SEP-00 com 10687 0 0 1
20-SEP-00 ru IE 3085 0 0 0
20-SEP-00 ru Netscape 1639 0 0 0
20-SEP-00 ru 4724 0 0 1
20-SEP-00 15411 0 1 1
31865 1 1 1
15 rows selected.
Для CUBE это указание работает аналогично. Функция GROUPING возвращает 1 в тех случаях, где ROLLUP или CUBE в GROUP BY выдают NULL в качестве признака, что по этому полю подсчитана итоговая сумма. Фирма Oracle предлагает теперь воспользоваться отбором по маске полей T, D и B, но к сожалению, на моей версии 8.1.6 для NT такой отбор не работает так, как заявлено в документации. Предложение HAVING вообще не воспринимает имен столбцов, присвоенных в теле запроса, например, имени "D". Вместо этого следует явно указать, к примеру, GROUPING (browser) = 1. Надо надеяться на исправление этой ошибки в будущих версиях.
Как справиться с двусмысленными строками
NULL как отсутствующее значение и NULL как результат вычисления свертки могут доставить путаницу, так как в выдаваемом ответе неразличимы. Предположим, что наше приложение знакомо только с браузерами Navigator/Communicator и IE, ничего не знает об Opera, из-за чего добавляет в таблицу такую сроку:
INSERT INTO visits VALUES ('10-sep-2000', 'com', NULL, 3095);
1 row created.
Теперь сделаем запрос:
SELECT time, browser, SUM(hits) AS hits
FROM visits
GROUP BY CUBE(time, browser)
TIME BROWSER HITS
--------- --------------- ---------
10-SEP-00 IE 12662
10-SEP-00 Netscape 3792
10-SEP-00 3095
10-SEP-00 19549
20-SEP-00 IE 11130
20-SEP-00 Netscape 4281
20-SEP-00 15411
IE 23792
Netscape 8073
3095
34960
11 rows selected.
Что в третьей и четвертой сверху, а также в двух последних строчках ? Свертка или отсутствующие значения? И в каких полях что?
Чтобы в этом разобраться, можно снова воспользоваться функцией GROUPING в сочетании с DECODE:
SELECT
DECODE(GROUPING(time), 1, 'All Times', 0, time) AS time,
DECODE(GROUPING(browser), 1, 'All Browsers', 0, browser) AS browser,
SUM(hits) AS hits
FROM visits
GROUP BY CUBE(time, browser);
TIME BROWSER HITS
--------- --------------- ---------
10-SEP-00 IE 12662
10-SEP-00 Netscape 3792
10-SEP-00 3095
10-SEP-00 All Browsers 19549
20-SEP-00 IE 11130
20-SEP-00 Netscape 4281
20-SEP-00 All Browsers 15411
All Times IE 23792
All Times Netscape 8073
All Times 3095
All Times All Browsers 34960
11 rows selected.
Попытка отобрать напрямую из результата нужные подстроки на версии 8.1.6 мне также удалась только повторением во фразе HAVING полной формулировки формируемого столбца.
Какую реализацию OLAP-работы выбрать?
Из сказанного видно, что сервер 8i предлагает задаром специально оптимизированные (это нужно отметить особо) возможности для получения сверток по таблицам типа "гиперкуб". Конечно неприятно, что в реализации ROLLUP и CUBE в существующей версии есть недоработки (сверх указанного можно еще привести непонимание новых конструкций некоторыми продуктами третьих фирм, разработанными для Oracle). Однако, хотя и не очень изящно, их часто можно обойти. А вот проблемы перенесения данных из операционной БД в аналитическую здесь не существует. Программировать анализ данных можно с помощью знакомых языков программирования Oracle - его диалекта SQL и PL/SQL.
С другой стороны, специализированные OLAP-системы имеют в себе много встроенных готовых средств графической выдачи данных и специально разработанный интерфейс для указания необходимых срезов данных и сверток. Но это - еще и фактическое отсутствие методологии проектирования, свой самостоятельный язык и отдельная цена.
Оправдана последняя в вашем конкретном случае, или нет - решать вам.