Datawarehousing voor een set van standaard KPI's

[jordan2k]

Heren en Dames van het goede leven,

Ik ben hier al een een paar dagen over aan het nadenken en zit een beetje vast.
ik ben in Mn werk verantwoordelijk voor een groot aantal rapportages over omzet afzet etc etc.
nu zijn we recent overgestapt naar een nieuw erp systeem en hebben we gekozen voor een Temp omgeving voor rapportages ( niet de beste keuze ) omdat de Temp omgeving niet geoptimaliseerd is voor query's te draaien.
nu wil ik voor mezelf tot ze bij ICT klaar zijn met een datawarehouse project voor me zelf al een tijdelijke DWH opzetten. heb er de capaciteit voor en de vrijheid om een Development omgeving op te zetten, zolang het MT maar iedere week zn cijfertjes krijgt.
omdat het om datasets gaat van ongeveer 4,5 miljoen records per jaar en er in een gemiddelde rapportage 3 jaar zit ivm trend analyse vroeg ik me af wat het beste is.

op dit moment heb ik een reeks Access databases die in het weekend dmv een scheduler uit die temp omgeving de data haalt en daarna een reeks rapporten draait. je snapt het al die databases die zijn na een half jaar al te groot geworden

dus alternatief heb ik een SQL database draaien, waar ik dan maar incremental iedere dag of weekend data in wil bijwerken. alleen is de vraag is het verstandig om alle data over te zetten of alleen die data die interessant is om te meten ? ( dus handig is voor KPI's )

wie heeft hier wel vaker een DWH op gezet en heb je dan alles maar over gezet of alleen die data die interessant is voor KPI's

[Freelancer]

Alleen de data die je nodig hebt,.. i.v.m. performance/doorlooptijd van de run

[ Voor 7% gewijzigd door Freelancer op 21-05-2008 22:49 ]

[Fish]

Het is een database .. daar query je op je maakt dus een selectie wat dus zowiezo al inhoud dat er meer inzit dan je gebruikt

ik ben van het type alles bewaren ... soms heb je het domweg nodig. maar jij bent de enige die dat in kan schatten voor jezelf/bedrijf. informeer desnoods bij het mt of ze overwegen andere kpi's te gebruiken.

[Motrax]

Het ligt er echt volkomen aan hoeveel van je data waar je op zou kunnen meten beschikbaar blijft in het ERP systeem. Als alle data in het ERP systeem blijft, dan zou ik alleen de relevante data overzetten en niets meer.

Dit hangt ook samen op welk niveau je de dimensies/fact tabellen opbouwt. Inkooporder niveau, of toch op invoice en goods receipt? Doorlooptijd per week of doorlooptijd per order? Aan te raden is zolang de performance niet slecht is een laag niveau te kiezen, zodat wanneer eisen/wensen veranderen aan de rapportage je niet je gehele DWH overnieuw hoeft te bouwen/laden.

Wanneer performance een probleem is, kan je er over denken om data voor te bewerken in een ETL laag voordat het in je DWH gaat. Bijvoorbeeld extractie op order niveau om de doorlooptijd per week in je ETL laag te berekenen en vervolgens alleen de doorlooptijd per week in je DWH te laden. En dan pas je dimensies bij te werken op basis van de historische data in je DWH en de net recent geladen data.

Een andere afweging is hoe de archivering in je bronsysteem is. Rapportage van drie jaar, maar wat als het ERP systeem maar twee jaar bewaart? Of als het vijf jaar is, en er wordt plotseling om een rapportage van 6 jaar gevraagd?

4.5 miljoen records is niks, tenzij je SQL database op een zwakke box draait, je queries qua performance beroerd zijn en je indexen zoek zijn. Ik zou voor meer data gaan dan je per direct voor je KPI nodig hebt.

Wanneer omvang van de database en extractietijden de spuigaten uitloopt, dan wordt het tijd om juist met zo min mogelijk zoveel mogelijk te rapporteren. Tot die tijd kan je je meer bezig houden met de kwaliteit van de rapportage.

Natuurlijk wel binnen enkele grenzen. Zorg als vuistregel er altijd voor dat je incrementeel je data verwerkt, nooit volledige extracties. Ik heb al zat situaties gezien waar men begon met volledige extracties, de data (nieuw en historisch in DWH) toenam en men met de grootste moeite naar incrementeel toeging. Vanaf het begin incrementeel scheelt zoveel moeite later. Wat als je tijdelijke oplossing ineens nog eens twee jaar extra gebruikt wordt?

[ Voor 22% gewijzigd door Motrax op 21-05-2008 23:01 ]

[Gomez12]

Alleen de data die je nodig hebt in de kleinste vorm aanbieden aan het dwh. Je dwh denormaliseert/totaliseert het wel weer om een goede performance te krijgen.

Maar 1 tip als je zelf een dwh wilt opzetten. Vergeet alle normalisatie regels, sommige data ga je waarschijnlijk 10x redundant opslaan in 10 verschillende vormen.
Verzin hier dus controle middelen voor ( als een dag omzet verkeerd is dan is waarschijnlijk de maandomzet ook verkeerd en de jaaromzet ook ), maar onthoud ook heel goed dat bij een beetje dwh het bijna ondoenlijk is om een jaaromzet opnieuw te calculeren.
Dus een dagomzet bereken je niet om 12:01, een weekomzet bereken je niet op een zondag om 16:00 in een batchrun. Je berekent je totalen pas als je alle gegevens binnen hebt. Bereken je het op statische tijdstippen dan krijg je problemen als 1 machine om de een of andere reden zijn gegevens niet heeft aangeleverd. En doorwerkende fouten zijn bij een dwh fataal.

En denk ook goed na over de KPI's, denk ook na wat deze KPI's over 6 maanden kunnen zijn, in wezen wil je aan een dwh zo weinig mogelijk data voeren ( hij produceert zelf al genoeg data ) maar je wilt ook niet over 6 maanden eventjes een tabelletje toevoegen omdat dit kan betekenen dat je een heel groot gedeelte opnieuw kan gaan berekenen.

Wat ik ook altijd geleerd heb met dwh's is om de data zo plat mogelijk te slaan. Heb je je orderregels nu over 15 tabellen verdeeld zitten ( relationeel goed en goed genormaliseerd ) breng dit dan terug naar 1 tabel, een dwh draait om snelheid, snelheid en nog eens snelheid.
Wil jij een trend bepalen over wat verkoper x over de laatste 5 jaar als artikel met de meeste omzet verkocht heeft dan ga je in een genormaliseerde dbase tegen gigantische joins aanlopen terwijl je in een tabel met een kolom verkoper,artikel, verkoppprijs, inkoopprijs, omzet per order het zo naar boven haalt. Ja, omzet is te berekenen maar in een beetje groot dwh wil je niet rekenen, je wilt data tonen dus reken die omzet lekker uit als het in de dwh gezet wordt en sla het in je dwh gewoon op als decimal.

En wees niet bang voor storage, ben ooit eens betrokken geweest bij een dwh van een callcentre, live datagroei was 100Mb per dag, dwh datagroei was meer dan 2Gb per dag, maar je kon wel inzoomen van een jaarrapport van 5 jaar geleden tot aan 1 enkel telefoongesprek van 5 jaar geleden binnen 1 sec.

Een dwh moet gewoon data stampen en niet rekenen.

[Gomez12]

Motrax schreef op woensdag 21 mei 2008 @ 22:57:
Het ligt er echt volkomen aan hoeveel van je data waar je op zou kunnen meten beschikbaar blijft in het ERP systeem. Als alle data in het ERP systeem blijft, dan zou ik alleen de relevante data overzetten en niets meer.

Joepie, de ene rapportage haal je uit het erp en de andere uit het dwh. Nee, daar zal het management dol op zijn... IMHO gooi je alle relevante info voor je KPI's in je dwh, erp is alleen nodig voor een 100% live overzicht.

Wanneer performance een probleem is, kan je er over denken om data voor te bewerken in een ETL laag voordat het in je DWH gaat. Bijvoorbeeld extractie op order niveau om de doorlooptijd per week in je ETL laag te berekenen en vervolgens alleen de doorlooptijd per week in je DWH te laden.

Let hierbij dus op toekomstige KPI's. Een doorlooptijd per week is leuk om als totaal in je DWH op te slaan, maar als jij nu al voorziet dat er in de toekomst een KPI komt per dag dan moet je toch echt je dagdata ook mee opslaan.

4.5 miljoen records is niks, tenzij je SQL database op een zwakke box draait, je queries qua performance beroerd zijn en je indexen zoek zijn. Ik zou voor meer data gaan dan je per direct voor je KPI nodig hebt.

IMHO werken indexen / berekenings query's extreem beroerd op dwh's. Een index is leuk om 1 record op te halen tussen 4,5 miljoen andere. Maar als jij voor een trendrapportage opeens 3,5 miljoen records nodig hebt dan werkt die index opeens niet zo goed meer.
En 4,5 miljoen records is niks inderdaad, maar 4,5 x 4,5 miloen records omdat je opeens een selfjoin op een genormaliseerde tabel moet doen is opeens toch best wel veel records.

Zowieso kan je je afvragen of je een dwh wel in een relationele dbase wilt hebben of dat het niet handiger is om naar een object based dbase te gaan kijken.

[jordan2k]

ik besef me heel goed dat ik meer moet mee nemen dan alleen de direct benodigde data, dit met het constant varierende vraag van Management. Ik ga er voor om Transactionele data over te zetten maar dan wel de relevante velden.

- Tabel 1: Order en factuur stroom ( paklijst en factuur informatie 4,5 miljoen records per jaar heb 4 jaar data.)
- Tabel 2: Order en factuur stroom ( op productgroepen )
- Tabel 3: Purchage orders ( openstaande inkoop orders bij leveranciers )
- Tabel 4: Shipments ( inkopen per leverancier vs. budget etc)
- Tabel 5: Stam, vooraad & Incourant

Gomez12 schreef op woensdag 21 mei 2008 @ 23:39:
Maar 1 tip als je zelf een dwh wilt opzetten. Vergeet alle normalisatie regels, sommige data ga je waarschijnlijk 10x redundant opslaan in 10 verschillende vormen.
Verzin hier dus controle middelen voor

dit is wel een intresante, hier ga ik zo nog wel even naar zoeken en kijken wat ik kan vinden.

[MMUilwijk]

Gomez12 schreef op woensdag 21 mei 2008 @ 23:58:
[...]

Joepie, de ene rapportage haal je uit het erp en de andere uit het dwh. Nee, daar zal het management dol op zijn... IMHO gooi je alle relevante info voor je KPI's in je dwh, erp is alleen nodig voor een 100% live overzicht.

Helemaal mee eens. Daarnaast wil je 1 versie van de waarheid hebben, en door twee verschillende bronnen te gebruiken (met een eigen versie van de business rules) kan je dit niet garanderen. Gebruik het ERP systeem voor de operationele rapportages en je DWH voor je management rapportages.

Gomez12 schreef op woensdag 21 mei 2008 @ 23:58:
[...]

Let hierbij dus op toekomstige KPI's. Een doorlooptijd per week is leuk om als totaal in je DWH op te slaan, maar als jij nu al voorziet dat er in de toekomst een KPI komt per dag dan moet je toch echt je dagdata ook mee opslaan.

IMHO werken indexen / berekenings query's extreem beroerd op dwh's. Een index is leuk om 1 record op te halen tussen 4,5 miljoen andere. Maar als jij voor een trendrapportage opeens 3,5 miljoen records nodig hebt dan werkt die index opeens niet zo goed meer.
En 4,5 miljoen records is niks inderdaad, maar 4,5 x 4,5 miloen records omdat je opeens een selfjoin op een genormaliseerde tabel moet doen is opeens toch best wel veel records.

Zowieso kan je je afvragen of je een dwh wel in een relationele dbase wilt hebben of dat het niet handiger is om naar een object based dbase te gaan kijken.

Interessante stelling. In mijn ervaring wil je toch echt wel indeces op je data warehouse tabellen hebben. Bekijk de executionplan's maar eens van de queries die je gebruikt voor je rapportages. In MSSQL heb je daar echt veel voordelen van.

Wil je een data warehouse in een relationele database? Ja, maar als het even kan met zo min mogelijk joins. Hangt een beetje van je aan te hangen model af (Snowflake, ster, Inman, Kimball etc.), maar ik zie de voordelen van een oo-bases DBMS niet echt in. Kun je daar de voordelen van op een rijtje zetten?

Verder, zorg ervoor dat je het begrip KPI helder hebt. Een KPI is zoals de naam het al aangeeft bedoeld voor sturing op hoog (strategisch) niveau. Rapportages betreffende omzet (uitgesplitst naar productcategorie, geografie, klantsegmentatie e.d.) zijn IMHO geen KPI's.

Ga aub niet frotten met Access, maar gebruik een fatsoenlijk DBMS. Kies je voor MSSQL 2005 (Enterprise), dan heb je gelijk een ETL tool en een OLAP omgeving tot je beschikking.

Neem in principe alleen de data die je nodig hebt in je managementrapportages, maar kies wel voor het juiste detail (grain) niveau. Heb je in de nabije toekomst rapportages op dag / product / klant niveau nodig, trek dan de data nu alvast over. Kies voor nu voor het bouwen van zogenaamde snapshots die je aggregeert vanuit een ETL laag, deze kun je later genereren uit feitentabellen met lagere grains. Pak wel alvast het dimensie niveau mee wat je later nodig hebt en bouw ze aub slowly changing, dat voorkomt een hoop ellende in de toekomst.

Last but not least, een goede BI omgeving (dwh, ETL, OLAP etc.) opzetten is een vak apart. Het is geen rocketscience, maar je hebt een stevige basis in datamodelleren, sql kennis e.d. nodig. Ik kom te veel "hobby" (n.o.i) projectjes tegen die omvallen en waarbij vooral de budgethouders niet vrolijk worden van de kosten voor een echte volwassen omgeving.

[Gomez12]

MMUilwijk schreef op donderdag 22 mei 2008 @ 20:46:
[...]
Interessante stelling. In mijn ervaring wil je toch echt wel indeces op je data warehouse tabellen hebben. Bekijk de executionplan's maar eens van de queries die je gebruikt voor je rapportages. In MSSQL heb je daar echt veel voordelen van.

Ja en nee, Ja je wilt wel indexen op selectievelden binnen 1 tabel, maar doordat het over het algemeen zonder joins gaat heb je geen indexen tussen tabellen meer nodig. Een unique index is bijv leuk, maar behalve bij factuurnrs is er over een periode van 5 / 10 jaar aan data nog maar heel weinig echt unique. Je erp kent de uniciteitsregels en de data die aan je dwh aangeleverd wordt die is per x uniek.

Wil je een data warehouse in een relationele database? Ja, maar als het even kan met zo min mogelijk joins.

Hmmm, dacht altijd dat relationeel ging over de relaties tussen de tabellen, de joins dus. Wens je zo min mogelijk joins gebruiken ( ideale situatie nul ) dan zie ik het nut niet in van een relationele dbase.

Kun je daar de voordelen van op een rijtje zetten?

Niet zo 1,2,3. Maar even een eerste poging :
- Je wilt geen joins gebruiken, waarom dan nog relationele dbase.
- Het gaat bij een dwh niet meer om een ordertabel met een join naar een verkopertabel ( om de omzet per verkoper te geven ) of een join naar een kalendertabel ( om een omzet per periode te geven ), het gaat om de omzet per verkoper of om de omzet per periode. Noem dit dan ook gewoon objecten.
- Bij een relationeel datamodel heb je relatief veel vrijheid via querys ( als je een ander rapport wilt hebben join je even tabel x/y erbij ) over het algemeen is deze vrijheid handig.
Bij een dwh heb je deze vrijheid helemaal niet nodig ( vanwege de aantallen gegevens kan een verkeerde query opeens 12 uur draaien ) je hebt gewoon x vastgestelde KPI's. Hier moet je het mee doen, noem ze dan objecten en optimaliseer die objecten.

Verder, zorg ervoor dat je het begrip KPI helder hebt. Een KPI is zoals de naam het al aangeeft bedoeld voor sturing op hoog (strategisch) niveau. Rapportages betreffende omzet (uitgesplitst naar productcategorie, geografie, klantsegmentatie e.d.) zijn IMHO geen KPI's.

Wederom ja en nee. Ja het is niet allemaal nodig voor het hoog strategisch niveau, maar menig dwh wordt niet alleen gebruikt door het hoge niveau maar ook door de uitvoerende managers etc en voor hun zijn dit wel weer KPI's. Een algemeen directeur van een wereldbedrijf heeft niet echt als KPI staan welk product er in Oost-knollendam het meest verkocht wordt. Maar een directeur nederland die van de algemeen directeur te horen krijgt dat hij meer omzet moet draaien omdat er in belgie 10 miljoen meer omgezet wordt wil toch best wel weten wat zijn belgische collega dan voor dingen doet die zo winstgevend zijn... KPI's zijn afhankelijk van persoon tot persoon.

Ik kom te veel "hobby" (n.o.i) projectjes tegen die omvallen en waarbij vooral de budgethouders niet vrolijk worden van de kosten voor een echte volwassen omgeving.

Mwah, dat kom je overal tegen en is imho in 90% vd gevallen ontstaan omdat de directie voor een dubbeltje op de 1e rang wil zitten. Ze werden toch niet vrolijk van de kosten van een echte volwassen omgeving.