SQL – OLAP 2. óra
Multi-dimenzionális adatmodell A normalizált relációs modell bonyolult a felhasználók számára TELEP(tkod, nev, kozpont, regio,...) TERMÉK(kod, megnevezes, egysegar,...) TERMELES(termek, telep, datum, db, kategoria,...) ‘termelés alakulása a keleti régióra vonatkozóan az elmúlt három hónapra vonatkoztatva..’ CREATE VIEW v1 AS SELECT termek, datum, sum(db) as odb FROM termeles WHERE datum BETWEEN sysdate() AND sysdate() – 90 GROUP BY termek, datum; SELECT b.megnevezes, c.odb, b.egysegar*c.odb as ertek, c.datum FROM Telep a, termek b, v1 c WHERE a.tkod = c.telep AND c.termek = b.kod AND a.regio = “Kelet” ORDER BY megnevezes, datum ; Kovács László,GEIAL
1
Multi-dimenzionális adatmodell ‘ugyanez keresztreferencia táblázat formában .. grafikonon…’ CREATE VIEW v2 AS SELECT b.megnevezes, c.odb, b.egysegar*c.odb as ertek, c.datum FROM Telep a, termek b, v1 c WHERE a.tkod = c.telep AND c.termek = b.kod AND a.regio = “Kelet” ORDER BY megnevezes, datum ; SELECT SUM(CASE megnevezes WHEN ‘cipo’ THEN ertek ELSE 0) CIPO, SUM(CASE megnevezes WHEN ‘kalap’ THEN ertek ELSE 0) KALAP, SUM(CASE megnevezes WHEN ‘ing’ THEN ertek ELSE 0) ING, … FROM v2 GROUP BY datum
termékek dátumok Kovács László,GEIAL
Multi-dimenzionális adatmodell A relációs táblák egydimenziós (egy kulcs) struktúrák Lehetővé kell tenni, hogy több kulcs is rendelhető legyen az adatokhoz
termek
telep
dátum
Adatkocka felépítése Kovács László,GEIAL
2
Adatkocka és relációs tábla összevetése termék
termelés
telep
termek
telep dátum
Kétirányú átalakítás, ekvivalens struktúrák Kovács László,GEIAL
Adatkocka alkotó elemek Változó (measure)
Tény (fact)
Adatkocka (cube)
Tag (member) Dimenzió
(dimension)
termek
Dimenzió érték Tulajdonság (attribute)
telep
dátum
Adatcella Dimenzió hierarchia
Kovács László,GEIAL
3
MD séma modell
Vásárlás
Dátum - év - hó -- nap
Vevő - név - kód
- érték - tömeg
Bolt - név - cím -
Cella -érték - darab - tömeg
bolt vevő
Csillag (star) modell Kovács László,GEIAL
Minta csillag modell
nehéz a különböző aggregációs szintek, ismétlődő dimenziók kezelése Kovács László,GEIAL
4
MD séma modell
reklamáció
termék
hónap
napi forgalom forgalom
bolt
dátum
napi forgalom
Galaxis (fact constellation) modell Kovács László,GEIAL
Minta a galaxis modellre
Kovács László,GEIAL
Nehéz a kapcsolódó dimenziók kezelése
5
Csillag modell A forgalmat bolt és régió bontásban is szeretnénk látni a: két külön dimenzió (érték függőség, ritka kocka) régió
bolt
forgalom
b: egy dimenzió (eltérő szint, nem egyenrangú, korlátozott) bolt - régió
forgalom
külön dimenzió kellene, úgy hogy a kapcsolat megmaradjon Kovács László,GEIAL
Dimenzió hierarchia
Ország A
régió AA
ország
Ország B
régió AB
megye AB1
Járás AB11
Település AB12A
régió megye AB2
megye
Járás AB12
járás település
Település AB122B
előfordulás
bázisszint
séma
Kovács László,GEIAL
6
Összetett dimenzió hierarchia
Kovács László,GEIAL
MD séma modell
hónap termék
forgalom
dátum
kategória bolt
munkahét
Hópehely (snowflake) modell Kovács László,GEIAL
7
Minta a hópehely modellre
Example of a Snowflake Schema Supplier_Key
Time Dimension Table Many Time Attributes
Store Dimension Table Many Store Attributes
Sales Fact Table
Product Dimension Table
Time_Key
Supplier_Key
Product_Key
Product_Key
Store_Key Location_Key
Location Dimension Table Location_Key
unit_sales
Measures
Country
dollar_sales
Location_Key Yen_sales
Region Location_Key
osztott dimenziók kezelése
Kovács László,GEIAL
MD séma modell
reklamáció
gyártó termék
régió
forgalom
bolt
dátum(nap)
hónap negyedév
Hópehely-háló modell Kovács László,GEIAL
8
Befoglalt adatkocka Egy adatkocka (cube) adatait a dimenzióhierarchia mentén haladva és a dimenziók bevonásával eltérő részletezettségi szinten szemlélhetjük. Ezek a cuboid-ok
Kovács László,GEIAL
Date
TV1Qtr2Qtr3Qtr4Qtr sum U.S.A PC VCR sum Canada
Degenerált dimenziók
Country
Teljességet adó dimenziók
Pr od u
konzisztens dimenziók
ct
Tervezési irányelvek
Mexico sum
Többértékű dimenziók Aggregációs függvények lehetnek: - disztributív (min(), max(), sum()) - algebrai (avg(), stddev()) - holistic (median(), rank()) Kovács László,GEIAL
9
Relációs modell konverziója - tényadatok feltárása - kapcsolatok feltárása - ténytáblák , tagok meghatározása - dimenziók kijelölése - idő dimenzió behozatala - egyéb dimenzió bővítés - attribútumok meghatározása - dimenzió hierarchia meghatározása közben ügyelni a következőkre: - dimenzió konzisztencia - dimenzió teljesség - osztott dimenziók - időbeliség (változik-e) Kovács László,GEIAL
Konverziós mintapélda CREATE TABLE TEL(CIM C(30), VEZ REF(DOLG), NEV C(20), HELY REF (VAROS), PK(NEV)) CREATE TABLE TERTEKESIT(ARU REF(TERM), DATUM D, TELEP REF(TEL), OSSZ N(6), SELEJT N(6), PK(ARU,DATUM,TELEP)) CREATE TABLE DOLG(KOD N(3), NEV C(20), BEOSZT REF(BEO), FIZ N(5), PK(KOD)) CREATE TABLE RENDELES(RKOD N(6), IDO D, DARAB N(5), ARU REF(TERM), VEVO REF(VEVO), PK(RKOD)) CREATE TABLE TERM (KOD N(4), NEV C(20), KATEG C(20), PK(KOD)) CREATE TABLE VEVO (KOD N(4), NEV C(20), VAROS REF VAROS, UCIM C(20), PK KOD) CREATE TABLE VAROS (NEV C(20), MEGYE C(20) CREATE TABLE BEO (BKOD N(3), NEV C(20), ALAPFIZ N(6), PK(BKOD)) Kovács László,GEIAL
10
Konverziós mintapélda
TEL
DOLG TERTEKESIT
VAROS
RENDELES
TELEPHELY
VEZETO ERTEKESITES
VAROS
RENDELES
BEO TERM VEVO BEOSZTAS TERMEK VEVO
Kovács László,GEIAL
Konverziós mintapélda ERTEKESITES
RENDELES
TELEPHELY VEZETO DATUM BEOSZTAS VAROS
HO
TERMEK
EV KATEGORIA
MEGYE
VEVO
Kovács László,GEIAL
11
Konverziós mintapélda
TELEPHELY cim nev
ERTEKESITES KATEGORIA nev
OSSZDB SELEJTDB
VEZETO nev BEOSZTAS megn alapfiz
TERMEK cim nev
DATUM nap
VAROS megn MEGYE megn
EV ev
HO ho
Kovács László,GEIAL
MD séma rekordszinten név
dimenzió tábla
típus név típus tény tábla név név típus
típus dimenzió tábla
dimenzió tábla Kovács László,GEIAL
12
Fizikai megvalósítás
TELEPHELY cim nev
TERMEK cim nev
OSSZDB SELEJTDB Audi
Opel
Baja
7 2
Miskolc
9 1
Fiat
6 1
KATEGORIA nev
Lada
7 0
3 2
7 4 7 2
Dorog
4 2
Logikai struktúra
Kovács László,GEIAL
Fizikai megvalósítás K G A
P F
L Audi
Baja Miskolc Dorog
O Opel
7,2
6,1
9,1
7,4
7,2
4,2
Fiat
Lada 7,0
3,2
ritkán kitöltött kocka Kovács László,GEIAL
13
Tervezési irányelvek- minőségbiztosítás
Data Warehouse Back-End Reporting / OLAP tools
Quality Quality Issues Issues Metadata Repository
DSA
Sources
Data Marts
DW Quality Quality Issues Issues
End User Administrator
Administrator
Designer
EDBT Summer School - Cargese 2002
17
Kovács László,GEIAL
DW ≠ Materialized Views ! DS.PS_NEW
DS.PS_NEW1.PKEY, DS.PS_OLD1.PKEY
SUPPKEY=1
DS.PS1.PKEY, LOOKUP_PS.SKEY, SUPPKEY
DATE
COST
1
DIFF1
DS.PS1
Add_SPK1
$2€
SK1 rejected
DS.PS_OLD
A2EDate rejected
U
rejected
1
DS.PS_NEW
DS.PS_NEW2.PKEY, DS.PS_OLD2.PKEY
SUPPKEY=2
Log
Log
Log
DS.PS2.PKEY, LOOKUP_PS.SKEY, SUPPKEY
COST
DATE=SYSDATE
QTY>0
2
DIFF2
DS.PS2
Add_SPK2
NotNULL
SK2 rejected
DS.PS_OLD
AddDate
CheckQTY
rejected
2
Log
Log
DSA PKEY, DAY MIN(COST)
S1_PARTSU PP
FTP1
DW.PARTSU PP
Aggregate1
DW.PARTSUPP.DATE, DAY S2_PARTSU PP
FTP2
Sources
TIME
Aggregate2
V2
DW
A DW több mint aggregált adattáblák rendszere EDBT Summer School - Cargese 2002
V1
PKEY, MONTH AVG(COST)
21
Kovács László,GEIAL
14
Időbeli változás követése A struktúra jelentős változáson mehet át - dimenzió változás - dimenzió hierarchia változás - tényváltozó változása átiródik Változó dimenziók
teljes verzió tulajdonság verzió
Változások konzisztens követése?
Kovács László,GEIAL
Időbeli változás követése Issues
Second Case Study Location dimension: C1
C1
D
D1 2001
2001
D2 2002
2002
D
100
-
D1
-
150
D2
-
50
Query: « Total number of births per year and district ? » 1. Exact view 2001 2002
2. First Structure Evo
D
100
-
?
D1
-
150
?
D2
-
50
?
Nov 8 2002
D
3. Second Structure
2001
2002
Evo
100
200
2001
2002
Evo
D1
40*
150
D2
60**
50
* D1
~ 40 % of the births of D1 ** D2 ~ 60 % of the births of D1 DOLAP 2002 McLean USA
Kovács László,GEIAL
15
Kocka megalkotása A problémakör több fogalmat fog egybe, ezek rendezhetők - hybercube sémába vagy - multicubes sémába -Hypercube egyszerűség ritka kitöltésű nagy eltérés a fizikai szinttől -Multicube: - block mode több változó egységben - series mode egy kocka csak egy változó Kovács László,GEIAL
16
