Wykład 7

Hurtownie danych a bazy danych

Streszczenie

Tematem wykładu są hurtownie danych i ich związki z bazami danych.

W pierwszej części jest omówiona specyfika hurtowni danych, w szczególności czym hurtownie danych różnią się od operacyjnych baz danych. Hurtownia danych jest zwykle częścią specjalnego systemu wspomagania podejmowania decyzji w firmie (DSS - Decision Support System) nazywanego także systemem Business Intelligence. W systemach tego rodzaju generalnie chodzi o wydobywanie informacji z gromadzonych w wyniku działalności firmy danych, a w oparciu o informacje budowanie wiedzy.

W drugiej części są przedstawione narzędzia do tworzenia i używania hurtowni danych dostępne na serwerze Oracle.

7.1 Wprowadzenie do hurtowni danych

Typowe zastosowania baz danych dotyczą wspomagania bieżącego funkcjonowania firmy przede wszystkim rejestrację wszystkich zmian, jakie zachodzą w odniesieniu do obiektów działalności firmy. Umożliwiają szybkie znajdowanie zapisanych na dysku informacji o obiektach, także w powiązaniu z innymi obiektami. Podstawowymi problemami są: utrzymanie spójności zmienianych danych (więzy spójności, transakcje) oraz szybkość znajdowania odpowiednich informacji (np. poprzez indeksy). Zastosowania tego rodzaju nazywają się operacyjnymi lub OLTP (ang. On-Line Transaction Processing), a używane bazy danych bazami operacyjnymi.

Oprócz tego istnieją zastosowania analityczne, nazywane też OLAP (ang. On-Line Analytical Processing), których celem jest wyciąganie z danych przechowywanych w bazie danych informacji biznesowych takich jak informacje o wzorcach i trendach biznesu z myślą o wspomaganiu procesu podejmowania decyzji w firmie. Dziedziną, na której one operują, są dane historyczne z działalności firmy zwykle z ostatnich kilku lub kilkunastu lat. Używane w tym celu bazy danych nazywają się hurtowniami danych (używa się też terminów magazyny danych lub bazy analityczne). Oto ich podstawowe cechy:

• przechowują zintegrowane dane pochodzące z różnych, często  heterogenicznych baz operacyjnych używanych w firmie;

• przechowują dane obejmujące długie okresy czasu, często rozszerzone o informacje sumaryczne;

• mają rozmiary liczone w gigabajtach i terabajtach;

• oczekuje się szybkiej odpowiedzi na złożone zapytania – dlatego staje się konieczne wcześniejsze przetworzenie danych;

• rzadko ma miejsce modyfikowanie danych a tylko zachodzi regularne dopisywanie nowych danych napływających ze źródłowych baz operacyjnych.

Typową hurtownią danych jest baza danych o sprzedaży. Dane do takiej hurtowni spływają z wielu operacyjnych baz używanych w oddziałach firmy. W oparciu o wielomilionowy zbiór rekordów osoby, które podejmują decyzje w firmie, chcą uzyskiwać odpowiedzi na takie pytania jak:

• Które produkty przynoszą firmie najwięcej zysku?
• Którzy klienci przynoszą najwięcej zysku? Co mają ze sobą wspólnego?
• W których regionach kraju firma osiąga największe zyski i z jakiego rodzaju działalności te zyski pochodzą?
• Jakie usługi są najbardziej pożądane dla klientów firmy?
• Kiedy powinno się wprowadzić na rynek nowe produkty?
• Które produkty powinno się wycofać z rynku?
• Którzy dostawcy są najmniej pewni?

Typowym pytaniem zadawanym przy wykonywaniu analizy danych jest:
 

Jakie są dane o ... w rozbiciu na ... ?

np. dane o sprzedaży tegorocznej i ubiegłorocznej w rozbiciu na produkty i miesiące roku; dane o zyskach w rozbiciu na regiony kraju i rodzaj działalności; dane o obrotach w rozbiciu na poszczególnych klientów i sektory rynku.

Własności hurtowni danych

Hurtownia danych powinna być:

• zorientowana tematycznie – wokół tematów takich, jak dane o klientach, produktach, partnerach handlowych, dostawcach, sprzedaży czy pracownikach. Dane dotyczące jednego tematu mogą się znajdować w kilku aplikacjach operacyjnych: na przykład dane o klientach firmy w aplikacji obsługi zamówień i w aplikacji księgowej.

• nieulotna - po załadowaniu bazy analitycznej jej dane są tylko odczytywane. Spostrzeżenie to pociąga za sobą wiele konsekwencji: jeżeli dane mają być tylko odczytywane, nie jest tak ważne jak w przypadku baz operacyjnych zapewnienie im trzeciej postaci normalnej w celu uniknięcia anomalii; nie jest też konieczne zapewnienie mechanizmów transakcyjnych - jeżeli dane zostały do niej raz zapisane, to powinny pozostawać tam bez zmian.

• wielowersyjna-czasowo - dane pochodzą z okresu kilku, a czasem nawet, kilkunastu lat. Istotnym elementem wszystkich danych jest czas. Jeśli w danych przychodzących do hurtowni danych nie ma wymiaru czasowego, trzeba go dodać.

Z baz operacyjnych dane wpływają do hurtowni danych regularnie (powiedzmy raz na dzień). Najpierw następuje ich transformacja do jednolitego formatu, następnie ich przetworzenie przez obliczenie pewnych ich agregacji i w końcu załadowanie wszystkiego do hurtowni danych. Dane są pobierane ze źródłowych baz operacyjnych, przetwarzane i wpisywane zgodnie z pewną ich organizacją określoną przez repozytorium metadanych (słownik danych).

Rys. 7.1 Analityczne przetwarzanie danych za pomocą hurtowni danych

Z danych przechowywanych w hurtowni danych korzystają konkretne narzędzia analityczne OLAP przedstawiające dane w postaci informacji. Stosuje się zapytania podobne do operacji na arkuszach kalkulacyjnych i na wielowymiarowych kostkach danych. Specjalne znaczenie ma specjalistyczne oprogramowanie, które w oparciu o zgromadzone dane prowadzi eksploracyjne wyszukiwanie (ang. data mining) interesujących trendów i anomalii.

W hurtowni danych problem nie polega na szybkim odszukaniu pojedynczego rekordu (jak w aplikacji operacyjnej); obliczenia są wykonywane dla dużej grupy rekordów, często dla wszystkich rekordów ze zbioru.

Projektując hurtownię danych stosujemy zupełnie odwrotne podejście niż przy projektowaniu baz operacyjnych. Zamiast pytać: "Po co przechowywać miesięczne salda kont bankowych, skoro można je wyliczyć mając zapis ciągu wykonanych na nim operacji?" powinniśmy postawić pytanie: "Czemu nie wyliczyć raz i nie przechowywać miesięcznych stanów kont, skoro 90% wykonywanych analiz wymaga właśnie tak przygotowanych danych?". Dobrze zaprojektowane wstępne przetworzenie danych może istotnie przyśpieszyć czas wykonywania 90% analiz!

W hurtowni danych stosuje się dwie metody pomagające rozwiązać powyższe problemy:

• agregacje - wstępne wyliczenie pewnych miar przydatnych w późniejszych analizach;

• podziały na partycje - podział tabel na części tak, by zmniejszyć rozmiar danych, które trzeba przeczytać w trakcie wykonywania analiz lub tak, aby umożliwić ich równoległe przetwarzanie.

7.2 Schemat hurtowni danych

Fakty i wymiary

Mając ustalony temat, wyróżniamy dla niego dwa rodzaje danych:

1. wielkości analizowane (fakty) - dane ilościowe opisujące pewne fakty jak np. sprzedaż, zyski, obroty;

2. wielkości klasyfikujące (wymiary) - dane klasyfikujące opisywane fakty według okoliczności ich zaistnienia, takich jak czas, miejsce, osoba itp.

Stąd wynika, że każdy fakt istnieje w wielowymiarowej przestrzeni, np. fakt pojedynczej sprzedaży istnieje w wielowymiarowej przestrzeni, w której poszczególne wymiary to: czas, struktura sprzedaży, struktura klientów, struktura produktów itp.

Każdemu wymiarowi jest przypisany zbiór atrybutów opisowych (klasyfikujących).

Atrybuty opisowe są układane w hierarchie, w naturalny sposób obrazujące drogę przejścia od danych szczegółowych do ogólnych.

Na przykład, dla wymiaru czasu:

 

dzień -> miesiąc -> kwartał -> rok dzień -> tydzień -> rok

dla wymiaru geograficznego:
 

sklep -> miasto -> województwo
sklep -> oddział

dla wymiaru produktów:
 

opakowanie -> produkt -> grupa produktów

Przy czym hierarchie nie muszą być jednoznaczne, np. jeden oddział firmy może obsługiwać dwa województwa oraz jednocześnie może istnieć województwo z dwoma oddziałami.

Dla użytkownika końcowego dane do analizy to zestaw osobnych tabel opisujących fakty w rozbiciu na wymiary, plus ewentualnie informacja o tym, jak poszczególne atrybuty klasyfikujące wymiary łączą się w hierarchie.

Schemat gwiazda

Naturalną strukturą danych do przeprowadzania analiz danych jest schemat gwiazda. W schemacie tym mamy jedną tabelę faktów w środku układu i zbiór tabel po bokach, każda z nich opisująca jeden wymiar. Identyfikatory wartości wymiarów stanowią klucze obce w tabeli faktów.

Oto przykład schematu gwiazda w odniesieniu do tabeli faktów sprzedaży w podziale względem wymiaru struktury sprzedaży, czasu, wymiaru geograficznego i wymiaru produktów.

Rys. 7.2 Schemat gwiazda

Tabele wymiarów nie zawsze są w trzeciej postaci normalnej np. w rozważanym przykładzie istnieją zależności takie jak: miasto determinuje województwo, województwo determinuje makroregion. Czasami tabele wymiarów dodatkowo normalizuje się uzyskując schemat nazywany płatkiem śniegu.

Same tabele z wymiarami mogą posłużyć użytkownikom do znajdowania odpowiedzi na pytania dotyczące klasyfikacji danych, np.

• Na jakie regiony geograficzne zostały podzielone dane?

• Jakie województwa zawiera dany makroregion?

• Ile pozycji będzie zawierał raport w rozbiciu na oddziały firmy?

Na pytania takie jak powyższe można odpowiedzieć nie sięgając w ogóle do tabeli faktów.

Osobnym problemem jest, gdzie zapisywać wyniki operacji agregujących (podsumowujących). Do tego celu można:

• albo użyć tego samego zestawu tabel - dodając w każdej tabeli wymiaru atrybut poziomu agregacji danych, względem którego są agregowane wartości analizowane,
• albo zapisywać wyniki agregacji do osobnych tabel – najlepiej kontrolowanych przez sam system.

Schemat kostki wielowymiarowej

Jak stwierdziliśmy uprzednio, każdy fakt, np. dotyczący sprzedaży, można zinterpretować jako funkcję z wielowymiarowej przestrzeni wymiarów w zbiór wartości analizowanych np. dla schematu gwiazdy powyżej z pominięciem czwartego wymiaru - sprzedawcy (tzn. z agregacją danych dla poziomu sprzedawców):
 

Rys. 7.3 Kostka 3-wymiarowa

Operacje na danych uzyskują interpretację geometryczną.

Każdą taką kostkę czyli funkcję można bezpośrednio reprezentować za pomocą wielowymiarowej tablicy ( wielowymiarowego arkusza kalkulacyjnego). Przy dużej liczbie wierszy trzeba przechowywać zawartość tej tablicy na dysku i tylko częściami sprowadzać do pamięci wewnętrznej w celu wykonania obliczeń.

Każdą tabelę wymiaru można też reprezentować za pomocą tablicy i ze względu na niewielki jej rozmiar i częste użycie można ją przechowywać w pamięci wewnętrznej. W ten sposób otrzymujemy hurtownię danych, która nie jest oparta na relacyjnej bazie danych, tylko na zbiorze tablic wielowymiarowych. Rozwiązanie takie nosi nazwę MOLAP – ang. multidimensional on-line analytical processing, w odróżnieniu od poprzednio rozważanego rozwiązania, nazywanego ROLAP – ang. relational on-line analytical processing. Jest też używane rozwiązanie hybrydowe HOLAP:

• Dane główne są przechowywane w relacyjnych tabelach
• Dane zagregowane są przechowywane w wielowymiarowych tablicach.

Operacje w hurtowni danych

Niezależnie od sposobu implementacji (ROLAP czy MOLAP) są rozważane następujące typowe operacje na prezentowanych użytkownikowi danych odwołujące się do modelu wielowymiarowego:

• pivoting (zestawienie krzyżowe) – wskazanie do analizy: miary np. sprzedaży oraz dwóch wymiarów np. miasta i roku oraz przypisaniu wartościom tych wymiarów pewnej agregacji wartości wybranej miary np. sumarycznej sprzedaży w danym mieście w danym roku. Wygodną reprezentacją wyniku operacji pivoting jest tablica; w przykładzie w tab. 7.1, wiersze tablicy są etykietowane latami, a kolumny miastami.

• drill-down – rozwijanie – rozwinięcie danego wymiaru do kolejnego elementu tego wymiaru – np. mając wyniki sprzedaży w rozbiciu na lata, chcemy poznać wyniki sprzedaży w rozbiciu na miesiące. Operacją odwrotną jest roll-up - zwijanie od rozbicia na bardziej szczegółowe elementy wymiaru do bardziej ogólnych.

• slice-and-dice – wycinanie – operacja projekcji na wybrany podzbiór wymiarów dla wybranych wartości innych wymiarów np. dane o sprzedaży poszczególnych produktów (projekcja na wymiar produktów) w ubiegłym roku (selekcja na wymiarze czasu).

7.3 Implementacja hurtowni danych w Oracle

Przedstawimy teraz konstrukcje w serwerze Oracle służące do definiowania i używania hurtowni danych.

1. Histogramy

Instrukcja ANALYZE oblicza statystyki używane przez optymalizator zapytań (takie jak liczba wierszy, liczba różnych wartości w kolumnie, liczba NULL w kolumnie, najmniejsza i największa wartość w kolumnie). W szczególności, tak zwane histogramy określają dystrybucję wartości w kolumnie tabeli. Histogramy oblicza się za pomocą instrukcji ANYLYZE TABLE:

ANALYZE TABLE Sprzedaz
COMPUTE STATISTICS FOR COLUMNS Wartosc_sprzedazy
SIZE 10; -- liczba przedziałów, na które dzieli się wartości sprzedaży

Statystyki są używane przez optymalizator zapytań i można je odczytać z perspektyw słownika danych USER_TABLES, USER_TAB_COLUMNS i USER_TAB_HISTOGRAMS.

2. Wersja równoległa serwera Oracle

Tworząc tabelę możemy określić jej stopień zrównoleglenia:

CREATE TABLE XXX(....)
PARALLEL (DEGREE 8); -- stopień zrównoleglenia zapytań dla tej tabeli

3. Obiekty z partycjami (partycjonowane)

Partycje umożliwiają rozłożenie obiektów bazy danych do różnych dysków w celu zrównoleglenia operacji wejścia/wyjścia. Dotyczy to zarówno tabel, indeksów jak i perspektyw zmaterializowanych, o których będzie dalej mowa.

CREATE Klienci(
Id NUMBER(5) PRIMARY KEY,
Kraj CHAR(2), ......)
PARTITION BY RANGE (kraj)
 (PARTITION p1 VALUES LESS THAN ('C')
 TABLESPACE Data01;
 PARTITION p2 VALUES LESS THAN ('I')
 TABLESPACE Data02;
 ...
 PARTITION p19 VALUES LESS THAN MAXVALUE
 TABLESPACE Data19);

Następnie możemy utworzyć indeks podzielony na partycje

CREATE INDEX Indx_Klienci_Kraj ON Klienci(Kraj) LOCAL;

4. Indeksy bitmapowe

Indeksy bitmapowe są zakładane na kolumnach, których zbiór wartości jest niewielki i które występują często w warunku WHERE zapytań z równością np.

WHERE (Rok_produkcji=1992 OR Rok_produkcji=1993) AND
      (Producent = 'Fiat' OR Producent='Ford') AND
      (Kraj_klienta='Polska' OR Kraj_klienta='USA')

Są na przykład odpowiednie dla tabeli zawierającej milion wierszy oraz na kolumnie mającej tylko 1000 różnych wartości.

CREATE BITMAP INDEX Ind_Sprzedaż_Producent ON Sprzedaż(Producent);

Nazwisko	Płeć	Stanowisko
Kowalski	M	Dyrektor
Jankowski	M	Sprzedawca
Malinowski	M	Sprzedawca
Gazda	K	Kasjer
Wiśniewski	M	Kasjer
Bojanowska	K	Sprzedawca

Oto indeksy bitmapowe na kolumnach Płeć i Stanowisko:
 

Indeks na Płeć      Indeks na Stanowisko

Tab. 7.3 Dwa indeksy bitmapowe

Warunek:

WHERE (Płeć = 'K' AND Stanowisko = 'K') OR
      (Płeć = 'M' AND Stanowisko = 'D')

([000101] AND [000110]) OR ([111010] AND [100000]) = [100100]

Implementacja indeksu bitmapowego

Aby móc użyć indeksu bitmapowego potrzebujemy dwóch pomocniczych struktur danych:

• jednej, która w oparciu o wartość atrybutu pozwala szybko wyznaczyć przypisany jej wektor bitów (np. struktura danych B+ drzewa - omawiana w dalszych wykładach);
• druga, która w oparciu o numer wiersza (pozycję jedynki w wektorze bitów) pozwala szybko wyznaczyć ten wiersz (np. tablica lub B+ drzewo).

W hurtowniach danych indeksy bitmapowe są zakładane w tabeli faktów na kolumnach kluczy obcych odwołujących się do tabel wymiarów (duża liczba faktów, o rząd mniejsza liczba wartości wymiaru).

5. Transformacja STAR

W przypadku wykrycia prawidłowości:

jedna duża tabela faktów i wiele drobnych tabel wymiarów

Jest dodatkowe wymaganie w systemie Oracle.

Przykład

Transformacja STAR przekształca instrukcję:

SELECT * FROM Sprzedaż, Miejsce, Towar, Czas
   WHERE Sprzedaż.Id_miejsca = Miejsce.Id
      AND Sprzedaż.Id_towaru = Towar.Id
      AND Sprzedaż.Id_czasu = Czas.Id
      AND Miejsce.Miasto IN ('WAW','KRA','RAD','LUB')
      AND Towar.Kategoria = 'OPROGRAMOWANIE'
      AND Czas.Rok > 1996;

SELECT * FROM Sprzedaż
WHERE Sprzedaż.Id_miejsca IN
               (SELECT Miejsce.Id FROM Miejsce
                        WHERE Miejsce.Miasto IN ('WAW','KRA','RAD','LUB'))
   AND Sprzedaż.Id_towaru IN
               (SELECT Towar.Id FROM Towar
                         WHERE Towar.Kategoria = 'OPROGRAMOWANIE')
   AND Sprzedaż.Id_czasu IN
               (SELECT Czas.Id FROM Czas
                         WHERE Czas.Rok > 1996);

• najpierw oblicza się podzapytania,

• następnie stosuje się indeksy bitmapowe i operacje na wektorach bitów, aby znaleźć fakty spełniające jednocześnie wszystkie trzy warunki IN.

6. Perspektywy zmaterializowane (migawki)

Po zaprojektowaniu tabel faktów i wymiarów projektuje się następnie perspektywy zmaterializowane określające wymagane złączenia i agregacje (podsumowania) danych z bazowych tabel. Na perspektywie zmaterializowanej można zakładać indeksy w szczególności bitmapowe, więc przy wykonywaniu zapytań, perspektywa zmaterializowana ma te same własności co tabela.

Ponadto można zażyczyć sobie, aby zapytania pisane w terminach tabel faktów i wymiarów były automatycznie przekształcane przez optymalizator do zapytań korzystających z perspektyw zmaterializowanych.

Aby optymalizacja była możliwa, w definicji perspektywy zmaterializowanej należy użyć klauzuli ENABLE QUERY REWRITE.

Na przykład:

CREATE MATERIALIZED VIEW Sprzedaz_mv
ENABLE QUERY REWRITE
AS
   SELECT s.Nazwa_sklepu, SUM(f.Wielkosc) AS Suma
   FROM Sklep s, Fakt f
   WHERE f.IdSklepu = s.IdSklepu
   GROUP BY s.Nazwa_sklepu;

Użycie opcji QUERY REWRITE umożliwia zasłonięcie szczegółów technicznych związanych z agregacjami danych w hurtowni danych przed użytkownikiem, który pisze i używa aplikacji w terminach tabel faktów i wymiarów. W szczególności, gdy zmienią się definicje perspektyw zmaterializowanych, aplikacje będą dalej działać poprawnie. W ten sposób użycie perspektyw zmaterializowanych jest podobne do użycia indeksów – w obu przypadkach stosuje je optymalizator zapytań. Oba rodzaje obiektów muszą być aktualizowane po wykonaniu operacji INSERT, DELETE i UPDATE.

Obok wcześniejszego wyliczania agregacji perspektywy zmaterializowane używane są też do wcześniejszego wyliczania złączeń tabeli faktów z pewnymi wybranymi tabelami wymiarów. Przy takim zastosowaniu perspektywy zmaterializowane pełnią rolę tzw. indeksów złączeniowych (przy bitmapowej implementacji - bitmapowych indeksów złączeniowych). Na przykład, dla tabel Emp i Dept można zbudować perspektywę zmaterializowaną pełniącą rolę indeksu złączeniowego na kolumnach (d.Deptno, e.Empno) tabeli: (Emp e INNER JOIN Dept d).

Zawartości perspektywy zmaterializowanej są odświeżane używając poniższych opcji (podawanych przy tworzeniu perspektywy zmaterializowanej:

1. COMPLETE – przez powtórne wykonanie zapytania.
2. FAST – skorzystanie z dzienników zapisujących zmiany w tabelach bazowych (MATERIALIZED VIEW LOG) (dzienniki wyjaśnione w wykładzie 13).
3. ON DEMAND – przez wykonanie explicite instrukcji odświeżającej (DBMS_MVIEW.REFRESH).
4. ON COMMIT – automatycznie po każdym COMMIT dotyczącym tabel bazowych
5. START ...   NEXT  ... - kiedy rozpocząć odświeżanie i co kiedy.

Na przykład:

CREATE MATERIALIZED VIEW Sprzedaz_mv
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096
ENABLE QUERY REWRITE
AS
   SELECT s.Nazwa_sklepu, SUM(f.Wielkosc) AS Suma
   FROM Sklep s, Fakt f
   WHERE f.IdSklepu = s.IdSklepu
   GROUP BY s.Nazwa_sklepu;

(lub REFRESH FAST ON COMMIT).

7. Grupowanie ROLLUP i CUBE

Wyciągając z bazy dane przy użyciu samej klauzuli GROUP BY nie uzyskamy częściowych podsumowań po poszczególnych poziomach wymiarów. Do wprowadzenia wyników częściowych podsumowań służą operatory ROLLUP i CUBE. Zapytanie:

SELECT Czas.rok, Miejsce.miasto, Towar.kategoria, SUM(Sprzedaż.wartość)AS "W sumie"
FROM Sprzedaż NATURAL JOIN Miejsce NATURAL JOIN Towar NATURAL JOIN Czas
GROUP BY ROLLUP(Czas.Rok, Miejsce.Miasto, Towar.Kategoria);

   ROK MIASTO     KATEGORIA      W sumie
------ ---------- ----------- ----------
1996   Warszawa   Komputery       100000
1996   Warszawa   Telewizory       50000
....
1996   Warszawa       *          1000000
1996   Łódź       Komputery        50000
1996   Łódź       Telewizory       25000
         .....
1996   Łódź            *          250000
         .....
1996     *             *         2000000
1997   Warszawa   Komputery      1500000
         ....
  *       *            *        10000000

Operator ROLLUP daje wszystkie częściowe podsumowania po poszczególnych wymiarach – w danej kolejności. W wyniku istotna jest kolejność grupowanych kolumn. W szczególności zapytanie:

SELECT e.Deptno, e.EmpNo, SUM(e.Sal) AS Zarobki
FROM Emp e
GROUP BY ROLLUP(e.Deptno, e.EmpNo);

    DEPTNO      EMPNO    ZAROBKI
---------- ---------- ----------
        10       7782       2450
        10       7839       5000
        10       7934       1300
        10                  8750
        20       7369        800
        20       7566       2975
        20       7788       3000
        20       7876       1100
        20       7902       3000
        20                 10875
        30       7900        950
        30       7499       1600
        30       7521       1250
        30       7654       1250
        30       7698       2850
        30       7844       1500
        30                  9400
                           29025

Operator ROLLUP daje więc w szczególności możliwość, której brakowało dotychczas w SQL – zwracania w wyniku zarówno szczegółowych wierszy jak ich podsumowań.

Operator CUBE daje nawet więcej niż ROLLUP, bo również wszystkie częściowe podsumowania po poszczególnych wymiarach – we wszystkich możliwych kolejnościach.

Na przykład, gdybyśmy w powyższej instrukcji SELECT zamienili ROLLUP na CUBE, dostalibyśmy oprócz powyższych wierszy również takie wiersze jak:

 *   Warszawa    Komputery    1000000
          ...
 *   Warszawa         *       9000000
          ...
 *       *       Komputery    8000000
          ....
1996     *       Komputery     500000

Jest też możliwość łączenia zwykłego grupowania z grupowaniem typu ROLLUP i CUBE. Na przykład:

GROUP BY Miejsce.Miasto, ROLLUP(Czas.Rok, Towar.Kategoria)

8. Funkcje analityczne

Funkcje analityczne to specjalna konstrukcja języka SQL dotycząca operacji statystycznych wykonywanych na wierszach wynikowych zapytania na samym końcu jego realizacji i tylko przed zastosowaniem klauzuli ORDER BY.

Mianowicie, dla każdego wynikowego wiersza zapytania określamy zbiór powiązanych z nim wierszy - nazywany oknem tego wiersza. Definiuje się go za pomocą tzw. klauzuli analitycznej. Rozmiary okien określa się, albo za pomocą liczby wierszy stosując klauzulę ROWS, albo za pomocą przedziałów wartości, takich jak czas, stosując klauzulę RANGE. Zbiór wszystkich wynikowych wierszy można najpierw podzielić na zbiór rozłącznych grup (np. pracownicy pracujący w tym samym departamencie, pracownicy pracujący na tym samym stanowisku), a następnie w ramach jednej grupy określać okno danego wiersza.

Oto składnia funkcji analitycznej:
 

nazwa_funkcji_grupowej(argument,...) OVER (klauzula_analityczna)

gdzie klauzula analityczna może zawierać następujące cztery podklauzule:

• PARTITION BY wyrażenie, ...  określa podział całego zbioru wynikowego wierszy na grupy; jeśli zostanie opuszczona, cały zbiór wynikowych wierszy stanowi jedną grupę. Wiersze wchodzące w skład okna są zawsze ograniczone (zawarte) do jednej grupy.

• ORDER BY wyrażenie, ...       określa porządek wierszy w ramach podziału określonego w grupie przez podklauzulę PARTITION BY.

• ROWS specyfikacja_okna      specyfikuje okno poprzez określenie liczby wierszy;

• RANGE specyfikacja_okna     specyfikuje okno poprzez określenie zakresu wierszy.

W przypadku gdy nie ma ani ROWS ani RANGE, okno danego wiersza pokrywa się z jego grupą.

Podamy teraz kilka przykładów zastosowań.

Przykład (podklauzula PARTITION)

PARTITION jest podobne do GROUP BY z tym, że wartości podsumowujące są załączane do każdego wiersza w grupie, a nie tylko raz dla całej grupy.

SELECT e.Ename, e.Sal,
       SUM(e.Sal) OVER (PARTITION BY e.Deptno) AS Suma_zarob,
       Round(100*e.Sal/(SUM(e.Sal) OVER (PARTITION BY e.Deptno))) AS
                                                        Proc_zarob
FROM Emp e
ORDER BY e.Deptno, e.Ename;

SUM(e.Sal) OVER (PARTITION BY e.Deptno) jest funkcją analityczną, która sumuje zarobki wszystkich pracowników pracujących w tym samym departamencie.

W tym przypadku okno obliczeniowe dla danego pracownika pokrywa się z grupą określoną przez PARTITION BY - czyli ze zbiorem pracowników pracujących w tym samym departamencie co dany pracownik. Oto wynik:

ENAME             SAL SUMA_ZAROB PROC_ZAROB
---------- ---------- ---------- ----------
CLARK            2450       8750         28
KING             5000       8750         57
MILLER           1300       8750         15
ADAMS            1100      10875         10
FORD             3000      10875         28
JONES            2975      10875         27
SCOTT            3000      10875         28
SMITH             800      10875          7
ALLEN            1600       9400         17
BLAKE            2850       9400         30
JAMES             950       9400         10
MARTIN           1250       9400         13
TURNER           1500       9400         16
WARD             1250       9400         13

Przykład (podklauzula RANGE)

Rozważmy problem analityczny wyznaczenia dla każdego pracownika, liczby pracowników, których zarobki są co najwyżej 50zł mniejsze lub 150zł większe od zarobków tego pracownika. W tym przypadku okno obliczeniowe dla danego pracownika o zarobkach e.Sal nie zależy od departamentu i obejmuje wszystkich pracowników, których zarobki są w przedziale [e.Sal-50, e.Sal+150]. (Jest określona tylko jedna grupa - zbiór wszystkich wierszy.)

Zauważmy, że co najmniej jedna osoba spełnia ten warunek – pracownik względem którego określamy okno.

SELECT e.Ename, e.Sal,
COUNT(*) OVER (ORDER BY e.Sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS Licznik
FROM Emp e;

ENAME         SAL LICZNIK
--------- ------- -------
SMITH        800        2
JAMES        950        2
ADAMS       1100        3
WARD        1250        3
MARTIN      1250        3
MILLER      1300        3
TURNER      1500        2
ALLEN       1600        1
CLARK       2450        1
BLAKE       2850        4
JONES       2975        3
SCOTT       3000        3
FORD        3000        3
KING        5000        1

Przykład (podklauzula ROWS)

Dla każdego pracownika podaj numer jego kierownika, datę zatrudnienia, zarobki oraz średnią wartość zarobków pracowników zatrudnionych bezpośrednio przed i po zatrudnieniu tego pracownika (włącznie z tym pracownikiem) wśród pracowników tego kierownika.

SELECT mgr, ename, hiredate, sal,
AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ave
FROM Emp;

Oto wynik:

       MGR ENAME      HIREDATE        SAL        AVE
---------- ---------- -------- ---------- ----------
      7566 FORD       81/12/03       3000       3000
      7566 SCOTT      82/12/09       3000       3000
      7698 ALLEN      81/02/20       1600       1425
      7698 WARD       81/02/22       1250       1450
      7698 TURNER     81/09/08       1500       1333,3
      7698 MARTIN     81/09/28       1250       1233,3
      7698 JAMES      81/12/03        950       1100
      7782 MILLER     82/01/23       1300       1300
      7788 ADAMS      83/01/12       1100       1100
      7839 JONES      81/04/02       2975       2912,5
      7839 BLAKE      81/05/01       2850       2758,3
      7839 CLARK      81/06/09       2450       2650
      7902 SMITH      80/12/17        800        800
           KING       81/11/17       5000       5000

Tab. 7.9 Ilustracja działania podklauzuli ROWS

Oracle dostarcza dodatkowych funkcji statystycznych do zastosowania w funkcjach analitycznych. Dwie z nich są pokazane poniżej.

Przykład

Rozważmy problem analityczny wyznaczenia dla każdego pracownika procentowego zaszeregowania jego zarobków w jego departamencie (procentowe zaszeregowanie zarobków danego pracownika określamy jako pr=(i-1)/(n-1), gdzie n>1 jest liczbą pracowników w dziale, a i jest numerem zaszeregowania zarobków danego pracownika wśród zarobków pracowników w jego dziale według wzrastających zarobków).

SELECT e.Deptno, e.Ename, e.Sal,
PERCENT_RANK() OVER (PARTITION BY e.Deptno ORDER BY e.Sal) AS Pr
FROM Emp e;

 DEPTNO ENAME       SAL     PR
------- ------- ------- ------
     10 MILLER     1300      0
     10 CLARK      2450     .5
     10 KING       5000      1
     20 SMITH       800      0
     20 ADAMS      1100    .25
     20 JONES      2975     .5
     20 SCOTT      3000    .75
     20 FORD       3000    .75
     30 JAMES       950      0
     30 WARD       1250     .2
     30 MARTIN     1250     .2
     30 TURNER     1500     .6
     30 ALLEN      1600     .8
     30 BLAKE      2850      1

Przedstawione dane można użyć do sporządzenia wykresu słupkowego obrazującego zaszeregowania zarobków pracowników w ramach ich działów.

Przykład

Rozważmy problem analityczny wyznaczenia dla każdego sprzedawcy stosunku jego zarobków do sumy zarobków wszystkich sprzedawców.

SELECT e.Ename, e.Sal, RATIO_TO_REPORT(e.Sal) OVER () AS Rr
FROM Emp e
WHERE e.Job = 'SALESMAN';

ENAME     SAL          RR
-------- ---- ------------
ALLEN    1600  .285714286
WARD     1250  .223214286
MARTIN   1250  .223214286
TURNER   1500  .267857143

Tab. 7.11 Ilustracja działania funkcji RATIO_TO_REPORT

Jako dodatkową informację podajemy możliwość stosowania algorytmów eksplorujących dane na serwerze bazy danych Oracle.

ODM - Oracle Data Mining

Oracle rozpoczął w wersji 10g wprowadzanie pojęć i algorytmów data-miningowych na razie głównie przy pomocy pakietu DBMS_DATA_MINING (jeszcze nie na poziomie SQL i specjalnych obiektów zapisywanych w bazie danych). Centralne pojęcie to model określający parametry, algorytm data-miningowy i wprowadzone dane uczące.

Dane do analizy – zbiór punktów w przestrzeni wielowymiarowej - są dostarczane w jednej tabeli. Wiersze są nazywane przypadkami (ang. cases). Kolumna ID przypadku dostarcza jednoznacznego identyfikatora np. CUSTOMER ID w tabeli klientów. Kolumny są nazywane atrybutami. Np. model regresji może przewidzieć poziom dochodów klienta (atrybut typu target) w oparciu o datę urodzenia i płeć (atrybuty typu predictors).

Parametry procedury CREATE_MODEL

• model_name -- nazwa modelu
• mining_function -- stała reprezentująca rodzaj problemu eksploracji danych np. klasyfikacja, clustering, regresja
• data_table_name -- nazwa tabeli z danymi „uczącymi” model
• case_id_column_name
• target_column_name -- NULL dla modeli deskryptywnych, nie NULL dla modeli predyktywnych
• settings_table_name -- ustawienia dla funkcji i algorytmu (np. nazwa algorytmu eksploracyjnego)

Procedura APPLY (model)

Stosuje podany model eksploracyjny do podanych danych i generuje wyniki w tabeli, której nazwa jest specyfikowana w APPLY. Operacja APPLY jest też nazywana scoring. Dla modeli predyktywnych, operacja APPLY generuje wynik w kolumnie docelowej. Dla modeli deskryptywnych jak clustering, operacja APPLY przyporządkowuje każdemu przypadkowi prawdopodobieństwo należenia do klastra.

DBMS_DATA_MINING.APPLY
(model_name IN VARCHAR2,
data_table_name IN VARCHAR2,
case_id_column_name IN VARCHAR2,
result_table_name IN VARCHAR2 -- schemat tworzonej przez APPLY tabeli zależy od algorytmu );

7.4 Podsumowanie

W pierwszej części wykładu została omówiona specyfika hurtowni danych, w szczególności czym hurtownie danych różnią się od baz danych rozpatrywanych do tej pory.

W drugiej części wykładu zostały  przedstawione narzędzia do tworzenia i używania hurtowni danych dostępne w serwerze Oracle.

Podsumowując serwer relacyjnej bazy danych posiada możliwości implementacji hurtowni danych typu ROLAP. W szczególności, nowy rodzaj indeksów bitmapowych, daje się implementować za pomocą B-drzew.
 

7.5 Słownik pojęć

miara (fakt) - dane ilościowe opisujące pewne fakty biznesowe jak sprzedaż, zyski, obroty.

wymiar - dane klasyfikujące opisywane fakty wg okoliczności ich zaistnienia jak czas, miejsce, osoba itp.

schemat gwiazda - schemat  złożony z tabeli faktów w środku układu i zbioru tabel, po bokach, każda z nich opisująca jeden wymiar. Identyfikatory wartości wymiarów stanowią klucze obce w tabeli faktów.

schemat kostki wielowymiarowej - nierelacyjna reprezentacja hurtowni danych w postaci wielowymiarowej tablicy.

indeks bitmapowy - specjalny indeks zakładany na kolumnach, których zbiór wartości jest niewielki i które często występują w warunku WHERE razem z operatorem równości lub operatorem IN.

transformacja STAR - przekształcenie zapytania, w którym występuje złączenie tabeli faktów z tabelami wymiarów, na zapytanie dotyczące tylko tabeli faktów tak aby można było zastosować indeksy bitmapowe na kolumnach kluczy obcych (odpowiadającym wymiarom).

perspektywa zmaterializowana - perspektywa, której wynik jest obliczany i zapisywany do bazy danych. W hurtowni danych perspektywa zmaterializowana określa wymagane agregacje (podsumowania) danych z bazowych tabel. Na perspektywie zmaterializowanej można zakładać indeksy, więc ma ona podobne  własności co tabela.

ROLLUP - operator rozszerzający możliwości GROUP BY. Operator ROLLUP daje wszystkie częściowe podsumowania po poszczególnych wymiarach – w danej kolejności. W wyniku istotna jest kolejność grupowanych kolumn.

CUBE - operator rozszerzający możliwości zarówno GROUP BY jak i ROLLUP. Operator CUBE daje wszystkie częściowe podsumowania po poszczególnych wymiarach – we wszystkich możliwych kolejnościach grupowanych kolumn.

funkcja analityczna - nowy rodzaj funkcji SQL agregującej dane. Dla każdego wiersza wynikowego określa się zbiór wierszy (nazywany oknem), który służy do obliczenia wartości funkcji analitycznej.
 

7.6 Sprawdzenie wiedzy

1. Które cechy są charakterystyczne dla hurtowni danych:
   uwzględnienie czasu w danych
   trzecia postać normalna
   nieulotność danych
   zorientowanie na wybrany temat

2. W temacie wyróżniamy:
   agregacje
   fakty
   partycje
   wymiary

3. W schemacie gwiazdy:
   Tabela faktów znajduje się w centrum schematu.
   Tabele wymiarów są połączone przez klucze obce z tabelą faktów.
   Tabela faktów jest połączona przez klucze obce z tabelami wymiarów.
   Tabela faktów jest w trzeciej postaci normalnej.
   Tabele wymiarów są w trzeciej postaci normalnej.

4. Które operacje są wykonywane na danych w hurtowni danych:
   wycinanie (slice-and-dice)
   rozwijanie (drill-down)
   pivoting
   normalizacja

5. Indeksy bitmapowe są zakładane na kolumnach:
   których zbiór wartości jest duży
   które występują często w warunku WHERE zapytań w połączeniu z OR i AND
   klucza obcego w tabeli faktów,
   klucza głównego w tabelach wymiarów

6. Perspektywa zmaterializowana:
   Określa wymagane w obliczeniach analitycznych złączenia i agregacje tabel bazowych.
   Można na niej zakładać indeksy.
   Jest tym samym co migawka.
   Jest tym samym co perspektywa.

7.7 Zadania

1. Dla przykładowej bazy danych dotyczącej sprzedaży produktów zaproponuj indeksy bitmapowe i perspektywy zmaterializowane. Podaj przykłady zapytań, przy wykonywaniu których system skorzysta z tych indeksów i perspektyw.

2. Napisz zapytanie wyznaczające dla każdego pracownika, średnią wartość zarobków tych pracowników, których zarobki są co najwyżej 100zł mniejsze lub 100zł większe od zarobków tego pracownika i którzy pracują w tym samym departamencie.