Kővári Attila BI tanácsadó, SQL Server MVP biprojekt.hu Kovari.Attila @ biprojekt.hu

1. Hatékony SQL Server 2005 Analysis Services (SSAS)-alapúBI rendszerek tervezése Kővári Attila BI tanácsadó, SQL Server MVP www.biprojekt.hu Kovari.Attila@biprojekt.hu

2. Az előadásról Kinek szól az előadás: Akik már ismerik valamennyire az AnalysisServices-t. Tudnak dimenziót, kockát építeni. Az előadás célja Hogy segítséget adjon Önöknek hatékony OLAP-alapúvezetői információs Rendszerek, vagy BI megoldások tervezéséhez.

3. Tematika • Miért használjunk OLAP-ot? • Hatékony OLAP rendszerek alapkövetelményei • Hatékony • Dimenziók • Attribútumok • Hierarchiák tervezése • Az idő dimenzió jelentősége és problémái • Adatkockák tervezési kérdései

4. Miért használjunk OLAP-ot? • Egy jól megépített csillagséma és egy OLAP kocka elemei egymásnak kölcsönösen megfeleltethetőek. (1:1 mapping) • Dimenzió tábla = dimenzió (Adatkocka éle) • Ténytábla = Adatkocka cellája • Csillag séma = Adatkocka • Akkor miért használjuk? • Lényegesen jobb lekérdezési sebesség (és nehéz úgy megfektetni, mint a relációst) • Klasszisokkal jobb riportkészítő és lekérdező eszközök léteznek hozzá, mint a relációshoz • Lényegesen jobb elemzési támogatást nyújt (idősor összehasonlítások) • Jogosultság kezelés: A relációs oldalon nehéz olyan jogosultság kezelést kialakítani, hogy valaki láthatja az összegző szint adatait, de részletező sorokat már nem (ad-hoc lekérdezések esetén)

5. Tematika • Miért használjunk OLAP-ot? • Hatékony OLAP rendszerek alapkövetelményei • Hatékony • Dimenziók • Attribútumok • Hierarchiák tervezése • Az idő dimenzió jelentősége és problémái • Adatkockák tervezési kérdései

6. Hatékony OLAP rendszerek alapkövetelményei • Építsünk jó csillagsémát! • Ne bízzuk az AnalysisServices-re a csillag séma vagy az ETL folyatat hibáinak, hiányosságainak kijavítását!!! • Ismeretlen dimenzióelem, duplikált dimenzióelem, ismeretlen szülő, … Mind-mind ETL probléma, ott kell megoldani! • Hiányzó elsődleges kulcsok, kalkulációk mind-mind csillagséma probléma, ott kell megoldani! • Ha ezen követelményeknek megfelelünk, akkor a problémák java részét kiküszöböltük

7. Tematika • Miért használjunk OLAP-ot? • Hatékony OLAP rendszerek alapkövetelményei • Hatékony • Dimenziók • Attribútumok • Hierarchiák tervezése • Az idő dimenzió jelentősége és problémái • Adatkockák tervezési kérdései

8. Dimenziók – Attribútumok – Hierarchiák (alapfogalmak) • Dimenziók • Vizsgálati szempontok (idő, termék, vevő, …) • A dimenziók attribútumokból, és hierarchiákból épülnek fel. • Attribútumok • Felfogható, mint egy hierarchia szintjei (Év, negyedév, hónap, nap,…) • Felfogható, mint dimenzió elemek tulajdonsága (egyszintű hierarchia) (Hét napjai, Munka-, vagy szabadnap) • Attribútumok kapcsolata • 1:1 es attribútum (pl. egy ügyfél e-mail címe, telefonszáma, …) (ex memberproperty) • 1:M-es attribútum (Hét napjai) • Hierarchiák • Az attribútumok láncolata (év alatta negyedév, alatta hónap, …) • Fajtái • Természetes (natural) • Reporting. (Sió, alatta 1 literes, alatta 12% gyümölcstartalom, ..)

9. Dimenziók – Attribútumok – Hierarchiák (Tervezés) Attribútumok: (User) Hierarchia Attribútum hier: (1:M Attribútum) Attribútum reláció (1:1-es Attribútum) Termék ALL AttributeHierarchyEnabled=false Termékcsoport Termékcsoport Gyümölcstart Termékek Gyümölcstart Kiszerelés Kiszerelés EAN (Cikk kód) EAN (Cikk kód)

10. Hatékony dimenziók tervezése • Használjunk kevés dimenziót (5-10/kocka) sok-sok attribútummal és hierarchiával • Építsünk egy „conform” dimenziót (pl. Dátum), és ezekből származtassunk sok-sok szerepjátszó dimenziót (rendelés dátuma, szállítás dátuma, …) • Csak azokat az elemeket válogassuk be a szerepjátszó dimenzióba, amelyre az adott téma elemzőjének szüksége lesz (Belföldi vevők, export vevők)

11. Hatékony attribútumok tervezése • Egy attribútum ne legyen egyszerre eleme egy attribútum hierarchiának (Attribute hierarchy), és egy felhasználói hierarchiának (user hierarchy). • Csak a szükséges információkból képezzünk attribútumot! • Minden attribútum rendelkezzen EGYEDI kulccsal! (Q1 helyett Q1 2007, Q1 2006) • A kulcsok legyenek egész számok (mint a surrogate key-k) • Állítsuk Rigid-re azon attribútumok RelationshipTypetulajdonságát, amelyek nem változnak az időben. (pl. az ügyfél neme) • Használjunk default membert a nem aggregálható attribútumokra!

12. Hatékony attribútum relációk tervezéseHasználjunk tranzitív relációkat! A tranzitív reláció olyan reláció, amely szerint ha az első és második elem, továbbá a második és harmadik elem azonos viszonyban áll egymással, akkor ez a viszony az első és harmadik elem között is fennáll. • Azon attribútumokat kössük össze, amelyek között a valóságban is létezik KÖZVETLEN kapcsolat! • Az Analysis Services felismeri és megfelelően használja a tranzitív relációkat • A tranzitív reláció segít az aggregáció tervezőnek!

13. Hatékony attribútum relációk tervezésekerüljük a redundáns relációkat! • Megnövelik a dimenzió méretét • Megnehezítik az aggregáció tervezést • Megnövelik a felösszegzések időszükségletét • Speciális esetben csupa NULL értéket adhat vissza a lekérdezés

14. Hatékony hierarchiák tervezése A hierarchiák csak az attribútumok közti kapcsolatot írják le (előre definiált bejárási útvonal) Az attribútumok beágyazásával előállíthatunk ad-hoc hierarchiákat is Akkor miért használjuk? Mert az ember is használ hierarchiákat fogalmai rendezéséhez. Mert a kliens alkalmazások még hierarchiákra (és nem attribútumokra) vannak optimalizálva Mert segít az aggregáció tervezőnek optimalizálni

15. Hatékony hierarchiák tervezésefolytatás… Csak természetes hierarchiákat definiáljunk (ország-város; év-negyedév-hónap) Ne definiáljunk reporting hierarchiákat! Ezt bízzuk a felhasználókra (Összes termék, alatta 1 literes kiszerelésűek, alatta alma ízűek, alatta 12%-os gyümölcstartalmúak) klasszikus hiba: ország, alatta vevő

16. Tematika Miért használjunk OLAP-ot? Hatékony OLAP rendszerek alapkövetelményei Hatékony Dimenziók Attribútumok Hierarchiák tervezése Az idő dimenzió jelentősége és problémái Adatkockák tervezési kérdései

17. Hatékony idő dimenzió tervezése • Az idő dimenzió az OLAP-alapúrendszerek lelke • Mind a dimenziónak, mind attribútumainak Time típusúnak kell lennie! • Az attribútumait rendezni kell! (OrderBy tulajdonság) • Mindig teljes évet vegyünk fel! • Kódok: • Memberkey (Mesterséges kulcs: 20070516) • Membervalue (természetes kulcs: 2007-05-16) • Membername (május 16, 2007)

18. Hatékony idő dimenzió tervezése • Hierarchia szintenként 2 attribútum • Hónap attribútum • Elemei: január, 2007; február, 2007; • Elemszáma = évek száma * 12 • Ebből építsünk hierarchiát! • Az év hónapjai attribútum • Elemei: január, február, március, … • Elemszáma = 12 • Ez legyen független attribútum (Ezt használjuk szezonalitás vizsgálathoz.)

19. Hatékony idő dimenzió tervezéseYTD, YoY, … • Hova tegyük a YTD, YoY, … kalkulációkat? • Measures? • Idő dimenzióba mint attribútum? • Hozzunk létre külön idősor kalkulációk dimenziót!

20. Hatékony idő dimenzió tervezésea problémák: • Gyémánt alak (Diamond shape) • Egy dimenzión belül két olyan legfelső szint, ahol nincs ALL member

21. Hatékony idő dimenzió tervezésének problémái: ALL level • Az idő dimenzióra nem teszünk ALL szintet, mert üzletileg nincs értelme! • De ha két hierarchiát is használunk, (Pénzügyi, naptári) akkor kénytelenek vagyunk. • Megoldás: Vagy ALL szint, vagy csak egyféle hierarchia.

22. Hatékony adatkockák tervezése • Használjuk a forrásadat collation-jét • Válasszuk a szükséges legkisebb adattípust

23. Összefoglaló • Építsen jó csillagsémát, és erre ültessen egy OLAP kockát! • Használjon kevés „conform” dimenziót sok attribútummal • Fordítson extra figyelmet az attribútum kapcsolatok tervezésére • Csak természetes hierarchiákat definiáljon

24. További információk • OLAP Design Best Practices for Analysis Services 2005 • http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx • Analysis Services 2005 Performance Guide • http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc • Project REAL: Analysis Services Technical Drilldown • http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx • Magyar nyelvű anyagok • http://www.biprojekt.hu/blog/Analysis_Services_2005.htm