Data Cube Štefan Ignáth Tomáš Severýn
Úvod
Aplikace poskytující analýzu dat typicky pracují s jednou či více vrstvami agregací ve standardním SQL pomocí operátoru Group By a agregačních funkcí lze dosáhnou pouze 0 až 1 dimenzionální agregáty
N-dimenzionální zevšeobecnění
Operátor - datová kostka (DATA CUBE) • zjednodušeně kostka (cube)
Zevšeobecňuje datové operace potřebné pro analýzu dat • Histogram • Dimenzionální model • Mezisoučty
Datová analýza
Hledá různé vzorky v datech • kategorie • trendy
4 základní kroky • formulace otázky nad velkou databází • extrahování agregovaných dat do souboru nebo tabulky • vizualizace • analýza výsledku a formulace nového dotazu
Vizualizace
Potřebná pro zobrazení • trendů • rozdílů • anomálií
Výstup většinou jako množiny • množiny můžou být obecně v Nrozměrném prostoru
Více rozměrů
<=3 Standardně pomocí 2,3D grafiky čas(pohyb) a barvy přidávají další dva rozměry • dohromady tedy 5 dimenzí
Proč více dimenzí
SQL tabulky jsou ploché Ale Relační systémy modelují Ndimenzionální jako relaci s N atributy N - atributových domén
Situační příklad 1
Počasí 4 rozměry (místo = „x,y,z“,čas) další sloupce – měření v 4D prostoru • teplota, tlak, vlhkost a rychlost větru • často hodnoty agregované přes čas, nebo prostor
Tabulka 1. Počasí:
Zem. šírka
Zem. délka
Nad. výška
Teplota
Tlak
27/11/94:1500
37:58:33N
122:45:28W
102
21
1009
27/11/94:1500
34:16:18N
27:05:55W
10
10
1024
Čas
Standardní SQL
Dovoluje agregaci jen přes jednu dimenzi Jednoduché příklady • SELECT AVG(Teplota) FROM Počasí; SELECT COUNT(DISTINCT Čas) FROM Počasí
Zvláštní agregační fce
Mnoho systémů si přidává statistické, fyzikální, matematické, finanční a další funkce Illustra • Init(&handle) – nastaví handle • Iter(&handle) – přidá do handle hodnotu • Hodnota = final(&handle) – vrátí co spočetl
GROUP BY
Agregační funkce vrátí jedinou hodnotu pomocí GROUP BY můžeme vytvořit množinu hodnot indexovaných množinou atributů v klauzuli Group By Systém RED BRICK – rozšířil Group By, ale není std. SQL
Problémy s GROUP BY
Agregační funkce jsou často používané Př. Databázový benchmark (test výkonu, česky občas benčmark) Tabuľka 2. SQL agregáty ve standartních benčmarcích
Otázka
Agregáty
Počet GROUP BY
TPC-A,B
1
0
0
TPC-C
18
4
0
TPC-D
16
27
15
Wisconsin
18
3
2
AS3AP
23
20
2
SetQuery
7
5
1
Benchmark
Problémy GROUP BY
Histogramy
• Agregáty bez vypočítané kategorie Př:počasí v závislosti na dni a zemi SELECT den, stát, MAX( Teplota ) FROM Počasí GROUP BY Day( Čas ) AS den, Country( Zem. Délka, Zem. Šířka ) AS stát;
Problémy GROUP BY
Některé implementace podporují histogramy, ale není to standard proto je potřeba vyrobit tabulkově orientovaný dotaz SELECT deň, štát, MAX( Teplota ) FROM Počasie FROM ( SELECT Day( Čas ) AS deň, Štát( Zem. Šírka, Zem. Dĺžka ) AS štát, Teplota FROM Počasie ) AS foo GROUP BY deň, štát;
Vícedimenzionální dotazování
V případě, že se chceme pohybovat pomocí dimenzí • nahoru(roll-up) zevšeobecňování informací • dolu (drill-down) získávání agregátů k jemnějším datům
Př: • roll-up město->stát->kontinent • drill-down rok->měsíc->den
Používaná řešení
Správy často používají • co atribut to jednotlivá agregace
Problém - co null hodnoty v klíči Tabuľka 3. Prodej aut podle Modelu, Roku a Barvy Rok
Barva
Prodej podle Modelu, Roku a Barvy
1994
Č
50
B
40
Model Chevy
Prodej podle Modelu a Roku
Prodej podle Modelu
90 1995
Č
85
B
115 200 290
Řešení?
Přidána hodnota ALL Duplikovány klíče Naplnění • jednoduchý dotaz,kde je pro každou dimenzi jeden UNION
Tabuľka 4. Sumár predaja Rok
Farba
Jednotiek
Chevy
1994
Č
50
Chevy
1994
B
40
Chevy
1994
ALL
90
Chevy
1995
Č
85
Chevy
1995
B
115
Chevy
1995
ALL
200
Chevy
ALL
ALL
290
Model
Naplnění
jednoduchý dotaz, kde je pro každou dimenzi jeden UNION
SELECT Model, ALL, ALL, SUM( Predaj ) FROM Predaj WHERE Model = ‘Chevy’ GROUP BY Model UNION SELECT Model, Rok, ALL, SUM( Predaj ) FROM Predaj WHERE Model = ‘Chevy’ GROUP BY Model, Rok UNION SELECT Model, Rok, Farba, SUM( Predaj ) FROM Predaj WHERE Model = ‘Chevy’ GROUP BY Model, Rok, Farba;
Problém
Tato agregace je nesymetrická Tabulka 4 neagreguje prodej podle roku Přidám
UNION SELECT Model, ALL, Farba, SUM( Predaj ) FROM Predaj WHERE Model = ‘Chevy’ GROUP BY Model, Rok
Symetrie
Výsledkem symetrické agregace je křížová tabulka (cross-tabulation) někdy do češtiny (výrobci tabulkových editorů) překládáno jako pivotní tabulky Tabuľka 5. Predaj Chevy – Krížová tabulka 1994
1995
Spolu
Čierna
50
85
135
Biela
40
115
155
Spolu
90
200
290
Chevy
více dimenzí
Musíme přidat další křížovou tabulku např pro Ford Ford
1994
1995
Spolu
Čierna
50
85
135
Biela
10
75
85
Spolu
60
160
220
Tímto se dostáváme do 3. rozměru Reprezentace pomocí křížových tabulek je ekvivalentní s ALL formulací
Proč voláme po zlepšení I
Reprezentace Tabulky 4 a sjednocení GROUP BY „řeší“ problém reprezentace agregovaných dat v relačním modely. Problémem zůstáva vyjádření histogramů, roll-up-ů, drill-down-ů a krížových tabulkových otázek pomocí konvenčního SQL
Proč voláme po zlepšení II
6D krížová tabulka potřebuje sjednocení 64 členů a 64 různych GROUP BY tzn. 64 průchodů dat a 64 tříďení - dlouhé vykonávání dotazů. Křížové tabulky pomocí tradičního SQL nejsou relačními objekty. V tabulce 5 a 5a je poslední řadek a poslední sloupec „zvláštní“
Data CUBE (Datová kostka) Aggregate Sum
Group By (with total) By Color
RED WHITE BLUE
•Zevšeobecnění předchozích ideí je jasné •0 rozměrný je jen výsledek fce •1 rozměrný – group by •2 rozměrný – křížové tabulky •3 – datová krychle 3 dim.
Sum
Cross Tab Chevy Ford By Color
RED WHITE BLUE
By Make Sum
The Data Cube and The Sub-Space Aggregates FO CH RD EV Y
By Year
0 199 991 1 992 1 993 1
By Make
By Make & Year
RED WHITE BLUE
By Color & Year By Make & Color
Sum
By Color
Využití
OLAP aplikace( podskupina MOLAP ) Datové sklady Dolování dat z rozsáhlých databází
Rozšíření GROUP BY o CUBE
Operátor CUBE (kostka) rozšiřuje syntaxi : GROUP BY ( { (
| ) [ AS ] [ ] ,...} [ WITH ( CUBE | ROLLUP ) ] )
Jak to funguje?
Operátor kostka vytváří tabulku obsahující všechny agregované hodnoty poslední řádek je ALL,ALL,....,ALL,F(*) Nazývá se úplný agregát Nižší agregáty mají nižší počet ALL hodnot
Jak to funguje?
Počet řádků takto vytvořených • záleží na počtu atributů • na kombinaci daných atrib. v datech
Bez detailních znalostí dat se velikost nedá předpokládat, pouze horní mez Horní mez • násobek počtu různých hodnot+1 pro každý atribut • označme počet hodnot i-teho atributu
m = ∏ (ci + 1)
Použitie I
GROUP BY stĺpec WITH CUBE nevráti hodnoty ALL v riadkoch obsahujúcich superagregáty. Transanc-SQL (T-SQL) vráti hodnoty NULL, typu nazývaného GROUPING NULL, znamenajúce „všetky existujúce hodnoty pre tento stĺpec“. Funkcia GROUPING dovoľuje rozlíšiť medzi rôznymi typmi NULL.
Použitie II
Operátor kocka vráti maximálny počet riadkov iba ak sa všetky kombinácie hodnôt vyskytli v dátach. Inak je počet vrátených riadkov menší. Pre nahradenie vráteného NULL možeme použit SQL funkciu ISNULL.
Príklad SELECT Jednotky=SUM(Predane_jednotky),Produkt= ISNULL(Produkt, 'ALL'), Krajina = ISNULL(Krajina, 'ALL'), Jazyk = ISNULL(Jazyk, 'ALL') FROM Predaj_produktu GROUP BY Produkt, Krajina, Jazyk ID Produkt Krajina Jazyk Predane_jednotky WITH CUBE
Nad tabuľkou 6 Predaj_produktu:
1
Word
US
French
5
2
Word
US
Spanish
87
3
Word
US
English
62
4
Word
Canada
French
54
5
Word
Canada
Spanish
95
6
Word
Canada
English
49
7
Word
Mexico
French
31
8
Word
Mexico
Spanish
54
9
Word
Mexico
English
71
10
Excel
US
French
64
11
Excel
US
Spanish
62
12
Excel
US
English
63
13
Excel
Canada
French
52
14
Excel
Canada
Spanish
9
15
Excel
Canada
English
55
16
Excel
Mexico
French
27
17
Excel
Mexico
Spanish
62
18
Excel
Mexico
English
39
Funkcia GROUPING
Ak dáta neobsahujú žiadne NULL vo význame žiadnych dát • možeme použiť funkciu ISNULL na zmenu hodnôt NULL (vo význame ALL) na hodnotu ALL
Predstavme si, že tabuľka povoľuje vloženie hodnoty NULL do niektorého stĺpca a vložíme nasledujúci riadok:
INSERT Predaj_produktu VALUES (NULL, NULL, ‘US’, 10);
Funkcia GROUPING
Posledný riadok v kocke, tak ako sme si ju predtým definovali, by bol nerozlíšiteľný od teraz vloženého riadku Na odlíšenie tohto rozdielu existuje funkcia GROUPING().
Vracia • 1(TRUE) Ak je element ALL • 0(FALSE) ak je element dátová hodnota alebo explicitná hodnota NULL
GROUPING berie ako parameter meno stľpca
Tabuľka obsahujúca riadok s NULL SELECT Jednotky = SUM(Predane_jednotky), Produkt, ‘ALL Produkt’ = GROUPING(Produkt), Krajina = ‘ALL Krajina’ = GROUPING(Krajina), Jazyk = ‘ALL Jazyk’ = GROUPING(Jazyk) FROM Predaj_produktu GROUP BY Produkt, Krajina, Jazyk WITH CUBE
Produkt Jednotiek
Výsledok Tabuľka 7
Všimnime si, že prvý a posledný riadok majú rovnako hodnoty NULL v stľpcoch Produkt a Krajina, ale odlišujú sa v stľpcoch ALL Produkt a ALL Krajina.
ALL Produkt
Krajina
ALL Krajina
Jazyk
ALL Jazyk
10
NULL
0
NULL
0
Spanish
0
10
NULL
0
NULL
0
NULL
1
10
NULL
0
NULL
1
NULL
1
55
Excel
0
Canada
0
English
0
9
Excel
0
Canada
0
Spanish
0
64
Excel
0
Canada
0
NULL
1
39
Excel
0
Mexico
0
English
0
62
Excel
0
Mexico
0
Spanish
0
101
Excel
0
Mexico
0
NULL
1
63
Excel
0
US
0
English
0
62
Excel
0
US
0
Spanish
0
125
Excel
0
US
0
NULL
1
290
Excel
0
NULL
1
NULL
1
49
Word
0
Canada
0
English
0
54
Word
0
Canada
0
French
0
95
Word
0
Canada
0
Spanish
0
198
Word
0
Canada
0
NULL
1
62
Word
0
US
0
English
0
...
...
...
...
...
...
...
5
NULL
1
US
0
French
0
149
NULL
1
US
0
Spanish
0
279
NULL
1
US
0
NULL
1
157
Excel
0
NULL
1
English
0
111
Word
0
NULL
1
English
0
268
NULL
1
NULL
1
English
0
59
Word
0
NULL
1
French
0
59
NULL
1
NULL
1
French
0
10
NULL
0
NULL
1
Spanish
0
133
Excel
0
NULL
1
Spanish
0
182
Word
0
NULL
1
Spanish
0
325
NULL
1
NULL
1
Spanish
0
Použitie CASE SELECT Jednotky = SUM(Predane_jednotky), Produkt = CASE WHEN (GROUPING(Produkt)=1) THEN ‘ALL’ ELSE ISNULL(Produkt,’N/A’) END, Krajina = CASE WHEN (GROUPING(Krajina)=1) THEN ‘ALL’ ELSE ISNULL(Krajina,’N/A’) END, Jazyk = CASE WHEN (GROUPING(Jazyk)=1) THEN ‘ALL’ ELSE ISNULL(Jazyk,’N/A’) END FROM Predaj_produktu GROUP BY Produkt, Krajina, Jazyk WITH CUBE
Produkt
Krajina
Jazyk
10
N/A
N/A
Spanish
10
N/A
N/A
ALL
10
N/A
ALL
ALL
55
Excel
Canada
English
9
Excel
Canada
Spanish
64
Excel
Canada
ALL
39
Excel
Mexico
English
62
Excel
Mexico
Spanish
101
Excel
Mexico
ALL
63
Excel
US
English
62
Excel
US
Spanish
125
Excel
US
ALL
290
Excel
ALL
ALL
49
Word
Canada
English
54
Word
Canada
French
95
Word
Canada
Spanish
...
...
...
...
Jednotiek
Výsledok Tabuľka 8
62
ALL
Mexico
Spanish
101
ALL
Mexico
ALL
125
ALL
US
English
5
ALL
US
French
149
ALL
US
Spanish
279
ALL
US
ALL
157
Excel
ALL
English
111
Word
ALL
English
268
ALL
ALL
English
59
Word
ALL
French
59
ALL
ALL
French
10
N/A
ALL
Spanish
133
Excel
ALL
Spanish
182
Word
ALL
Spanish
325
ALL
ALL
Spanish
Tabuľka
Tabuľka obsahuje každú kombináciu produktu, jazyka a krajiny Pre uloženie takejto kocky pre ďalšie požiadavky možeme zmeniť požiadavok na pohľad alebo uložiť výsledok do novej tabuľky. Nasledujúcim spôsobom uložíme výsledok do novej tabuľky príhodne nazvanej Vsetky_kombinacie
Vsetky_kombinacie SELECT Jednotky = SUM(Predane_jednotky), Produkt = CASE WHEN (GROUPING(Produkt)=1) THEN ‘ALL’ ELSE ISNULL(Produkt,’N/A’) END, Krajina = CASE WHEN (GROUPING(Krajina)=1) THEN ‘ALL’ ELSE ISNULL(Krajina,’N/A’) END, Jazyk = CASE WHEN (GROUPING(Jazyk)=1) THEN ‘ALL’ ELSE ISNULL(Jazyk,’N/A’) END INTO Vsetky_kombinacie FROM Predaj_produktu GROUP BY Produkt, Krajina, Jazyk WITH CUBE
ROLLUP
Možnosť použiť operátor CUBE na operátor GROUP BY je veľmi silným nástrojom. Výsledok často obsahuje viac riadkov ako je potrebné SQL nám dáva možnosť použiť operátor ROLLUP ako alternatívu k operátoru CUBE ROLLUP vracia iba hodnoty pre hierarchiu atribútov, ktoré špecifikujeme.
CUBE na ROLLUP SELECT Jednotky = SUM(Predane_jednotky), Produkt = CASE WHEN (GROUPING(Produkt)=1) THEN ‘ALL’ ELSE ISNULL(Produkt,’N/A’) END, Krajina = CASE WHEN (GROUPING(Krajina)=1) THEN ‘ALL’ ELSE ISNULL(Krajina,’N/A’) END, Jazyk = CASE WHEN (GROUPING(Jazyk)=1) THEN ‘ALL’ ELSE ISNULL(Jazyk,’N/A’) END FROM Predaj_produktu GROUP BY Produkt, Krajina, Jazyk WITH ROLLUP
Výsledok
je oveľa kompaktnejší ako výsledok CUBE neodpovedá na každú otázku, na ktorú odpovedá CUBE • stále dostaneme viac informácií ako použitím obyčajného GROUP BY Výsledok ROLLUP je podobný výsledku, ktorý dáva operátor T-SQL COMPUTE BY • ale ROLLUP generuje relačnú tabuľku narozdiel od operátora COMPUTE BY.
Záver
Použitie operátorov CUBE a ROLLUP zaleží na tom, koľko informácií (a aké podrobné) je potrebné z dát získať. Pri generovaní CUBE sa najviac času strávi pri operácii GROUP BY. Práca ktorú musí SQL Server vykonať naviac pre vykonanie operátoru CUBE oproti bežnému GROUP BY je zanedbateľná.
Implementované
Microsoft SQL server 2000 Oracle 9i SQL7
