Сервер Oracle8i: ще SQL, але вже OLAP, Інші СУБД, Бази даних, статті

Користувачі 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-системи мають в собі багато вбудованих готових засобів графічної видачі даних і спеціально розроблений інтерфейс для вказівки необхідних зрізів даних і згорток. Але це – ще й фактична відсутність методології проектування, свій самостійний мова та окрема ціна.

Виправдана остання у вашому конкретному випадку, чи ні – вирішувати вам.


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*