Transcript Document

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 ;
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
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
Adatkocka és relációs tábla összevetése
termék
termelés
termek
telep
dátum
Kétirányú átalakítás, ekvivalens struktúrák
telep
Adatkocka alkotó elemek
Változó
(measure)
Tény
(fact)
Adatkocka (cube)
Tag (member)
Dimenzió
(dimension)
termek
Dimenzió érték
Tulajdonság
(attribute)
Adatcella
Dimenzió hierarchia
telep
dátum
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
Minta csillag modell
nehéz a különböző aggregációs szintek, ismétlődő dimenziók kezelése
MD séma modell
reklamáció
hónap
termék
napi
forgalom
forgalom
bolt
dátum
napi
forgalom
Galaxis (fact constellation) modell
Minta a galaxis modellre
Nehéz a kapcsolódó dimenziók kezelése
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ó
forgalom
bolt
b: egy dimenzió (eltérő szint, nem egyenrangú, korlátozott)
forgalom
bolt
- régió
külön dimenzió kellene, úgy hogy a kapcsolat megmaradjon
Dimenzió hierarchia
Ország A
régió AA
Ország B
ország
régió AB
régió
megye AB1
Járás AB11
Település AB12A
megye AB2
megye
Járás AB12
járás
Település AB122B
előfordulás
település
bázisszint
séma
Összetett dimenzió hierarchia
MD séma modell
hónap
termék
forgalom
dátum
kategória
bolt
Hópehely (snowflake) modell
munkahét
Minta a hópehely modellre
osztott dimenziók kezelése
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
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
Tervezési irányelvek
Teljességet adó dimenziók
Date
TV1Qtr2Qtr3Qtr4Qtr sum
U.S.A
PC
VCR
sum
Canada
Degenerált dimenziók
Country
konzisztens dimenziók
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())
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)
Konverziós mintapélda
CREATE TABLE TEL(CIM C(30), VEZ REF(DOLG), NEV C(20),
HELY REF (VAROS), FUVAROZO REF(FUV), 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 FUV (FKOD N(3), NEV C(20),CÍM C(50),
PK(FKOD))
Konverziós mintapélda
TEL
DOLG
TERTEKESIT
VAROS
TELEPHELY
RENDELES
TERM
VEVO
FUVAROZO
ERTEKESITES
VAROS
FUV
RENDELES
TERMEK
VEVO
Konverziós mintapélda
ERTEKESITES
RENDELES
TELEPHELY
FUVAROZO
DATUM
HO
VAROS
TERMEK
EV
KATEGORIA
MEGYE
VEVO
Konverziós mintapélda
TERMEK
cim
nev
TELEPHELY
cim
nev
ERTEKESITES
KATEGORIA
nev
OSSZDB
SELEJTDB
FUVAROZO
nev,
cim
DATUM
nap
VAROS
megn
MEGYE
megn
EV
ev
HO
ho
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
dimenzió tábla
típus
dimenzió tábla
Fizikai megvalósítás
TELEPHELY
cim
nev
OSSZDB
SELEJTDB
KATEGORIA
nev
Audi
Opel
Baja
7
2
Miskolc
9
1
Dorog
TERMEK
cim
nev
6
1
Fiat
7
0
Lada
3
2
7
4
7
2
4
2
Logikai struktúra
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
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
EDBT Summer School - Cargese 2002
Designer
17
DW  Materialized Views !
DS.PS_NEW
DS.PS_NEW1.PKEY,
DS.PS_OLD1.PKEY
SUPPKEY=1
DS.PS1.PKEY,
LOOKUP_PS.SKEY,
SUPPKEY
COST
DATE
1
DIFF1
DS.PS1
Add_SPK1
SK1
$2€
rejected
DS.PS_OLD
A2EDate
rejected
U
rejected
1
DS.PS_NEW
DS.PS_NEW2.PKEY,
DS.PS_OLD2.PKEY
SUPPKEY=2
2
DIFF2
DS.PS2
Add_SPK2
Log
Log
Log
DS.PS2.PKEY,
LOOKUP_PS.SKEY,
SUPPKEY
COST
DATE=SYSDATE
NotNULL
SK2
rejected
DS.PS_OLD
AddDate
QTY>0
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
Sources
FTP2
TIME

PKEY, MONTH
AVG(COST)
Aggregate2
DW
EDBT Summer School - Cargese 2002
A DW
több mint aggregált adattáblák rendszere
V1
21
V2
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?
Időbeli változás követése
Issues
Second Case Study
Location dimension:
C1
2001
2002
D
100
-
D1
-
150
D2
-
50
C1
D
D1
2001
D2
2002
Query: « Total number of births per year and district ? »
1. Exact view
2001 2002
2. First Structure
Evo
D
100
-
?
D1
-
150
?
D2
-
50
?
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
~ 60 % of the births of D1
** D2
Nov 8 2002
DOLAP 2002
McLean USA
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ó
Projekt feladat
Minta MD modell kidolgozása PE-re
Katica csavargyár
modulok:
1. raktár
2. gyártás
3. rendelés/vevői és saját
4. számlázás
5. munkaügy
6. szerviz
7. bérügy