Iedere BI ontwikkelaar zou het Kimball concept van een slowly changing dimension in een dimensionaal model moeten kennen: iedere versie van een dimensie member kan meerdere keren voorkomen wanneer deze over de tijd wijzigt. Iedere member wordt vervolgens uniek gemaakt door een surrogate key te gebruiken. Op basis van deze surrogate keys worden de facts aan de dimensietabellen gerelateerd.
Als we een op deze manier ontworpen dimensionaal model hebben, hoe gaat Analysis Services daar dan mee om? Het antwoord op deze vraag heb ik op basis van de AdventureWorks voorbeelden bepaald. In het AdventureWorks datawarehouse, zitten twee slowly changing dimensies: de Product en de Employee dimensie. Helaas bevatten deze dimensies alleen wijzigingen op laag niveau. Dat wil zeggen, in de Employee dimensie kon ik helemaal geen wijzigingen ontdekken; in de Product dimensie zijn voor een aantal producten wijzigingen doorgevoerd in bijv. prijs en kosten. Om het voorbeeld iets interessanter te maken, heb ik een andere in de praktijk vaak voorkomende situatie nagebootst: een dimensiemember die hiërarchisch wijzigt. In de Product dimensie heb ik daartoe van één van de producten die al historisch wijzigt een extra wijziging doorgevoerd: het product ‘AWC Logo Cap’ heb ik per 1 juli 2003 van de subcategorie ‘Caps’ verplaatst naar ‘Helmets’ (en daarmee van de categorie ‘Clothing’ naar ‘Accessories’). De vraag is nu: wat laat Analysis Services ons zien? Het antwoord is: de waarheid.
In bovenstaand voorbeeld, zie je de situatie voor de wijziging. Het product ‘AWC Logo Cap’ is in 2003 in twee varianten verkocht (met een verschil in prijs). Na de wijziging waarbij de tweede variant niet alleen van prijs maar ook van subcategorie is gewijzigd, zien de cijfers er als volgt uit:
De waarheid, want in de tweede helft van 2003 zijn er 1.761 AWC Logo Caps verkocht die op dat moment onder de categorie ‘Helmets’ vielen en in de eerste helft van dat jaar 916 exemplaren die toen nog onder de Caps vielen. Analysis Services doet dus niet meer dan rekenen met wat er in het dimensionaal model is vastgelegd.
Sommige gebruikers zullen echter op een andere manier naar de cijfers willen kijken. Wanneer een succesvolle verkoper in de loop van het jaar naar een andere regio verhuisd, zal het resultaat van zijn oorspronkelijke regio sterk achterblijven op de prognose waardoor alle stoplichten op rood springen. Vandaar dat men soms wil kijken naar een ‘andere waarheid’, bijv. alsof de verkoper nog steeds in de oorspronkelijke regio actief is, of altijd al in de huidige regio. Vergelijking van cijfers wordt daarmee in bepaalde situaties eenvoudiger. Sommige analyse tools bieden standaard mogelijkheden om dit te doen. En toen ik begon met dit onderzoekje, dacht ik dat ook Analysis Services daar iets mee kon. Je kunt bij een attribuut namelijk een type aangeven en bij de mogelijke waarden kom je dan tegen: ScdStartDate, ScdEndDate, ScdOriginalID en ScdStatus. Maar helaas, het blijken slechts hints voor clients te zijn die dan mogelijk iets kunnen gaan doen met de wetenschap dat een attribuut een bepaalde SCD betekenis heeft. En helaas is er nog geen client die daar iets mee doet. Je zult dus zelf aan de slag moeten. En omdat Analysis Services niet meer doet dan rekenen met wat er in het dimensionaal model is vastgelegd, moet je dat eigenlijk al in dat onderliggende dimensionale model doen.
In het AdventureWorks datawarehouse, heb ik om dit eens te toetsen de Product, ProductSubcategorie en ProductCategorie dimensie ‘platgeslagen’ tot één nieuwe Product dimensie met een aantal extra kolommen.
Deze extra kolommen zijn voor iedere historische versie van het betreffende product gevuld met de op dat moment geldige subcategorie en categorie, maar ook met de ‘as is’ situatie, de huidige situatie. Vervolgens heb ik in de product dimensie in de Analysis Services database een alternatieve ‘as is’ hiërarchie opgenomen.
De Category en Subcategory levels in deze ‘as is’ hiërarchie zijn hierbij gebaseerd op de ‘as is’ attributen in de onderliggende dimensietabel. Op deze manier kun je tijdens het analyseren kiezen of je naar de ‘at time of fact’ waarheid wilt kijken, of naar de alternatieve waarheid alsof een product altijd al onder de huidige categorie valt:
Volgens deze waarheid, zie je de AWC Logo Caps helemaal niet meer terug onder de Caps (deze subcategorie zie je daardoor helemaal niet meer terug) en valt de omzet in heel 2003 geheel onder de huidige subcategorie: Helmets. Je ziet dat je met wat moeite de business vraag kunt beantwoorden. Waarbij ik nadrukkelijk de kanttekening wil plaatsen dat de hier gekozen oplossing niet meer dan een voorbeeld is en je hopelijk op weg helpt naar een eigen oplossing.
Referenties
- Slowly Changing Dimension type 2 in SSAS (blog van Ella Maschiach)
- AttributeType Enumeration (TechNet, let vooral op de Remarks sectie onderaan het artikel)
- Configuring Attribute Types (MSDN)
One comment
Heel mooi geschreven. Misschien wil je nog een deel twee schrijven over hoe je een rapport datum “As Is” kan implementeren. De gebruikers kunnen dan “hoe was de huidige waarde toen ik op datum X” naar de cijfers keek. En nog een deel drie of je wel of niet referenced dimensions moet gebruiken wanneer deze allebei SCD zijn.
Constantijn enders