Jaargemiddelden op iedere dag (MDX)

In een rapportage omgeving bij één van mijn klanten, werd gevraagd om een gemiddelde te berekenen op iedere kalenderdag over het afgelopen jaar. Op 31 december 2014, wilde men dus een gemiddelde zien over de periode 1 januari 2014 t/m 31 december 2014. En dat dan voor iedere dag en over een aantal verschillende dimensies. Bijkomende complicatie: op het laagste niveau, kunnen elementen in een dimensie op een willekeurig moment in de tijd worden ‘verhangen’. Ze komen dan onder een andere parent te hangen. In dat geval wilde men dat het gemiddelde van de oorspronkelijke parent afvlakt, terwijl het gemiddelde van de nieuwe parent toeneemt. Onderstaande screenshot toont het gevraagde rapport. Hierbij wordt X ergens in het tweede kwartaal verhangen van A naar B.

In eerste instantie probeerden we dit probleem op te lossen met T-SQL queries op enkele aggregatie tabellen. Naast een beroerde performance, bleek het ook bijzonder lastig jaargemiddelden te berekenen voor X onder A in een periode waarin er geen actuele cijfers meer zijn voor X onder A.

Analysis Services en MDX to the rescue! Met een hele eenvoudige kubus met een paar dimensies en een paar meetwaarden, is het heel simpel een gemiddelde schuldrest te bepalen op een willekeurige datum over een gewenste periode:

CREATE MEMBER CURRENTCUBE.[Measures].[AvgNetDebt12Mth]
AS null,
VISIBLE = 1 ,
ASSOCIATED_MEASURE_GROUP = 'NetDebt';

SCOPE ([dim_Date].[Calendar].[Date].Members, [Measures].[AvgNetDebt12Mth]);
this = SUM([dim_Date].[Calendar].CurrentMember.LAG(364)/365;
END SCOPE;

Eigenlijk was hiermee de vraag beantwoordt, de kubus verving de aggregatie tabellen (waarvan het laden een klein half uur kostte, terwijl het laden van de kubus seconden kost!) en de performance van de rapporten verbeterde enorm. Maar er zit nog een kleine onzuiverheid in deze eerste oplossing: een jaar heeft niet altijd 365 dagen! En de klant wilde iedere datum als peildatum kunnen kiezen. Dus, bij een peildatum van 29 februari 2016 moet de periode 1 maart 2015 t/m 29 februari 2016 worden genomen waarin 366 dagen vallen. Op 28 februari 2016 moet ook 1 maart 2015 als beginpunt worden genomen maar vallen er 365 dagen in de periode.

In eerste instantie probeerden we dit vraagstuk op te lossen door bovenstaande berekening uit te breiden met een tamelijk complexe dynamische set in de SUM functie. Omdat deze berekening voor iedere dag wordt gedaan, kwam dit de performance van de rapporten niet ten goede. Uiteindelijk hebben we het probleem als volgt opgelost:

1. In de data source view is aan de tabel waarop de tijddimensie is gebaseerd een named calculation toegevoegd waarmee het aantal dagen in een volledig jaar voorafgaand aan die datum wordt bepaald met de volgende formule: datediff(day, dateadd(year, -1, Date_yyyyMMdd), Date_yyyyMMdd). Date_yyyyMMdd is hierin een bestaand veld in de tabel waarin een datum staat. De berekening bevat op deze manier voor 28 februari 2016 365 als uitkomst, en vanaf 29 februari 2016 t/m 28 februari 2017 366.
2. Op deze berekende kolom, is een property toegevoegd aan de tijddimensie (AttributeHierarchyEnabled is op False gezet).
3. De berekening in de cube is aangepast waarbij de harde cijfers 364 en 365 uit het voorbeeld hierboven zijn vervangen door het gebruik van deze nieuwe property.

this =
 SUM([dim_Date].[Calendar].CurrentMember.LAG(
 [dim_Date].[Calendar].CurrentMember.Properties(
 "Number Of Days In Last Year") - 1) :
 [dim_Date].[Calendar].CurrentMember, [Measures].[Amount])
 / [dim_Date].[Calendar].CurrentMember.Properties(
 "Number Of Days In Last Year");

Dit gaf een naar volle tevredenheid werkende oplossing met een excellente performance!

Overigens liepen we in dit rapport tegen een bekend issue in MDX aan: als je met ParallelPeriod probeert 3 maanden terug te gaan vanaf bijv. 31 december, krijg je een lege periode terug (MDX is namelijk niet ’time aware’ en gaat met ParallelPeriod vanaf 31 december letterlijk terug naar het Month level om op de december member uit te komen, gaat dan 3 maanden terug om op september uit te komen, om vervolgens weer naar de 31e member onder september te gaan, die niet bestaat). Vandaar dat onderstaande MDX query een lege waarde teruggeeft in de tweede kolom:

WITH MEMBER [Order Count -3M] AS
(ParallelPeriod([Date].[Calendar].[Month], 3), [Measures].[Order Count])
SELECT {[Measures].[Order Count], [Order Count -3M]} on 0
FROM [Adventure Works]
WHERE [Date].[Calendar].[Date].&[20131231]

Om dit op te lossen, moet je weer trucjes toepassen, bijv. (er zijn andere en misschien wel betere oplossingen):

WITH MEMBER [Order Count -3M] AS
(Iif(IsEmpty(ParallelPeriod([Date].[Calendar].[Month], 3)),
[Date].[Calendar].Parent.Lag(3).LastChild,
ParallelPeriod([Date].[Calendar].[Month], 3)), [Measures].[Order Count])
SELECT {[Measures].[Order Count], [Order Count -3M]} on 0
FROM [Adventure Works]
WHERE [Date].[Calendar].[Date].&[20131231]