Information Technology | Databases » Smulovics Péter - OLAP Eszközök

OLAP Eszközök I. rész Alapok Smulovics Péter (smulovicsp@avalon.autbmehu) OLAP Eszközök: Rövid áttekintés a Microsoft SQL Server 2000 OLAP Eszközök felett I. rész Alapok Budapest 2001. szeptember Budapesti Műszaki és Gazdaságtudományi Egyetem Automatizálási és Alkalmazott Informatika Tanszék OLAP Eszközök 2001 Smulovics Péter OLAP Eszközök: Rövid áttekintés a Microsoft SQL Server™ 2000 OLAP Eszközök felett Összefoglalás: Bevezetés az Online Analytical Processing-be (OLAP) és a Microsoft® SQL Server™ OLAP Eszközökbe. Áttekintjük az OLAP terminológiát, koncepciókat és az OLAP Eszköz Architektúrát, és megvitatunk jó néhány kihívást az OLAP implementációval kapcsolatban. I. rész Alapok Bevezetés Az Online Analytical Processing (OLAP) egy növekvő népszerűségű technológia, amely drasztikusan m egnöveli a z ü zleti a nalízis s ikereit, azonban ismert a d rága é s ügyetlen eszközökről, és a rugalmatlan

telepítésről is. A M icrosoft megbirkózott az OLAP problémával és létrehozott egy megoldást, amely a multidimenzionális analízist szélesebb kör számára teszi lehetővé és potenciálisan jóval kisebb TCO-t tesz elérhetővé. A M icrosoft® S QL S erver™ O LAP Eszközök e gy kimerítően teljes O LAP implementáció, ami a Microsoft SQL Server 2000 része. Az OLAP Eszközöknek része egy middle-tier szerver, ami a felhasználók számára lehetővé teszi, hogy modern analízist h ajtsanak végre a datok n agy v olumenén kivételes t eljesítménnyel. Egy másik része az OLAP Eszközöknek a kliens cache és a számítási motor, amit PivotTable® -nek hí vnak, ami tovább segíti a teljesítmény növelését és cs ökkenti a hálózati f orgalmat. A PivotTable segítségével a f elhasználók a nalíziseket h ajthatnak végre, miközben nem csatlakoznak a céges hálózathoz. Az OLAP egy k ulcsfontosságú komponens az adattárházak f eldolgozásakor, és

az OLAP Eszközök megadják azt a szükséges funkcionalitást, ami applikációk tág körében f elhasználható a c ég kimutatásoktól a d öntéstámogatásig. A z O LAP funkcionalitás SQL Szerverbe helyezésével a multidimenzionális analízis megengedhető lett és nagyobb közönséghez jutnak el az OLAP előnyei. Ezen közönség nem csak a kisebb vállalatokat foglalja magában, hanem csoportokat és egyéni felhasználókat is a nagyobb vállalatoknál, amelyek eddig kimaradtak az OLAP iparból az ár vagy a jelenleg kapható eszközök komplexitása miatt. Az OLAP alapjai Történetileg, a céges számítástechnikai befektetések nagy része (ha n em mind), olyan rendszerekbe történt, amelyek nagy mennyiségben adatokat állítanak elő vagy gyűjtenek be, olyanokat, mint a számlázás, a r endelések f eldolgozása é s a felhasználók i nformációi. M anapság olyan t echnikákba és a pplikációkba i s t örténnek befektetések, sőt, kezdik átvenni a

vezető helyet, amelyek újabb értékeket, információkat szűrnek le az összegyűjtött adatokból. A Data warehouse a z adatgyűjtés, tisztítás, egyforma formátumra hozás (pl. a különböző operációs rendszerek okán), é s a z így k apott információ a nalízis c éljából s zéles k özönség számára való eljuttatásának folyamata. A Microsoft Data Warehouse stratégiája Sok évvel ezelőtt a Microsoft két új kezdeményezésbe kezdett bele azzal a céllal, hogy kiterjessze a data warehouse és döntés támogatás lehetőségeit az ü zleti világban. A két kezdeményezés a Microsoft Data Warehouse Framework, egy roadmap a Microsoft termék fejlesztéshez, és a M icrosoft A lliance f or D ata Warehousing, egy i pari-üzleti k oalíció, ami a Microsoft platform elkötelezetteiből áll és a Data Warehousing F ramework a f ejlesztéshez é s m arketinghez. A kezdeményezések a Microsoft következő alapvető elvein alapulnak: • Csökkenteni a

beszerzés, implementáció és karbantartás költségeit. 2/12. oldal OLAP Eszközök 2001 Smulovics Péter • Újraképezni a skálázhatóságot, hogy ne csak a nagy rendszereknek legyen megfelelő, hanem az egyedi felhasználók számára is. • Növelni a third-party integrációs eszközök használhatóságát. A Microsoft Data Warehouse Framework A Data Warehousing Framework egy nyitott architektúra, ami leírja az adatok és meta-adatok megosztását a data warehouse és data mart létrehozásához és menedzsmentjéhez. Az alapvető technológia ez alatt az OLE DB adat interfész és egy példánya a Microsoft Repository-nak, ami egy SQL Szerveren fut. A Microsoft Repository egy adatbázis, ami tárolja a leíró információkat a program komponensekről és azok kapcsolatáról (meta-adat). Kész meta-adat modellek állnak rendelkezésre a Microsoft Repository-ban adatbázis sémákhoz, adat transzformációkhoz, és OLAP adatbázis sémákhoz. Az SQL S erver 2

000 r endelkezésünkre bocsátja azokat a z a lap k omponenseket, amelyek egy data warehouse felépítéséhez és karbantartásához kellenek. Ezek magukban f oglalják a z a datbázis te rvezést g rafikus s éma designerrel; n agy kapacitású tárolást; adat transzformációs lehetőségeket a Data Transformation Services ( DTS) segítségével; és természetesen OLAP lehetőségeket az OLAP Eszközökön át. Kép 1. Data Warehouse Framework Adat komplexitás Annak ellenére, hogy a data warehousing része a végfelhasználó számára az adatok előfeldolgozása, a relácionális data w arehouse a datai n ehezen találhatóak meg, illetve nyerhetőek ki. Gyakran túl bonyolultak az adat-struktúrák ahhoz, hogy megértse őket a felhasználó, vagy a kérdések (mint pl. a "Ki adott el legtöbbet régiónként é s h ónaponként t avaly") t úl b onyolultak S QL n yelven. Né hány i lyen problémát céloznak meg a fejlett lekérdező eszközök, melyek elrejtik

az adatbázis komplexitását. Ennek ellenére a Microsoft hisz abban, hogy optimális megoldás az ilyen p roblémákra, mikor m ultidimenzionális a datokat v izsgálunk, a z O LAP technológia segítségével. Az OLAP nem e gy ú j k oncepció, de e z a z elnevezés csak 1 993-tól i smert. Dr E F Codd adatbázis-szakértő 12 szabályban fogalmazta m eg a z O LAP a pplikáció fogalmát. Nigel Pendse és Richard Creeth a definíciókat a GAMMI teszté finomította, 3/12. oldal OLAP Eszközök 2001 Smulovics Péter ami egyszerűsítve azt jelenti, hogy egy OLAP alkalmazás gyors a nalízisre k épes megosztott multidimenzionális információhalmazon. Részleteiben: • GyorsAz információ többé-kevésbé állandó sebességgel érkezik. A legtöbb lekérdezés öt vagy annál kevesebb másodpercig tart. • AnalízisAlapvető numerikus és statisztikai analízist hajt végre az adatokon. • MegosztottImplementálja a biztonsági követelményeit az esetlegesen bizalmas

adatok megosztásának sok felhasználó felé. • MultidimenzionálisMegfelel az OLAP egyik alapvető követelményének. • InformációMinden adatot és információt elér, ami releváns és szükséges az alkalmazás számára, függetlenül attól, hogy hol van az, és hogy milyen mennyiségű. OLAP Terminológiák és koncepciók Fontos, ho gy d efiniáljunk é s t isztázzunk l e n éhányat a f ontos terminológiák és koncepciók közül, amiket az OLAP-pal kapcsolatban merülhetnek fel. Az OLAP Adat Modell Az OLAP adat modellben a z információhalmaz mint kocka jelenik meg, a mi leíró kategóriákból (dimenziók) és kvantitív értékekből (mértékek) áll. A multidimenzionális adat modell a felhasználók számára könnyűvé teszi komplex lekérdezések megfogalmazását, az adatok elrendezését, a részletes és az összefoglaló közti váltást, és az adatok szűrését vagy v ágását é rtelmes alhalmazokra. P éldául, tipikus d imenziói e gy k

ockának az idő, a hely, az áru, a szállítás, a z o rganizáció é s a s zkenárió ( büdzsé v agy a ktuális). T ipikus m értékei ennek a kockának a dollár eladás, egység eladás, készlet, létszám, bevétel, és a költség. Minden egyes dimenziója az OLAP adat modellnek hiearchiába organizálható, ami az adat részletességi szintjét mutatja. Például az idő dimenzión belül megkülönböztethetjük a következő szinteket: év, negyedév, hónap, nap; ugyanígy a hely viszonylatában: ország, régió, megye, város. Az OLAP modell felhasználója le és fel mozog a szintek között, hogy több vagy kevesebb részletet lásson egyszerre. Aggregáció és Tárolási Modellek Kockák, d imenziók, hiearchiák é s m értékek a multidimenzionális n avigáció fő eszközei a z O LAP-ban. Í gy p rezentálva é s l eírva a z a datokat a f elhasználó a kár intuitívan navigálhat az adatok komplex halmazában. A fő elve az OLAP-nak, hogy a felhasználó

konzisztens válaszidőket kapjon akárhányszor újabb nézetet, szeletet, egyáltalán adatok kér. Mivel az adatot általában csak a legrészletesebb szinten gyűjtjük, emiatt az információk összegzése előre megtörténik. Ezek az előre számolt értékek (aggregációk) az alapjai az MS OLAP jó teljesítményének. Az OLAP technológia első napjaiban a legtöbb gyártó egyetértett abban, hogy az egyetlen tárolási mód az OLAP applikációk számára egy specializált, nem relácionális modell. Később, más gyártók felfedezték, hogy adatbázis struktúrák (csillag és hópehely sémák), indexelés, aggregációk tárolás segítségével relácionális adatbáziskezelő rendszerek (RDBMS) is használhatók az OLAP-hoz. Ezek a gyártók megoldásukat relácionális OLAPnak (ROLAP) hívták. Az első technológia használói a multidimenzionális OLAP (MOLAP) elnevezést kapták. A MOLAP implementációk általában jobban teljesítenek a ROLAP technológiánál,

de problémák vannak a skálázhatóságnál. A másik oldal, hogy a ROLAP implementációk jobban skálázhatóbbak és gyakran tetszetősebbek az ügyfelek számára, mivel újra felhasználhatóak a relációs adatbázisokba fektetett összegek és tapasztalatok. Egy újonnan felfedezés a hibrid OLAP (HOLAP), amelyik kombinálja a ROLAP és MOLAP architektúrákat, hogy mindkettő legjavát nyújthassa: kitűnő teljesítmény és jó skálázhatóság. Egyik HOLAP m egoldás h ogy a részletes rekordokat (ez a 4/12. oldal OLAP Eszközök 2001 Smulovics Péter legnagyobb a datmennyiség) r elácionális a datbázisokban m enedzseljük, m íg a z aggregációkat ettől függetlenül, MOLAP rendszerben tároljuk. OLAP Eszközök Architektúra A Microsoft SQL Szerver OLAP Eszközök elejétől végéig olyan szellemben készült, hogy hatékonyan csökkentse az OLAP applikációk építésével és karbantartásával járó összegeket. Az OLAP Eszközök tartalmaznak mind

szerver, mind kliens (middle-tier) software komponenseket. Kép 2. OLAP Eszközök Architektúra A s zerver oldalon a z OLAP Eszközök szerver e gy Microsoft W indows N T/2000® szerviz képében jelenik meg, és adja az alap számítási funkcionalitást. A programozott hozzáférés az OLAP szerver adminisztratív funkcióihoz egy Decision Support Objects (DSO)-nak hívott jól dokumentált objektum modellen át történik. Az OLAP Manager, a beépített adminisztratív felhasználói felület is a DSO-n alapszik és g azdag f elhasználói é lményeket n yújt p rogramozási tu dás n élkül i s. Az O LAP Manager, ami egy, az OLAP szervertől független gépen is futtatható, lehetővé teszi az a dminisztrátor s zámára, h ogy s ok m ás f unkció m ellett O LAP a dat m odelleket készítsen, hozzáférjen az RDBMSben tárolt információkhoz, aggregációkat készítsen, OLAP adat tá rolókat o sszon m eg. Az O LAP me ta-adat definíciók egy p rivát repositoryban vannak,

de exportálhatóak a Microsoft Repositoryba az OLAP Open Information Model (OIM) segítségével. Az O LAP Eszközök a f orrás a datot b ármely tá mogatott O LE D B a dat szolgáltatón á t elérhetik, ami így nem csak az SQL Szervert, hanem elég sok desktop és szerver adatbázist jelent, mint például: Microsoft Access, Microsoft FoxPro®, Oracle, Sybase, és Informix. Minden adatbázis, ami támogatja az Open Database Connectivity (ODBC) interfészt ugyancsak működőképes az OLE DB azon képessége által, hogy képes az ODBC drivereket úgy becsomagolni, hogy mint natív OLE DB interfészek legyenek elérhetőek. Ezen adat források elhelyezhetőek más platformon is, mint a Windows NT v agy 2 000 o perációs rendszerek, például U NIX, m ainframe r endszerek és IBM DB2 vagy Teradata adatbázisok. 5/12. oldal OLAP Eszközök 2001 Smulovics Péter A kliens oldalon az OLAP Eszközök tartalmaznak egy komponens, amit PivotTablenek hívnak. A PivotTable

Eszközök egy lehetőség, amely összeköti az OLAP kliens alkalmazást az OLAP szerverrel. Minden adathozzáférést az OLAP Eszközök végeznek a PivotTable Eszköz OLE DB for OLAP interfészén keresztül, saját programok vagy kliens eszközök segítségével. Mind a kliens, mind a szerver komponense az OLAP Eszközöknek könnyen bővíthető. A jól dokumentált DSO segítségével bővíthető a numerikus, az adat menedzsment illetve az alkalmazás funkcionalitási rész. Az OLAP implementáció nehézségei Az OLAP implementálása sok problémát vet fel. Az OLAP Adat Modell felépítése Az alapvető nehézség a z O LAP h asználatakor a kiinduló a datbázis séma megfeleltetése egy multidimenzionális modellnek. Ez régebbi termékeknél jelentős programozói tudást igényelt. Az OLAP fejlődésével az OLAP adatbázis design egy specializálódott és misztikus fo lyamat lett, erősen kötődve a használt implementációhoz. A l egtöbb O LAP i

mplementációban f eltételezzük, h ogy az a dat m ár p reparált a z analízishez, tehát operációs rendszertől független, tisztított, validált, és különböző tények szerint csoportosított. Ez egy fontos lépés a folyamatban, biztosítja, hogy az OLAP felhasználó á ltal v izsgált a dat k orrekt, k onzisztens é s f edi a z e redeti definíciókat. Manapság egyre jellemzőbb, hogy az információk egy data warehouseban csillag vagy hópehely sémában organizáltak, ami egyszerűsíti a felhasználók számára az adatok értelmezését, kinyerését, maximalizálja az adatbázis lekérések sebességét a d öntés-támogató a lkalmazásokban és k evesebb tá rolókapacitást kíván nagy adatbázisoknál is. A következő ábra egy illusztráció a csillag sémára. Ebben az adatbázis sémában egy központi tény táblához kapcsolódnak a dimenzió táblák. Kép 3. A Csillag Séma A c sillag é s h ópehely s émák relácionális m egközelítései a z O

LAP a dat m odellnek é s megfelelő kiindulási pontok, hogy OLAP kocka definíciókat hozzunk létre. Néhány OLAP megoldás nem vette figyelembe ezt a trendet, nem adva egyszerűen 6/12. oldal OLAP Eszközök 2001 Smulovics Péter használható e szközöket a f elhasználó k ezébe, hogy m egfeleltessenek e gy c sillag sémát az OLAP modellnek, és ennek eredményeképpen az OLAP modellek építési költségét értelmetlenül magason és a fejlesztési időt értelmetlenül hosszan tartják. Intuitív felhasználói interfész Egyike az OLAP Eszközök kulcs vonásainak az OLAP Manager felhasználói felület, mely a tapasztalatlan OLAP adatbázis adminisztrátorokat szem előtt tartva készült. A felület a M icrosoft Management C onsole (MMC) snap-in-jeként ha sználható, és a Microsoft B ackOffice® család a dminisztratív f elületéhez h asonló. Az emiatt nyilvánvaló előny az O LAP a datbázis a dminisztrátornak, h ogy a z S QL Szervernél i ll. más

Microsoft termékeknél megszerzett adminisztrációs tudását itt hasznosíthatja. Az előny még n yilvánvalóbbá válik, h a m egértjük a z M MC e rejét és rugalmasságát. Az OLAP Eszközök között találunk jó néhány varázslót, melyek keresztül vezetik a tapasztalatlan és újonc felhasználót a gyakori feladatokon. Például tartalmaz egy teljes tutorialt az OLAP koncepciókról és egy lépésről-lépésre végigvezetést egy OLAP kocka készítéséhez. Egy t eljes c sokornyi v arázsló á ll re ndelkezésünkre a gyakori funckiók végrehajtására, mint például dimenzió definíció készítése. Ráadásul, az OLAP Eszközök olyan data warehouse fejlesztésére lettek optimalizálva ahol csillag vagy hópehely sémákat hoztak létre. A Cube Wizard teljesen megfelel az ilyen előre épített sémákhoz, és a multidimenzionális modellé való átalakítás nagyon gyors. Ha előfordulnának más sémák, az OLAP Eszközök akkor is könnyen tud hozzájuk

alkalmazkodni. Adat robbanás menedzselés aggregációval Mint m ár k iderült, az aggregációk előre kiszámítása az egyik kulcs t eljesítmény növelő eszköz a legtöbb OLAP termékben. Viszont az előaggregáció egy jelentős költséggel jár: az aggregációk száma gyorsan túllépheti a tényleges adatok számát, és a tárolt adatok száma drasztikusan megnőhet. Az aggregációk száma az OLAP modellben a dimenziók számával, a hiearhia nagyságával és a szülő-gyermek viszonyokkal együtt nő. Valós életből vett példákkal is alátámaszthatjuk ezt az elméletet: egy másik cég OLAP implementációjánál ez a robbanási faktor 240, tehát 2.4 gigabyte hely szükséges 10 megabyte adat forrásadathoz. A szükséges tárolókapacitás biztosítása az egyik legnagyobb költség a nagy volumenű OLAP implementációknál, és gyakran szűk határokat ad ahhoz, hogy a cég összes szükséges bemeneti adatát analizáljuk. Kép 4. Aggregáció Az

adatrobbanás miatt az OLAP alkalmazásoknál sokkal nagyobb problémát okoz, ha a forrás vagy részlet adatok szorványosan vannak a multidimenzionális kockában szétszórva. Hiányzó vagy érvénytelen adatok is okozhatják ezt az OLAP adat modellben. 7/12. oldal OLAP Eszközök 2001 Smulovics Péter Kép 6. Szorványos adatok A szorványos adatokhoz, ami egy nagy kihívás az OLAP Eszköz gyártóknak, több különböző sikerű megoldás készült. A legrosszabb implementációnál az eredmény adatbázis üres értékeket is tárol, ami alacsony sűrűséget jelent, valamint pazarló a hellyel és az erőforrásokkal. A Microsoft OLAP Eszközök nem tárolja az üres értékeket, és ennek eredményeképpen a még szorványosan feltöltött kockák sem robbanak fel méretileg. Rugalmas tárolási formák A Microsoft hisz benne, hogy az OLAP Eszközök piacvezető lesz azon okból, hogy rugalmas megoldásokat ajánl fel az OLAP adatbázis adminisztrátorok számára,

hogy eldöntsék, melyik tárolási modell a legmegfelelőbb. Az OLAP Eszközök t ámogat e gy teljes MOLAP vagy ROLAP implementációt, és egy HOLAP megoldást. Például, egy adminisztrátor dönthet úgy, hogy a gyakrabban elért adatokat egy MOLAP-ba helyezi, é s a ré gebbi a datokat, a mikkel t öbb s kálázási p robléma s zokott f elmerülni, ROLAP-ban tárolja. Mindezek ellenére a kliens applikáció számára a alapozó adat modell teljesen láthatatlan, és felhasználói csak kockákat lát. Függetlenül a ttól, h ogy v alaki a M OLAP, RO LAP vagy H OLAP a dat m odellt választja, fontos, h ogy a z OL AP E szközök i ntegrációja a r elácionális adatbázisokkal zökkenőmentes legyen. A grafikus felhasználói felület és design eszközök illetve wizardok erősen OLE DBhez kötésével az OLAP Eszközök erős és gyors kapcsolatokat tud fenntartani a forrás adatok, a multidimenzionális OLAP meta-adatok és maguk az aggregációk között. Amikor ROLAP adat

modelleket implementálunk, az OLAP Eszközök definiálja, terjeszti és adminisztrálja az összes relácionális adatbázis struktúrát. Ez leveszi a terhet a fejlesztő válláról, hogy ne kelljen ilyen automatikus feladatokat végrehajtania, illetve ami még rosszabb, hogy komplex lekérdezéseket kelljen programoznia több táblán vagy szerveren átnyúlva. Intelligens előaggregáció A Microsoft OLAP Eszközök egy másik alapvető problémáját is megoldotta az OLAP technológiának: az adatrobbanást a túlzott előaggregációnak köszönhetően. Mint előbb láttuk, az OLAP adat robbanás kiváltó oka a multidimenzionális előaggregáció. A tradícionális OLAP rendszerekben hacsak nem futás közbeni kiszámítással, de a nem előaggregált adatok nem állnak rendelkezésre analízis és riportkészítés céljából. A tradícionális OLAP termékekben az aggregációk előre kiszámításával és tárolásával minden lehetséges kombinációra massziv adat

robbanást érünk el. Ezzel a megközelítéssel (hogy minden lehetséges aggregációt kiszámoljunk) ellentétben a Microsoft OLAP Eszközök meghatározza, mely aggregációk biztosítják a legjobb te ljesítmény n övekedést, d e m egadja az O LAP a datbázis adminisztrátor számára a lehetőséget, hogy döntést hozzon a rendszersebesség és a tárolókapacitás felhasználás között a Store Design Wizard segítségével. Ha a fejlesztőnek kedve támad előre kiszámoltathat mindent, számolva az adatrobbanás 8/12. oldal OLAP Eszközök 2001 Smulovics Péter jelenségével. V agy p ont fordítva, d önthet ú gy, hogy n em s zámolva ki semmit a tároló kapacitás szükséget minimalizálja - ezzel a rendszer sebességét is. A legtöbb esetben az OLAP Eszközök a lekérdezések 80 %-t az aggregációk teljes kiszámítása n élkül t udja v égrehajtani. A nalízálja a z O LAP m eta-adat m odellt é s heurisztikusan m eghatározza a z a ggregációk o

ptimális k iválasztását, a honnan a z összes többi aggregáció meghatározható. Eredményként a nem aggregált adat néhány m ár a ggregált a datból m egállapítható, a helyett h ogy a z e gész d ata warehouse-t áttekintenénk. A részleges előaggregációnak ez a stratégiája viszont csak a kezdőpont. Annak e llenére h ogy a h eurisztikák t ökéletesek, m atematikai a lapokon, m odelleken alapulnak, vagy megfelelnek az aktuális használathoz, vagy nem. Hogy optimalizáljuk a teljesítményt az aktuális esethez, az OLAP Eszközök opcionálisan naplózza a szervernek küldött kéréseket. Ezek a naplók később jól használhatók a szerver á ltal k iválasztott a ggregációk f inomhangolására. P éldául, a Usage-Based Optimization Wizard lehetővé teszi az adatbázis adminisztrátornak, hogy autmatikusan k észítsen ú j a ggregációs c soportokat m indazon l ekérésekhez, amelyekre a válaszadás tovább tartana, mint n másodperc (n 10 vagy több

másodperc). Sok s zervezetnél a feldolgozási idő nagyobb súllyal e sik latba, m int a t árolási kapacitás. B ármikor v ehetünk e gy ú j t árolási egységet, d e h a tö bb mint e gy n apig tart a napi információ feldolgozása, nincs leghetőségünk további időt venni. Az OLAP Eszközök m egoldása az a dat robbanás csökkenti azt az időt ami az alap feltöltésekhez és az inkrementális frissitésekhez szükséges (miközben a tárhelyszükségletet is csökkenti). Ugyancsak látványos és innotatív előrelépés történt a szorványos adatfeltöltés irányában. Miközben a belső implementáció részletei a megoldás sajátja, a külső eredmény az, hogy mind a MOLAP, mind a ROLAP implementációk nagyon jól kezelik a tárolókapacitás kérdését, gyakran kisebb OLAP adatbázist eredményezve, mint a kiinduló volt. Virtuális kockákat használhatunk akkor, ha szeretnénk két különböző kockából összekapcsolt információkat kinyerni, ha akár

csak egy közös dimenziójuk van (mint a JOIN). Hasonlóan a relációnális koncepcióhoz, a virtuális kocka egy vagy több kocka a lekérdezés idejekor összekötve egy vagy több közös dimenzió szerint. Egyik előnye a virtuális kockáknak ott lehet, ahol a szorványos kitöltés lényeges probléma. Például, e gy k ocka a mi m értékeket t artalmaz e gységenkénti á r é s eladási á r n éven ugyancsak t artalmazna e gy m értéket a l istaárról p éldául, h ogy a leértékeléseket könnyebben kiszámíthassuk, de ez sokszor ismétlődne. Építve egy listaár kockát, és virtuális kockaként hozzákötve az említett kockához, az adatbázis adminisztrátor a redundancia nagy részét ki tudná küszöbölni. Teljesítmény és skálázhatóság A pontos teljesítménye az OLAPnak sok különböző faktoron múlik, mint pl. adatbázis nagyság, hardware számítási kapacitás, és felhasználható tárolókapacitás az előaggregált adatokhoz. Viszont a való

életben fontos mérték az, hogy a legtöbb kérdésre 5 másodpercen belül kapunk választ, és mindegyikre választ kapunk 10 másodpercen belül. Az OLAP Eszközök particionált kocka technológiája jól skálázható. Egy particionált kocka lehetővé teszi, hogy egy logikai kocka több fizikai kockán vagy akár több fizikai s zerveren á t n yúlhat. A f elhasználó lekérdezése a z O LAP á ltal s zétosztódik a particiós szervereken, lehetővé téve a parallel válaszadást. Például, nézzük azt az esetet, amikor van egy alkalmazás, ami 10 különböző geográfiai h ely t elefonhívási adatait gyűjti, napi többmillió hívással. Az analízis céljából s zét l ehet o sztani a z a datot 1 0 s zerver k özött, m indegyik e gy-egy h ely 9/12. oldal OLAP Eszközök 2001 Smulovics Péter adatait analizálná. A felhasználó szemszögéből így is csak egy logikai kocka látszik Minden beérkező kérésre válaszul az OLAP l áthatatlanul

átalakítja a l ekérdezéseket, ahogy kell mind a 10 szerverhez és egy eredmény szetet ad vissza. Természetesen, a 10 szerver mindegyike is külön megcímezhető a külön eléréshez. OLAP Információ szolgáltatás a felhasználónak Régebben a z OLAP Szerver technológia a megfelelő kliens technológián szorosan kötődött, így megfosztva a felhasználót a választás lehetőségétől. Ez a szoros kapcsolat magas implementációs költségekhez vezetett és gyakran alkalmatlan és ügyetlen volt eszközválasztás azoknál az applikációknál, melyek mind server/kliens, mind Web-alapú OLAP információátadást tettek szükségessé. Sok évvel ezelőtt a relácionális a datbázis i par r ájött, h ogy egy k özös a datbázis f elület interfész szükséges az applikációk felé, amely a nyitottság elvében készül. Ez lett az ODBC Ipari standardok Az OLAP eszközök nyitottsági kérdése először 1996-ban merült fel, amikor is egy gyártókból álló

konzorcium, amit OLAP Council-nak hívtak, bejelentett egy együttműködési standardon alapuló multidimenzionális alkalmazás programozási interfészt (MDAPI), ami a tervek szerint megnyitotta a piacot. A sok résztvevő, a gyártok összefogása ellenére, az MDAPI kútba fulladt. Felismerve a z e gységes s tandard s zükségét, a mivel a kár ú jrahasznosítható l ehet a z eddig megszerzett tudásunk, a Microsoft nekikezdett az OLE DB adat elérés API kibővítésére, hogy az a multidimenzionális adat elérést is támogassa. 1998 Februárjában, k ét d raft v erzió u tán, k ijött a v égleges v erzió, a minek m ár b éta verzióját is 18 gyártó támogatta. Ma az OLE DB és az OLAP API több mint 30 gyártó által támogatott, legtöbbjük fent van a www.microsoftcom/data/oledb/olap címen lévő listán A Microsoft SQL Server 2000 O LAP Se rvices t ámogatóinak a ktuális (és természetesen egyre bővülő) listája pedig a

www.microsoftcom/sql/techinfo/BI/analysisasp címen található Kapcsolatmentes és Web-alapú szolgáltatás Sok a nalizálónak k ell a datok a nalizálnia o lyankor i s, m ikor a c éges h álozattól t ávol van, mint például laptopján, utazás közben. Ezek a kapcsolat nélküli felhasználók tipikusan néhány k is s zeletét s zeretnék e gyszerre vizsgálni a k ockának, p éldául közeledve egy telephelyhez az illető telephely lokális adatait. Erre olyan gyakran volt szükség, hogy létrejött a desktop OLAP (DOLAP), amihez nincs szükség egy megosztott szerverre a multidimenzionális adat eléréshez. A legtöbb OLAP technológia nem ad egyszerű és átlátszó lehetőséget a DOLAP kockák létrehozására. Emiatt maradt ez a lépés is, mint annyi lenézett fejlesztésintenzív kezdeményezés az OLAP kliens számára, a szükség okán ellátva a klienseket a DOLAP működéshez szükséges funkcionalítással. Ez a particionálás viszont költség és

bonyolultság növelőnek bizonyult azon alkalmazási eseteknél, ahol mindkét működési módra szükségünk volt. A minden információra használható (különösen multidimenzionális információra) népszerű megjelenítési eszköz, a Web böngésző se maradhatott ki a sorból. A kulcs eszköz a f elhasználónkénti k öltség c sökkentésére, eredeti c élja m ellett n agyobb közönség f elé i s kinyitotta a z O LAP a pplikációkat. J elenleg ta lálható n éhány n agyon jó m egoldás és e szköz az OLAP adatok i ntranetre t ételére, d e egyik s e a d egyszerű mechanizmust az egyéni OLAP megjelenítők fejlesztésére. PivotTable Service Az OLAP Eszközök cache-lik nemcsak az adatot magát, hanem a felhasználó lekérdezéseit és a meta-adatokat is. A cache-lt lekérdezés definíciók és meta-adatok teszik lehetővé az OLAP Eszközök számára, hogy az új lekérdezésekre az eddigiek adataira támaszkodva gyorsabban adjon választ a lemezhozzáférés

minimumon tartásával. 10/12. oldal OLAP Eszközök 2001 Smulovics Péter Ami az OLAP Eszközökben szokatlan, hogy a kliens a szerverhez hasonló funkciógazdagsággal b ír. M inden kliens a P ivotTable S ervice h asználatával kapcsolódik az OLAP Eszközökhöz, í gy egy meghajtó szerepét játsza menedzselve a kliens és a szerver k özti kapcsolatot. A PivotTable kódja sok hasonlóságot mutat az OLAP szerverrel, a szerver multidimenzionális számolási motorját, cache lehetőségeit, lekérdezés menedzsmentjét a kliensen is elérhetővé teszi. Az eredmény egy innotatív kliens/szerver adat-menedzsment modell, ami optimalizálja a teljesítményt és minimalizálja a hálózati forgalmat. Ennek ára nagyon csekély számítási/erőforrásbeli kapacitás: tárolási szempontból körülbelűl 2 Mb, a memória szükséglet pedig mindössze 500 kilobytetal több, mint a cache-lt adat. Kép 8. PivotTable Service Az OLAP Eszközök intelligens kliens/szerver

architektúrája képes rá, hogy meghatározza hogyan válaszoljon a felhasználónak olyan gyorsan, ahogy lehet eliminálva a felesleges hálózati forgalmat. Az architektúra kulcsa a kliens és szerver megosztott meta-adatai. Amikor a felhasználó információt kér a szervertől, mind a tényleges adat, mind a meta-adat (a kocka-struktúra definíciója) letöltődik a kliensre. A k ocka m eta-adatok s egítségével e zekután a k liens tu dja h asználni a PivotTable Service-t, hogy kiválogassa azokat a kéréseket, amiket biztos, hogy a szerverhez kell küldeni. A P ivotTable S ervice a dja a s zükséges f unkcionalítást a k apcsolatmentes működéshez is. A definiált és szerverről elért kockák egyes részei lementhetők a kliensen későbbi, kapcsolatmentes eléréshez. Ezzel a módszerrel az üzleti felhasználók m agukkal v ihetik a z a datbázis egyes r észeit m iközben u taznak, d e ennek ellenére m inden analízis f unkciónalítás a re ndelkezésükre

áll. Ezen felül a PivotTable Service lehetővé teszi a felhasználóknak, hogy egyszerű OLAP modelleket lokálisan hozzhassanak létre, elérve az OLE DB kompatibilis adatforrásokban lévő információkat, legyen az egy desktop adatbázis vagy egy szövegfile. Végül, a P ivotTable S ervice a dja a W eb-alapú a pplikációk s zámára a k apcsolat lehetőségét. Miközben az OLE DB for OLAP egy alacsony szintű programozói interfész, a ddig a z ActiveX® D ata O bjects ( ADO) újonnani k iterjesztése m agas színtű multidimenzionális adatelérést tesz lehetővé. Ez a bővítés (ADO/MD) használható a rra, h ogy új A ctiveX i lletve Windows k ontrollokat h ozzunk l étre a m ár ismert Microsoft Visual Basic® vagy az új Microsoft Visual Studio.NET® rendszer alatt, amelyekkel egyszerűen megoldhatjuk, hogy böngésszünk, diagrammot generáljunk, v agy riportot k észítsünk e gy W eb o ldalba á gyazva. A z ADO/MD a legjobb applikációs programozói eszköz

ahhoz, hogy az OLAP Eszközök teljes funkciónalitásához natívan hozzáférhessünk. 11/12. oldal OLAP Eszközök 2001 Smulovics Péter Megengedhető OLAP Eszközök A Microsoft OLAP Eszközök árai minden várakozást alulmúlnak. Egy OLAP termék tipikusan 15-től 30 millió Ft-ba kerül 50 felhasználó alatt. A Microsoft ezzel szemben felismerte, hogy az OLAP egy természetes és kivánkozó bővítménye az adatbázis technológiának, és így az OLAP Eszközöket mint kiterjesztés beleépítette a Microsoft SQL Server termékcsaládba, már a 7.0 verziótól Az SQL Server 2000 része sok m ás komponens, mely jól használható data warehouse céljára: • Visual Database Tools, az adatbázis sémák elkészítéséhez. • Data Transformation Services (DTS), hogy az operatív adatokat kinyerhessük és transzformálhassuk a data warehouse-ba. • Microsoft Repository, hogy egységes meta-adat tárolás legyen SQL alapon. Microsoft Office integráció Az O

ffice 20 00-s v erziójától k ezdve, a z OL AP E szközökkel k ompatibilisan s ok és gazdag OLAP böngészési lehetőséggel bővült az Excel. Az OLE DB for OLAP interfészen alapulva ezek az új lehetőségek élő kapcsolatot kínálnak a szerverekkel, de ugyanúgy választhatjuk a kapcsolatmentes illetve Web-alapú hozzáférést. Először az Excel 2000-ben megjelent új PivotTable kínált dinamikus vizsgálati lehetőségeket, kapcsolatot egy Excel számolótábla és az OLAP szolgáltató között. Abban az esetben, ha a Microsoft OLAP Eszközöket használjuk, további lehetőségeink is vannak, mint például lokális logikai kockaszeleteket hozhatunk létre az Excel segítségével. Az új Office Web Komponensek segítségével lehetőségünk nyílik, hogy egyszerű OLAP böngészési illetve diagram készítési lehetőségeket nyújtsunk egy ActiveX kontroll segítségével, a mit bármilyen Weblaphoz hozzáadhatunk, akár S harepoint Portal Server DashBoard-hoz is.

Mivel ez a technológia is az OLE DB for OLAPon alapul, bármilyen ezzel kompatibilis OLAP szolgáltató használható. Third-party kliens eszközök Az OLE DB for OLAP gyors elfogadása termékek nagy csoportját tette OLAP Eszközök kompatibilissá különböző software gyártoktól. Mivel sok új illetve már bejáratott kliens eszköz és komponens áll a felhasználó rendelkezésére, hogy elérje az OLAP Eszközökben t árolt i nformációkat, sokkal több lehetőség áll re ndelkezésre kiválasztani a megfelelő tudású és árú összeállítást. Konklúzió A Microsoft vezető pozicióba került az iparban a data warehousing funkció hozzáadásával a Microsoft SQL Server 2000-hez, és segített kiterjeszetni a piacot elérhető árú szoftverével. A Microsoft Office 2000-ben lévő OLAP böngészési lehetőségekkel a felhasználók biztosak lehetnek abban, hogy data warehousing és döntés támogató rendszerük cégükkel együtt nő és minden

lehetőségnek megfelel. OLAP Eszközök I. rész, Alapok 12/12. oldal OLAP Eszközök II. rész Kockák Smulovics Péter (smulovicsp@avalon.autbmehu) OLAP Eszközök: Rövid áttekintés a Microsoft SQL Server 2000 OLAP Eszközök felett II. rész Kockák Budapest 2001. szeptember Budapesti Műszaki és Gazdaságtudományi Egyetem Automatizálási és Alkalmazott Informatika Tanszék OLAP Eszközök 2001 Smulovics Péter OLAP Eszközök: Rövid áttekintés a Microsoft SQL Server™ 2000 OLAP Eszközök felett Összefoglalás: Bevezetés az Online Analytical Processing-be (OLAP) és a Microsoft® SQL Server™ OLAP Eszközökbe. Teszt eredményeket vizsgál a kockák teljesítményével k apcsolatban, m ely t eszt e redmények k onkrét l épésekre v ezetnek, amelyekkel multidimenzionális adatbázisának teljesítményét javíthatja II. rész, Kockák Bevezetés Az Online Analytical Processing (OLAP) egy növekvő népszerűségű technológia, amely drasztikusan

megnöveli az üzleti analízis sikereit, de ugyancsak ismert a drága és ügyetlen eszközökről, és a rugalmatlan telepítésről. A Microsoft megbirkózott az OLAP problémával és létrehozott egy megoldást, ami a multidimenzionális analízist szélesebb kör számára teszi lehetővé és potenciálisan jóval kisebb TCO-t tesz elérhetővé. A M icrosoft® SQ L Server™ OLAP Eszközök e gy kimerítően teljes O LAP implementáció, ami a Microsoft SQL Server 2000 része. Az OLAP Eszközöknek része egy middle-tier szerver, ami a felhasználók számára lehetővé teszi, hogy modern analízist hajtson végre adatok nagy volumenén kivételes teljesítménnyel. Egy másik része az OLAP Eszközöknek a kliens cache és a számítási motor, amit PivotTable® nek hí vnak, ami tovább segíti a teljesítmény növelését és c sökkenti a h álózati forgalmat. A PivotTable segítségével a felhasználók analíziseket hajthatnak végre, miközben nem csatlakoznak a

céges hálózathoz. Az OLAP egy kulcsfontosságú komponens az adattárházak feldolgozásakor, és a z OLAP Eszközök megadják azt a szükséges funkcionalitást, ami applikációk tág körében f elhasználható a c ég kimutatásoktól a d öntéstámogatásig. A z O LAP funkcionalitás SQL Szerverbe helyezésével a multidimenzionális analízis megengedhető lett és nagyobb közönséghez jutnak el az OLAP előnyei. Ezen közönség nem csak a kisebb vállalatokat foglalja magában, hanem csoportokat és egyéni f elhasználókat is n agyobb v állalatokban, amelyek e ddig kimaradtak az OL AP iparból az ár vagy a jelenleg kapható eszközök komplexitása miatt. Az OLAP alapjai Történetileg, a céges számítástechnikai befektetések nagy része (ha n em mind), olyan rendszerekbe történt, amelyek nagy mennyiségben adatokat állítanak elő vagy gyűjtenek b e, ol yanokat, m int a s zámlázás, a r endelések f eldolgozása é s a felhasználók i nformációi. M

anapság olyan t echnikákba és a pplikációkba i s t örténnek befektetések, sőt, kezdik átvenni a vezető helyet, amelyek újabb értékeket, információkat szűrnek le az összegyűjtött adatokból. A Datawarehouse az adatgyűjtés, tisztítás, egyforma formátumra hozás (pl. a különböző operációs rendszerek okán), é s a z így k apott információ a nalízis c éljából s zéles k özönség számára való eljuttatásának folyamata. OLAP Terminológiák és koncepciók Fontos, ho gy d efiniáljunk é s t isztázzunk l e n éhányat a f ontos terminológiák és koncepciók közül, amiket az OLAP-pal kapcsolatban merülhetnek fel. Az OLAP Adat Modell Az OLAP adat modellben a z információhalmaz mint kocka jelenik meg, ami leíró kategóriákból (d imenziók) é s kvantitív értékekből (mértékek) áll. A multidimenzionális adat modell a felhasználók számára könnyűvé teszi komplex lekérdezések megfogalmazását, az adatok elrendezését, a

részletes és az összefoglaló közti váltást, és az adatok szűrését vagy vágását értelmes alhalmazokra. P éldául, tipikus d imenziói e gy k ockának az idő, a hely, az áru, a 2/14. oldal OLAP Eszközök 2001 Smulovics Péter szállítás, a z o rganizáció é s a s zkenárió ( büdzsé v agy a ktuális). T ipikus m értékei ennek a kockának a dollár eladás, egység eladás, készlet, létszám, bevétel, és a költség. Minden egyes dimenziója az OLAP adat modellnek hiearchiába organizálható, ami az adat részletességi szintjét mutatja. Például az idő dimenzión belül megkülönböztethetjük a következő szinteket: év, negyedév, hónap, nap; ugyanígy a hely viszonylatában: ország, régió, megye, város. Az OLAP modell felhasználója le és fel mozog a szintek között, hogy több vagy kevesebb részletet lásson egyszerre. Aggregáció és Tárolási Modellek Kockák, dimenziók, hiearchiák és mértékek a multidimenzionális

navigáció fő eszközei a z O LAP-ban. Í gy p rezentálva é s l eírva a z a datokat a f elhasználó a kár intuitívan navigálhat az adatok komplex halmazában. A fő elve az OLAP-nak, hogy a felhasználó konzisztens válaszidőket kapjon akárhányszor újabb nézetet, szeletet, egyáltalán adatok kér. Mivel az adatot általában csak a legrészletesebb szinten gyűjtjük, emiatt az információk összegzése előre meg történik. Ezek az előre számolt értékek (aggregációk) az alapja az MS OLAP jó teljesítményének. Az OLAP technológia első napjaiban a legtöbb gyártó egyetértett abban, hogy az egyetlen tárolási mód az OLAP applikációk számára egy specializált, nem relácionális modell. Később, más gyártók felfedezték, hogy adatbázis struktúrák (csillag és hópehely sémák), indexelés, aggregációk tárolás segítségével relácionális adatbáziskezelő rendszerek (RDBMS) is használhatók az OLAP-hoz. Ezek a gyártók megoldásukat

relácionális OLAP-nak (ROLAP) hívták. Az első technológia használói a multidimenzionális OLAP (MOLAP) elnevezést kapták. A MOLAP implementációk általában jobban teljesítenek a ROLAP technológiánál, de problémák vannak a skálázhatóságnál. A másik oldal, hogy a ROLAP implementációk jobban skálázhatóbbak és gyakran tetszetősebbek az ügyfelek számára, mivel újra felhasználhatóak a relációs adatbázisokba fektetett összegek és tapasztalatok. Egy újonnan felfedezés a hibrid OLAP (HOLAP), amelyik kombinálja a ROLAP és MOLAP architektúrákat, hogy mindkettő legjavát nyújthassa: kitűnő teljesítmény és jó skálázhatóság. Egyik HOLAP megoldás, hogy a részletes rekordokat (ez a legnagyobb a datmennyiség) r elácionális a datbázisokban m enedzseljük, m íg a z aggregációkat ettől függetlenül, MOLAP rendszerben tároljuk. Kocka teljesítmény optimalizálás OLAP Eszközöknél Sok informatikai vállalatnál, főleg ahol nagy

adatbázisok i s v annak, a z a datbázisok teljesítményének fokozása i nkább a lapul m egérzéseken és a nekdotákon, m int bizonyított módszereken. Ezen dokumentáció megpróbálja összegyűjteni azokat a konkrét lépéseket, me lyek a multidimenzionális a datbázisok optimalizációjánál jól használhatóak. Amikor az SQL Server 2000 OLAP Eszközöket használva kockákat hozunk létre, a két legfontosabb tervezési kérdés a tárolási mód (MOLAP/HOLAP/ROLAP) és az aggregáció mértéke. Azok a tervezők, akik az alapvető döntéseket hozzák a datawarehouse design szempontjából, a Microsoft SQL Server 2000 dokumentációból megtudhatják a megfelelő irányelveket és kompromisszumokat. De a rendelkezésre álló információknak csak kis része alapul közvetlen tapasztalatokon, például, hogy hogyan b efolyásolja a t árolási m ód é s az aggregáció m értéke a f eldolgozás é s lekérdezés teljesítményét. Következzenek a t esztek, a mikkel

a p roblémákat izolálhatjuk ebben a t émakörben. Áttekintjük a v izsgálati k ényszereket, h ogy h ogyan h oztuk l étre a z OLAP k ockákat, és m egvizsgáljuk a l ekérdezési e ljárást. A nalízisunk továbbá illusztrálja a használható metódusok választékát a kocka feldolgozási időn és tárolási adatokon, illetve az MDX és SQL közti összehasonlításon keresztül. 3/14. oldal OLAP Eszközök 2001 Smulovics Péter A kocka kihívás Egy reprezentatív S QL Server és OLAP a datbázis f elállításával k ezdünk. Ker esésünk eredményeképpen azonosítottunk néhány példa üzleti lekérést, hogy egy már létező VLDB ( Very Large DataBase) ellen futassuk őket. Az adatbázisból izoláltuk azokat a táblákat és mezőket, amik a tényleges információt tartamazzák. Ezután létrehoztunk és populáltunk egy SQL Server data mart-ot, ami egy csillag séma designon alapul. Kipróbáltunk több különböző tárolási módot és több

különböző aggregációs mértéket, hogy több különböző OLAP reprezentációs kockát hozzunk létre. V égül S QL é s MDX l ekérdezéseket h oztunk l étre a z ü zleti lekérdezésekre alapozva és futtatuk ezeket, hogy összehasonlíthassuk a data martot és az OLAP kockákat. A tanulmány közben megvizsgáltunk számos beállítást, ami befolyásolja a teljesítmény o ptimalizációt. A t anulmány t artalmazza a z ö sszehasonlító t eszt eredményeket kocka feldolgozás idő, adatbázis helyfoglalás, CPU használat (RDBMS és OLAP, üres és feldolgozás közben), MDX végrehajtási idő, meleg és hideg cache indítás, felépítési helyfoglalás (OLAP kocka és csillag séma data mart), és MDX-SQL végrehajtási idő szempontjából. A teszt részletei A vizsgált adatbázis VLDB, 4 millió felhasználót és 10 millió accountot tartalmaz, SQL Server 2000 szerveren. A létrehozott csillag séma közepén lévő fact tábla körülbelül 13 millió sort

tartalmaz. A teszt összeállítása A teszt összeállítása jónéhány lépésből állt. Többek között definiáltuk a lekérdezéseket, összeállítottuk a data martot, populáltuk, és felépítettük az OLAP kockát. A lekérdezések definiálása Először definiáltunk a megfelelő és releváns üzleti kérdéseket. H ét k érdést választottunk, aztán megvizsgáltuk a már létező VLDB entitás-reláció diagrammot, 4/14. oldal OLAP Eszközök 2001 Smulovics Péter hogy m eghatározzuk mely t áblák t artják a k érdés m egválaszolásához s zükséges információt. Data mart összeállítása Aztán ö sszeállítottuk a data m artot a v álaszadáshoz sz ükséges t ábla információval. Egy csillag sémát választottunk, mert ez a design közel van a tervezett OLAP kocka felépítéséhez. Hogy l étrehozzuk a s zükséges d ata m artot, egy f act tá blát d efiniáltunk Account Prof Fact néven, hogy az 1996 és 19 97 közti adatokat tároljuk.

A tábla az accounting a datokat m onthly s napshot s zinten tá rolja. M eghatározzuk a n ekünk szükséges öt dimenziót (produkt, idő, régió, hely, felhasználói szegmens). Data mart populálása Miután k észen v agyunk a data m art d esign-nal, a D ata T ransformation S ervices (DTS) használatával az Account Prof Fact fact táblát és a hozzákapcsolt dimenzió táblákat a data martba mozgatjuk. Az itt következő táblázat tartalmazza a különböző táblák (fact és dimenzió) méreteit. DataMart Tábla Sorok Méret Account Prof Fact 13,036,152 2,910 MB CustSegmentDim 7 8 KB HouseholdDim 200,001 35 MB ProductDim 14 8 KB RegionDim 51 16 KB TimeDim 24 8 KB OLAP kocka építése Hogy létrehozzuk a kockákat, először egy multidimenzionális OLAP adatbázist kell létrehoznunk, mondjuk AccountProfitabilityOLAPDatabase néven. Az adatbázison belül 12 különböző kockát hoztunk létre, mind ugyanolyan struktúrával, d e különböző

tárolási módokkal és aggregációs mértékkel. Négy különböző aggregációs szintet vizsgáltunk, 0, 30, 60 és 90 százalék. Azért ezeket választottuk, mert ezek a legelterjedtebbek, és közöttük már lehet következtetni. A nagyobb aggregációs mérték reprezentálja az elvárt teljesítmény növekedést a 0 százalékoshoz képest. Ez a z á bra a z egyik e lkészült k ocka s truktúráját m utatja, neve AccountProfitabilityCubeM90 (MOLAP, 90% aggregáció). A következő lépés a fact 5/14. oldal OLAP Eszközök 2001 Smulovics Péter mértékek m eghatározása. H ogy p ontos k épet kapjunk az adatábázisban lévő adatokról nyolc mértéket határoztunk meg. Hogy befejezzük a kocka struktúrájának kialakítását, öt kocka dimenziót választunk ki a data mart csillag sémájának megfelelően. Ebben a táblázatban az OLAP dimenziók részletei találhatóak: Dimenzió Sorok Szintek Dimenzió méret HouseholdDim 200,001 2 19,128 KB

ProductDim 14 1 3 KB RegionDim 51 2 8 KB TimeDim 24 3 5 KB CustSegmentDim 7 1 2 KB Név Tárolási mód Aggregációs % AccountProfitabilityCubeM0 MOLAP 0 AccountProfitabilityCubeM30 MOLAP 30 AccountProfitabilityCubeM60 MOLAP 60 AccountProfitabilityCubeM90 MOLAP 90 AccountProfitabilityCubeH0 0 HOLAP AccountProfitabilityCubeH30 HOLAP 30 AccountProfitabilityCubeH60 HOLAP 60 AccountProfitabilityCubeH90 HOLAP 90 AccountProfitabilityCubeR0 0 ROLAP AccountProfitabilityCubeR30 ROLAP 30 AccountProfitabilityCubeR60 ROLAP 60 AccountProfitabilityCubeR90 ROLAP 90 Kocka feldolgozási és tárolási eredmények Mielőtt tesztelnénk lekérdezéseinket, összehasonlítottuk a különböző tárolási módok (Multidimensional O LAP ( MOLAP), H ybrid O LAP ( HOLAP), és R elational O LAP (ROLAP)) feldolgozási idejét és t árolási s zükségletét. U gyancsak összevetettük a MOLAP kocka tárolási szükségletét a csillag sémával. Feldolgozási

idők az egyes tárolási módokhoz Előszöt is megnéztük a feldolgozási időket a három különböző tárolási módhoz. Hogy pontos információink legyenek, ugyanazt a struktúrájú kockát dolgoztattuk fel 12 módon. Tárolási Mód 0% Aggregáció 30% 60% 90% Aggregáció Aggregáció Aggregáció MOLAP 30 p, 31 mp 36 p 40 p 2 ó, 17 p HOLAP 26 p, 24 mp 34 p 35 p 2 ó, 16 p ROLAP 1 p(!), 29 mp 48 p 1 ó, 28 p 5 ó(!), 14 p 6/14. oldal OLAP Eszközök 2001 Smulovics Péter Feldolgozási idők az egyes tárolási módokhoz 6:00:00 Feldolgozási idő (óra:perc) 4:48:00 3:36:00 2:24:00 1:12:00 0:00:00 0 30 60 90 MOLAP 0:30:31 0:36:00 0:40:00 2:17:00 HOLAP 0:26:24 0:34:00 0:35:00 2:16:00 ROLAP 0:01:29 0:48:00 1:28:00 5:14:00 Aggregáció % Mint m ár e z k iderült, m ikor a M icrosoft O LAP E szközöket h asználjuk a k ockák létrehozásához, a l egfontosabb t ervezési k érdések a t árolási m ód é s a z a

ggregáció mértéke. Az OLAP Eszközök értelmezésében az aggregációk előre kiszámolt összegzések a fact táblából, a dimenziók különböző szintjén. A felhasználó ezeket az aggregációkat használja, hogy (sokkal gyorsabban) megválaszolja a lekérdezéseket, és hogy újabb aggregációkat hozzon létre. Amikor meghatározzuk, hogy m ennyi legyen a z a ggregáció m értéke, a t árolási h ely és a végrehajtási idő között kell egyensúlyt találnunk. Egyik végletként minden aggregációt (v agy m ajdnem m inden, 9 0%) k iszámolva e xtrém nagy t árolási szükségletünk tá madhat, a m ásik v égletként n agyon lassú l ekérdezéseket kaphatunk. A teszt lényeges különbségeket fedett fel mind a mérték, mind a mód szempontjából. A ROLAP kocka, 0 %-s aggregációval adja a leggyorsabb időt, ez annak köszönhető, mert az OLAP Eszközök nem m ásolja á t a f act é s d imenzió adatokat, h anem a relácionális a datbázisban hagyja. V

iszont az a ggregációs m érték növekedtével az idő túllépi a MOLAP és HOLAP kockák idejét. Meglepetést okozott a magas aggregációs szint. A MOLAP és HOLAP kockák közti különbség nem volt lényeges 30 és 60 %-nál, de a feldolgozási idő többszörösére ugrott 60 és 90 százalék között. A ROLAP kockáknál a növekedés exponenciális volt, ez a görbén szemmel is jól látszik. Tárolási szükségletek az egyes tárolási módokhoz Ez az ábra mutatja a három tárolási mód tárolási szükségleteit. Ú gy találtuk az ábrából, hogy előzetes elvárásainknak megfelelően mind a tárolási mód, mind az aggregációs mérték szignifikánsan érintheti a tárolási szükségleteket. Tárolási Mód 0% 30% 60% 90% Aggregáció Aggregáció Aggregáció Aggregáció MOLAP 441 MB 442 MB 443 MB 619.30 MB HOLAP 20 MB 20.09 MB 22.11 MB 198.3 MB ROLAP 20 MB 20.437 MB 40 MB 602 MB 7/14. oldal OLAP Eszközök 2001 Smulovics Péter

Tárolási szükségletek az egyes tárolási módokhoz 700 Tárolási szükséglet (MB) 600 500 400 300 200 100 0 0 30 60 90 MOLAP 441 442 443 619.3 HOLAP 20 20.09 22.11 198.3 ROLAP 20 20.437 40 602 Aggregáció % Láthatjuk, hogy a MOLAP kockák több helyet igényelnek, mint a HOLAP és ROLAP kockák. Ennek o ka, h ogy MOLAP e setben a z O LAP Eszközök a z e redeti f act é s dimenzió táblákat átmásolja az OLAP adatbázisba. Bár a MOLAP kocka több tá rolási kapacitást igényel, a lekérdezéseknél a c sillag s émára t ovábbra n incs s zükség. Viszont a H OLAP k ockák h asználják a legkevesebb h elyet, m ivel a z O LAP Eszközök nem másolják át az eredeti fact és dimenzió táblákat az OLAP adatbázisba és az aggregációkat OLAP adatbázisban tárolják egy nagyon hatékony multidimenzionális formátumban. A tárolási szükségletek nem növekednek jelentősen 0 és 60 % között, de minden tárolási módnál erősen

megugranak 90 %-t megközelítve. A R OLAP e setben e z különösen jelentős. Tárolási szükségletek (MOLAP kocka és csillag séma összehasonlítása) Ez a táblázat a MOLAP kocka helyfoglalását veti össze az eredeti RDBMS csillag séma fact é s d imenzió t áblák h elyfoglalásával. A t esztek s zerint a z a ggregációk k iépítése csak 20 %-kal több helyet igényelt. Aggregáció % MOLAP hely RDBMS csillag séma (Fact Adat tömörítés % a és Dimenzió Táblák) MOLAP kockában 0 441 MB 2945.04 MB 85.03 30 442 MB 2945.04 MB 85.00 60 443 MB 2945.04 MB 84.96 90 619.30 MB 2945.04 MB 78.97 8/14. oldal OLAP Eszközök 2001 Smulovics Péter MOLAP hely (MB) 700 600 Tárolási szükséglet (MB) 500 400 300 200 100 0 MOLAP hely (MB) 0 30 60 90 441 442 443 619.3 Aggregáció % A táblázat memutatja, hogy adatrobbanás helyett a valóságban az OLAP Eszközök adat tömörítést alkalmaz az OLAP kockák felépítése közben.

Meglepő, hogy még a 90 %-s aggregáció mellett is majdnem 79 %-s tömörítési arányt tudtunk elérni. MDX és SQL lekérdezések Hogy teszteljük a lekérdezések válaszidejét, hasonló kérdések csoportját futtattuk le különböző környezetben: SQL lekérdezések SQL Szerveren, és MDX lekérdezések egy M OLAP k ocka e llen e gy OLAP s zerveren. Bár nem lehet egyértelműen összehasonlítani az SQL és MDX végrehajtási időket, az eredmények megmutatják, hogy az OLAP Eszközökön MDX-t használva többszörösen túlléphetjük a lekérdezések teljesítményét az SQL-lel szemben, mivel az OLAP Eszközök előaggregál és tárol értékeket az erre optimalizált OLAP kockákban. A 2., 3, 4, 6 és 7 MDX kérdésekre adott válasz másodperceken belül érkezett, míg a megfelelő SQL lekérdezések 100 másodperces n agyságrendben m ozgott. A z 1 és 5. lekérdezés kicsit tovább tartott MDX módszerrel, köszönhetően annak, hogy ezek a l ekérdezések egy

d istint c ount s zámolt m értéket érnek el a k ockában. (A distinct count s zámolt m érték meghatározza a dimenzió minden e gyes elemére, h ogy hány különböző legalsó szintű eleme egy másik dimenziónak osztja meg a sorokat a f act táblában. Ha ugyanazon elem többször fordul elő, akkor is csak egyszer számoljuk) Az SQ L Sz erver 7 .0-ban m ég M DX kifejezést k ellett h asználni h ogy implementálhassuk a distinct countot egy számolt tagként, ami jelentősen befolyásolhatja a lekérdezések teljesítményét, amelyek használják ezt. Mivel a számolt tagok nem előaggregáltak, az OLAP Eszközöknek ezeket a számításokat a lekérdezés végrehajtásakor kell megoldanai. Ezenkívül, az OLAP Eszközöknek el kell olvasnia minden alsó szintű elemet, hogy kiszámolhassa a distinct countot. Az 1 és az 5 . MDX lekérdezés 200000 különböző elemet ér el a HouseholDim dimenzióban Ilyen mennyiségű elem elérése és a distinct count kiszámolása

megmagyarázhatja a megnövekedett v álaszidőket hideg cache esetén (A h ideg c ache ü res v agy n em tartalmaz releváns adatokat.) Ezzel szemben az SQL Szerver 2000-ben a distinct count egy standard aggregált funkcióként van implementálva ugyanúgy, mint a sum, min vagy max funkciók. Egy distinct count implementálása az SQL Server 2000-ben sokkal jobb teljesítményt tesz lehetővé mint egy számított mérték használata, és a distinct count hasonlóan működik a szinteken, mint a többi aggregált funkció. A feldolgozási időt teszteltük egy csoportnyi lekérdezéssel, ami mind SQL szerveren végrehajtandó SQL lekérdezéseket, mind OLAP Eszközökön, MOLAP kockán végrehajtandó MDX 9/14. oldal OLAP Eszközök 2001 Smulovics Péter lekérdezéseket tartalmazott. Az egyik használt lekérdezés a külön kiemelt SQL/MDX volt. Bonyolultsága ellenére a kevesebb mint 1 másodpercig tartott végrehajtása Query Száma OLAP MDX végrehajtás SQL Query

Hozzáfért (30% aggregáció) (mp) végrehajtás SQL rekordok Serveren (mp) száma 1 48 (SQL 7), 1 (SQL 2000) 134 ~ 13 millió 5 70 (SQL 7), 1 (SQL 2000) 95 543,000 6 1 126 ~ 13 millió 7 1 126 ~ 13 millió Az MDX-SQL teszthez használt egyik lekérdezés-pár: MDX Query WITH member [Measures].[Average Economic Income] as Sum({ [Measures].[Economic Income]} )/ ([Measures][Distinct Household Count] ) SELECT{ [Measures].[Average Economic Income] } ON columns, { [ProductDim].[Product Id]members} ON rows FROM AccountProfitability SQL Query CREATE TABLE #qry1 temp1 (product id INT,households int, totalei money ) insert into #qry1 temp1 (product id,households, totalei) select product id, count(distinct household id), sum(economic income) from VLDBMart.dboAccount prof fact group by product id select aprod name, b.totalei/bhouseholds from VLDBMartdboProductDim a, #qry1 temp1 b where a.product id=bproduct id MDX lekérdezési idők (MOLAP, ROLAP és HOLAP) Miután

rögzitettük a relatív feldolgozási időket az MDX és SQL lekérdezéseknél, rátérhetünk a három OLAP tárolási mód összevetésére. A teszthez hét MDX lekérdezést h oztunk l étre. A l ekéréseket különböző aggregációs mértékű MOLAP, ROLAP és HOLAP tárolási módú kockák ellen probáltuk ki, hideg és meleg cache-l is. A hideg cache-t a server újraindításával é rtük e l, a m eleg cache-t a query újra bejátszásával. Az átlagos végrehajtási idő minden tárolási tipusnál s zignifikánsan lecsökken meleg cache használatakor. Ugyancsak igaz ez a második lekérdezés (nem ugyanaz, hanem bármelyik) v égrehajtásakor, m ivel e kkor a z O LAP/RDBMS a datbázis e gy r észe a cache-ben van. A h ideg c ache h asználatakor k apott e redmények v iszont n em t eljesen f elelnek m eg annak az elvárásnak, hogy ROLAP és HOLAP használatakor az aggregáció 0 %-ról 30 és 60 %-ra emelésével a sebesség növekedése szignigikáns. A valóságos

növekedés alatta marad a z e lvártnak. H ideg cache ellenében a MOLAP teljesít a l egjobban, habár az aggregáció növekedtével nem sok változik. A meleg cache alapvetően más eredményeket mutatott, itt nem okozott nagy változtatást az aggregációs szint növelése (az ábrán nem látszik, de 0 %-ról 30 %-ra növelve mindhárom tipusnál körülbelül kétszeres sebességnövekedést értünk el). 10/14. oldal OLAP Eszközök 2001 Smulovics Péter Átlagos idők a leggyakrabban használt lekérdezésekben 160 140 Átlagos idő (mp) 120 100 80 60 40 20 0 0 30 60 90 MOLAP 43 20 5 3 HOLAP 140 22 4 3 ROLAP 140 26 7 7 Aggregáció % Az ábra nagyítása (elhagyva a 0 %-s aggregációt): 35 30 25 20 MOLAP HOLAP ROLAP 15 10 5 0 30 60 90 Egy másik fontos eset vizsgálata, mikor hideg, illetve meleg cache-sel indítjuk útjára a v izsgálatot. ( A h ideg cache ü res v agy n em t artalmaz re leváns a datokat) A z é les

rendszeren már biztos, hogy a meleg eset lesz a jellemző, de fontos, hogy tudjunk az átmenetről. 11/14. oldal OLAP Eszközök 2001 Smulovics Péter Átlagos idők (hideg és meleg cache) 160 140 Átlagos idő (mp) 120 100 MOLAP (hideg) HOLAP (hideg) ROLAP (hideg) MOLAP (meleg) HOLAP (meleg) ROLAP (meleg) 80 60 40 20 0 0 30 60 90 Aggregáció % A teszthez használt MDX lekérdezések: Query 1.) WITH member [Measures].[Distinct Household Count] as Count( Crossjoin ( {[Measures].[Economic Income]}, [HouseholdDim][Household Id].members), EXCLUDEEMPTY ) member [Measures][Average Economic Income] as Sum({ [Measures].[Economic Income]} )/ ( [Measures].[Distinct Household Count] ) SELECT { [Measures][Average Economic Income] } ON columns, { [ProductDim].[Product Id]members} ON rows FROM AllAccountProfitCube Query 2.) SELECT non empty crossjoin ( {[TimeDim].[Year]members} , {[CustomerSegmentDim].[All CustomerSegmentDim][High Balance], [CustomerSegmentDim].[All

CustomerSegmentDim][Traditional]} ) ON columns, crossjoin ( { [RegionDim].[State]members }, { [ProductDim].[All ProductDim][Regular Checking], [ProductDim][All ProductDim].[Savings] } ) ON rows FROM AllAccountProfitCube where ( [Measures].[MeasuresLevel][Economic Income] ) Query 3.) WITH member [Measures].[Moving Average of Economic Income] as Avg ( { [TimeDim].currentmember, [TimeDim]currentmemberlag(1), [TimeDim].currentmemberlag(2) }, [Measures][Economic Income] ) select {[Measures].[Economic Income], [Measures][Moving Average of Economic Income]} on columns, {[TimeDim].[Month]members} on rows from AllAccountProfitCube Query 4.) WITH MEMBER HouseholdDim.[ %IncomeOfCustomerasComparedtoZipcode ] AS sum( { [HouseholdDim].[All HouseholdDim][07401][100476] }, [Measures].[Economic Income] ) / sum( {[HouseholdDim][All HouseholdDim].[07401] }, [Measures][Economic Income]) , format string=#.00% SELECT { HouseholdDim[ %IncomeOfCustomerasComparedtoZipcode ]} ON columns, { Descendants (

[TimeDim].[All TimeDim][1996] , [TimeDim]month ) } ON rows FROM AllAccountProfitCube 12/14. oldal OLAP Eszközök 2001 Smulovics Péter Query 5.) WITH member [Measures].[Distinct Household Count] as Count( Crossjoin ( {[Measures].[Economic Income]}, [HouseholdDim][Household Id].members) , EXCLUDEEMPTY ) member [Measures][Average Economic Income] as Sum({ [Measures].[Economic Income]} )/ ( [Measures].[Distinct Household Count] ) SELECT { [Measures][Average Economic Income] } ON columns, { [ProductDim].[Product Id]members} ON rows FROM AllAccountProfitCube where ([TimeDim].[All TimeDim].[1996][Quarter 1][January] ) Query 6.) WITH MEMBER [Measures].[Economic Income for 1997] AS ([Measures].[Economic Income], [TimeDim][All TimeDim][1997] ) MEMBER [Measures].[Economic Income for 1996] AS ([Measures][Economic Income], [TimeDim].[All TimeDim][1996]) MEMBER [Measures][Economic Income change between 1996 & 1997] AS ( [Measures].[Economic Income], [TimeDim].[All TimeDim][1997] )

- ([Measures][Economic Income], [TimeDim].[All TimeDim][1996]) SELECT { [Measures][Economic Income for 1997] , [Measures].[Economic Income for 1996] , [Measures].[Economic Income change between 1996 & 1997] } ON columns, {[CustomerSegmentDim].[Cust Seg Id]members } ON rows FROM AllAccountProfitCube Query 7.) WITH MEMBER CustomerSegmentDim.[ %IncomeOfHighBalanceSegment ] AS sum( {[CustomerSegmentDim].[All CustomerSegmentDim][High Balance] }, [Measures].[Economic Income] ) / sum( {[CustomerSegmentDim][Cust Seg Id].members}, [Measures][Economic Income]) , format string=#00% MEMBER CustomerSegmentDim.[ %IncomeOfTraditionalSegment ] AS sum( {[CustomerSegmentDim].[All CustomerSegmentDim][Traditional] }, [Measures].[Economic Income] ) / sum( {[CustomerSegmentDim][Cust Seg Id].members}, [Measures][Economic Income]) , format string=#00% MEMBER CustomerSegmentDim.[ %IncomeOfCreditChallangedSegment ] AS sum( {[CustomerSegmentDim].[All CustomerSegmentDim][Credit Challanged] },

[Measures].[Economic Income] ) / sum( {[CustomerSegmentDim][Cust Seg Id].members}, [Measures][Economic Income]) , format string=#.00% SELECT { CustomerSegmentDim[ %IncomeOfHighBalanceSegment ], CustomerSegmentDim.[ %IncomeOfTraditionalSegment ], CustomerSegmentDim.[ %IncomeOfCreditChallangedSegment ]} ON columns, { [TimeDim].[Year]members} ON rows FROM AllAccountProfitCube CPU felhasználás MDX végrehajtás közben A végrehajtási idő mellett a CPU felhasználása is gyakran kritikus lehet a nagyobb adatbázisoknál. M onitoroztuk m indkét szerver C PU h asználatát, e redményeink a táblázatban láthatóak. Az adatokból kiderül, hogy MOLAP tárolási mód választása esetén természetesen a f eldolgozás n agy ré szét a z O LAP s zerveren h ajtja v égre az OLAP Eszközök, az RDBMS data marthoz szinte nem is nyúlva (a táblázatban látható mennyiség szerver mérési zaj is lehet). Tárolási Mód CPU Használat CPU Használat OLAP-on RDBMS-n MOLAP 18.13 0.24

ROLAP 8.23 35.53 HOLAP 5.84 45.12 13/14. oldal OLAP Eszközök 2001 Smulovics Péter Viszont, főleg a distinct count használata miatt, a ROLAP és HOLAP tárolási módok esetén az RDBMS szerver használata megnő. Teljesítményt fokozó tippek A teszt alatt több különböző adatbázis komponensel és karakterisztikával ismerkedhettünk m eg, é s í gy f elfedezhettünk n éhány t echnikát, a mivel csökkenthetjük az OLAP kocka feldolgozási idejét és a lekérdezések válaszidejét. Először is, ajánlott egy dimenzionális séma használata a data mart-hoz. Például a csillag séma modell jól passzol a dimenzionális OLAP kocka designhoz és teljesítmény szempontjából is kitűnő. A kocka feldolgozási ideje csökkenthető a kulcsok és indexek más módon történő felhasználásával. Amellett, h ogy kulcsot r endelünk minden egyes dimenzióhoz é s magához a fact táblához is a data martban és külső kulcsokkal kötjük össze a fact és

dimenzió táblákat, ajánlott, hogy létrehozzunk egy összetett indexet minden külső kulcsra és egyszerű indexeket minden egyes külső kulcsra. Használjuk a Optimize Schema lehetőséget az OLAP Eszközök kocka szerkesztőben, hogy minimalizáljuk a kapcsolatokat (join), amiket az OLAP Eszközök létrehoz miközben feldolgozza a kockát. Határozzuk meg a maximális folyamat buffer méretét az OLAP Eszközök tulajdonságok dialógus ablakában. Az MDX lekérdezés optimalizációra három módszer van. Először is, tegyük az OLAP Eszközöket (az OLAP kockák) és az SQL Szervert (data mart) különböző gépekre, és legyünk biztosak abban, hogy az OLAP gép elegendő memóriával rendelkezik. Másodszor, ha nagy mennyiségű adatot használunk, a particionálás jelentősen javíthatja az eredményeket. Harmadszor, a tesztek alapján a MOLAP tárolási mód adja a legjobb teljesítményt. Az v álasztott aggregációs mérték mind a feldolgozási időt, mind a

végrehajtásit befolyásolja. M onitorozzuk a re ndszer t eljesítményét, é s ehhez k épest á llítsuk b e a mértéket. A jánlott 2 5 % -ról i ndulni, é s s zükség esetén növelni Ha t ovábbi aggregációk é pítése v álik s zükségessé, j ó m egoldás a Usage-Based O ptimization Wizard, ami s egít to vábbi k ocka p articíók l étrehozásában, a lapul v éve a zon lekérdezések sorát, amit már elküldtünk a kockának. Ovatósan alkalmazva az i tt bemutatott technikákat az OLAP k ockák k észítéséhez és használatához, szinte bármilyen méretű adatázist hozhatunk létre, mely nem robban az a datokkal v aló f eltöltéskor, h atékonyan k arbantartható é s g yorsan é s eredményesen lekérdezhető Konklúzió A Microsoft vezető pozicióba került az iparban a datawarehousing funkció hozzáadásával a Microsoft SQL Server 2000-hez, és segített kiterjeszetni a piacot elérhető árú szoftverével. A cikkből kiderül, milyen módszerekkel

tehetjük a kockák felépítését és használatát könnyebbé és nagyobb teljesítményűvé. OLAP Eszközök II. rész, Kockák 14/14. oldal