Cumulatief over alle perioden tot nu toe bepalen in MDX

MDX, het blijft voor veel mensen een soort zwarte magie. Terwijl de kracht van MDX juist is dat het antwoord op een vraag soms héél eenvoudig is. Neem bijvoorbeeld het bepalen van het cumulatief van een bepaalde meetwaarde over alle voorbije perioden, vanaf de eerste datum in de database. Als je zo’n cumulatief in de tijd uit zou zetten, zou je onderstaande blauwe lijn te zien krijgen wanneer de maandelijkse feiten overeen komen met de lichte staafjes (het cumulatief is uitgezet tegen de linker as, de maandelijkse omzet tegen de rechter as).

Zou je slechts een deel van de tijd bekijken, wil je nog steeds het cumulatief vanaf het begin van de tijddimensie in de database zien, bijv.:

In essentie is de benodigde MDX expressie heel eenvoudig:

AGGREGATE(PERIODSTODATE([Date].[Calendar].[(All)]), [Measures].[Internet Sales Amount])

In de expressie wordt gebruik gemaakt van de PERIODSTODATE functie. Deze functie heeft twee argumenten: een level en een member (het tweede argument is optioneel). De functie geeft een set van members terug. De laatste member in deze set is altijd de opgegeven member. De eerste member in de set wordt bepaald door de opgegeven member en het opgegeven level. Geef je op de Adventure Works DW 2008 database als level Calendar Year in de Calendar hiërarchie op en als member May 2004 (Month level), dan geeft de functie de volgende set aan members terug:

{[Date].[Calendar].[January 2004], [Date].[Calendar].[February 2004], [Date].[Calendar].[March 2004], [Date].[Calendar].[April 2004], [Date].[Calendar].[May 2004]}

Het tweede argument van de PERIODSTODATE functie is optioneel. Indien niet opgegeven, is het de current member in de hiërarchie die in het eerste argument is opgegeven. Door als eerste argument het (All) level op te geven ([Date].[Calendar].[(All)]), krijg je een set van members die altijd begint bij de allereerste member in het huidige level van de huidige member in de tijd dimensie.

Door deze set op te geven als eerste argument in de AGGREGATE functie en als tweede argument een measure, krijg je altijd het cumulatief over de volledige tijddimensie.

Een nadeel van deze MDX expressie is dat het cumulatief door blijft lopen wanneer je geen data meer hebt, maar nog wel members in de tijddimensie. In de eerste afbeelding in dit artikel zie je dat een klein beetje voor de laatste maanden waar de lijn vlak door blijft lopen. Ongewenst wanneer je tijddimensie erg ver in de toekomst doorloopt. Eigenlijk wil je dan geen cumulatief meer berekenen. Ook dit kan door een eenvoudige uitbreiding van de expressie:

IIF(ISEMPTY([Measures].[Internet Sales Amount]), Null, AGGREGATE(PERIODSTODATE([Date].[Calendar].[(All)]), [Measures].[Internet Sales Amount]))

In dit geval test je of er in de huidige tijdsperiode omzet is, zo niet, wordt er geen cumulatief berekend. Natuurlijk moet je in een realistische omgeving kijken of de hier gebruikte conditie bruikbaar is en zou je bijvoorbeeld wat VBA code toe kunnen voegen om te kijken of de huidige member in de tijddimensie voorbij de kalenderdatum ligt, maar het gaat hier om het idee. Het idee dat MDX geen zwarte magie is!