oldi

Сервер Oracle8i: еще SQL, но уже OLAP

Владимир Пржиялковский

Туман вокруг OLAP

Демонстрационный пример

Указания ROLLUP и CUBE

Как выбрать нужную итоговую сумму

Как справиться с двусмысленными строками

Какую реализацию OLAP-работы выбрать

 

Пользователи Oracle при слове «OLAP» чаще всего вспоминают Oracle Express, но не все знают, что для OLAP-работы вполне хватает и функциональности, присутствующей в Oracle8i. Что конкретно имеется в виду и как упомянутая функциональность соотносится с возможностями Oracle Express, мы расскажем в этой статье.

Туман вокруг OLAP

Разработчики OLAP-продуктов изрядно потрудились не только над самими продуктами, но и над созданием терминологического тумана вокруг них. Видимо, в тумане легче продавать. В этой статье мы не будем пытаться очистить «авгиевы конюшни» терминологии, но некоторые понятия все-таки постараемся уточнить.

С OLAP в сегодняшней торгово-популярной литературе связывается преимущественно визуальный анализ данных, проводимый в результате манипуляций над их базовым многомерно организованным представлением, над так называемой многомерной базой данных. Очевидно, что многомерный «гиперкуб» есть попытка реализации математического отношения, заданного на множестве имеющихся данных. Аналогичной попыткой реализации в реляционной теории является таблица. Реляционной таблице всегда однозначно соответствует гиперкуб, а гиперкубу — реляционная таблица. Для современных СУБД аналогичное утверждение верно только с определенными оговорками — вследствие невыполнения в них некоторых важных требований реляционной теории. И тем не менее это утверждение — ключ к пониманию того, что есть общего у OLAP-систем и реляционных систем и чем они различаются. Если мы хотим анализировать данные, получая «срезы» гиперкуба и свертки, то необязательно прибегать к услугам многомерной БД. Функционально все то же самое можно делать и в более знакомой реляционной базе данных. Разница только в хранении данных и в удобстве имеющихся для такой работы средств. Но последним фактором в целом ряде случаев можно и пожертвовать, не конфликтуя с требованиями, предъявляемыми прикладной системе.

В частности, если говорить об уровне работы с данными и не касаться вопросов графического представления, OLAP-операции можно выполнять и в обычной базе данных Oracle. В сервере Oracle8i, однако, для этих целей добавлены некоторые расширения 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.

В начало В начало

Как выбрать нужную итоговую сумму

Оба полученных результата имеют один минус — выдают много разных итоговых сумм одновременно и не позволяют одним предложением отобрать сразу конкретный перечень нужных сверток. Для того чтобы это сделать, в Oracle8i добавлена возможность указания 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, используемого Oracle, и языка PL/SQL.

Кроме того, специализированные OLAP-системы имеют множество встроенных готовых средств графического отображения данных и специально разработанный интерфейс для указания необходимых срезов данных и сверток. Но их применение — это еще и фактическое отсутствие методологии проектирования, свой самостоятельный язык и отдельная цена.

Оправдана ли последняя в вашем конкретном случае или нет — решать вам.

 

Автору будет интересно узнать ваше мнение о статье по e-mail: mail@interface.ru.

КомпьютерПресс 7'2001