VOORWOORD INLEIDING...5

Inhoudsopgave VOORWOORD......................................................................................................................4 1

INLEIDING .....................................................................................................................5 1.1 PROBLEEMSTELLING ..................................................................................................5 1.2 DATA WAREHOUSES ...................................................................................................6 1.3 TYPE INFORMATIE IN EEN DATA WAREHOUSE .................................................................7 1.4 ONTWERPEN VAN EEN DATA WAREHOUSE .....................................................................7 1.4.1 Waarom redundantie in een data warehouse? ..................................................7 1.4.2 Ontwerp van feitentabel en dimensietabel(len) ..................................................8 1.5 DATA WAREHOUSE (OLAP) VERSUS TRADITIONELE DBMS (OLTP)................................9 1.6 DATA WAREHOUSE ARCHITECTUUR ..............................................................................9 1.6.1 De rol van OLTP systemen binnen een data warehouse ...................................9 1.6.2 Extractie uit OLTP systeem.............................................................................10 1.6.3 OLAP view op extractie van OLTP systeem ....................................................12 1.7 NAVIGATIE BINNEN EEN DATA WAREHOUSE .................................................................12 1.8 GERELATEERD WERK ...............................................................................................13 1.8.1 Dimensionaal modelleren middels sterschema’s .............................................14 1.8.2 Modelleren middels sneeuwvlokschema’s .......................................................15 1.8.3 Dimensionaal modelleren middels data cubes .................................................16 1.9 HISTORIE IN DATA WAREHOUSES................................................................................17 1.9.1 Langzaam veranderende dimensies................................................................17 1.9.1.1 1.9.1.2 1.9.1.3

Updaten data warehouse middels ‘overschrijven van gegevens’ .............................. 18 Updaten data warehouse middels ‘stapelen van velden’ .......................................... 18 Updaten data warehouse middels ‘toevoegen nieuw veld’........................................ 19

1.9.2 Inflexibiliteit van sterschema’s.........................................................................20 1.10 DEFINITIE VAN EEN DATA WAREHOUSE........................................................................21 2

REPRESENTATIE EN ONTTREKKING VAN DATA UIT EEN DATA WAREHOUSE ...22 2.1 2.2 2.3 2.4 2.5

3

DRILL-MODELLEN .....................................................................................................22 DE VERSCHILLENDE VORMEN VAN DRILLING ................................................................23 HET OASI MANIFEST ................................................................................................28 AGGREGATEN IN EEN DATA WAREHOUSE ....................................................................31 CONCLUSIE .............................................................................................................34

DIMENSIONALE DATA WAREHOUSE MODELLEN EN OPERATIES ........................35 3.1 DRILL-MODELLEN .....................................................................................................35 3.2 BOUWSTENEN VAN EEN DRILL-MODEL .........................................................................36 3.3 DATA WAREHOUSE MET BIJBEHOREND DRILL-MODEL ....................................................38 3.3.1 Drill-up, drill-down model.................................................................................39 3.3.2 Drill across model ...........................................................................................40 3.3.3 Drill around model...........................................................................................42 3.3.4 Views bij een drill-model .................................................................................43 3.3.5 Kortste pad en totaliseerbare kolommen .........................................................44 3.4 DRILL-MODEL VERSUS ONDERLIGGENDE ARCHITECTUUR ..............................................47 3.5 CONCLUSIE .............................................................................................................50

4

QUERIES BEHORENDEN BIJ HET DRILL-MODEL ....................................................51 4.1 NON-INCREMENTELE QUERIES ...................................................................................52 4.1.1 Non-incrementele queries bij drill-up en drill-down...........................................52 4.1.2 Non-incrementele queries bij drill across .........................................................57 4.1.3 Non-incrementele queries bij drill around ........................................................59 4.2 INCREMENTELE QUERIES ..........................................................................................60 4.2.1 Incrementele queries bij drill-up en drill-down ..................................................60 4.2.2 Incrementele queries bij drill across en drill around .........................................63 4.3 CONCLUSIE .............................................................................................................64

5

CONCLUSIES..............................................................................................................65

BIBLIOGRAFIE...................................................................................................................68

Afstudeerscriptie “Data warehouses: dimensionale technieken ontrafeld,

versie: 26-09-02

Voorwoord Voor u ligt de afstudeerscriptie van Tim Nieuwenhuis. Na het behalen van mijn diploma Bedrijfsgerichte Informatica in juli 1999 aan de HIO Arnhem, ben ik gaan studeren aan de Katholieke Universiteit Nijmegen (KUN). Daar heb ik de afgelopen twee jaar gestudeerd, eveneens in de richting Bedrijfsgerichte Informatica. Mijn stage en afstudeeropdracht aan de HIO Arnhem heb ik volbracht in het bedrijfsleven. Voor het uitvoeren van mijn afstudeeropdracht aan de KUN heb ik bewust gekozen voor een opdracht binnen de Universiteit, dit omdat de manier van werken naar mijn idee veel zelfstandiger is en minder toegespitst op een specifiek bedrijf. De afstudeerperiode liep van februari 2001 tot en met augustus 2001. In deze periode heb ik een onderzoeksopdracht uitgevoerd. De resultaten hiervan heb ik beschreven in deze scriptie. Ik wil hierbij graag een woord van dank richten aan een aantal mensen die me begeleid hebben bij het uitvoeren en beschrijven van mijn onderzoek. Allereerst is dit mijn afstudeerbegeleider, dr. Patrick van Bommel. Hij heeft mij in de aanloop, bij het schrijven van mijn plan van aanpak, en tijdens mijn onderzoek intensief begeleid. Hierbij hebben we meerdere malen inhoudelijk gesproken over de beschreven aspecten. Aan deze gesprekken heb ik erg veel gehad, het heeft me zeker geholpen om op een duidelijke manier een weergave te geven van verschillende onderdelen van mijn onderzoek. Daarnaast wil ik een woord van dank richten aan mijn collega afstudeerders, waarmee ik zeer regelmatig overleg heb gehad en ervaringen heb uitgewisseld. Ik hoop dat deze scriptie een helder beeld vormt van het onderzoek dat ik uitgevoerd heb.

Tim Nieuwenhuis, augustus 2001

Faculteit Natuurwetenschappen, Wiskunde en Informatica, door: Tim Nieuwenhuis

-4-


1

versie: 26-09-02

Inleiding

Alvorens onderzoek te doen naar technieken die binnen data warehouses gebruikt worden, is het allereerst van belang te weten wat data warehouses nu eigenlijk zijn en welke technieken daarbinnen gebruikt worden. In dit eerste hoofdstuk zal daarom een beschrijving worden gegeven van data warehouses in het algemeen. De beschreven onderwerpen zullen in de volgende hoofdstukken nader geformaliseerd worden. 1.1

Probleemstelling

Data warehouses zijn er in allerlei vormen en maten. Zoals in de volgende paragraaf zal blijken, zijn er geen eenduidige definities over wat er onder een data warehouse wordt verstaan. Aan het eind van dit hoofdstuk zal daarom, na bespreking van verschillende eigenschappen van belang voor deze scriptie, een definitie worden gegeven van het begrip data warehouse zoals in deze scriptie gehanteerd zal worden. De dimensionaal gemodelleerde data warehouses (zie paragraaf 1.8) zijn de meest toegepaste vandaag de dag. Er wordt in de verschillende literatuur hoog opgegeven over data warehouses, alsof data warehouses een hele nieuwe vorm van opslag- en modelleringtechniek zijn. Op het eerste gezicht zou inderdaad gezegd kunnen worden, dat dit het geval is. Het interessante is natuurlijke of de technieken die in een data warehouse worden toegepast ook inderdaad zo vernieuwend zijn. Met deze gegevens in het achterhoofd kan er een centrale vraag geformuleerd worden die in deze scriptie centraal zal staan en waar onderzoek naar zal worden gedaan. Deze centrale vraag luidt: “Zijn data warehouses, in specifiek dimensionaal gemodelleerde, een innovatie m.b.t. gegevensopslag en gegevensmodellering?” Om een antwoord op bovenstaande vraag te vinden zal er in speciaal gekeken moeten worden naar bestaande technieken op het gebied van gegevensopslag en gegevensmodellering. Om hier een antwoord op te geven zullen een aantal bestaande technieken bekeken worden en gekeken zal worden of met behulp van deze technieken de data warehouse technieken gerealiseerd kunnen worden. Dat brengt ons bij de subvraag die van belang is. Deze subvraag luidt: “Zijn de technieken voor gegevensopslag en gegevensmodellering, die binnen data warehouses gebruikt worden, te realiseren m.b.v. bestaande/traditionele technieken voor gegevensopslag en gegevensmodellering?” Met de beschreven probleemstelling zal getracht worden in deze scriptie een beeld te geven van data warehouses.


-5-


1.2

versie: 26-09-02

Data warehouses

De term data warehouse wordt te pas en te onpas gebruikt. In veel verschillende literatuur wordt deze term gebruikt. Wat opvalt is dat de term data warehouse in veel verschillende contexten wordt gebruikt. De definities die daardoor over data warehouses worden gegeven zijn vaak zo verschillend dat het moeilijk is om één algemene definitie te vinden. Wel kan er gekeken worden naar de eigenschappen die een data warehouse bezit. Eén van deze eigenschappen is de opbouw. De opbouw van een data warehouse is steeds volgens eenzelfde principe, enerzijds een feitentabel[1] anderzijds één of meerdere dimensietabellen[1]. Deze structuur is weergegeven in figuur 1

dimensietabel

dimensietabel feitentabel

dimensietabel

Figuur 1, data warehouse sterschema

De feitentabel is het belangrijkste onderdeel van een data warehouse. In deze feitentabel is het centrale feit opgeslagen dat middels het data warehouse gerepresenteerd moet worden. De dimensietabellen vormen een aanvulling op deze feitentabel. Zoals in figuur 1 ook is aangegeven bestaan er vanuit de centrale feitentabel verwijzingen naar de dimensietabellen. Deze verwijzingen worden middels verwijzende sleutels gerealiseerd. Om het geheel wat duidelijker te maken een concreet voorbeeld. Stel er moet een data warehouse opgesteld worden waarin verkoopfeiten van een supermarkt worden opgeslagen. De feitentabel zou dan bestaan uit verkoopfeiten, met andere woorden welke producten zijn er verkocht. De dimensietabellen vormen een aanvulling op de feitentabel. Mogelijke dimensietabellen in dit voorbeeld zouden kunnen zijn, tijd en geografie. Deze dimensietabellen geven extra informatie over respectievelijk wanneer bepaalde producten verkocht zijn en waar deze producten verkocht zijn (er kunnen immers meerdere vestigingen van een winkel zijn). Een eerste aspect, de opbouw van een data warehouse, is dus bekend. Deze opbouw roept ook vragen op, waarom is er voor deze manier van opbouw gekozen en wat zijn de voordelen van hiervan? Om hier een antwoord op te geven zal er gekeken worden naar een ander aspect, namelijk type informatie dat gerepresenteerd moet worden.


-6-


1.3

versie: 26-09-02

Type informatie in een data warehouse

In eerste instantie kan het lijken alsof data warehouses niets anders zijn dan de traditionele databasesystemen zoals b.v. het relationele database management systeem (RDBMS). Toch is dit niet waar. Het belangrijkste verschil schuilt in het type systeem. De traditionele systemen als RDBMS’en zijn gericht op het verwerken van informatie, ook wel OLTP (OnLine Transaction Processing) terwijl data warehouses zogeheten OLAP (On-Line Analytical Processing) systemen zijn, gericht op het analyseren van informatie. Data warehouses worden daarom veel gebruikt door managers van bedrijven om analyses uit te voeren op de gegevens van hun organisatie, en aan de hand van deze analyse mogelijke strategische beslissingen te nemen. Voorbeelden van analyses kunnen zijn de omzetcijfers, hoeveel producten zijn er verkocht, welke producten worden veel verkocht, welke vestiging maakt de grootste omzet. Data warehouses zijn dus in tegenstelling tot de meeste traditionele database management systemen gericht op het analyseren van informatie (OLAP). Met dit aspect in het achterhoofd kan er ook iets gezegd worden over het ontwerp van een data warehouse in het algemeen. 1.4

Ontwerpen van een data warehouse

Bij het ontwerp van een data warehouse wordt uitgegaan van een feitentabel en één of meerdere dimensietabellen. Deze architectuur wordt ook wel een sterschema genoemd, vanwege zijn vorm. De feitentabel als middelpunt en de dimensietabellen daar omheen. In traditionele database management systemen is redundantie, het dubbel opslaan van zekere informatie, een aspect dat vermeden dient te worden. In een data warehouse is het toegestaan, of beter gezegd wenselijk, om redundantie van informatie te hebben. De reden hiervoor is gelegen in het feit dat in een data warehouse ‘ snelle’ responstijden gewenst zijn. Redundantie heeft een grote invloed op deze responstijden, door redundantie toe te staan hoeven er veel minder joins uitgevoerd te worden om het gewenste resultaat te berekenen. Het is duidelijk dat redundantie van groot belang is bij het ontwerp van een data warehouse. 1.4.1

Waarom redundantie in een data warehouse?

Het sterschema, feitentabel en dimensietabel(len), hoeft niet genormaliseerd te worden, immers normalisatie wordt toegepast om redundantie te vermijden. Het argument van snelle responstijden is reeds genoemd, toch zijn er belangrijkere aspecten te noemen waardoor in een data warehouse redundantie is toegestaan. Deze aspecten zijn: •

In een data warehouse kan een gebruiker geen update operaties (insert, delete, update) uitvoeren, de informatie wordt periodiek uit een operationeel OLTP systeem geladen in het data warehouse, waarnaar er geen update operaties meer gebruikt worden. Update analomiën, die het klassieke argument voor normalisatie vormen, zijn hier dus niet aan de orde.

•

De performance van een sterschema is vele malen beter dan de performance van een genormaliseerd schema, doordat in een genormaliseerd schema veel meer joins worden toegepast.

•

De ruimtebesparing door normalisatie is beperkt.

De eerste twee argumenten klinken logisch, maar het derde argument klinkt een beetje onrealistisch. Daarom een voorbeeld waarin aangegeven wordt dat de ruimtebesparing in een data warehouse door normalisatie beperkt is.


-7-


versie: 26-09-02

Stel er is een dimensietabel van 500 bytes (50 records van 10 bytes). Zeg dat deze dimensietabel 40000 records heeft, wat voor een gemiddeld systeem niet veel is. De totale ruimte die voor deze dimensietabel nodig is 20 Mb (500 bytes * 40000 records). Stel door normalisatie toe te passen kan een ruimtebesparing van maar liefst 50% bewerkstelligd worden, wat overeenkomt met 10Mb (50% van 20Mb). De ruimte die in een data warehouse nodig is voor een feitentabel is per definitie vele malen groter dan de ruimte die nodig is voor een dimensietabel. Stel in de feitentabel moet informatie worden opgeslagen betreffende verkopen van een bepaalde winkelketen voor een periode van 1000 dagen, 40000 producten en 500 verschillende filialen. Natuurlijk worden er niet iedere dag 40000 producten verkocht, zeg er worden 2000 verschillende producten per dag verkocht, de zogeheten dichtheid is dan (2000 / 40000) * 100% = 5%. De grootte van de feitentabel verkopen wordt dan 32 Gb (1000 dagen * 40000 producten * 500 filialen * 5/100 dichtheid * 32 bytes recordlengte). De besparing van 10 Mb op een feitentabel van 32 Gb is slechts (10Mb / 32Gb) * 100% = 0,03125%. Met deze besparing, die eigenlijk te verwaarlozen is, kan gesteld worden dat normalisatie niet nodig is in een data warehouse. 1.4.2

Ontwerp van feitentabel en dimensietabel(len)

De vraag welke dimensietabel(len) een rol spelen in een data warehouse kan eigenlijk vrij eenvoudig worden bepaald. Hierbij is het allereerst van belang te weten welk feit vastgelegd dient te worden. Al eerder is het voorbeeld aangehaald van een winkel die meerdere producten verkoopt. Het centrale feit is hier de verkoop van deze producten. Om nu mogelijke dimensietabellen te bepalen is het van belang de volgende vragen te stellen[2, 3]: • • • • • •

Wat gebeurt er? Waar gebeurt het? Wanneer gebeurt het? Voor wie gebeurt het? Onder welke omstandigheden gebeurt het? Wat is het detailniveau van de informatie?

De antwoorden op deze vragen geven al snel een indruk welke dimensietabellen een rol spelen, nog niet direct de invulling van deze dimensietabellen. Zo geeft de vraag waar een dimensietabel geografie, de vraag wanneer een dimensietabel tijd en de vraag voor wie een dimensietabel demografie. De exacte invullingen van deze tabellen, hun opbouw, wordt bepaald door de informatie uit het operationele systeem, middels een zogeheten snapshot, hierover in de volgende paragraaf meer. De feitentabel bestaat voor het grootste deel uit sleutelattributen die de verwijzing vormen naar de primaire sleutels in de verschillende dimensietabellen. Als extra kunnen er in een feitentabel zogeheten totaliseerbare kolommen gebruikt worden. Dit zijn onderdelen uit het operationele systeem, waarvan een zogeheten extractie is gemaakt middels een snapshot, die niet binnen 1 van de dimensietabellen geplaatst kan worden en waarmee gerekend kan worden in de zin dat het getalwaarden zijn. Voorbeelden hiervan zijn aantal en kosten. Een nadere uitleg betreffende deze extractie en snapshots zal verderop in dit hoofdstuk aan bod komen. Het ontwerpen van een data warehouse middels een sterschema is een belangrijke overweging die gemaakt dient te worden. Een voordeel is de eenvoud van het concept van een sterschema. Een nadeel is dat de dimensietabellen erg ‘ plat’ zijn, meta-informatie betreffende functionele afhankelijkheden en hiërarchieën zullen op een andere manier opgeslagen moeten worden. Hoe dit in zijn werk gaat valt buiten de context van deze scriptie.


-8-


1.5

versie: 26-09-02

Data warehouse (OLAP) versus traditionele DBMS (OLTP)

Nadat het ontwerp van het data warehouse gemaakt is, kan deze gevuld worden met informatie. Deze informatie wordt middels een zogeheten snapshot uit een operationeel systeem geëxtraheerd en in het data warehouse geladen. Voordeel is dat de analyse van deze gegevens uit het data warehouse geen negatieve invloed hebben op de performance van het operationele systeem. Het extraheren van de gegevens gebeurt bij voorkeur op rustige momenten, b.v. ‘ s nachts, wanneer er weinig mensen van het systeem gebruik maken. De vraag die hierbij op kan komen is dat de informatie uit het operationele systeem niet up to date meer is, op het moment dat deze in het data warehouse wordt geanalyseerd. In principe klopt deze stelling, echter in data warehouses speelt dit een ondergeschikte rol. Dit komt doordat in een data warehouse informatie over een grote periode gewenst is, b.v. de verkochte producten het laatste jaar, de verkochte producten van de laatste dag zullen op deze algemene informatie weinig invloed hebben. Er zijn nu reeds enkele aspecten van data warehouses besproken. In tabel 1 is een vergelijking gegeven van de belangrijkste verschillen tussen traditionele on-line systemen en data warehouses (OLAP). Eigenschap Soort systeem Update operaties Query operaties Detailniveau Aantal gebruikers Soort gebruikers Robuustheid Aanpasbaarheid Ontwerpproces

On-line systeem OLTP Veel kleine transacties Veel kleine operaties Veel detail >> 1000 Productiewerkers Kritiek voor bedrijfsproces Vaak moeilijk Waterval

Data warehouse OLAP Eén groot data laadproces Grote queries met veel joins Weinig detail << 100 Managers Mag tijdelijk uit de lucht zijn Flexibel Incrementeel

Tabel 1, on-line systeem versus data warehouse

1.6

Data warehouse architectuur

Om het verschil tussen een traditionele DBMS (OLTP) en een data warehouse (OLAP) duidelijk te maken zal in deze paragraaf een architectuur van een data warehouse worden weergegeven. Het doel hierbij is om duide lijk te maken welke, verschillende, rollen de OLTP en OLAP systemen spelen. 1.6.1

De rol van OLTP systemen binnen een data warehouse

In figuur 2 is een architectuur getekend die duidelijk aangeeft welke rol een OLTP systeem binnen een data warehouse speelt. Deze OLTP systemen zijn de on-line systemen waar als het ware een data warehouse op gedefinieerd moet gaan worden. Dit zijn vaak grote systemen die informatie bevatten over een zeker onderwerp, bijvoorbeeld verkopen binnen een supermarktketen. Nu werkt het data warehouse niet direct op deze systemen, hoofdreden hiervoor is om het on-line systeem niet te extra te belasten. De oplossing hiervoor is extractie van gegevens uit het on-line systeem naar een ‘ nieuwe’ structuur. Een andere reden waarom er een extractie gemaakt wordt is het feit dat in deze nieuwe data warehouse architectuur een andere manier van gegevensopslag kan worden toegepast, het is dus geen simpele kopie uit het on-line systeem. Redundantie is één van de aspecten die binnen een data warehouse wordt geïntroduceerd om snellere responstijden te krijgen van de queries die op deze structuur worden uitgevoerd. In de on-line systemen is redundantie


-9-


versie: 26-09-02

taboe, de responstijden van de data warehouse queries zouden aanzienlijk toenemen aangezien er veel meer joins moeten worden uitgevoerd. Extractie is dus noodzakelijk.

1.6.2

Extractie uit OLTP systeem

Eén van de belangrijkste redenen die aangegeven werd voor extractie is ontlasting van het on-line systeem[2, 3]. Bij de extractie van informatie zal het on-line systeem toch ook flink belast worden. Er moet al het ware een hele conversie plaatsvinden naar de data warehouse structuur waar de gegevens opnieuw moeten worden opgeslagen. Derhalve vindt extractie plaats op tijdstippen dat het on-line systeem niet erg belast is door ‘ andere’ gebruikers. Dit hele proces wordt veelal ‘ s nachts uitgevoerd. Een andere naam die hiervoor veel gebruikt wordt is een zogeheten snapshot. Deze naam geeft eigenlijk heel goed aan wat er nu daadwerkelijk gebeurt. Er wordt een extractie gemaakt van de inhoud van het on-line systeem op een zeker moment. Veel mensen zullen zeggen dat dit misschien een vreemde benadering is aangezien de inhoud van een on-line systeem continu wijzigt en derhalve het data warehouse ook. Dit is echter een misverstand. Data warehouses worden gebruikt om grote overzichten te genereren over ‘ langere’ periodes. Hierbij moet gedacht worden aan overzichten als: • • • •

Omzet in boekingsjaar 1998 Het aantal verkochte producten de afgelopen 6 maanden De totale opbrengst van product X Het aantal manuren besteedt de afgelopen 10 maanden op project Y

Duidelijk mag zijn dat hier de informatie over het laatste paar uur niet echt van belang is op de overzichten. Daarom worden deze snapshots slechts een beperkt aantal keer gemaakt. Nadat de snapshots gemaakt zijn en opgeslagen in een nieuwe data warehouse structuur, komt een tweede onderdeel ter sprake de data warehouse view (OLAP) op dit snapshot.


- 10 -


Data warehouse

versie: 26-09-02

OLAP

View

Data warehouse Db1

Data warehouse Db2

Data warehouse Dbn

Extractie

On-line DBMS Db1

On-line DBMS Db2

On-line DBMS Dbn

OLTP

Figuur 2, architectuur data warehouse


- 11 -


1.6.3

versie: 26-09-02

OLAP view op extractie van OLTP systeem

Nadat de extractie gemaakt is kan het data warehouse met deze gegevens aan de slag. Vanuit het data warehouse wordt als het ware een view gedefinieerd op de geëxtraheerde gegevens. Hierin kunnen overzichten gegenereerd worden van de gewenste informatie. Benadrukt moet worden dat het data warehouse zelf geen update operaties uitvoert. Er wordt uitsluitend informatie geselecteerd en weergegeven in een overzicht. Een belangrijk aspect dat hierbij om de hoek komt kijken zijn zogeheten aggregaten. Aggregaten zijn niets anders dan tellingen die vooraf zijn uitgevoerd en opgeslagen om de gewenste resultaten nog sneller aan de gebruiker te kunnen tonen. Deze aggregaten werken, net als de hele data warehouse , op de extractie die in het data warehouse is opgeslagen. In het volgende hoofdstuk zal hier nader op ingegaan worden. In figuur 2 zijn meerdere data warehouse databases weergegeven, alsmede on-line DBMS’en. Hiermee wordt aangegeven dat het mogelijk is om binnen één data warehouse extracties te maken van meerdere on-line systemen. Dit hoeft echter niet per definitie het mag ook één systeem zijn waar een extractie van gemaakt wordt. De gebruiker heeft de mogelijkheid om in het resultaat van deze extractie te navigeren. Op deze manier is het mogelijk om, indien gewenst, meer of minder detail te zien. Verderop in deze scriptie zal hier nader op ingegaan worden. Doordat een view werkt op de geëxtraheerde informatie, is het mogelijk om informatie uit verschillende typen on-line systemen te benaderen. Aangezien de data warehouse databases van een uniform type zijn. 1.7

Navigatie binnen een data warehouse

Navigatie is een belangrijk aspect van een data warehouse. Met navigatie wordt bedoeld het tonen van minder of meer detail in een view op de informatie uit het data warehouse. Dit navigeren wordt ook wel drilling genoemd. Drilling is eigenlijk niets anders dan het focussen op een stuk informatie (meer detail) of het uitfocussen (minder detail). In figuur 3 is een voorbeeld van een data warehouses gegeven. Dimensie TIJD

VERKOOP feiten

Dimensie PLAATS

dag

dag

stad

maand

stad

provincie

jaar

aantal

land

Figuur 3, data warehouse

Het data warehouse bestaat uit een tweetal dimensietabellen en een feitentabel. De feitentabel bevat twee verwijzende sleutels naar respectievelijk de dimensietabel tijd en plaats. De kolom aantal is een totaliseerbare kolom. Duidelijk is te zien dat er in de dimensietabellen een hiërarchie aanwezig is.


- 12 -


versie: 26-09-02

De entiteiten uit de dimensietabellen geven respectievelijk meer en minder detail over een zeker feit, in dit geval een verkoopfeit. Uit deze dimensietabellen en feitentabel kan een zogeheten drill-model worden afgeleid. In dit model wordt een grafische weergave gegeven van de verschillende detailniveaus binnen het data warehouse. Een zekere view bevat steeds één entiteit uit elke dimensietabel en dit voor alle dimensietabellen. Voor figuur 3 zouden mogelijke views steeds twee entiteiten bevatten, aangezien er twee dimensietabellen zijn. Een drill-model is opgebouwd uit knopen, waarin elke knoop een representatie is van een verzameling van entiteiten, uit elke dimensietabel één entiteit. Een aantal knopen uit het data warehouse van figuur 3 zouden zijn: [dag, stad], [dag, provincie], [jaar, stad], [jaar, land]. Zo’n drill-model bestaat dan uit de complete verzameling van knopen die alle mogelijke combinaties van entiteiten uit de aanwezige dimensietabellen weergeeft. In dit voorbeeld met twee dimensietabellen met ieder drie entiteiten zijn er 3 * 3 = 9 knopen in het drill-model aanwezig. Hoe deze knopen in het drill-model met elkaar verbonden zijn zal verderop in deze scriptie nader worden uitgelegd. De mate van gedetailleerdheid wordt bepaald door de knoop waarin men zich bevindt. De meest gedetailleerde knoop, dus met de meest specifieke informatie, wordt gevormd door de meest elementaire entiteiten. Dit zijn de entiteiten uit de dimensietabellen die helemaal bovenin de dimensietabel staan, in dit voorbeeld is dat de knoop [dag, stad]. Het navigeren naar een andere knoop gebeurt middel zogeheten drill-operaties. Er zijn er vier: drill-up, drilldown, drill across en drill around. Hun exacte werking wordt verderop in deze scriptie uitgelegd. 1.8

Gerelateerd werk

Over data warehouses zijn reeds vele artikelen geschreven. Deze artikelen behandelen verschillende vormen van informatiemodellering. In tabel 2 is een overzicht gegeven van de verschillend vormen van informatiemodellering binnen data warehouses. Auteur

Onderwerp

Relatie / koppeling

Ralph Kimball

Dimensionaal modelleren middels sterschema’s

Dimensionaal model dient als basis van ieder data warehouse

Thomas J. Kelly

Sneeuwvlokschema’s

Uitbreiding op het dimensionale model

Bill Inmon, Ralp Kimball

Data cubes

Speciale vorm van dimensionale modellering.

Tabel 2, vormen van informatiemodellering binnen een data warehouse

In tabel 2 staan de drie belangrijkste hoofdgroepen waarin de modellering van data warehouses in onder te verdelen is. In deze scriptie zal de dimensionale vorm van modelleren middels sterschema’s de uitgangspositie vormen. De reden hiervoor is dat deze vorm van modellering het meest toegepast wordt bij het modelleren van een data warehouse. Om de andere vormen van modellering ook te belichten zullen deze kort besproken worden. Ook hun onderlinge relatie met de daarbij behorende voor en nadelen zullen aan de orde komen.


- 13 -


1.8.1

versie: 26-09-02

Dimensionaal modelleren middels sterschema’s

Deze vorm van modellering wordt vandaag de dag het meest toegepast bij het modelleren van data warehouses. Uitgangspunt is hierbij een centrale feitentabel met daaromheen meerdere dimensietabellen, zoals in figuur 1 reeds weergegeven is. De vraag kan natuurlijk gesteld worden waarom deze vorm van modelleren zo’n voorkeur heeft. De belangrijkste reden hiervoor is dat sterschema’s niet genormaliseerd hoeven te worden. Een andere reden is dat het ‘ dimensionale denken’ erg aansluit bij de belevingswereld van de gebruiker. Deze vorm van modelleren wordt erg aangehangen door Ralph Kimball, een van de goeroes op het gebied van data warehouses. In figuur 4 is een voorbeeld gegeven van een sterschema.

dimensie 1

feiten

dimensie 2

primaire sleutel

verwijzende sleutel

entiteit

verwijzende sleutel

primaire sleutel

entiteit

entiteit

entiteit

entiteit

entiteit

entiteit Figuur 4, sterschema met zijn bouwstenen

Wat opvalt is dat een sterschema veel overeenkomsten heeft met een Entity Relationship diagram[10]. De overeenkomsten zijn: • • • • •

Gebruikmaking van entiteiten Gebruikmaking van attributen Relaties tussen tabellen Cardinaliteit, het is ook mogelijk om bij relaties aan te geven wat voor een relatie het betreft een één op één relatie een één op veel relatie of een veel op veel relatie Gebruik van primaire en verwijzende sleutels

Een sterschema is dus een bijzondere vorm van een Entity Relationship diagram. Toch bezit een sterschema één essentieel verschil: •

Een sterschema hoeft niet genormaliseerd te zijn

Daarnaast voegt Ralph Kimball in zijn visie de eis eraan toe dat een sterschema, of zoals hij zelf zegt[1, 2, 3]: “een sterschema is Kimbaliaans of ‘ puur’ als de primaire sleutels van de dimensietabellen (en dus de verwijzende sleutels in de feitentabel) betekenisloze nummers zijn, die niet in het bronsysteem (waar het data warehouse van is afgeleid) voorkomen, maar puur de functie van pointers van de feitentabel naar de dimensietabellen vervullen”.


- 14 -


1.8.2

versie: 26-09-02

Modelleren middels sneeuwvlokschema’s

Sneeuwvlokschema’s zijn eigenlijk een uitbreiding op het sterschema. Het belangrijkste verschil met het sterschema: •

Een sneeuwvlokschema is genormaliseerd

De aandachtige lezer zal zich afvragen waarom sneeuwvlokschema’s dan niet de voorkeur genieten boven een sterschema, aangezien normalisatie een eigenschap is die graag gezien wordt. In paragraaf 1.3.1. is al uitgelegd waarom een data warehouse liever niet genormaliseerd wordt. Toch wordt deze vorm van modellering wel toegepast. In [10] wordt een data warehouse besproken waarin er wel sneeuwvlokschema’s gebruikt worden en dus genormaliseerd wordt. Een voorbeeld van zo’n genormaliseerd sneeuwvlokschema is in figuur 5 weergegeven.

jaar

maand

dag

verkoop

winkel

stad

Figuur 5, genormaliseerd sneeuwvlokschema

Hier komt vooral het aspect performance om de hoek kijken. Ten opzichte van een niet genormaliseerd sterschema moet er veel meer gejoined worden om een gewenste overzicht uit het data warehouse te genereren. Duidelijk is te zien dat de dimensietabellen uit het sterschema hier ontrafeld zijn in meerdere losse tabellen. De garantie die bij een sterschema gegeven kan worden is dat de queries daar maximaal 1 tabel diep gaan, aangezien dimensietabellen zelf geen verdere verwijzingen hebben naar andere dimensietabellen. Dit in tegenstelling tot de sneeuwvlokschema’s. Het gebruik van een sneeuwvlokschema om een data warehouse te modelleren is niet per definitie een slecht alternatief. Er zijn voorbeelden waar binnen data warehouses wel degelijk updates uitgevoerd moeten worden. Dit speelt met name een rol bij het bijhouden van de historie van gegevens in een data warehouse. In deze gevallen werkt een genormaliseerd schema beter, immers in een genormaliseerd schema hoeft slechts op 1 plaats een feit aangepast te worden terwijl in een niet-genormaliseerd schema op veel plaatsen hetzelfde feit aangepast moet worden, door meervoudige opslag van gegevens. Over de historie in data warehouses later meer.


- 15 -


1.8.3

versie: 26-09-02

Dimensionaal modelleren middels data cubes

Data cubes[5, 13] zijn als het ware een speciale vorm van dimensionaal modelleren. Hierbij worden de verscheidene dimensies uit het data warehouse samengenomen. Een voorbeeld hiervan is weergegeven in figuur 6.

Plaatsdimensie

plaats ‘N ijmegen’

product ‘w asmiddel’ Tijdsdimensie

Productdimensie

tijd ‘ 25 mei 1998’

verkoopfeit ‘w asmiddel verkocht in Nijmegen op 25 mei 1998’

Figu ur 6, dimensies visueel gemaakt in een data cube

Hier zijn 3 dimensies, plaats, tijd en product, in een data cube. De ‘ cube’ metafoor levert een nieuwe methode om de wijze waarop de data in een data warehouse is georganiseerd visueel te maken. Een data cube kan opgebouwd zijn uit 2 of meer dimensies. Iedere dimensie representeert een identificerend attribuut. De vorm van de cube geeft aan dat: •

Verschillende dimensies tegelijkertijd gebruikt kunnen worden om een feit weer te geven

•

Hoe meer dimensies gebruikt worden des te hoger het detailniveau van de feiten

•

Dimensies kunnen tevens gebruikt worden als constraint, dit kan door alleen de rijen terug te geven die voldoen aan een waarde of een set van waarden die in de dimensie zijn opgeslagen. Een voorbeeld zou kunnen zijn een dimensie tijd. Hierbij zou in de data cube binnen deze dimensie een constraint geplaatst kunnen worden die forceert dat alleen feiten met een datum voor ‘25 maart 2000’ getoond worden.

Data cubes worden in veel OLAP-tools vandaag de dag gebruikt. De gebruiker kan hierbij zelf in een grafische omgeving een feit uit de cube selecteren en automatisch wordt een querie samengesteld die voor de gebruiker het gewenste resultaat berekent. In sommige OLAP -tools kan de gebruiker zelf een combinatie van dimensies kiezen waaruit een data cube wordt opgebouwd en waaruit vervolgens gewenste feiten worden berekend. Stel een manager van een bedrijf is geïnteresseerd in de verkochte producten per winkel. De dimensies product en plaats zijn hierbij van belang. Een dimensie als tijd, die in het data warehouse aanwezig kan zijn kan hierbij buiten de data cube gelaten worden.


- 16 -


1.9

versie: 26-09-02

Historie in data warehouses

Op het eerste gezicht lijkt dit een onderwerp dat niet zo heel erg belangrijk is. Toch schuilt hier een belangrijk aspect c.q. probleem dat het ontwikkelen c.q. beheren van een data warehouse moeilijk kan maken. Eerder is al aangegeven dat de verwijzende sleutels in een data warehouse bij voorkeur bestaan uit betekenisloze nummers. De eerste reden die daarvoor reeds gegeven is, is ruimtebesparing in de feitentabel. De andere reden komt in deze paragraaf aan de orde en heeft te maken met het omgaan met langzaam veranderende dimensieobjecten. Een zekere vorm van historie is al opgenomen in het data warehouse, n.l. de dimensie tijd. Iedere keer worden nieuwe feiten aan het data warehouse toegevoegd en is er op deze manier een vorm van historie gecreëerd. 1.9.1

Langzaam veranderende dimensies

In eerste instantie lijkt het dat dimensies die eenmaal in een data warehouse zijn opgenomen nooit meer wijzigen. Dit is echter een groot misverstand. Sommige dimensies veranderen in de loop der tijd wel degelijk, het gaat dan concreet om de opbouw van een bepaalde dimensie. In figuur 7 is een voorbeeld gegeven van de dimensie plaats.

dimensie plaats winkel_key naam plaats provincie indeling PK winkel_key 1 2 3

FK naam Waterstraat Kanaalstraat Leegmolen

plaats Nijmegen Eindhoven Groningen

provincie Gelderland Brabant Groningen

indeling Standaard Ruim Standaard

Legenda: PK = primaire sleutel FK = verwijzende sleutel Figuur 7, dimensie ‘plaats’ en bijbehorende tabel

De dimensie plaats geeft een naam van een winkel de plaats waar de winkel zich bevindt en de bijbehorende provincie en als laatste een indeling. Winkels worden aangeduid met een standaard of ruime indeling. Tot zover geen probleem, stel de winkel uit de Waterstraat wordt verbouwd en de indeling wijzigt van ‘ standaard’ naar ‘ ruim’. Deze wijziging moet echter wel in het data warehouse meegenomen worden. Tot dusver is als uitgangspunt gekozen dat in een data warehouse geen update operaties plaatsvinden. Voor dit soort situaties, veranderende dimensies, moet dit echter wel gebeuren. De vraag is nu alleen hoe dit moet gebeuren.


- 17 -


1.9.1.1

versie: 26-09-02

Updaten data warehouse middels ‘overschrijven van gegevens’

De meest voor de hand liggende oplossing lijkt inderdaad te zijn; het overschrijven van de gegevens. In dit geval zou de kolom ‘ indeling’ voor het filiaal ‘Waterstraat’ overschreven worden met de indeling ‘R uim’. Echter is dit geen goede oplossing. Data warehouses worden gebruikt om analyses te maken over een zekere periode. En bij deze analyse moet het filiaal Waterstraat voor de verbouwingsdatum onder de categorie ‘ Standaard’ worden gezien en niet onder ‘R uim’. Echter is dit na overschrijving niet meer te traceren. Toch is deze oplossing niet in alle gevallen taboe. Er zijn situaties denkbaar waarbij het overschrijven geen problemen oplevert. Hierbij moet dan wel goed gelet worden op de volgende twee punten[4]: •

De historie van gegevens moet niet belangrijk zijn, dus het is niet essentieel van belang dat informatie van voor de wijziging als zodanig weergegeven wordt

•

De modellering van de onderdelen die gewijzigd worden wel genormaliseerd is

Een voorbeeld van zo’n situatie kan zijn b.v. NAW-gegevens die van een klant opgenomen zijn uitsluitend ten behoeve van mailingdoeleinden. In deze situatie is het overschrijven geen enkel bezwaar. 1.9.1.2

Updaten data warehouse middels ‘stapelen van velden’

Een geheel andere oplossing voor het omgaan met veranderende dimensies is het stapelen van velden. Deze oplossing is weergegeven in figuur 8. PK winkel_key 1 2 3 5

FK naam Waterstraat Kanaalstraat Leegmolen Waterstraat

geldig vanaf 05-05-1998 05-05-1998 05-05-1998 08-07-2001

stad Nijmegen Eindhoven Groningen Nijmegen

Provincie Gelderland Brabant Groningen Gelderland

indeling Standaard Ruim Standaard Ruim

Legenda: PK = primaire sleutel FK = verwijzende sleutel Figuur 8, gewijzigde tabel bij dimensie ‘plaats’

Als oplossing is er een nieuwe rij toegevoegd aan de tabel. Dit is geen bezwaar aangezien in een data warehouse gegevens rustig toegevoegd mogen worden. Hier valt ook meteen het nut op van het gebruik van de nummers als primaire sleutel, eerder is al opgemerkt dat een reden was om ruimte te besparen, de tweede reden wordt hier weergegeven, voor het toevoegen van een nieuwe rij kan eenvoudig een nog niet gebruikt nummer ingevoegd worden om de nieuwe rij uniek te identificeren. Een andere verandering die zich voordoet is de verwijzende sleutel. Eerst lag deze verwijzende sleutel over het veld ‘ naam’. Echter wordt bij deze oplossing de naam dubbel ingevoerd, ‘ Waterstraat’ komt nu tweemaal voor, vandaar dat de verwijzende sleutel nu komt te liggen over de kolommen ‘ naam’ en ‘ geldig vanaf’. In veel data warehouses worden deze velden standaard opgenomen. Op deze manier is bijvoorbeeld uit te zoeken hoeveel omzetverhoging de uitbreiding van een winkel met zich meebrengt. Een nadeel van deze oplossing is dat het data warehouse inconsistent raakt. Met andere woorden; doordat een data warehouse veelal niet genormaliseerd is moeten wijzigingen vaak op meerdere plaatsen doorgevoerd worden, het gevaar is dat dit niet altijd


- 18 -


versie: 26-09-02

gebeurt op alle plaatsen (zekere bij zeer grote data warehouses). Inconsistentie doet dan al snel zijn intrede. 1.9.1.3

Updaten data warehouse middels ‘toevoegen nieuw veld’

Een geheel andere situatie kan zich voordoen op het moment dat er besloten wordt te reorganiseren en de indeling van winkels niet meer weer te geven met ‘ standaard’ en ‘ ruim’ maar met ‘ klein’, ‘middel’ of ‘ groot’. Wanneer hier de stapeloplossing uit de vorige paragraaf gehanteerd zou worden, zou dit leiden tot een enorme explosie van de dimensie ‘plaats’. Immers voor iedere vestiging moet een nieuwe rij ingevoegd worden in de tabel. Om dit te voorkomen wordt een nieuw veld toegevoegd. Een voorbeeld is weergegeven in figuur 9. PK

FK

winkel_key 1 2 3

naam Waterstraat Kanaalstraat Leegmolen

plaats Nijmegen Eindhoven Groningen

provincie Gelderland Brabant Groningen

indeling Groot Middel Groot

indeling_oud Ruim Standaard Standaard

Legenda: PK = primaire sleutel FK = verwijzende sleutel Figuur 9, gewijzigde dimensie ‘plaats’ door toevoeging kolom

Als oplossing is er hier gekozen om de nieuwe indeling te plaatsen in de ‘ oude’ kolom ‘ indeling’. De waarden die eerst in de kolom ‘ indeling’ stonden (zie figuur 7), zijn nu geplaatst in de nieuwe kolom ‘ indeling_oud’. Voordeel van deze oplossing is: •

De dimensie ‘ explodeert’ niet, m.a.w. er wordt niet voor ieder item een nieuwe rij ingevoegd

•

Oude reeds gedefinieerde queries blijven werken, kolomnaam is ongewijzigd, echter hoeft alleen gezocht te worden op de nieuwe naam die gebruikt wordt voor de ‘ indeling’

•

Historie is gewaarborgd, oude ‘ indeling’ kan opgevraagd worden indien gewenst. Mocht de oude ‘ indeling’ op een zeker moment niet meer van belang zijn, dan kan de kolom ‘ indeling_oud’ simpelweg verwijderd worden

Deze oplossing kan dus het beste toegepast worden wanneer er plotseling grote veranderingen worden doorgevoerd. Met grote veranderingen wordt bedoeld veranderingen die invloed hebben op bijna alle rijen uit een zekere dimensie. Betreft het wijzigingen van een kleinere vorm, bijvoorbeeld één winkelindeling die gewijzigd moet worden, zoals in de vorige paragraaf is beschreven, dan volstaat de oplossing met het stapelen van velden. Tot zover lijken de problemen met veranderende dimensies mee te vallen. Aan het begin is al aangegeven dat deze vorm van wijzigingen wel degelijk een probleem kan vormen, waar de meeste leveranciers van data warehousetools (nog) geen oplossing voor gevonden hebben.


- 19 -


1.9.2

versie: 26-09-02

Inflexibiliteit van sterschema’s

De oplossingen die beschreven zijn voor het omgaan met dimensies die wijzigen lijken een redelijk goede oplossing te bieden. Toch schuilt hier een probleem.

PK

FK

product_key 1 2 3

naam tandpaste pindakaas wasmiddel

code tp05 pk06 wm07

groep toiletartikel etenswaren schoonmaak

leverancier Derksen b.v. Janssen b.v. Kaartman b.v.

Legenda: PK = primaire sleutel FK = verwijzende sleutel Figuur 10, dimensie ‘product’

In figuur 10 is een voorbeeld gegeven van een deel van een data warehouse, het betreft hier de dimensie ‘product’. Van grote supermarktketens verandert de leverancier van een zeker product nog wel eens. Graag wil men ook hier in het data warehouse historie inbouwen. De oplossing lijkt simpel, voeg een kolom toe waarin de oude leveranciers staan, maak uit het operationele OLTP systeem middels extractie een lijst met de nieuwe leveranciers die bij een zeker product horen en voeg deze toe aan het data warehouse. Er lijkt niets aan de hand, echter zijn hier de huidige leveranciers gekoppeld aan de verkopen uit het verleden en het is onduidelijk of tijdens de verkopen uit het verleden dit wel de juiste leverancier was. Middels metagegevens zou eigenlijk bekend moeten zijn vanaf welke datum een kolom geldig is, op zich is dit nog wel te doen. Het is echter ook gewenst dat een eindgebruikerstool (OLAP) de gebruiker waarschuwt als een query mogelijk foutieve resultaten teruggeeft. Stel er is een leverancierskolom geïntroduceerd op 25 juni 1998. De vraag ‘ geef mij de verkopen per leverancier vanaf 25 augustus 1998’ kan correct beantwoord worden. De vraag ‘ geef mij de verkopen per leverancier vanaf 25 mei 1998’ kan niet correct beantwoord worden. Toch geven de query-hulpmiddelen hier vrolijk een incorrect antwoord op. Naast het hierboven beschreven metaprobleem is er nog een andere complicatie die kan optreden.

PK demogr_key 1 2 3 4 5 6

FK Leeftijdcat 0 tot 25 0 tot 25 25 tot 50 25 tot 50 >50 >50

Inkomen 0 tot 100.000 >100.000 0 tot 100.000 >100.000 0 tot 100.000 >100.000

geslacht M M M M M M

Legenda: PK = primaire sleutel FK = verwijzende sleutel Figuur 11, deel van de dimensie ‘demografie’


- 20 -


versie: 26-09-02

In figuur 11 is een deel van de dimensie ‘demografie’ weergegeven. Stel dat in deze dimensie een kolom ‘ regio’ toegevoegd wordt. De dimensie zal dan flink groter worden, maar dat is niet het grootste probleem. Het vervelende hier is dat, zelfs als alleen de huidige waarde correct aangegeven moet worden, sommige klanten die tot voor de wijziging dezelfde ‘ demografische‘ sleutel hadden, nu met een ander moeten worden aangeduid. En deze wijziging moet ook in de feitentabel worden doorgevoerd. Voor deze beschreven problemen zijn mogelijk wel oplossingen te vinden, alleen worden ze in de literatuur niet beschreven. De goeroes op het gebied van data warehouses, zoals Bill Inmon en Ralph Kimball zwijgen over dit soort problemen in alle toon aarden. Dit is een van de grootste problemen op dit moment binnen data warehouses. In de meeste data warehouse omgevingen zijn deze problemen (nog) niet opgelost.

1.10 Definitie van een data warehouse Een aantal belangrijke eigenschappen van een data warehouse zijn inmiddels behandeld. Om een duidelijk en helder beeld te geven zal er nu, gegeven de besproken eigenschappen, een definitie worden gegeven van het begrip data warehouse van waaruit de rest van deze scriptie is geschreven. Def1:

Data warehouse = verzameling van feitentabel(len) en dimensietabel(len) opgeslagen in een structuur waarin geen update operaties plaatsvinden en die gebruikt wordt om analytische informatie te verschaffen.

Deze analytische informatie, ofwel managementinformatie, geeft een overzicht van een zekere periode (b.v. de omzet over het laatste halfjaar). Data warehouses worden daarom ook veel gebruikt door managers, ze bieden namelijk snel een overzicht van globale gegevens, detailinformatie speelt meestal geen rol. Desgewenst kan deze detailinformatie middels de beschreven navigatie (drilling) verkregen worden. In het volgende hoofdstuk zal er in detail worden ingegaan op de onderdelen beschreven in dit eerste hoofdstuk.


- 21 -


2

versie: 26-09-02

Representatie en onttrekking van data uit een data warehouse

Sterschema’s met dimensietabellen vormen de basis van de meeste data warehouses vandaag de dag. In dit hoofdstuk zal in meer detail worden ingegaan op deze dimensionale sterschema’s en de daarbij behorende drill-modellen. Een drill-model is een afgeleid model dat gemaakt kan worden van een sterschema. Een drill-model beschrijft als het ware alle mogelijke toestanden waarin een data warehouse zich kan bevinden. Daarnaast zal er aandacht besteed worden aan het onttrekken van informatie uit een data warehouse middels queries. 2.1

Drill-modellen

Een drill-model is een navigatiemodel dat behoort bij een zeker data warehouse sterschema en beschrijft in welke verschillende toestanden een data warehouse zich kan bevinden. Een drill-model kan afgeleid worden uit een sterschema. Aan de hand van een eenvoudig voorbeeldje zal dit worden toegelicht.

dimensie tijd

feit verkoop

dimensie plaats

dag

dag

stad

maand

stad

provincie

jaar

aantal

land


In figuur 12 is een data warehouse sterschema getekend. Het betreft hier een eenvoudig sterschema met slechts twee dimensietabellen. In een view op dit sterschema zullen altijd twee dimensies aanwezig moeten zijn, aangezien er twee dimensietabellen zijn. Het aantal vertices in het drill-model wordt daarom gevormd door het aantal mogelijke combinaties van entiteiten uit de betreffende dimensietabellen. In dit geval is dit de volgende verzameling van knopen; {(dag, stad), (dag, provincie), (dag, land), (maand, stad), (maand, provincie), (maand, land), (jaar, stad), (jaar, provincie), (jaar, land)}. Het concrete drill-model dat bij deze verzameling hoort is weergegeven in figuur 13.


- 22 -


dag stad

maand stad

jaar stad

dag provincie

maand provincie

jaar provincie

dag land

maand land

jaar land

versie: 26-09-02

Figuur 13, concreet drill-model

Wat opvalt is dat er geen directe verbindingen zijn tussen alle knopen. Het is dus onmogelijk om rechtstreeks van de knoop [dag, land] naar de knoop [jaar, land] te gaan. De overgang van de ene naar de andere knoop wordt dan drilling genoemd. Hierdoor is het mogelijk om als eindgebruiker op verschillende detailniveaus naar informatie uit het data warehouse te kijken. De term drilling is wat algemeen, er zijn namelijk verschillende vormen van drilling mogelijk, afhankelijk van de opbouw van het data warehouse. 2.2

De verschillende vormen van drilling

Een drill-model kan onderverdeeld worden in een viertal submodellen. Er zijn 4 soorten van drilling[1, 14], derhalve zijn er ook 4 submodellen te abstraheren, te weten: • • • •

Drill-down model Drill-up model Drill across model Drill around model

De vraag die hierbij gesteld kan worden is: “kan uit elk model van een data warehouse (b.v. sterschema) het viertal submodellen gegenereerd worden?” Om deze vraag te beantwoorden zal er gekeken moeten worden naar de eigenschappen van deze vier vormen van drilling. Drill-up en Drill-down: deze vorm van drilling kan binnen elk data warehouse model worden toegepast. Er wordt respectievelijk minder of meer detail getoond in de data warehouse view. Een voorbeeld van een drill-up en drill-down is gegeven in figuur 14.

maand provincie

drill-up

jaar provincie

dag land

drill-down

maand land

Figuur 14, drill-up/drill-down voorbeeld


- 23 -


versie: 26-09-02

De drill-up operatie gaat van de knoop [maand, provincie] naar [jaar, provincie]. In woorden zou dit betekenen dat er van een view waarin de verkopen per maand per provincie wordt overgegaan naar een view met de verkopen per jaar per provincie. Dit levert een kleinere minder gedetailleerde view. Aangezien er nu niet meer per maand een overzicht wordt gegeven maar slechts per jaar. De andere kant op, drill-down, heeft het omgekeerde effect. Deze operatie gaat van de knoop [maand, land] naar [dag, land]. In woorden zou dit betekenen dat er van een view waarin de verkopen per maand per land worden weergegeven wordt overgegaan naar een view met de verkopen per dag per land. Dit levert juist een grotere meer gedetailleerde view. Immers worden nu de verkopen per dag weergegeven i.p.v. gegroepeerd per jaar. Een geheel andere vorm van drilling is drill across en drill around. In het volgende hoofdstuk zal hier in meer detail op worden ingegaan, evenals drill-up en drill-down operaties. Toch hier alvast een klein voorproefje. Drill across is als het ware het combineren van feiten uit verschillende dimensietabellen. In concreet zou dit betekenen dat een data warehouse dat feiten representeert over verkopen in een supermarkt per plaats in een zekere periode, weergegeven door de dimensies plaats en tijd en een data warehouse betreffende personele kosten per plaats in een zekere periode, gecombineerd kunnen worden in één view. Schematisch is dit weergegeven in figuur 15.


- 24 -


personeelskosten feiten

verkoop feiten

dimensie tijd

versie: 26-09-02

dimensie plaats

dimensie tijd

dimensie plaats

DRILL ACROSS

verkoop en personeelskosten feiten

dimensie tijd

dimensie plaats

Figuur 15, drill across voorbeeld


- 25 -


versie: 26-09-02

Drill around berust op hetzelfde principe als drill across, echter zijn hier de feitentabellen die dezelfde dimensies delen niet gegroepeerd in een lineaire manier. Hierover in het volgende hoofdstuk meer. Nu een klein voorproefje in tabel 3. Aan de hand van een voorbeeld zal er een concreet voorbeeld gegeven worden van drilling across en drilling around.

tijd

product

plaats

units

kosten

Juni Juni Juni Juni Juni Juni

Kaas Kaas Worst Eieren Eieren Eieren

Haarlem Haarlem Nijmegen Nijmegen Nijmegen Nijmegen

4 5 4 5 4 6

40 50 40 50 40 60

tijd

product

plaats

afdeling

voorraad




1 2 3 5 5 5

500 450 300 340 360 385

Tabel 3, drill across over 2 feitentabellen

Bovenstaande feitentabellen delen drie dimensietabellen, tijd, product en plaats. Belangrijk is dat deze dimensietabellen exact overeenkomen, slechts dan kan een drill across worden toegepast. Er kan nu middels een drill across een combinatie van feiten worden weergegeven. Stel dat middels een drill across de dimensies tijd, product en plaats worden beschouwd en daarbij de kosten uit de eerste feitentabel en voorraad uit de tweede feitentabel worden gecombineerd. Het resultaat zou er dan uitzien zoals tabel 4 weergeeft.

tijd

product

plaats

kosten

voorraad




40 50 40 50 40 60

500 450 300 340 360 385

Tabel 4, resulterende tabel na drill across


- 26 -


versie: 26-09-02

Om een drill across toe te passen moet er het volgende gelden: •

Er moeten minimaal 2 feitentabellen aanwezig zijn

•

Deze feitentabellen moeten exact dezelfde dimensies delen waarlangs geaggregeerd wordt;

Voor een drill around gelden dezelfde regels. Het verschil is terug te voeren naar de manier waarop de gerelateerde feitentabellen opgebouwd zijn. Drill around is een vorm van drill across waarbij de gerelateerde feitentabellen, die dezelfde dimensies delen, niet lineair gerangschikt zijn. Een voorbeeld in figuur 16:

kliniek

ziekenhuis

patiënt

longziekten

verzekering

Figuur 16, drill around voorbeeld

De patiënt is hier het centrale feit, de andere vier feitentabellen delen alle informatie over de patiënt met elkaar. Om een drill around toe te passen moet er het volgende gelden: • •

Er moeten minimaal 2 feitentabellen aanwezig zijn Deze feitentabellen moeten exact dezelfde dimensies delen, deze dimensies waarlangs geaggregeerd wordt, worden gedeeld in 1 centrale feitentabel

Terugkomend op de vraag of uit ieder dimensionaal data warehouse model de vier mogelijke drill-modellen te generen zijn, kan het volgende gezegd worden: •

Een drill-up en drill-down model kan uit ieder dimensionaal data warehouse model (met 1 feitentabel en 1 of meerdere dimensietabellen) gegenereerd worden

•

Een drill across en drill around model kan niet uit ieder dimensionaal data warehouse model gegenereerd worden. Dit doordat deze vormen van drilling uitgaan van het linken van feitentabellen die 1 of meerdere dezelfde dimensies delen. De voorwaarde hiervoor is dus de aanwezigheid van minimaal 2 feitentabellen die minimaal 1 dimensie met elkaar delen.


- 27 -


2.3

versie: 26-09-02

Het OASI manifest

Over representatie van gegevens uit een data warehouse is inmiddels al het een en ander gezegd. Een ander onderdeel is het onttrekken van de gegevens uit een data warehouse. Dit lijkt misschien een erg technische benadering en erg gericht op implementatie, de reden om dit wel te bespreken is om in het begrip sterschema wat meer inzicht te krijgen. In figuur 17 is een voorbeeld sterschema van een data warehouse gegeven.

dimensie tijd

feitentabel verkoop dimensie plaats

dag maand

#kosten

plaats provincie

jaar

land

Figuur 17, sterschema zonder keys

De sleutels, de eerder gebruikte ‘ betekenisloze’ nummers, zijn weggelaten. Eerder is er al gesteld dat deze nummers slechts dienen als een pointer van de centrale feitentabel naar de dimensietabellen. Het feit dat ze er zijn kan ook afgeleid worden aan de ge tekende lijnen. Het feit dat de verwijzende sleutels niet getekend worden impliceert dat de gebruiker ze ook niet nodig heeft bij het onttrekken van gegevens aan het data warehouse. In feite worden de meeste queries door het OLAP-tool geconstrueerd en hoeven niet door de gebruiker ingetypt te worden. In eerste instantie lijkt het erop dat de betreffende kolommen wel degelijk nodig zijn. De reden dat ze nodig zijn is puur gebaseerd op het feit dat tabellen uit het sterschema gejoined worden. In figuur 18 is een query gegeven die qua structuur representatief is voor het onttrekken van gegevens uit een data warehouse.

select from where AND AND group by

dag, plaats, SUM(kosten) tijd t, verkoop v, plaats p v.plaats_key = p.plaats_key v.tijd_key = t.tijd_key indeling = ‘standaard’ dag, plaats

Figuur 18, query op sterschema

Uit de query blijkt dat de sleutels uitsluitend gebruikt worden om dimensietabellen te joinen. Verder wordt een restrictie gesteld aan het aantal rijen dat terug wordt gegeven, men is uitsluitend geïnteresseerd in winkels met een ruime indeling. Kortom de sleutels spelen een puur technische rol.


- 28 -


versie: 26-09-02

Een goed data warehouse OLAP-tool zou de join-condities zelf moeten kunnen bedenken. Immers is bekend welke tabellen betrokken zijn (deze zijn opgegeven in de from clausule van de query). Het OLAP-tool moet dan natuurlijk wel beschikken over zogenoemde metainformatie over primaire en verwijzende sleutels. Nu geldt niet in alle gevallen dat de joincondities eenvoudig af te leiden zijn, denk bij voorbeeld aan een situatie waarbij tussen twee tabellen twee verwijzende sleutels aanwezig zijn. Een voorbeeld uit staat in figuur 19. vlucht feiten van_key

dimensie vliegveld

naar_key vliegveld_key # kilometers naam stad Figuur 19, dubbele verwijzende sleutel

De manier om met dit probleem om te gaan valt buiten de context van deze afstudeerstage. Het wiskundige model hiervoor wordt uitgebreid beschreven in [9]. Op dit moment volstaat te weten dat een sterschema een zodanig simpele databasestructuur is dat join-condities inderdaad eenvoudig af te leiden zijn. De query uit figuur 18 kan derhalve herschreven worden zoals in figuur 20 weergegeven. select from where AND group by

dag, plaats, SUM(kosten) tijd t, verkoop v, plaats p {door systeem gegenereerde joincondities} indeling = ‘standaard’ dag, plaats

Figuur 20, vereenvoudigde query op sterschema

Echter kan er nog een stap verder gegaan worden. De bewering is dat de structuur van een sterschema helemaal niet relevant is voor een eindgebruiker. Als er gekeken wordt naar de kolommen die een rol spelen in deze query dan zijn dat dag, plaats, kosten en indeling. Voor al deze kolommen geldt dat eenduidig te bepalen is van welke tabel zei deel uitmaken, immers iedere kolomnaam komt slechts in 1 tabel voor. De meta-informatie in de from clausule is dus ook overbodig. Het systeem kan zelf uitzoeken welke tabellen moeten worden aangesproken om betreffende kolommen te raadplegen. De query die dan overblijft staat in figuur 21.

select from where AND group by

dag, plaats, SUM(kosten) {door systeem bepaalde tabellen} {door systeem gegenereerde joincondities} indeling = ‘standaard’ dag, plaats

Figuur 21, verder vereenvoudigde query op sterschema


- 29 -


versie: 26-09-02

De eindgebruiker hoeft dus slechts aan te geven welke attributen hij nodig heeft. Kortom de gebruiker hoeft slechts een lijst van niet-sleutelattributen te hebben om een query te kunnen construeren. Deze lijst kan beschouwd worden als 1 virtuele tabel. De benodigde query zou er uitzien als in figuur 22 is weergegeven. select from where AND group by

dag, plaats, SUM(kosten) {virtuele tabel} {door systeem gegenereerde joincondities} indeling = ‘standaard’ dag, plaats

Figuur 22, vereenvoudigde query op sterschema met virtuele tabel

De kern van het verhaal is het zogeheten OASI manifest[6, 7]. OASI manifest: “alleen de complete set van niet-sleutelattributen van een sterschema moet als metakennis gedeeld worden tussen de back-end data warehouse en het front-end OLAP-tool” Kern van dit verhaal is dat de gebruiker geen kennis hoeft te hebben van de structuur van een sterschema. Alleen de set van niet sleutelattributen speelt een rol. Duidelijk is dat er een scheiding is tussen technische dimensies en gebruikersdimensies. In figuur 23 hiervan een voorbeeld. Gebruikersdimensies

OASI

technische dimensies

adres postcode locatie

woonplaats voornaam

klant

achternaam persoonlijk

geslacht

Figuur 23, gebruikersvisie op OASI

De gebruiker ziet als het ware alleen het OAS-interface, met de technische dimensie heeft hij niets te maken. De mogelijkheid bestaat om als gebruiker eigen dimensies te maken op het OAS-interface. Op deze manier is het mogelijk om een afstelling te krijgen op een voor die gebruiker gewenste manier. De technische kant blijft zo onaangetast, wanneer de technische dimensies wijzigen zal ook het OAS-interface (de virtuele tabel) wijzigen. De gebruiker zal zijn gebruikers dimensies daarop aan moeten passen. Veelal is dit een kwestie van selecteren van nieuwe dimensieattributen en deze plaatsen in een gewenste gebruikersdimensie. Kortom sterschema’s zijn een puur technische modellering voor de opslag in een data warehouse. De eindgebruiker hoeft geen kennis te hebben van deze sterschema structuur, hij kan afhankelijk van eigen wensen een eigen interface met dimensies definiëren.


- 30 -


2.4

versie: 26-09-02

Aggregaten in een data warehouse

Een ander aspect dat samenhangt met het onttrekken van gegevens aan een data warehouse zijn aggregaten. Aggregaten zijn eigenlijk niets anders dan een voortelling van gegevens. In deze paragraaf zal in specifiek aandacht worden besteed aan aggregaatnavigatie[7, 8]. In combinatie met het eerder besproken OAS-interface is dit eenvoudig te realiseren. De performance van queries is veelal nog sterk te verbeteren. Een mogelijkheid daartoe is om na het laden reeds voortellingen klaar te zetten. Stel dat iemand reeds eerder verkoopfeiten per provincie en per plaats uit het data warehouse heeft laten berekenen en deze resultaten bewaard heeft in een tabelvorm, agg1_verkoop. Stel dat in dit aggregaat de dimensie tijd nog niet weggeaggregeerd is, deze doet nog mee dus op het fijnste detailniveau zoals in de oorspronkelijke dimensietabel tijd. De tabel tijd kan daardoor samen met het opgeslagen aggregaat agg1_verkoop makkelijk gebruikt worden. Samen vormen ze als het ware een klein sterschema, dit is weergegeven in figuur 24.

agg1_verkoop

product dimensie

OASI van agg1_verkoop

prod_key stad provincie

prod_key naam merk groep

naam merk groep groep_code kleur verpakking

Figuur 24, aggregaat-ster en OASI

Het OAS-interface van het data warehouse staat geheel rechts weergegeven. Stel dat nu berekent moet worden de omzetten per provincie en categorie voor winkels met een standaard indeling. Met gebruikmaking van het getoonde aggregaat zou de bijbehorende query er uitzien als in figuur 25 is weergegeven. select from where AND group by

provincie, categorie, SUM(omzet) agg1_verkoop, product agg1_verkoop.prod_key = product.prod_key indeling = ‘standaard’ provincie, categorie

Figuur 25, vereenvoudigde query op sterschema m.b.v. aggregaat

Het grote voordeel is dat agg1_verkoop vele malen kleiner is dan de oorspronkelijke feitentabel en bovendien is er 1 join minder nodig. Een aggregaat is bruikbaar als de gewenste verzameling van attributen die in de query een rol spelen (A), een deelverzameling is van de OAS van het aggregaat(Aggoas). De OAS van het aggregaat is op zijn beurt weer een deelverzameling van de OAS van het oorspronkelijke sterschema(Steroas). Een aggregaat is dus bruikbaar als geldt: A ⊆ Aggoas ⊆ Steroas


- 31 -


versie: 26-09-02

Deze vorm van gebruik van aggregaten werkt goed voor queries die te voorspellen zijn. Een ander feit is dat het opslaan van aggregaten wel weer redundantie oplevert, aangezien het aggregaat zelf afleidbare informatie bevat. Een geheel ander probleem ontstaat bij zogeheten ad-hoc queries, dit zijn queries die moeilijk vooraf voorspelbaar zijn. De vraag die hierbij naar voren komt is; “hoe kan er bepaald worden of er voor de ad-hoc querie een aggregaat beschikbaar is en zo ja welk aggregaat het meest geschikt is om te gebruiken”. De oplossing voor het vinden van een geschikt aggregaat wordt opgelost middels de zogeheten aggregaatnavigatie[7, 8]. Hierbij wordt meta-informatie beschikbaar gesteld over de reeds opgeslagen aggregaten. Aggregaatnavigatie moet geschieden door het RDBMS zelf. In tabel 5 is een voorbeeld gegeven voor aggregaatnavigatie.

kolom stad provincie land dag maand jaar

agg_0 × × × × × ×

agg_1 × ×

agg_2 ×

×

× ×

x

×

agg_3 × ×

agg_4

× ×

× × ×

gewenst ×

× ×

Tabel 5, tabel voor aggregaatnavigatie

Er zijn een vijftal aggregaten die in de tabel zijn weergegeven, te weten: •

agg_0 dit is de standaard OAS, dit is de oorspronkelijke set van alle attributen

•

agg_1 dit is een aggregaat dat verkoopfeiten geaggregeerd per stad per provincie per dag en per jaar

•

agg_2 dit is een aggregaat dat verkoopfeiten geaggregeerd per stad per land per dag en per jaar weergeeft

•

agg_3 dit is een aggregaat dat verkoopfeiten geaggregeerd per stad per provincie per dag en per maand weergeeft

•

agg_4 dit is een aggregaat dat verkoopfeiten per land per maand en per jaar weergeeft

•

gewenst is de gewenste query die een gebruiker wil uitvoeren, hierbij zijn de kolommen stad, dag en jaar geselecteerd

Voor het gewenste aggregaat geldt dat het kleinste aggregaat dat de als deelverzameling de complete set van gewenste kolommen uit de geformuleerde query bezit. Met kleinste aggregaat wordt bedoeld dat aggregaat dat naast de kolommen die in de geformuleerde query voorkomen zo min mogelijk (liefst geen) andere kolommen bevat. Een geschikt aggregaat vinden is dus een kwestie van controleren of de set van kolommen uit de aanwezige kandidaat aggregaten een deelverzameling is van de set van kolommen uit de gewenste query. agg_0 is toegevoegd om te voorkomen dat er geen geschikte aggregaat gevonden wordt door het systeem, aangezien agg_0 de oorspronkelijke OAS is met alle kolommen uit het data warehouse is deze altijd geschikt in geval een betere aggregaat niet voorhanden is. In het gegeven voorbeeld is agg_2 dus de meest geschikte om te gebruiken.


- 32 -


versie: 26-09-02

Om de aggregaatnavigatie wat meer technische weer te geven zal deze nader uitgewerkt worden. Om de aggregaatnavigatie te laten werken is meta-informatie nodig, echter zeer weinig meta-informatie. Deze meta-informatie afkomstig uit tabel 5 kan simpelweg gerepresenteerd worden in een tabel met 2 kolommen, zoals weergegeven in tabel 6.

agg_nr 0 0 0 0 0 0 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4

kolomnaam stad provincie land dag maand jaar stad provincie dag jaar stad land dag jaar stad provincie dag maand land maand jaar Tabel 6, metadata voor aggregaatnavigatie

Uit bovenstaande tabel kan meta-informatie worden afgeleid; b.v. “de kolom ‘ jaar’ komt voor in het aggregaat met relatieve wenselijkheid 2” . Stel dat de informatie over de kolommen die bij de gewenste query (gewenst) betrokken zijn gerepresenteerd zijn zoals weergegeven in tabel 7. Kolomnaam Stad Dag Jaar Tabel 7, kolomnamen nodig in gewenste query

Om nu het gewenste aggregaat te laten berekenen door het systeem, kan simpelweg de query uit figuur 26 losgelaten worden, hierbij wordt gebruik gemaakt van de tabellen ‘aggregaat‘ en ‘query’ uit respectievelijk tabel 6 en tabel 7. select from where group by having order by

agg_nr aggregaat a, query q a.kolomnaam = q.kolomnaam a.agg_nr COUNT(*) = (select COUNT(*) from q) a.agg_nr DESC

Figuur 26, query voor bepalen meest geschikte aggregaat


- 33 -


versie: 26-09-02

Eerder is gesproken dat de OAS van de gewenste query een deelverzameling moet zijn van de OAS van het geschikte aggregaat. Deze eis is ingewilligd door de having-clausule uit de query van figuur 26. De kolom agg_nr vervult een dubbele rol in de beschreven query. Enerzijds is het een identificatie voor een zeker aggregaat anderzijds geeft het de relatieve wenselijkheid aan van het gebruiken van het aggregaat weer: hoe hoger het nummer hoe wenselijker het aggregaat. Er van uitgaande dat de aggregaten van laag naar hoge nummers steeds minder attributen bevatten, slechts met deze manier van opslag wordt gewaarborgd dat met de beschreven query het meest geschikte aggregaat naar voren komt. Aggregaatnavigatie blijkt dus een essentieel onderdeel bij het onttrekken van informatie aan een data warehouse. Zeker omdat data warehouses veelal enorm grote tabellen hebben is het van belang de rekentijden voor de queries te beperken door gebruik te maken van aggregaten zoals beschreven. Een mogelijke uitbreiding op de beschreven techniek van aggregaatnavigatie, is het analyseren van queries die worden losgelaten op het data warehouse. Op deze manier zouden nieuwe aggregaten ingericht kunnen worden. 2.5

Conclusie

In dit hoofdstuk is aandacht besteed aan representatie en onttrekking van data uit een data warehouse. De drill-modellen zijn beschreven als belangrijkste representatiemiddel van dimensionale sterschema’s. Duidelijk is geworden dat niet uit ieder sterschema ieder willekeurig drill-model gegenereerd kan worden. Naast representatie speelt het onttrekken van gegevens aan het data warehouse een belangrijke rol. Hierbij is duidelijk geworden dat sterschema’s niets meer zijn dan een technische representatie van het data warehouse. De eindgebruiker hoeft geen kennis te hebben van de opbouw van dit sterschema om m.b.v. OLAP-tools het data warehouse te benaderen en gegevens te ontrekken. Het blijkt voldoende te zijn de set van niet-sleutelattributen aan de eindgebruiker te tonen. Met deze set wordt de eindgebruiker in staat gesteld om eigen dimensies te definiëren die niet overeen hoeven te komen met de dimensies uit het sterschema. Verder blijkt aggregaatnavigatie een eenvoudig doch zeer effectief middel om queries te berekenen. Wel moet opgemerkt worden dat de meeste OLAP-tools nog in de kinderschoenen staan wat betreft goede query-navigatie. In combinatie met het beschreven OASI-concept is het vrij eenvoudig een query-navigatie te realiseren, alhoewel dit principe ook niet voorziet in het zelfstandig inrichten van nieuwe aggregaten op basis van queries die door gebruikers worden losgelaten op het data warehouse, daar ligt nog een belangrijk onderdeel voor ontwerpers van OLAP -tools.


- 34 -


3

versie: 26-09-02

Dimensionale data warehouse modellen en operaties

In het vorige hoofdstuk zijn de data warehouse drill-modellen en technieken voor het ontrekken van gegevens aan een data warehouse reeds globaal besproken. In dit hoofdstuk zal meer in een technisch perspectief gekeken worden naar de drill-modellen. Welke relatie hebben deze modellen bijvoorbeeld met een concreet sterschema, zijn er restricties verbonden aan het genereren van deze modellen. Ook zal er gekeken worden naar het feit of en zo ja hoe er een drill-model gegenereerd kan worden uit een sterschema. Er zal getracht worden om de eigenschappen en restricties van deze modellen te formuleren in wiskundige formules. Op deze manier wordt er een hogere mate van abstractie gecreëerd, wat het redeneren hierover makkelijker maakt. 3.1

Drill-modellen

Drilling is een algemene term voor een viertal vormen van navigatie binnen een informatiedomein. Het doel is om vanaf een zeker detailniveau meer of minder detail te laten zien van een specifiek gegevensdomein. De vier vormen van drilling die binnen data warehouses gebruikt worden: Drill-down:

meer detail tonen van een specifiek gegevensdomein door een entiteit uit een dimensietabel te tonen die specifieker is dan de huidige entiteit.

Drill-up:

minder detail tonen van een specifiek gegevensdomein door een entiteit uit een dimensietabel te tonen die minder specifiek is dan de huidige entiteit.

Drill across:

koppelen van 2 of meer feitentabellen op hetzelfde detailniveau, hetzelfde detailniveau is aanwezig wanneer verschillende feitentabellen 1 of meerdere exact dezelfde dimensies delen.

Drill around:

hetzelfde als drilling across alleen is er hier een restrictie dat de feitentabellen met hetzelfde detailniveau niet gegroepeerd zijn in een lineaire manier.

Voorbeelden van deze specifieke vormen van drilling zullen verderop in dit hoofdstuk gegeven worden. Eerst zal er in meer detail gekeken moeten worden wat een drill-model nu in technische zin inhoudt. In de volgende paragraaf zal daarom eerst bekeken worden welke bouwstenen er nu in een drill-model aanwezig zijn. Wanneer deze bouwstenen helder zijn zal er gekeken worden naar de verschillende drill-modellen. Tevens kan er met deze bouwstenen in de hand bekeken en beschreven worden of en zo ja hoe een drill-model gegenereerd kan worden uit een gegeven sterschema.


- 35 -


3.2

versie: 26-09-02

Bouwstenen van een drill-model

De bouwstenen van een drill-model geven inzicht in de wijze waarop een sterschema en een drill-model samenhangen. Er zijn eigenlijk vijf hoofdonderdelen te onderscheiden die een rol spelen bij de technische opbouw van een drill-model. Hieronder volgen deze vijf onderdelen met een korte beschrijving van hun functie: •

Vertices (χ), hiermee worden de verschillende toestanden aangegeven, van waaruit de gebruiker met een zeker detailniveau naar (een deel van) de informatie kijkt.

•

Edges (ε) tussen vertices, hiermee worden de koppelingen tussen de verschillende vertices (χ) aangegeven. Vanaf iedere vertex is er een andere view op het gegevens domein.

•

Queries (ϕ), de queries zorgen dat de informatie op gewenst detailniveau uit het onderliggende datamodel gehaald wordt.

•

Functie θ: χn → χn+1 , per edge ε is er een functie θ ∈ ι. ι is een zekere taal, in deze scriptie zal de taal SQL aangehouden worden. Op deze manier kan er van de ene naar de andere vertex worden gegaan middels een statement uit die betreffende taal ι

•

Functie ψ: χ → ϕ, deze functie zorgt ervoor dat er een koppeling ontstaat vanuit een zekere vertex χ naar een query ϕ die de informatie uit het onderliggende datamodel haalt.

Met deze bouwstenen in de hand moet het mogelijk zijn om een drill-model te beschrijven. Belangrijk is om te realiseren dat er onderscheid gemaakt kan worden tussen twee ‘werelden’ enerzijds het drill-model dat een schematische weergave is van het sterschema waaruit het betreffende data warehouse is opgebouwd. Via dit drill-model kijkt de eindgebruiker op het betreffende data warehouse. Anderzijds is het onderliggende datamodel. De gegevens uit het data warehouse liggen fysiek opgeslagen in een database structuur. Wanneer de eindgebruiker vanuit zijn visie op het drill-model bepaalde gegevens wil bekijken, zal dit op een zekere manier uit het onderliggende datamodel moeten worden gehaald. Deze koppeling tussen drill-model enerzijds en het onderliggende datamodel anderzijds wordt gerealiseerd door de functie: ψ: χ → ϕ. In figuur 27 is een schematische weergave gegeven van de bouwstenen waaruit een drill-model is opgebouwd. De bouwstenen zijn geplaatst op de plaatsen waarop ze werken. Ook hier is weer onderscheid gemaakt tussen enerzijds het drill model en anderzijds het onderliggende datamodel.


- 36 -


versie: 26-09-02

ε, θ χ

Drill-model ψ

Onderliggend datamodel ϕ

Onderliggend datamodel

Figuur 27, bouwstenen drill-model

Het drill-model bestaat uit de onderdelen zoals weergeven boven de horizontale lijn in figuur 27. ϕ is hierbij de verzameling van alle queries behorende bij dat specifieke drill-model. Deze queries werken op het onderliggende datamodel. De functie ψ zorgt ervoor dat vanaf een zekere edge (ε) een koppeling wordt gemaakt naar de verzameling queries (ϕ) die werken op het onderliggende datamodel. Middels deze queries wordt een overgang mogelijk gemaakt tussen de vertices (χ) waar de betreffende edge (ε) een verbinding tussen vormt. De manier waarop deze queries opgebouwd worden wordt later in dit hoofdstuk besproken. Om het geheel niet al te abstract te laten worden zal in de volgende paragraaf aandacht besteed worden aan een concreet voorbeeld. Uitgaande van een zeker sterschema zal er gekeken worden naar het drill-model dat daaruit voortvloeit. Tevens zal er gekeken worden naar de (on)mogelijkheden van het creëren van een drill -model uit een sterschema.


- 37 -


3.3

versie: 26-09-02

Data warehouse met bijbehorend drill-model

In figuur 28 is een schematische weergave gegeven van een data warehouse met feitentabellen en dimensietabellen. Zoals is aangeven komen hier 1 of meerdere feitentabellen (fn). Per feitentabel zijn er 1 of meerdere dimensietabellen (dx) gekoppeld aan deze feitentabel. De dimensietabellen hebben op hun beurt weer 1 of meerdere entiteiten (ey).

Data warehouse

Feitentabel f1

Dimensietabel d1

Entiteit e1

Feitentabel fn

Dimensietabel dx

Entiteit ey

Figuur 28, schematische opbouw van een data warehouse

Het beschreven data warehouse is een algemene representatie. De elementen die een rol spelen bij het genereren van een drill-model kunnen m.b.v. de gegevens uit het data warehouse beschreven worden. In het algemeen geldt het volgende voor het genereren van een drill-model uit een data warehouse schema: •

Het aantal vertices (χ) ⊆ ε * ε

•

Het aantal edges (ε) wordt bepaald door het aantal dimensietabellen, uitgaande van een schema met 1 feitentabel en 2 dimensietabellen geldt: |edges (ε)| = (((|ey.d1|–1) * 2) * |ey.d2|) + (((|ey.d2|–1) * 2) * |ey.d1|), wanneer er meer dan 2 dimensies zijn wordt het drill-model een meervoudige kubus waarbij afhankelijk ban het aantal dimensies het aantal edges met een zekere factor toeneemt

•

De functies θ en ψ worden bepaald door de entiteiten die een rol spelen in een zekere vertex. De opbouw van deze functies komt verderop nader aan de orde.


- 38 -


3.3.1

versie: 26-09-02

Drill-up, drill-down model

In dit voorbeeld zal er een concreet voorbeeld worden behandeld. Gegeven in figuur 29 is een data warehouse weergegeven middels een sterschema.

tijd dimensie

verkoop feiten

plaats dimensie

dag

dag

plaats

maand

winkel

provincie

jaar

aantal

land

omzet kosten


Het bovenstaande sterschema bestaat uit 1 feitentabel en 2 dimensietabellen. In de feitentabel zijn twee verwijzingen opgenomen naar de dimensietabellen. Tijd en plaats zijn in de feitentabel de identificerende elementen, algemeen kan worden gesteld dat tijd en plaats samen een uniek feit identificeren. In het voorbeeld is er sprake van slechts 1 feitentabel. Hierdoor is er uitsluitend een drill-up en drill-down toe te passen op dit schema. Van dit sterschema kan er een drill-model opgesteld worden. In dit drill-model wordt gespecificeerd welke drill-up en drill-down stappen er toegepast kunnen worden op dit schema. Het drill-model voor bovenstaand sterschema is weergegeven in figuur 30. DU_tijd

dag plaats

DD_tijd

DU_plaats

DU_tijd

maand plaats DU_plaats

DD_plaats

DD_tijd

DU_plaats

DU_tijd

maand provincie

DD_tijd

DD_tijd

jaar provincie DU_plaats

DD_plaats DU_tijd

dag land

DD_plaats

DU_plaats

DD_plaats

jaar plaats DU_plaats

DD_plaats DU_tijd

dag provincie

DD_tijd

DD_plaats DU_tijd

maand land

DD_tijd

jaar land

Figuur 30, resulterend drill-model


- 39 -


versie: 26-09-02

Duidelijk is geworden dat een drill-down drill-up model in principe altijd te genereren is uit een gegeven sterschema. Toch gelden ook voor het genereren van deze modellen een aantal voorwaarden. Om te kunnen bepalen of een drill-up en drill-down model gegenereerd kan worden moet er gekeken worden naar de opbouw van het sterschema. In figuur 30 is het drillup, drill-down model in 1 model opgenomen aangezie n er geldt drill-up = ¬drill-down, ze zijn elkaars tegengestelde. Wanneer er expliciet gekozen wordt voor twee afzonderlijke modellen, dus drill-up en drill-down beschreven in een apart model, kan het schema van figuur 30 beschouwd worden, echter met respectievelijk weglating van alle drill-down edges (DD) voor het drill-up model en weglating van de DU edges (DU) voor het drill -down model. Samenvattend, stel er is een data warehouse met feitentabel(len) F, dimensietabellen D en per dimensietabel entiteiten E, om een drill-up of drill-down model te kunnen genereren moet er gelden: • • •

Er moet minimaal 1 feitentabel aanwezig zijn; |F| ≥ 1 Er moet minimaal 1 dimensietabel aanwezig zijn; |D| ≥ 1 Voor alle dimensietabellen moet gelden dat ze minimaal 2 entiteiten bevatten ∀D: |D.E| ≥ 2

Als aan bovenstaande eisen wordt voldaan, dan is het mogelijk een drill-up, drill-down model te genereren. Eerder is al aangegeven dat naast deze modellen er ook zogeheten drill around en drill across modellen zijn. In de volgende paragrafen zal er aandacht worden besteed aan deze modellen. De voorwaarden voor generatie van deze modellen liggen iets ingewikkelder dan bij de beschreven drill-up en drill-down modellen.

3.3.2

Drill across model

Drill across is een andere vorm van drilling dan drill-up en drill-down. Het belangrijkste verschil zit hem in het feit dat drill-up en drill-down modellen per definitie betrekking hebben op sterschema met 1 feitentabel, terwijl drill across modellen feiten uit tenminste 2 feitentabellen combineert. In figuur 31 en figuur 32 zijn twee sterschema’s weergegeven.

tijd dimensie

verkoop feiten

dag

dag

plaats dimensie

maand

winkel

plaats

jaar

aantal

provincie

omzet

land

kosten Figuur 31, sterschema, verkoopfeiten per plaats op een zeker moment


- 40 -


tijd dimensie

versie: 26-09-02

budget feiten demografie dimensie

dag

dag

maand

naam

naam

jaar

inkomen

adres

belasting

postcode

kinderbijslag Figuur 32, sterschema, verkoopfeiten per plaats op een zeker moment

Wat opvalt in beide sterschema’s is dat ze 1 dimensietabel gemeenschappelijk hebben. Hiermee wordt bedoeld ze hebben beiden een dimensietabel die dezelfde entiteiten hebben delen, in dit geval is dit de dimensietabel tijd. Bij drill across is het mogelijk om feiten uit de feitentabellen van de verschillende sterschema’s, met minimaal 1 gemeenschappelijke dimensie, te combineren. In figuur 31 is een feitentabel gegeven die verkoopfeiten van een zeker warenhuis gegeven. In figuur 32 zijn budgetfeiten weergegeven. Hierin wordt van een zeker persoon aangegeven hoe zijn budget er in een zekere periode voor staat. Het kan interessant zijn om feiten uit feitentabellen te combineren. In ons geval zou het interessant kunnen zijn om te kijken of er een verband bestaat tussen het budget van mensen en de invloed op de omzet van een warenhuis, de verkoopfeiten. Het betreft hier een fictief voorbeeld, over de waarde van combinatie van de gegevens uit de verschillende feitentabellen wordt geen uitspraak gedaan. Het drill across model is getekend in figuur 33. DA_tijd

dag

DA_tijd

maand DA_tijd

jaar DA_tijd

Figuu r 33, drill across model opgebouwd uit de gemeenschappelijke dimensie ‘ tijd’

Alleen de gemeenschappelijke dimensie is opgenomen in het drill across model, dit omdat de andere dimensies niet gedeeld worden en derhalve niet in het gemeenschappelijke drill across model kunnen worden opgenomen. De pijlen in beide richtingen worden aangegeven als drill across (DA) dit omdat het in beide situaties een drill across betreft. Als het ware kan de pijl de linkerkant op gericht, gezien worden als een drill -down over meerdere feitentabellen tegelijk. De pijl de rechterkant op gericht kan worden gezien als een drill-up over meerdere feitentabellen. Het essentiële verschil komt naar voren in de querie die geformuleerd moet worden bij deze vorm van drilling. Daarover in het volgende hoofdstuk meer. Samenvattend, stel er zijn twee data warehouses met feitentabel F die een gemeenschappelijke dimensie D’ hebben, dimensietabellen D en per dimensietabel entiteiten E, om een drill across model te kunnen genereren moet er gelden: • • •

Er moeten minimaal 2 feitentabellen aanwezig zijn; |F| ≥ 2 Er moet minimaal 1 dimensietabel per feitentabel aanwezig zijn; ∀F: |D| ≥ 1, en iedere feitentabel moet tenminste 1 gemeenschappelijke dimensie hebben; ∀F: |D’| ≥ 1 Voor alle dimensietabellen moet gelden dat ze minimaal 2 entiteiten bevatten ∀D: |D.E| ≥ 2


- 41 -


versie: 26-09-02

Als aan deze eisen is voldaan is het mogelijk een drill across model te genereren. Dat model zal bestaan uit de gemeenschappelijke dimensies. Let wel onder een gemeenschappelijke dimensie wordt verstaan een identieke dimensie, dus evenveel entiteiten en ook nog exact dezelfde entiteiten. In het gegeven voorbeeld zou een dimensie ‘tijd’ met in de ‘ budget’ feitentabel een extra kolom ‘eeuw’ niet voldoen, er ontstaat dan een probleem doordat in de ene dimensietabel nog wel een drill across stap gemaakt kan worden, nl van de knoop [jaar] naar de knoop [eeuw], terwijl dit in de andere dimensietabel ‘tijd’ niet mogelijk is doordat deze entiteit daar ontbreekt. Aangezien de entiteiten, de zogeheten totaliseerbare kolommen[1, 14] uit de feitentabellen niet gecombineerd kunnen worden. Immers in de feitentabel ‘verkoop’ zijn simpelweg geen gegevens opgeslagen omtrent ‘aantal’ ‘omzet’ en ‘ kosten’ binnen een zekere eeuw.

3.3.3

Drill around model

Het laatste model is het drill around model. Deze vorm van drilling lijkt sterk op die van drill across. Het grootste verschil met drill across is dat bij drill around de feitentabellen met dezelfde dimensies niet gegroepeerd zijn in een lineaire manier. De structuur van de verschillende sterschema’s is hier gelijk, ze delen alle dimensies maar zijn toch opgeslagen in verschillende sterschema’s, dus hebben elk een aparte feitentabel. Om dit duidelijk te maken is in figuur 34 een voorbeeld gegeven van een sterschema wa ar een drill across op toegepast kan worden.

ziekenhuis

verzekering

patiënt

medewerker

medicijnen

Figuur 34, verschillende feitentabellen, allen gebaseerd op de patiënt

In het bovenstaande figuur is duidelijk te zien waar drill across betrekking op heeft, n.l. verschillende feitentabellen (ziekenhuis, verzekering, medewerker en medicijnen) die samen betrekking hebben op de patiënt.


- 42 -


versie: 26-09-02

Samenvattend, om een drill across toe te passen op feitentabellen F met daarbij dimensietabellen D en gemeenschappelijke dimensie D’ en per dimensietabel entiteiten E, moet er gelden: • Er moeten minimaal 2 feitentabellen aanwezig zijn; |F| ≥ 2 • Er moet minimaal 1 dimensietabel per feitentabel aanwezig zijn; ∀F: |D| ≥ 1, en alle feitentabellen moeten alle dimensies gekoppeld aan de centrale feitentabel (Fs) gemeenschappelijke hebben; ∀F: D’ ⊆ D • Deze gemeenschappelijke dimensies worden gedeeld in 1 centrale feitentabel (Fs); D’ = D ∈ Fs • Voor alle dimensietabellen moet gelden dat ze minimaal 2 entiteiten bevatten ∀D: |D.E| ≥ 2 Het drill across model wordt op dezelfde manier opgebouwd als het eerder beschreven drill around model. Het enige verschil ligt verscholen in het feit dat de gemeenschappelijke dimensies gedeeld worden in de centrale feitentabel. 3.3.4

Views bij een drill-model

Er is reeds gesproken over de zogeheten totaliseerbare kolommen uit de feitentabel. Hierbij doemt de vraag op wat de rol is van deze totaliseerbare kolommen. Ze vormen net als de onderdelen uit de dimensietabellen een onderdeel van het feit dat in het data warehouse is vastgelegd. Echter zijn ze niet te plaatsen binnen een van de dimensietabellen en zijn tevens altijd totaliseerbaar, dat wil zeggen ze vormen een waarde waarmee gerekend kan worden. In het drill-model is reeds gesproken over hetgeen er op een zeker moment in een view te zien is. Hierbij zijn deze totaliseerbare kolommen buiten beschouwing gelaten. Toch is er een samenhang tussen beiden. Immers kunnen (deelverzamelingen) van totaliseerbare kolommen nooit in hun eentje voorkomen in een zekere view. Er moet dus altijd een entiteit uit 1 of meerdere dimensietabellen aanwezig zijn alvorens deze totaliseerbare kolommen ook getoond kunnen worden in de betreffende view. Deze totaliseerbare kolommen vormen namelijk samen met de dimensie-keys uit de feitentabel een zeker feit, afzonderlijk zonder entiteiten uit de dimensietabellen zeggen deze kolommen niets. Binnen een zekere view κ en een data warehouse met dimensietabellen (D) met entiteiten (E) geldt er voor de totaliseerbare kolommen uit de feitentabel F: (F ∈ κ) ↔ (D.E ∈ κ) Een andere vraag die in het drill-model een rol speelt is; wat toont de view op het moment dat er niet uit alle dimensietabellen een entiteit is geselecteerd? In principe is het mogelijk om met weglating van entiteiten uit één of meerdere dimensietabellen een view te presenteren. Alleen is dan de vraag wat er door die betreffende view voor zinnige informatie wordt gegeven. Het antwoord hierop is dat er geen zinnige informatie wordt getoond. In een data warehouse worden feiten weergegeven wanneer er uit alle aanwezige dimensietabellen een entiteit in de betreffende view aanwezig is. Views die hier niet aan voldoen zijn dus wel mogelijk maar beschrijven dan geen uniek feit uit het betreffende data warehouse. In het algemeen geldt dus dat een view κ een feit uit het data warehouse (met n dimensietabellen en m totaliseerbare kolommen) beschrijft als: #entiteiten(κ κ\{m}) = n Kortweg gezegd. Een zekere view op een data warehouse beschrijft een feit als het aantal entiteiten in die view, met weglating van de verzameling van totaliseerbare kolommen, gelijk is aan het aantal dimensietabellen. Uit iedere dimensietabel is dus een entiteit in de betreffende view aanwezig.


- 43 -


3.3.5

versie: 26-09-02

Kortste pad en totaliseerbare kolommen

In de beschreven drill-modellen is goed te zien welke drill-operaties er in het bijbehorende drill-model toegepast kunnen worden. Het drill-model uit figuur 30 bestaat uit 9 knopen. Linksboven in dit model staat de knoop met de meest gedetailleerde informatie en rechtsonder staat de minst gedetailleerde knoop. Immers omzetten per dag per winkel zeggen meer dan omzetten per jaar per land, deze laatste groep bevat veel meer geaggregeerde feiten en is dus minder gedetailleerd. In het drill-model zijn ook paden te ontdekken. Verschillende knopen kunnen middels verschillende paden (verschillende combinaties van drill-up en drill-down) worden bereikt. Interessant is in ieder geval het kortste pad om vanaf het meest gedetailleerde niveau, linksboven, naar het gewenste detailniveau te komen. Het blijkt zo te zijn dat het kortste pad gevormd wordt door een aaneenschakeling van drill-up operaties. Hierbij zijn er verschillende paden te bewandelen die even lang zijn en in hetzelfde, gewenste, detailniveau uitkomen. In figuur 35 is de algemene structuur van een sterschema aangegeven.

dimensietabel

feitentabel

dimensietabel

entiteiten e1..en

entiteiten e1..en

entiteiten e1..en

Dn

Fn

Dn+1

dimensietabel entiteiten e1..en Dn+m Figuur 35, algemene opbouw sterschema

In het gegeven sterschema zijn er m dimensietabellen en 1 feitentabel. Iedere dimensietabel bevat n entiteiten. De minst gedetailleerde weergave bevat, de totaliseerbare kolommen e1…en uit de feitentabel buiten beschouwing gelaten, dn.e1, dn+1.e1…dn+m.e1. Waarbij e1 de meest gedetailleerde entiteit is uit de betreffende feitentabel en en de minst gedetailleerde. Om nu te kijken naar de lengte van het kortste pad, om van de ene naar de ander view te komen, moet er gekeken worden naar de afstand tussen het gewenste detailniveau en het huidige niveau.


- 44 -


versie: 26-09-02

Stel het huidige detailniveau is als volgt, uitgaande van n dimensietabellen: huidige niveau

gewenst niveau

d1.eα . . dn.eα

d1.eβ . . dn.eβ

Het kortste pad wordt nu bepaald door de afstanden per dimensie tussen huidig niveau en gewenst niveau te berekenen en dit voor alle dimensies te sommeren. KORTSTE PAD = ∑i=1….i=n(di.eβ - di.eα)

Het begrip ‘ totaliseerbare kolom’ is reeds een aantal malen gevallen, ook is er reeds een definitie gegeven van een totaliseerbare kolom. Om het begrip nog wat duidelijker te maken zal er een concreet voorbeeld behandeld worden. In figuur 36 is een schematische weergave gegeven van een data warehouse. Het data warehouse bestaat uit de feitentabel ‘verkoop’ de dimensietabellen ‘tijd’ en ‘plaats’ en verder heeft iedere dimensie een drietal entiteiten.

Data warehouse

verkoop f1

tijd d1

plaats d2

dag e1

plaats e1

maand e2

provincie e2

jaar e3

land e3

Figuur 36, schematische weergave van een data warehouse


- 45 -


versie: 26-09-02

Wat in dit schema niet is opgenomen zijn de totaliseerbare kolommen, dit zijn de entiteiten uit de feitentabel ‘verkoop’, in dit geval ‘aantal’, ‘omzet’ en ‘kosten’. Stel de entiteit maand uit de dimensietabel tijd wordt gekozen, feitelijk moet er ook een entiteit uit de dimensietabel ‘ plaats’ gekozen worden om een concreet feit weer te geven, maar om het begrip totaliseerbare kolom toe te lichten volstaat de keuze van één entiteit. Als alle totaliseerbare kolommen getoond worden in combinatie met de entiteit ‘maand’ levert dit tabel 8 op.

maand

aantal

omzet

kosten

Januari Februari Maart April Mei Juni Juli Augustus September Oktober November December

100 200 100 300 200 150 200 250 200 150 100 150

1000 2000 1000 3000 2000 1500 2000 2500 2000 1500 1000 1500

200 400 200 600 400 300 400 500 400 300 200 300

Tabel 8, view op data warehouse met totaliseerbare kolommen

Duidelijk is dat alle maanden die aanwezig zijn opgesomd worden. De kolommen aantal, omzet en kosten worden per maand uitgerekend door het systeem, ze vormen echter geen onderdeel van het drill-model, aangezien ze niet in een dimensietabel zijn opgeslagen. Een drill-up zorgt ervoor dat er een nieuwe query geformuleerd wordt. Stel er wordt een drillup geforceerd van ‘maand’ naar ‘jaar’. Het resultaat is weergegeven in figuur 37

jaar

aantal

omzet

kosten

1998 1999 2000 2001 2002 2003

300 400 350 450 350 250

3000 4000 3500 4500 3500 2500

600 800 700 900 700 500

Figuur 37, view op data warehouse na drill-up

Door de drill-up is er minder detail ontstaan in de view. Nu zijn slechts de jaren te zien terwijl eerst informatie per maand werd getoond. Hierbij valt ook de rol van de totaliseerbare kolommen op. Deze kolommen geven de informatie waar de eindgebruiker in geïnteresseerd is, vandaar dat deze ook in de feitentabel zijn opgeslagen en niet in de dimensietabellen. De waarden van deze kolommen worden simpelweg geaggregeerd net zoals de kolom ‘ jaar’.


- 46 -


3.4

versie: 26-09-02

Drill-model versus onderliggende architectuur

Een heel ander punt is de relatie tussen het drill-model en de onderliggende architectuur van het data warehouse, dit kan b.v. een RDBMS of ODBMS architectuur zijn. De vraag die hier gesteld kan worden is welke relatie er tussen het drill model en deze onderliggende architecturen bestaat. Om hier een antwoord op te geven zal er eerst gekeken moeten worden naar de relatievorm tussen beide onderdelen. De centrale vraag die hierbij gesteld kan worden is: •

Gegeven een data warehouse architectuur, kan hieruit een drill-model gegenereerd worden, en is hieruit 1 model te genereren of zijn er ook variant modellen te bedenken?.

De onderdelen die bij de generatie van een drill-model een rol spelen zijn de vertices (χ), de edges (ε), de queries (ϕ) en de functie van vertices naar queries (ψ: χ → ϕ). Het belangrijkste aspect dat bekeken wordt is de vertaling naar vertices (χ) en edges (ε) vanuit een data warehouse architectuur. In figuur 38 is aangegeven hoe een sterschema is opgebouwd. dimensietabel

feitentabel

dimensietabel

entiteiten e1..en

entiteiten e1..en

entiteiten e1..en

Dn

Fn

Dn+1

dimensietabel entiteiten e1..en Dn+m Figuur 38, algemene opbouw van een stersch ema

In dit voorbeeld is er sprake van 1 feitentabel, drill across en drill around zijn hier niet van toepassing aangezien deze vormen van drilling vereisen dat er minimaal 2 feitentabellen aanwezig zijn(|F| ≥ 2). De vertices die een rol gaan spelen in het drill-model zijn de entiteiten uit de verschillende dimensietabellen. De entiteiten uit de feitentabel worden buiten beschouwing gelaten. Deze vormen immers geen onderdeel van het drill-model, uitsluitend de entiteiten uit de dimensietabellen zijn van belang.


- 47 -


versie: 26-09-02

Laat υ de verzameling van vertices (χ) zijn die in het te genereren drill model voorkomen. Het volgende geldt dan: •

υ ⊆ E * E, waarbij E de entiteiten voorstellen, concreet geldt dat het aantal vertices gevormd wordt door het aantal entiteiten van alle aanwezige dimensietabellen te vermenigvuldigen met elkaar. Voor drill around en drill across modellen worden uiteraard alleen de entiteiten uit de gemeenschappelijke dimensietabellen meegerekend. Bij 2 dimensietabellen met 3 entiteiten per dimensietabel levert dit dan 3 * 3 = 9 vertices

De edges vormen het tweede onderdeel van het drill model. Het zijn de verbindingen tussen de verschillende vertices. Een belangrijke eigenschap die hier gebruikt moet worden is de afstand tussen een huidig en een gewenst detailniveau. Hiervan is bepaald dat deze afstand maximaal 1 mag zijn. Anders gezegd; edges mogen alleen bestaan tussen vertices waarbij de afstand tussen het huidig detailniveau en het gewenste detailniveau uit dezelfde dimensietabel 1 moet zijn. Een voorbeeld in figuur 39:

3 2 DD_dim2 DD_dim1

2 3

DU_dim1

3 3

4 3

DU_dim2

3 4

Figuur 39, drill-model met abstracte entiteiten

Er is een weergave gegeven van een deel van een data warehouse architectuur met 2 dimensietabellen. Deze dimensietabellen, dim1 en dim2, bevatten ieder 4 entiteiten. In werkelijkheid zou dit drill-model bestaan uit 4 * 4 = 16 vertices. In dit voorbeeld volstaat een deel van 4 vertices te beschouwen. Het bijbehorende data warehouse is weergegeven in figuur 40. Aan de hand van dit figuur zal een uitleg worden gegeven op welke wijze edges gekoppeld mogen worden.


- 48 -


versie: 26-09-02

dim1 dimensie

feit1 feiten

dim2 dimensie

dim1_key

dim1_key

dim2_key

entiteit 1

dim2_key

entiteit 1

entiteit 2

totaliseerbare kolommen

entiteit 2

entiteit 3

entiteit 3

entiteit 4

entiteit 4 Figuur 40, data warehouse sterschema

In het drill-model van figuur 39 is in het midden de huidige view aangegeven. Hierin staat 3 en 3. Dit betekent qua detailniveau dat er uit dimensietabel dim1 detailniveau 3 getoond wordt en uit dimensietabel dim2 detailniveau 3, dit komt overeen met ‘entiteit 3’ uit beide . Edges mogen nu gelegd worden tussen knopen die van één van beide dimensietabellen een detailniveau hoger of lager bevatten. Het resultaat is weergegeven in het gegeven drill model. Wel moet opgemerkt worden dat ‘entiteit 2‘ altijd de laatste entiteit is waarvandaan een edge aanwezig is naar een andere knoop middels een drill-down. Immers vanaf ‘entiteit 2‘ een drill-down uitvoeren levert de meest gedetailleerde entiteit n.l. ‘entiteit 1’ voor de betreffende dimensie. Vanaf ‘entiteit 1’ kan er uitsluitend een drill-up uitgevoerd worden naar een lager detailniveau. De laatste edge wordt gelegd tussen de hoogste en de op één na hoogste entiteit van de betreffende dimensietabel, in het voorbeeld dus tussen ‘entiteit 3’ en ‘entiteit 4’.

In voorgaand voorbeeld is uitgegaan van een concreet voorbeeld. Het is interessant om een beschrijving in algemene termen te maken. Hiertoe wordt een dimensietabel beschreven als een set van attributen. Hierbij zijn de volgende onderdelen gegeven: • • •

Het data warehouse met zijn feitentabel(len) en dimensietabel(len) T Dimensietabellen, D ⊆ T Per dimensietabel d ∈ D is er een aantal niveaus(de entiteiten(e ∈ E) uit de betreffende dimensietabel e ⊆ d). |d| = nd en |n| ≥ 1 omdat er altijd minstens 1 element in een dimensietabel aanwezig is, waarbij n het level van de huidige entiteit aangeeft.

Er zullen nu een aantal functies gedefinieerd worden als volgt: E deze functie geeft het volgende level uit de betreffende dimensietabel, het nextd : E betreft hier het level dat 1 level verwi jdert ligt van het huidige level. nextd ⊆ D * D ε ⊆ χ * χ en {n, m} ∈ χ De uiteindelijke formule voor het leggen van een edge ε tussen twee knopen wordt nu: ∈ ε ↔ ∈ nextd Kortweg, tussen entiteit n en entiteit m uit dimensietabel d. Mag een edge (ε) gelegd worden dan en slechts dan als de afstand tussen de levels van entiteit n en m 1 bedraagt, ze dus elkaars opvolger zijn (nextd).


- 49 -


3.5

versie: 26-09-02

Conclusie

In dit hoofdstuk is getracht een overzicht te geven van de verschillende drill-modellen. Duidelijk is geworden dat er bij het genereren van de verschillende drill-modellen ook verschillende eisen worden gesteld aan de opbouw van een data warehouse sterschema. Drill-up en drill-down modellen kunnen uit ieder sterschema gegenereerd worden terwijl drill across en drill around modellen alleen gegenereerd kunnen worden uit dimensietabellen met meer dan 1 feitentabel, waarbij er ook minimaal 1 gedeelde dimensie aanwezig moet zijn. Tevens is gebleken dat de verschillende bouwstenen van een drill-model geplaatst worden in twee werelden, enerzijds het drill-model, de view van de eindgebruiker op het data warehouse en anderzijds het onderliggende data model, waar een koppeling naar gemaakt wordt vanuit het data warehouse. Immers de gegevens uit een data warehouse liggen in dit data model opgeslagen en zullen middel queries onttrokken hieraan onttrokken moeten worden. Het laatste aspect dat belicht is, is de generatie van een drill-model, en dan in specifiek de vertices en edges, uit een data warehouse sterschema. Hier is gebleken dat er een duidelijk verband bestaat tussen het aantal vertices en het aantal aanwezige dimensietabellen. Het aantal entiteiten per dimensietabel in combinatie met het aantal dimensietabellen bepalen het aantal vertices in het te genereren drill-model. Wanneer deze losse vertices gegenereerd zijn is het van belang om deze onderling middels edges te verbinden. Middels een concreet voorbeeld is er een algemene regel afgeleid waaraan twee edges die onderling verbonden zijn met elkaar moeten voldoen. Zo blijkt dat de afstand tussen de verschillende levels van entiteiten uit de gekoppelde vertices precies 1 bedraagt voor 1 der dimensies in de specifieke vertex. Met deze gegevens in de hand wordt het mogelijk om uitgaande van een sterschema een drill-model te genereren. In de beschreven oplossing is geen onderscheid gemaakt tussen het genereren van een drill-up, drill-down, drill across en drill around model. Duidelijk is reeds geworden dat een drill-up en drill-down model altijd gegenereerd kunnen worden, maar een drill across en drill around model kan uitsluitend bij meer dan 1 feitentabel en minimaal 1 gedeelde dimensietabel worden toegepast. Om deze laatste twee modellen ook te kunnen genereren zou dan een extra controle vooraf uitgevoerd moeten worden die controleert hoeveel feitentabellen er aanwezig zijn en of er minimaal 1 gemeenschappelijke dimensietabel aanwezig is. Indien deze waarden overeenkomen met de beschreven eisen die aan een drill across of drill around model worden gesteld, kan het betreffende model vrij eenvoudig gegenereerd worden.


- 50 -


4

versie: 26-09-02

Queries behorenden bij het drill-model

In het vorige hoofdstuk is aandacht besteed aan de drill-modellen. Daarbij is het genereren van vertices(χ) en edges(ε) aan de orde geweest. In dit hoofdstuk zal er aandacht worden besteed aan de queries die horen bij de verschillende vormen van drilling. Aan de hand van concrete voorbeelden wordt getracht te komen tot algemene abstracte formules. Voordat de queries besproken worden moet er duidelijk onderscheid gemaakt worden tussen twee verschillende soorten queries. Deze twee soorten queries zijn de volgende: 1. Queries die gekoppeld zijn aan een knoop (λ), hierbij werkt de betreffende querie op de basisarchitectuur van het data warehouse, non-incrementeel, in het drill-model aangegeven met (ψ). 2. Queries die gekoppeld zijn aan een edge (ε), hierbij werkt de betreffende querie op het resultaat van de vorige knoop (λ), incrementeel, in het drill-model aangegeven met (θ). tijd dimensie

verkoop feiten

plaats dimensie

dag

dag

plaats

maand

winkel

provincie

jaar

omzet

land

kosten


In figuur 41 is een voorbeeld sterschema weergegeven dat gebruikt zal worden om de verschillende queries toe te lichten. Hierbij moet opgemerkt worden dat in dit voorbeeld de verwijzende sleutels in de feitentabel verkoop bestaan uit de meest elementaire elementen uit de aanwezige dimensietabellen. In eerste instantie zal gekeken worden naar non-incrementele queries die gekoppeld zijn aan een knoop (λ). Voor alle vormen van drilling zullen deze queries besproken worden. Steeds aan de hand van een simpel voorbeeld. Daarna zullen de incrementele queries besproken worden voor alle vormen van drilling. Alle voorbeelden zullen toegelicht worden met de bijbehorende query. Met deze voorbeelden in de hand zal getracht worden te komen tot een abstracte algebraïsche beschrijving van een query voor de betreffende vorm van drilling.


- 51 -


4.1

versie: 26-09-02

Non-incrementele queries

Deze queries zijn een gekoppeld aan een knoop(χ). Dit betekent dat de query werkt op de basisarchitectuur van het data warehouse. Onder de basisarchitectuur wordt verstaan de architectuur zoals opgeslagen in de data warehouse databases. Voordeel hiervan is dat de structuur waar de queries op werken steeds dezelfde is en van tevoren bekend is. Een mogelijk nadeel is dat er geen gebruik gemaakt kan worden van eerdere queries die zijn uitgevoerd op dezelfde data warehouse database. De eerste queries die bekeken zullen worden in de context van non-incrementele queries, zijn de queries behorende bij de drill-up en drill-down operatie uit het drill-model. 4.1.1

Non-incrementele queries bij drill-up en drill-down

In figuur 42 is een deel van het drill-model, behorende bij figuur 41, weergegeven.

DU_tijd

dag plaats

DD_tijd

maand plaats

Figuur 42, deel van drill -model

Er zal nu een drill-up worden uitgevoerd van [dag, plaats] naar [maand, plaats]. Aangezien het een non-incrementele querie betreft is het belangrijk te weten hoe de basis view eruit ziet, De basis view is de meest elementaire view, deze toont de elementen uit de verschillende dimensietabellen op het meest elementaire niveau. In dit voorbeeld is dit de view met de elementen dag en plaats. De view is hieronder weergegeven in tabel 9.

dag

plaats

omzet

kosten

5-jan-1999 5-jan-1999 12-jan-1999 12-jan-1999 15-jan-1999 15-jan-1999 18-jan-1999 18-jan-1999 05-feb-1999 05-feb-1999 18-feb-1999 18-feb-1999

H&M Nijmegen Zon Nijmegen H&M Nijmegen Zon Almere H&M Nijmegen Zon Nijmegen H&M Nijmegen H&M Almere Zon Nijmegen Zon Almere H&M Nijmegen Zon Nijmegen

1000 2000 1000 3000 2000 1500 2000 2500 2000 1500 1000 1500

200 400 200 600 400 300 400 500 400 300 200 300 Tabel 9, view bij knoop [dag, plaats]


- 52 -


versie: 26-09-02

De non-incrementele querie die wordt uitgevoerd op bovenstaande basis view, ziet er voor de drill-up van [dag, plaats] naar [maand, plaats] is hieronder weergegeven.

select from where group by

t.maand, v.plaats, SUM(v.omzet), SUM(v.kosten) tijd t, verkoop v t.dag = v.dag t.maand, v.plaats

Het resultaat van deze drill-up operatie is weergegeven in tabel 10.

maand

plaats

omzet

kosten

jan-1999 jan-1999 jan-1999 jan-1999 feb-1999 feb-1999 feb-1999

H&M Almere H&M Nijmegen Zon Almere Zon Nijmegen H&M Nijmegen Zon Almere Zon Nijmegen

2500 6000 3000 3500 1000 1500 3500

500 1200 600 700 200 300 700

Tabel 10, view bij knoop [maand, winkel]

Naast een drill-up met knopen die 1 of meerdere elementaire elementen bevatten, zoals in bovenstaand voorbeeld, zijn er ook drill-up operaties die werken op knopen die géén elementaire elementen bevatten. Een voorbeeld hiervan is de drill-up van [dag, provincie] naar [maand, provincie], weergegeven in figuur 43.

DU_tijd

dag provincie

DD_tijd

maand provincie

Figuur 43, deel van drill-model

De non-incrementele querie die wordt uitgevoerd, ziet er voor de drill-up van [dag, provincie] naar [maand, provincie] als volgt uit:


t.maand, p.provincie, SUM(v.omzet), SUM(v.kosten) plaats p, tijd t, verkoop v t.dag = v.dag AND v.plaats = p.plaats t.maand, p.provincie


- 53 -


versie: 26-09-02

Het resultaat van deze drill-up is weergegeven in tabel 11.

maand

provincie

omzet

kosten

jan-1999 jan-1999 feb-1999 feb-1999

Flevoland Gelderland Flevoland Gelderland

5500 9500 1500 4500

1100 1900 300 900

Tabel 11, view bij knoop [maand, provincie]

Een aantal observaties kan worden gemaakt bij de beschreven non -incrementele queries: •

Wanneer in de resulterende knoop (χ) elementen voorkomen die als verwijzende sleutel in de feitentabel voorkomen, hoeft er over deze betreffende sleutel niet ge-joined te worden.

•

Wanneer in de resulterende knoop (χ) geen elementen voorkomen die als verwijzende sleutel in de feitentabel voorkomen, moet er ge-joined worden over de betreffende verwijzende sleutel van de dimensietabel.

Met deze regels in handen kunnen de non-incrementele queries in algemenere termen beschreven worden. In het voorbeeld is uitgegaan van 2 dimensietabellen. In de praktijk kunnen het n dimensietabellen zijn. Een vast gegeven is dat bij een drill operatie er maar over 1 dimensie tegelijkertijd een drill kan worden uitgevoerd. Met andere woorden, bij n dimensies blijven altijd n-1 dimensies ongewijzigd. In het beschreven voorbeeld zijn er 2 dimensietabellen en uit het drill-model blijkt dat per drill-up of drill-down een van de dimensies slechts wijzigt. De drill-up beschreven in figuur 44 zou dan ook niet mogelijk zijn:

dag plaats

DU_tijd

maand provincie

Figuur 44, foutieve drill -up

De queries die besproken zijn kunnen in algemenere termen beschreven worden. De queries hebben alle betrekking op de knopen (χ) waarbij een der elementaire elementen, de verwijzende sleutels, aanwezig is. In het drill -model zijn dit de knopen met het element plaats en de knopen met het element dag erin.


- 54 -


versie: 26-09-02

De queries voor de knopen met het element dag erin zien er dan als volgt uit: X ∈{ maand, jaar} select from where group by

t.X, v.plaats, SUM(v.omzet), SUM(v.kosten) tijd t, verkoop v t.dag = v.dag t.X, v.plaats

X is een verzameling van niet sleutelattributen uit de betreffende dimensietabel. Het element dag ontbreekt hier, dit komt doordat dag het meest elementaire element uit de dimensietabel ‘ tijd’ is en derhalve al in de verwijzende sleutel is opgenomen. De meest elementaire view bevat dus automatisch het element dag. Via een querie hoeft deze dus niet meer verkregen worden. Hetzelfde kunnen gedaan worden voor de knopen met het element plaats erin. De queries voor deze knopen zien er dan als volgt uit: Y ∈{provincie, land} select from where group by

t.dag, v.Y, SUM(v.omzet), SUM(v.kosten) tijd t, verkoop v t.plaats = v.plaats t.dag, v.Y

Y is hierbij de verzameling van niet sleutelattributen van de dimensietabel ‘ plaats’. Het element plaats ontbreekt hier om dezelfde reden als hiervoor beschreven voor het element dag. Nu zijn er algemene queries gegeven voor de verschillende drill richtingen, over de verschillende dimensies. Nu kunnen de beschreven queries samengenomen worden in 1 nog algemenere querie. Hierbij gaat het niet alleen om knopen met elementaire elementen erin, maar ook om knopen met niet elementaire elementen erin. Deze querie ziet er voor het gegeven voorbeeld als volgt uit: X ∈{dag, maand, jaar}, Y ∈{plaats, provincie, land}


t.X, p.Y, SUM(v.omzet), SUM(v.kosten) plaats p, tijd t, verkoop v t.dag = v.dag AND p.plaats = v.plaats t.X, p.Y

De verzamelingen X en Y bevatten respectievelijk de elementen uit de dimensietabellen ‘ tijd‘ en ‘ plaats’. Merk op dat de meest elementaire elementen dag en plaats, nu wel in de verzamelingen zijn opgenomen. Dit is gedaan om deze querie in algemene termen te kunnen beschrijven. Het enige verschil is dat bij de selectie van deze elementaire elementen, het resultaat alsnog berekend wordt, dit had eigenlijk niet gehoeven aangezien ze als verwijzende sleutel opgenomen zijn in de feitentabel ‘verkoop’.


- 55 -


versie: 26-09-02

Het beschreven voorbeeld uit figuur 41 heeft betrekking op 1 feitentabel met 2 dimensietabellen. In de praktijk kunnen er meerdere dimensietabellen aanwezig zijn. Stel er is de volgende uitgangspositie;’ waarbij T de verzameling tabellen (feitentabel(len) F en dimensietabel(len) D) met E entiteiten, waarbij D een partitie van E is: • • • • •

1 feitentabel, F ∈ T n dimensietabellen, D ⊆ T, |D| = n per dimensietabel d ∈ D, entiteiten (e ∈ E) met 1 meest elementaire entiteit (de verwijzende sleutel) focus(f) per dimensietabel, een specifieke entiteit uit een dimensietabel die getoond dient te worden p totaliseerbare kolommen F’ in feitentabel F, dus F’ ⊆ F met |F’| = p

Om het geheel wat duidelijker te maken zal voor het data warehouse sterschema uit figuur 41 een invulling gegeven worden van bovenstaande onderdelen. Deze zien er als volgt uit:

T = {tijd, verkoop, plaats} F = {verkoop} F’ = {omzet, kosten} D = {tijd, plaats} E = {dag, maand, jaar, stad, provincie, land}

Er worden nu twee functies gedefinieerd, El() en Foc(), deze functies geven respectievelijk het meest elementaire element uit een dimensietabel(d) terug en het element waar de focus op ligt(dat een element is in de gewenste view). El : D

tevens geldt: El(d) ∈ d

E

Foc : D

E

tevens geldt: Foc(d) ∈ d

Met de geven functies kunnen nu twee andere definities geven worden, te weten: Dim = {Foc(x)| x ∈D} Sums = {Sum(k) | k ∈F’} Dim is hierbij de gewenste entiteit uit de dimensietabel, die geleverd wordt door de eerder gedefinieerde functie Foc(). Stel er wordt een drill-up uitgevoerd van [dag, plaats] naar [maand, plaats]. Dim wordt hierbij gerepresenteerd door maand en plaats. Sums zijn de verschillende entiteiten uit de feitentabel F die de gebruiker in zijn view wil zien, dit zijn de totaliseerbare kolommen. De algemene querie ziet er dan als volgt uit: select from where group by

Dim, Sums D x1.e1 = F.e1 AND … AND xn.en = F.en Dim


- 56 -


4.1.2

versie: 26-09-02

Non-incrementele queries bij drill across

Om te kunnen bepalen hoe de non-incrementele queries bij een drill across operatie eruit zien, is het van belang de eigenschappen van deze vorm van drilling te bekijken. Wat opvalt is dat er hier, in tegenstelling tot drill-up en drill-down, sprake is van minimaal 2 feitentabellen die bovendien minimaal 1 gemeenschappelijke (identieke) dimensie delen. In figuur 45 zijn twee sterschema’s weergegeven, ze delen de identieke dimensie Tijd.

tijd dimensie

verkoop feiten

plaats dimensie

dag

dag

plaats

maand

winkel

provincie

jaar

omzet

land

kosten

tijd dimensie

werknemer feiten

demografie dimensie

dag

dag

naam

maand

winkel

adres

jaar

salaris

woonplaats

gewerkte uren Figuur 45, sterschema’s met identieke dimensie ‘ tijd’

Middels een drill across kan via de dimensie ‘tijd’ een combinatie worden gemaakt van de feiten uit de feitentabellen ‘verkoop’ en ‘werknemer’. De algemene querie die hiervoor gebruikt kan worden is dezelfde als de algemene querie voor drill-up en drill-down, met de voorwaarde dat de dimensietabel waarmee de feitentabellen gecombineerd worden (de gemeenschappelijke dimensie) identiek is. Om te bepalen of twee dimensietabellen identiek zijn wordt er en functie Ident() gedefinieerd. Deze functie bepaald van twee dimensietabellen of ze identieke entiteiten bevatten wordt gebruik gemaakt van de functie nextd() uit paragraaf 3.4, waarmee bepaald kan worden of twee entiteiten uit een dimensietabel elkaars opvolger zijn. De functie Ident() ziet er dan als volgt uit: Ident() : D

Boolean


- 57 -


versie: 26-09-02

Als invoer worden twee dimensietabellen meegegeven. Dan ziet de procedure er in pseudocode als volgt uit: If El(d1) = El(d2) do begin next1 = nextd(d1) next2 = nextd(d2) while ((next1 = next2) ≠ ∅) do begin next1 = nextd(d1) next2 = nextd(d2) end if ((next1 = next2) = ∅) return True else return False else return False

// Elementaire elementen gelijk?

// Loop alle entiteiten af en vergelijk ze // Zoek de opvolgende entiteit in beide tabellen

// Laatste bereikt en alle twee de lege entiteit // dan beide identiteiten identiek // anders niet // elementaire entiteiten niet identiek dan dimensie // ook niet

end

De algemene querie ziet er dan als volgt uit: select from where group by

Dim, Sums D (x1.e1 = F.e1 AND … AND xn.en = F.en) AND (Ident(D)) Dim

De enige extra beperking die toegepast wordt is dus het feit dat de dimensie waarlangs de feitentabellen gecombineerd worden identiek zijn. Dit klopt ook, aangezien drill across operaties eigenlijk niets anders zijn dan drill-up of drill-down operaties over meerdere feitentabellen. In de praktijk is het belangrijk goed te letten op de echte identiekheid van de dimensietabellen. De volgorde waarin entiteiten in een dimensietabel voorkomen speelt ook een rol. Het volstaat dus niet om alleen maar te controleren of beide verzamelingen van entiteiten uit de kandidaat identieke dimensietabellen overeenkomen. Dit probleem is in de functie Ident() opgelost, doordat gebruik gemaakt wordt van de functie El() en nextd() deze houden rekening met de volgorde waarin entiteiten in een dimensietabel voorkomen. In de praktijk worden deze dimensietabellen vaak fysiek als 1 tabel opgeslagen, vanuit de feitentabellen is er dan een verwijzing naar deze ene tabel.


- 58 -


4.1.3

versie: 26-09-02

Non-incrementele queries bij drill around

Drilling around is de laatste vorm van drilling die besproken zal worden. Net als bij drilling across is hier ook sprake van minimaal 2 feitentabellen die minimaal 1 gemeenschappelijke (identieke) dimensie delen. Hert enige verschil is dat de feitentabellen die gemeenschappelijke dimensies delen niet gegroepeerd zijn in een lineaire manier. Bij drilling across zijn de verschillende feitentabellen gegroepeerd in een vaste volgorde, dat wil zeggen de ene feitentabel staat voor een andere feitentabel. Een goed voorbeeld is een feitentabel van verkopen van producten, de feitentabel met de voorraden van producten volgt hier achter. Bij drilling around is er geen volgorde, in figuur 34 van paragraaf 3.3.3 is reeds een voorbeeld gegeven. In dit sterschema staat de patiënt centraal alle feitentabellen met gemeenschappelijke dimensies hebben weer betrekking op die patiënt. Voordeel van vorm van drilling is dat er zeer krachtige en snelle queries geformuleerd kunnen worden op deze structuur. Op de verschillende feitentabellen worden dezelfde queries geformuleerd en middels een zogeheten outer-join worden deze gegevens gecombineerd en gegroepeerd. De algemene querie ziet er derhalve als volgt uit: select from where group by

Dim, Sums D (x1.e1 = F.e1 AND … AND xn.en = F.en) AND (Ident(D)) Dim

In bovenstaande query komt ook weer de functie Ident() voor. Deze volstaat inderdaad, ondanks dat nu gecontroleerd moet worden of alle dimensietabellen in de centrale feitentabel (Fs) gedeeld worden in alle gekoppelde feitentabellen. Hierbij kan eenvoudig van alle aanwezige dimensies in de centrale feitentabel (de veronderstelde gemeenschappelijke dimensies (D’)) nagegaan worden of ze voorkomen in de gekoppelde feitentabel(len). Dit voor alle veronderstelde gemeenschappelijke dimensies (D’) gedaan hebbende is te bepalen of er een drill around mogelijk is, hierbij is dan bepaald of voldaan is aan de eis dat alle gemeenschappelijke dimensies (D’) worden gedeeld in de centrale feitentabel (Fs). Drill around en drill across verschillen dus niet zo heel veel van elkaar, het essentiële verschil is dus feitelijk alleen de volgorde van feitentabellen, bij drill across is deze lineair bij drill around juist niet. Bovendien geldt bij drill around als extra eis dat de gemeenschappelijke dimensies gedeeld worden in een centrale feitentabel Fs, terwijl drill across deze eis niet stelt. Ook voor drill around geldt dat het eigenlijk weer drill-up en drill-down operaties zijn, maar dan uitgevoerd over meerdere feitentabellen. Deze eigenschap zal van belang zijn in de volgende paragraaf waar de incrementele queries behandeld zullen worden. Ook daar zal per vorm van drilling worden aangegeven hoe de betreffende incrementele querie er voor die specifieke vorm van drilling uitziet.


- 59 -


4.2

versie: 26-09-02

Incrementele queries

De beschreven non-incrementele queries werken steeds op de oorspronkelijke set van gegevens, dit is de set die bestaat uit de verzameling meest elementaire entiteiten uit een dimensietabel. Hierbij wordt het hoogste detailniveau weergegeven in de view. Toch kan er op een efficiëntere manier met de queries gewerkt worden. Hierbij werken de queries niet steeds op een view die bestaat uit de meest elementaire entiteiten, en dus ook de meeste rijen, maar op de entiteiten aanwezig uit de laatst berekende view. Voordeel is hierbij dat het aantal joins dat uitgevoerd moet worden gemiddeld een stuk lager is dan bij de nonincrementele queries. 4.2.1

Incrementele queries bij drill-up en drill-down

De bovenstaande queries werken allen non-incrementeel, ze werken dus steeds op dezelfde oorspronkelijke set van gegevens. Het is erg interessant om dezelfde queries nu incrementeel te definiëren. Het grootste verschil met non-incrementele queries is dat incrementele queries niet steeds werken op dezelfde oorspronkelijke set van gegevens, maar op het resultaat van een voorgaande querie. Om het geheel wat duidelijker te maken is in figuur 46 een voorbeeld gegeven van een drillup. Hierbij wordt er eerst gekeken hoe deze middels incrementele queries verwezenlijkt zou worden. Daarna zal er een vergelijking worden gemaakt met dezelfde querie, echter nu nonincrementeel gedefinieerd. Het gaat om de drill-up van [maand, stad] naar [jaar, stad], merk op dat hier geen elementaire elementen (verwijzende sleutels) in voorkomen. Dit is bewust gekozen om het verschil tussen incrementele en non-incrementele queries goed uit te leggen.

DU_tijd

maand stad

DD_tijd

jaar stad

Figuur 46, deel van een drill-model

Na de drill-up moet de view informatie verschaffen over de knoop [jaar, stad]. Aangezien het een non-incrementele querie betreft zal deze werken op het resultaat van de knoop [maand, stad]. Belangrijk om op te merken is dat het resultaat van de knoop [maand, stad] ook incrementeel is verkregen. Nu zijn er verschillende paden mogelijk in het drill-model van figuur 30 om van de elementaire knoop [dag, winkel] naar de knoop [maand, stad] te komen. In dit voorbeeld wordt uitgegaan van het pad [[dag, winkel], [dag, stad], [maand, stad]]. Stel de inhoud van deze drie knopen ziet er uit als weergegeven in tabel 12, 13 en 14:


- 60 -


versie: 26-09-02

dag

winkel

omzet

kosten

5-jan-1999 5-jan-1999 12-jan-1999 12-jan-1999 15-jan-1999 15-jan-1999 18-jan-1999 18-jan-1999 05-feb-1999 05-feb-1999 18-feb-1999 18-feb-1999

H&M Nijmegen Zon Nijmegen H&M Nijmegen Zon Almere H&M Nijmegen Zon Nijmegen H&M Nijmegen H&M Almere Zon Nijmegen Zon Almere H&M Nijmegen Zon Nijmegen

1000 2000 1000 3000 2000 1500 2000 2500 2000 1500 1000 1500

200 400 200 600 400 300 400 500 400 300 200 300

Tabel 12, view bij knoop [dag, winkel]

dag

stad

omzet

kosten

5-jan-1999 12-jan-1999 12-jan-1999 15-jan-1999 18-jan-1999 18-jan-1999 05-feb-1999 05-feb-1999 18-feb-1999

Nijmegen Almere Nijmegen Nijmegen Almere Nijmegen Almere Nijmegen Nijmegen

3000 3000 1000 3500 2500 2000 1500 2000 2500

600 600 200 700 500 400 300 400 500

Tabel 13, view bij knoop [dag, stad]

maand

stad

omzet

kosten

jan-1999 jan-1999 feb-1999 feb-1999

Almere Nijmegen Almere Nijmegen

5500 9500 1500 4500

1100 1900 300 900

Tabel 14, view bij knoop [maand, stad]


- 61 -


versie: 26-09-02

Om nu naar de resulterende knoop [jaar, stad] te komen middels een incrementele querie, die in dit geval werkt op de knoop [ maand, stad], hoeft er slechts ge-joined te worden over 8 rijen (4 rijen uit de kolom maand en 4 rijen uit de kolom stad) uit de knoop [maand, stad]. Het resultaat is weergegeven in tabel 15.

jaar

stad

omzet

kosten

1999 1999

Almere Nijmegen

7000 14000

1400 2800

Tabel 15, view bij knoop [jaar, stad]

Stel dat dezelfde drill-up was uitgevoerd met behulp van een non-incrementele querie. Deze querie had dan gewerkt op de oorspronkelijke set van gegevens, in dit geval dus de gegevens uit de knoop [dag, winkel]. Het aantal rijen dat dan ge-joined moet worden is 12 (12 rijen uit de kolom dag uit de kolom winkel hoeft er niet ge-joined te worden omdat deze kolom als verwijzende sleutel reeds aanwezig is in de feitentabel). De besparing (δ) in dit voorbeeld door incrementele queries te gebruiken in plaats van non-incrementele queries, is nu als volgt te berekenen: δ = 100% - (( |joins incrementeel| / |joins non-incrementeel| ) * 100%) Voor het geven voorbeeld komt dit neer op: δ = 100% - ((8 / 12) * 100%) δ = 33,3%

Het is natuurlijk erg mooi dat er door gebruikmaking van incrementele queries rekentijd gespaard kan worden. Het probleem is dat deze besparing niet gerealiseerd kan worden bij drill-up operaties. Het probleem ontstaat doordat een drill-up operatie werkt van een view met veel detail naar een view met minder detail. Doordat de resulterende view (na de drill-up), meer detail bevat moet er als het waren een decompositie plaatsvinden. Deze decompositie, het uitsplitsen van een zekere kolom in meer detail, is niet mogelijk wanneer er gebruik gemaakt wordt van entiteiten uit de laatst berekende view. Een goed voorbeeld is een drilldown operatie van de knoop [maand, stad] naar de knoop [dag, stad], aangegeven in respectievelijk tabel 14 en tabel 13 van paragraaf 4.2.1. Met de gegevens uit tabel 14, in specifiek de kolom maand is het niet mogelijk om middels een incrementele query op deze tabel naar de resulterende tabel 13 te gaan. Hier is goed te zien dat de kolom maand uit tabel 14 op een hoger niveau geaggregeerd is dan de kolom dag uit tabel 13. Bij drill-down operaties zal de toevlucht moeten worden gezocht tot een non-incrementele query.


- 62 -


4.2.2

versie: 26-09-02

Incrementele queries bij drill across en drill around

De laatste twee vormen van drilling die besproken worden zijn drill across en drill around. Aangezien deze vormen van drilling feitelijk ook niet anders zijn dan drill-up en drill-down operaties over meerdere feitentabellen, kan de kanttekening dat drill-down operaties uitsluitend non-incrementeel werken, ook hier toegepast worden. Dus ook drill across en drill around operaties kunnen uitsluitend incrementele queries gebruiken bij drill-up operaties.

jaar

stad

omzet

1999 1999

Almere Nijmegen

7000 14000

Tabel 16, view bij knoop [jaar, stad], uit feitentabel ‘ verkoop’

jaar

stad

werknemers

1999 1999

Almere Nijmegen

5085 8080

Tabel 17, view bij knoop [jaar, stad], uit feitentabel ‘ personeelskosten’

jaar

stad

omzet

werknemers

1999 1999

Almere Nijmegen

7000 14000

5085 8080

Tabel 18, view bij knoop [jaar, stad], aggregatie over feitentabellen ‘ verkoop’ en ‘ demografie’

In tabel 16, 17 en 18 is een voorbeeld gegeven van een drill-up operatie binnen een drill across of drill around operatie. Tabel 16 geeft informatie over verkopen in een zekere stad in een zekere periode en de bijbehorende omzetten. Tabel 17 geeft informatie over het aantal werknemers dat werkzaam was in hetzelfde jaar en in dezelfde stad als uit tabel 16. Een drill up operatie over deze twee feitentabellen levert tabel 18, hier zijn over de gedeelde dimensies ‘tijd’ en ‘ plaats’ waar respectievelijk ‘ jaar’ en ‘ stad’ deel van uitmaken. In dit voorbeeld zijn de kolommen ‘ jaar’ en ‘ stad’ beiden van hetzelfde detailniveau. Natuurlijk had één van beide tabellen of allebei ook een lager detailniveau mogen hebben, b.v. [dag, stad] (zie dimensie ‘tijd’ in figuur 45. Om dan tot het resultaat uit tabel 18 te komen had er een extra aggregatie binnen de feitentabel met het lagere detailniveau moeten plaatsvinden.


- 63 -


4.3

versie: 26-09-02

Conclusie

In dit hoofdstuk is aandacht besteed aan de queries die behoren bij de verschillende drillmodellen. Hierbij kan onderscheid worden gemaakt tussen twee soorten queries, enerzijds non-incrementele queries en anderzijds incrementele queries. Voordeel van incrementele queries t.o.v. de non-incrementele queries: •

Incrementele queries werken over het algemeen sneller doordat gebruik wordt gemaakt van reeds eerder berekende views, non-incrementele queries werken steeds op de meest elementaire view, die heeft per definitie meer rijen waardoor er meer joins en aggregaties moeten plaatsvinden.

Voordeel van non-incrementele queries t.o.v. de incrementele queries: •

Non-incrementele queries kunnen altijd worden toegepast, ondanks dat ze veelal langzamer werken. Bij incrementele moet namelijk eerst gecontroleerd worden of het een querie betreft die werkt van een hoog naar een lagergelegen detailniveau, is dit het geval dan kan de querie worden uitgevoerd anders moet alsnog de toevlucht worden genomen tot een non-incrementele querie.

De te gebruiken queries zijn beschreven middels abstracte algebraïsche definities. Hieruit is duidelijk geworden dat de werking van de queries qua opbouw een structuur hebben die algemeen toepasbaar is voor verschillende data warehouses. Als nadeel mag zeker worden genoemd dat bij de verschillende vormen van drilling alleen de drill-up variant zelf en de drillup operaties binnen drill across en drill around operaties gebruik kunnen maken van de incrementele queries, omdat deze per definitie werken van een hoog naar een lager detailniveau. De drill-down operaties zullen middels non-incrementele queries bewerkstelligd moeten worden.


- 64 -


5

versie: 26-09-02

Conclusies

In de inleiding van deze scriptie is de probleemstelling aan de orde gesteld. Middels een vraag en een daarbij behorende subvraag is getracht de kern van het onderzoek dat in deze scriptie is beschreven helder te maken. Hieronder volgen deze vraag en subvraag nogmaals. De centrale vraag luidt: “Zijn data warehouses, in specifiek dimensionaal gemodelleerde, een innovatie m.b.t. gegevensopslag en gegevensmodellering?” De subvraag luidt: “Zijn de technieken voor gegevensopslag en gegevensmodellering, die binnen data warehouses gebruikt worden, te realiseren m.b.v. bestaande/traditionele technieken voor gegevensopslag en gegevensmodellering?” Als eerste zal gekeken worden naar de bovenstaande subvraag. In deze scriptie is in specifiek gekeken naar dimensionaal gemodelleerde data warehouses middels sterschema’s. Duidelijk is geworden dat deze sterschema’s een puur hulpmiddel zijn om de gegevens die in een data warehouse opgeslagen zijn te kunnen representeren. In het eerste hoofdstuk zijn ook andere vormen van modelleren besproken, de sneeuwvlokschema’s en de data cubes. De sterschema’s die bij het dimensionale modelleren de boventoon voeren zijn puur ontworpen om te voldoen aan de eigenschappen van een data warehouse. Bij deze eigenschappen is het centrale punt dat er geen update operaties plaatsvinden in een data warehouse en dat de gegevens uit een data warehouse veelal gebruikt worden door managers die snel een overzicht willen zien van bepaalde feiten. Het ontwerpen van een data warehouse is ook compleet gericht op deze eigenschappen. Zo wordt redundantie toegestaan middels sterschema’s en worden dimensietabellen en feitentabellen geïntroduceerd omdat ze erg goed aansluiten bij de belevingswereld van de gebruiker. Wat betreft sterschema’s kan geconcludeerd worden: •

Puur een manier van representeren, kan ook middels andere bestaande modellen zoals sneeuwvlokschema’s en data cubes.

•

Sterk gericht op de specifieke eigenschappen van een data warehouse, zoals niet normaliseren en dus toestaan van redundantie en het gebruik van dimensies omdat deze erg aansluiten bij de belevingswereld van een gebruiker.

De technieken die gebruikt worden binnen data warehouses zijn ook nadrukkelijk besproken. Er is getracht een overzicht te geven van de drill-modellen die voortvloeien uit de sterschema’s. De bouwstenen van zo’n drill-model blijken te bestaan uit elementen, zoals deze gebruikt worden in andere (traditionele) systemen. Hierbij moet duidelijk onderscheid gemaakt worden tussen het drill-model, dat sterk gerelateerd is aan de structuur van het sterschema en het onderliggend datamodel. Wat betreft de drill-modellen kan geconcludeerd worden: •

Sterk gerelateerd aan structuur van een sterschema.

•

Drill-model zelf bestaat uit bouwstenen, deze zijn niet echt nieuw en worden ook in andere modellen toegepast


- 65 -


versie: 26-09-02

Het laatste punt waar aandacht aan besteed is, zijn de queries die horen bij het drill-model. Het blijkt dat er bij de drill-modellen queries horen die, wanneer de structuur van een sterschema gegeven is alsmede de gewenste entiteiten uit de view waar een drill operatie naar uitgevoerd wordt en de totaliseerbare kolommen uit de feitentabel(len), gegenereerd kunnen worden. In deze scriptie is de taal SQL aangehouden. Er zijn voor de verschillende queries abstracte definities gegeven, hieruit blijkt dat voor de verschillende vormen van drilling (up, down, across en around) met een aantal simpele uitbreidingen allemaal gerealiseerd kunnen worden, er kunnen dus voor alle vormen van drilling queries gegenereerd worden. Wat betreft de queries kan geconcludeerd worden: •

Als de structuur van een sterschema, de entiteiten van de dimensietabellen en de totaliseerbare kolommen uit de feitentabel(len) die een rol spelen in de view waar een drill operatie naar uitgevoerd wordt bekend zijn, kunnen de queries bij het drill-model gegenereerd worden

•

Met een aantal simpele uitbreidingen kunnen voor alle vormen van drilling queries gegenereerd worden.

Sterschema’s, drill-modellen en queries bij de drill-modellen hebben allen betrekking op de representatie van gegevens uit een data warehouse. Er is ook nog een onderliggende architectuur waar de gegevens uit een data warehouse fysiek opgeslagen worden. Kijkend naar deze onderliggende architectuur valt op dat hier traditionele systemen voor gegevensopslag gebruikt worden, zoals o.a. RDBMS en ODBMS. De sterschema’s en drillmodellen met bijbehorende queries zijn dus een interface tussen de onderliggende architectuur en de eindgebruiker. Het blijkt dat m.b.v. bestaande/traditionele technieken de gegevensmodellering en gegevensopslag binnen een data warehouse te realiseren is. Modellering met sterschema’s maar ook met b.v. traditionele sneeuwvlokschema’s. De opslag van gegevens gebeurt met traditionele systemen. Onttrekking van gegevens is te realiseren met queries uit een taal als b.v. SQL. Met deze feiten is de subvraag uit de probleemstelling beantwoord. Het antwoord op de hoofdvraag is wat lastiger. In het an twoord op de subvraag is al duidelijk geworden dat de gebruikte technieken voor modellering en opslag van gegevens vrij traditioneel zijn. Van een innovatie op deze punten is dan ook niet te spreken, toch vormen de gebruikte technieken wel degelijk een uitdaging. Het gaat hier dan specifiek om de wijze waarop technieken worden toegepast. Dit wordt bepaald door een aantal belangrijke eigenschappen van een data warehouse, zoals: •

Gebruik van dimensies, dimensionaal denken en modelleren

•

Modelleren met redundantie, doordat in data warehouses geen update operaties plaatsvinden

•

Typische OLAP omgeving waarin informatie middels een extractie uit het operationele (OLTP) systeem gehaald wordt


- 66 -


versie: 26-09-02

Door deze eigenschappen moet er op een speciale manier, gericht op de specifieke eigenschappen van een data warehouse, omgegaan worden met bestaande technieken. Deze speciale manier van omgaan maakt dat juist hier grote uitdagingen liggen. In deze scriptie zijn al een aantal ‘ problemen’ behandeld, waar nog geen echt goede oplossingen voor gevonden zijn, zoals historie, inflexibiliteit van sterschema’s. Daarbij zijn extractietools, die gegevens uit het OLTP systeem in het data warehouse (OLAP) plaatsen, erg mager op dit moment. Voor de toekomst zullen op deze punten grote uitdagingen liggen.


- 67 -


versie: 26-09-02

Bibliografie [1]

Kimball, R., The Data Warehouse Toolkit, Practical Techniques for Building Dimensional Data Warehouses, New York, 1996

[2]

van der Lek, H., Extractie kan goedkoop en handig, Database Magazine, januari 1998

[3]

van der Lek, H., Wanneer een ster de job doet, Database Magazine, maart 1998

[4]

van der Lek, H., Dimensies in soorten en maten, Database Magazine, mei 1998

[5]

van der Lek, H., Historie in de sterren, Database Magazine, juli 1998

[6]

van der Lek, H., De geboorte van een ster, Database Magazine, september 1998

[7]

van der Lek, H., Het pletten van een ster, Database Magazine, oktober 1998

[8]

van der Lek, H., Aggregaten, de planeten bij een ster, Database Magazine, november 1998

[9]

van der Lek, H., Note on the structure of joins, Bureau For Systems Development Baarn, 1997

[10]

Kelly, T. J., Dimensional Data Modelling, Sybase Professional Services. http://www.gca.net/solutions/sybase/syb_dim_data_mod.html

[11]

Labio, W. J., Quass, D., Adelberg, B., Physical Database Design for Data Warehouses, Stanford University Department of Computer Science, 1996

[12]

Cui, Y., Widom, J., Wiener, J. L., Tracing the Lineage of View Data in a Warehousing Environment, Stanford University Department of Computer Science, 1998

[13]

Harinarayan, V., Rajaraman, A., Ullman, J. D., Implementing Data Cubes Efficiently, Stanford University Department of Computer Science, 1995

[14]

Kimball, R., Drilling Down, Up, Across and Around, Understanding the vocabulary of navigating dimensions, DBMS, 1996


- 68 -

VOORWOORD INLEIDING...5

Recommend Documents