DW 4. előadás MD modell
Multidimenzioná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 ;
Multidimenzioná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
Induló SQL utasítás általában: SELECT mező1, …, mezőn, sum(menny) as mennyiség FROM táblanév GROUP BY mező1, …, mezőn;
Adattárházak adatmodelljei A relációs modell gyenge pontjai az adattárházaknál: - OLTP orientált, - szétdarabolt (normalizált) adatszerkezet, - SQL nyelv bonyolultsága, - lassú a kapcsolatok kezelése. A relációs modell előnyei az adattárházaknál: - bevált technológia, - szabványosság, megalapozottság, - létező motorok rendelkezésre állása. Adattárház rendszerek: ROLAP : relációs motorra épül MOLAP: MD modellre épül
MD adatmodell alapjai Célkitűzések, elvárások: - emberi szemlélethez közelebb álló legyen, - egyszerűbb parancs felület, - szemléletes, - OLAP orientált.
Elvégzett módosítások: - normalizáltság megszüntetése, - egységbe fogja az adatokat, - vizuális analógiára épít, - adatelemzési műveletekre támaszkodik. A több egydimenziós relációt egy összefogó adatkockába hozza össze.
Multidimenzioná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.
termék
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
FK-kapcsolat
Kétirányú átalakítás, ekvivalens struktúrák
telep
Adatkocka alkotó elemek
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 • Év • Hónap • Nap
Adatkocka alkotó elemek, fogalmak Változó, tag, tény: azon mennyiség aminek az értékét, alakulását vizsgáljuk (amit tárolunk az adatkockában); a működési paraméterek meghatározó mennyisége rendszerint numerikus, additív (aggregálható), de lehet semi-additív (pl. dátum mentén raktárkészlet, vagy tanulmányi átlag), vagy non-additív (pl. szín). Dimenzió: azon mennyiség, amitől függ a változó működési paraméter (a kulcsok, amiknek a függvényében érhetjük el az egyes tényadatokat) lehet összetett, szöveges, leíró. Tag: a változó komponense. Tulajdonság/attribútum: a dimenzió komponense (pl. termék dimenziónál a termék megnevezése, vagy egységára). Adatkocka: a változók és kapcsolt dimenzióik.
Adatkocka alkotó elemek, fogalmak Ténytábla: • idegen kulcs attribútumok gyűjteménye; • elsődleges kulcsa idegen kulcsokból áll össze, plusz az időbeliség; • reprezentálja az üzleti dimenziót (főleg numerikus adatok). CREATE TABLE TÉNYTÁBLA(AB REF(PROD), TRDATE D, BC REF(LOC), SUM N(6), COUNT N(6), PK(AB, TRDATE, BC))
Adatkocka reprezentációk
CUBE MOLAP
Relation-network ROLAP
MD séma modell Csillag (star) modell Vásárlás
Dátum - év - hó - nap
Vevő - név - kód
- érték - tömeg
Bolt - név - cím - régió
Cella - érték - darab - tömeg
dátum
bolt vevő
Középen vastag keretben a ténycella szerepel, a dimenziók hozzá kapcsolódnak, a dimenzió és a tény megadásnál az attribútumok is szerepelnek.
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ó
termék
napi forgalom forgalom
bolt
hónap
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
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
Összetett dimenzió hierarchia
Egy kulcsot több felbontási szinten is lehet értelmezni.
MD séma modell
hónap termék
forgalom
dátum
kategória bolt
Hópehely (snowflake) modell Bevezeti a dimenzió hierarchiát
munkahét
Hópehely (snowflake) modell
Hópehely (snowflake) modell
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 (4d-s rácsozása).
csúcspont
alap
Tervezési irányelvek Date Konzisztens dimenziók
Degenerált dimenziók
Country
1Qtr2Qtr3Qtr 4Qtr sum TV U.S.A PC VCR Teljességet adó dimenziók sum Canada
Mexico sum
Többértékű dimenziók Aggregációs függvények lehetnek: - disztributív (min(), max(), sum()), - algebrai (avg(), stddev()), - holistic, teljeskörű (median(), rank()).
Összeférő (conformed) dimenziók: olyan dimenziókat kell alkalmazni, amelyek minden adatkockában ugyanolyan jelentéssel és szerkezettel rendelkeznek.
A tartalmi és formai illeszkedés vizsgálatát és kialakítását minden dimenzió esetében el kell végezni. A dimenzió mellett a többi adatmodell elemre is értelmezhető az illeszkedés, a konformitás fogalma. Így a változók esetében is beszélhetünk összeférő változókról, amikor is az azonos változó elnevezés mögött azonos jelentés és azonos szerkezet húzódik meg az adattárház modell minden adatkockája esetén.
Az összeférő változók és dimenziók alkalmazása nemcsak logikai lezártságot jelent, hanem egyben szükséges előfeltétele is a különböző data mart vagy adatbázis forrásokból származó adatelemek integrálásának.
Szempontok a tervezés során a dimenzió és változó struktúrájának kialakításakor : • leíró elnevezés: az elnevezésnek sugallnia kell a tartalmat, • általános jelentésű legyen: a struktúrának minden lehetséges alkalmazási feladat és körülmény között alkalmazhatónak kell lennie, • tiszta, helyes ellenőrzött értékű legyen,
• jól tagolt: mivel az alkalmazások, a feldolgozások szempontjából az az előnyös, ha az igényelt adatelemek már készen rendelkezésre állnak, s nincs szükség bonyolult elemzési, kiemelési funkciókra, (ehhez azonban a tárolt információt elemi információkra és elemi adatelemekre célszerű feldarabolni), • hatékonyan kezelhető, indexelhető: a belső karbantartás hatékonysága szempontjából célszerű, ha az azonosító szerepet betöltő elemek kis méretűek és egyértelmű rendezés értelmezhető rajtuk.
A dimenziók tervezése során az irányelvek betartása mellett is előfordulhatnak olyan esetek, amikor a szokásostól eltérő struktúrákat kell létrehozni. Degenerált dimenzió: olyan dimenziót jelent, amelynek nincs attribútuma, egy tulajdonság nélküli dimenzió (pl. rendelési szám); fizikai szinten a degenerált dimenziók csak a ténytáblában fordulnak elő. A dimenzió a tény változó egyed egy jellemzőjét tartalmazza, kijelölve minden tény cellához egy dimenzió előfordulást. A dimenziók egyértelműen kijelölik az egyes ténycellák pozícióját az adatkockán belül. Előfordulhat, hogy minden tény cellához nem csak egyetlen egy dimenzió előfordulás köthető (pl. orvosi vizsgálatnál több gyógyszer). Többértékű dimenzió: a dimenzióknak több előfordulása is társítható az egyes ténycellákhoz. Beteg Hibás, mert egyértékű lehet a kapcsoló mező.
Gyógyszer - kód - gyártó - név -…
Orvosi vizsgálat - összeg - időtartam -…
Orvos - név - kód -…
- név - irsz. - település -… Dátum - év - hó - nap
Megoldás: egy külön kapcsoló tábla (híd (bridge) tábla) bevezetése, fel kell venni egy külön táblát, amelynek célja a kapcsolódó cella előfordulás és dimenzió előfordulás párosok nyilvántartása. Minden egyes ténycellának lesz egy egyedi csoportkulcsa, s a híd tábla cellái az összetartozó dimenzió kulcsot és csoportkulcsot tartalmazzák. Gyógyszer gykód …
vkód gykód
vizsgálat vkód
A híd táblát nem csak a többértékű dimenziók esetében szokás használni, hanem a hálós dimenziók esetében is, hiszen itt sem közvetlenül mutat a rendszer a tény cellából a hozzá tartozó dimenzió előfordulás hierarchia miden tagjára (pl. cikkek szerzői). Szerző - név - cím - fokozat -…
hivatkozás
Szakcikk - hivatkozási pont - oldalszám …
Terület - név - kód …
Folyóirat - név - kód - kiadó -… Dátum - év - hó - nap
Nem ismert előre, hogy milyen mélységű is lesz a dimenzió háló, ezért nem lehet fixen beépíteni a cella struktúrába az összes dimenzió előfordulásra mutató pointert. A tetszőleges méretű dimenzió háló kezelésére a híd kocka alkalmazása a megoldás, mivel segítségével rugalmasan leírhatók a változó méretű kapcsolat rendszerek is. Az egyes híd tábla cella előfordulások az egyes dimenzió előfordulások kapcsolatát adják meg, minden egyes leszármazotti viszonyhoz egy híd táblabeli cella előfordulás fog létrejönni. A híd cella előfordulás két kapcsoló mezőt tartalmaz, az egyik mutat az ős dimenzió előfordulásra, a másik pedig annak egy leszármazottjára.
Technikai okokból célszerű olyan híd cellát is létrehozni, melyben mindkét kapcsoló ugyanazon dimenzió előfordulásra mutat. Ekkor az induló ténytábla cella egy ilyen híd cella előfordulásra fog mutatni, mely mindkét kapcsoló mezőjével az érintett dimenzió előfordulást fogja tartalmazni. A rendszer tartalmazni fog még több olyan híd cella előfordulást is, melyek ezen induló dimenzió előfordulást a belőle származó dimenzió előfordulásokkal kötik össze.
skód1 skód2
Szerző skód név …
ckód skód
Cikk ckód
Egy dimenzió típus többszörözötten is kapcsolódhat egy ténytáblához. Például egy szervizelési folyamathoz többféle szerepkörben is hozzárendelhetjük az idő dimenziót (bejelentési, kiszállási, kezdési, befejezési időpont). Ezen elemek mindegyikét lehet egy-egy önálló dimenzióként szerepeltetni az adatmodellben, hiszen mindegyik más-más jelentéssel bír. A szokásos megvalósítás szerint ekkor mindegyik dimenzió mögött egy önálló dimenzió előfordulás táblázat állna. A szervizes példában az időpontok megadása több helyen is ismétlődne. Fizikailag egy megadott időpont leírása több helyen is előfordulna redundanciát okozva. A rendszer jobb helykihasználása végett azonban hasznosabb, ha magát az idő adatokat csak egyszer tároljuk le, és a különböző dimenziók esetében közösen kerülnek felhasználásra az egyes dimenzió előfordulások. Szerepkörökkel ellátott dimenzió: egy fizikai dimenzió több különböző logikai szerepkörben is megjelenik az adatkocka modellben.
Mindegyik szerepkör esetén: • a kapcsoló hivatkozások egy közös dimenzió előfordulás halmazba mutatnak, • a dimenzió előfordulásai csak egyszer, redundancia nélkül kerülnek letárolásra. Ezáltal • jelentős helytakarékosság érhető el és • sokkal hatékonyabb lesz az egyes szerepkörökön keresztüli összekapcsolások megvalósítása.
Nem kell különböző táblák rekordjait illeszteni , mert egy táblában helyet foglal az összes előfordulás. Értékelő oktató - okód - név - fokozat
felügyelő Vizsga - jegy - sorszám
Felügyelő oktató - okód - név - fokozat
Vizsga - jegy - sorszám …
értékelő
Oktató -okód -név -fokozat ..
Relációs modell konverziója multidimenzionális modellre
- 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 VAROS(NEV C(20), MEGYE C(20)) CREATE TABLE FUV(FKOD N(3), NEV C(20),CÍM C(50), PK(FKOD))
CREATE TABLE DOLG(KOD N(3), NEV C(20), BEOSZT REF(BEO), FIZ N(5), PK(KOD)) ’plusz CREATE TABLE BEO CREATE TABLE VEVO(KOD N(4), NEV C(20), VAROS REF(VAROS), UCIM C(20), PK(KOD)) CREATE TABLE TEL(CIM C(30), VEZ REF(DOLG), NEV C(20), HELY REF (VAROS), FUVAROZO REF(FUV), PK(NEV)) CREATE TABLE TERM(KOD N(4), NEV C(20), KATEG C(20), PK(KOD)) CREATE TABLE TERTEKESIT(ARU REF(TERM), DATUM D, TELEP REF(TEL), OSSZ N(6), SELEJT N(6), PK(ARU, DATUM, TELEP)) CREATE TABLE RENDELES(RKOD N(6), IDO D, DARAB N(5), ARU REF(TERM), VEVO REF(VEVO), PK(RKOD)) ’PK(ARU, IDO, VEVO)
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 (csak ERTEKESITES) 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 (2D-t nézve)
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ása, - dimenzió hierarchia változása, - tényváltozó változása.
átíródik Változó dimenziók
teljes verzió (history) tulajdonság verzió
Változások konzisztens követése?
Időbeli változás követése Érték változása Dolgozó név = KA fizetés = 5 …
Dolgozó név = KA fizetés = 5 …
Dolgozó név = KA fizetés = 5 …
új fizetés = 8
új fizetés = 8
új fizetés = 8
Dolgozó név = KA fizetés = 8 …
Dolgozó név = KA fizetés = 5 …
Dolgozó név = KA fizetés = 8 …
Dolgozó név = KA fizetés1 = 5 fizetés2 = 8 …
Időbeli változás követése Érték változása Kiegészítő mechanizmusok a méretek keretek között tartásához: • a kis számú gyakran változó tulajdonság szeparálása a többi, ritkán változó tulajdonságtól, • az érték változások gyakoriságának csökkentése egy intervallum-érték tárolásra történő áttéréssel. fizetés kategóriák -11 : A -18 : B -26 : C …
Dolgozó név = KA fizetés = A …
új fizetés = 8
új fizetés = 12
Hatékonyság: • tervezés során, • DW-rendszer motor mechanizmusai.
Dolgozó név = KA fizetés = A …
Dolgozó név = KA fizetés = A …
Dolgozó név = KA fizetés = B …
Időbeli változás követése Dimenzió hierarchia változása 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 - multicube sémába. - Hypercube (4d cube; kocka, amelyiknek 3-nál több dimenziója van; adatok logikailag egyszerű kockák; DW egy központi kocka): egyszerűség, ritka kitöltésű, nagy eltérés a fizikai szinttől. - Multicube (adatok kisebb kockák halmazába szeletelve; DW min. 2 kocka): - block mode több változó egységben, - series mode egy kocka csak egy változó.
Hypercube/hiperkocka Multidimenzionális struktúra Telephely
Termék Raktár A
Idő
Termék Jan.
Metrika
ABC
Költség
BAC
Bevétel
Dátum B
C
Jan. Febr. Márc.
Febr.
Márc.
CBA
Kiadás
DAB
Eladás
DBC
Nyereség
EFG
Selejt
Mennyiség
Beszállító
ABC/Eladás ABC/Költség BAC/Eladás BAC/Költség CBA/Eladás CBA/Költség 450 350 550 450 500 400 380 280 480 360 400 320 400 310 480 410 450 400
Oldal: raktár dim., sor: idő dim., oszlop: termék dim. és metrika kombinálva
Raktár
Vevő
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
