SBD

Wykład 15

Hurtownie danych a bazy danych

Streszczenie

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

Wykład ten jest podzielony na dwie części. W pierwszej jest 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 są przedstawione narzędzia do tworzenia i używania hurtowni danych dostępne w serwerze Oracle.

15.1 Wprowadzenie do hurtowni danych

Przykłady zastosowań baz danych, którymi zajmowaliśmy się do tej pory, miały specjalne cechy. Przede wszystkim dotyczyły wspomagania bieżącego funkcjonowania firmy. Umożliwiały rejestrację wszystkich zmian, jakie zachodzą w odniesieniu do obiektów działalności firmy. Umożliwiały szybkie znajdowanie zapisanych na dysku informacji o obiektach, także w powiązaniu z innymi obiektami. Podstawowymi problemami, z jakimi spotykaliśmy się, były: 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ż nazw 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, 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 np. klienci, produkty, partnerzy handlowi, dostawcy, dane o sprzedaży czy pracownicy. 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 przy uaktualnianiu; 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 agregatów 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. 15.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 należy wykonywać 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 pytania: "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.

15.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. 15.2 Schemat gwiazda

Tabele wymiarów nie są na ogół w trzeciej postaci normalnej np. w rozważanym przykładzie istnieją zależności takie jak: miesiąc determinuje kwartał, miasto determinuje województwo. 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 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 stwiedziliś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:

Rys. 15.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. Przy dużej liczbie pozycji trzeba przechowywać zawartość tej tablicy na dysku i tylko częściami sprowadzać do pamięci wewnętrznej w celu wykonania obliczeń.

Podobnie każdą tabelę wymiaru można zapisać w tablicy. Powstające w ten sposób tablice wymiarów można zapisać w pamięci wewnętrznej, ponieważ nie są zbyt duże. W ten sposób otrzymujemy hurtownię danych, która nie jest oparta na relacyjnej bazie danych, tylko na zbiorze tablic. 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.

Operacje w hurtowni danych

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

pivoting – 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, wiersze tablicy są etykietowane latami, a kolumny miastami.

WA KA Razem

1995 63 81 144

1996 38 107 145

1997 75 35 110

Razem 176 223 339

Tab. 15.1 Wynik operacji pivoting

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 konkretnym roku 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).

15.3 Implementacja hurtowni danych w Oracle

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

1. Histogramy

Histogramy określają dystrybucję wartości w kolumnie tabeli. Zakres wartości w kolumnie dzieli się na przedziały o tych samych rozmiarach i oblicza się ile wartości wpada do poszczególnych przedziałów. 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

Histogramy są używane przez optymalizator zapytań i można je odczytać z perspektyw słownika danych USER_HISTOGRAMS i USER_TAB_COLUMNS.

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ównoleglania zapytań dla tej tabeli

Proces przyjmujący w serwerze Oracle zgłoszenie użytkownika będzie się starał przyporządkować do obsługi zapytania z tą tabelą liczbę procesów serwera równą temu stopniowi. Urównolegleniu może ulec ładowanie danych do tabeli z różnych plików, również tworzenie tabel i indeksów.

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;

Słowo kluczowe LOCAL oznacza, że podział na partycje jest zgodny z podziałem wierszy tabeli. Gdybyśmy chcieli podzielić pozycje indeksu w inny sposób, musielibyśmy użyć klauzuli GLOBAL PARTITION BY RANGE specyfikując inne zasady podziału.

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);

Rozważmy prostszą przykładową tabelę:

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

Tab. 15.2 Przykładowa tabela

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

Indeks na Płeć Indeks na Stanowisko
Nr wiersza M K D S K ------------ ----------------- --------------------- 1 1 0 1 0 0 2 1 0 0 1 0 3 1 0 0 1 0 4 0 1 0 0 1 5 1 0 0 0 1 6 0 1 0 1 0

Tab. 15.3 Dwa indeksy bitmapowe

Warunek:

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

powoduje wykonanie trzech operacji na wektorach bitowych będących odpowiednimi kolumnami w indeksach przedstawionych powyżej:

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

dając jako wynik zapytania pierwszą i czwartą osobę – wskazane przez miejsca jedynek w wektorze.

Zauważmy, że aby móc użyć indeksu bitmapowego potrzebujemy dwóch dodatkowych struktur danych:

jedna, która w oparciu o stałą wartość atrybutu pozwala szybko wyznaczyć przypisany jej wektor bitów (np. B+ drzewo);
druga, która w oparciu o numer wiersza 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.

5. Transformacja STAR

W przypadku wykrycia prawidłowości:

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

optymalizator stosuje specjalne postępowanie - wykorzystujące w szczególności indeksy bitmapowe.

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;

na:

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);

W rezultacie:

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.

Uwaga: W rzeczywistości obie instrukcje SELECT nie są równoważne i wynik drugiej trzeba jeszcze złączyć z tabelami wymiarów – ale operacje te są już wykonywane na znacznie mniejszych tabelach, niż oryginalne złączenie. Poza tym, na ogół w wyjściowej instrukcji SELECT chodzi o wyliczenie pewnych agregacji, które jeszcze bardziej ograniczają rozmiar wyniku drugiej instrukcji SELECT.

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;

Jest dodatkowe wymaganie w systemie Oracle.

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łaczeniowych (przy bitmapowej implementacji - bitmapowych indeksów złączeniowych).

7. Grupowanie ROLLUP i CUBE

Używając 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ż, Miejsce, Towar, Czas GROUP BY ROLLUP(Czas.Rok, Miejsce.Miasto, Towar.Kategoria);

da wynik w rodzaju (oczywiście używane narzędzie OLAP przedstawi wynik tego zapytania w bardziej czytelny sposób np. za pomocą wykresu):

   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

Tab. 15.4 Wynik operacji ROLLUP

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);

daje wynik:

    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

Tab. 15.5 Wynik operacji ROLLUP

Operator ROLLUP daje więc tę 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

Tab. 15.5 Wynik operacji CUBE

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)

15.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.

15.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 kolejnościach grupowanych kolumn.

15.6 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.

Strona przygotowana przez Lecha Banachowskiego - 12/03/05 .

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

	WA	KA	Razem
1995	63	81	144
1996	38	107	145
1997	75	35	110
Razem	176	223	339