Zeker wanneer je een scorecard maakt op een Analysis Services kubus, wil je nog wel eens verschillende KPIs over verschillende jaren met elkaar kunnen vergelijken. Als dan het huidige kalenderjaar ook in de scorecard staat, zie je voor dat jaar eigenlijk de year-to-date waarde t/m de laatste maand. De cijfers worden dan moeilijk vergelijkbaar met vorige jaren. Je wilt dan eigenlijk voor ieder jaar het totaal t/m de huidige kalendermaand zien. Er zullen vast meerdere (en misschien eenvoudiger) oplossingen zijn om dit te doen, maar dit is mijn oplossing die in ieder geval doet wat ik wilde:
- Neem een extra dimensie op, bijv. ‘Time View’, met twee vaste members: ‘Regulier’ en ‘YTD’. Neem hiertoe in de data source view een named query op met bijv. het volgende statement:
SELECT 1 AS TimeViewKey, 'Regular' AS TimeViewDesc
UNION
SELECT 2 AS TimeViewKey, 'YTD' AS TimeViewDescLink deze dimensie tabel aan de feitentabel middels een nieuwe calculated column met de vaste waarde 1 (voor Regular) in de feitentabel zodat de volgende view ontstaat:
-
De dimensie die je op deze tabel maakt, mag geen aggregaties hebben (IsAggregatable = False) op de enige attribuut hiërarchie die de dimensie heeft. Neem de dimensie op als een nieuwe cube dimensie.
-
Neem in de kubus de volgende calculations op:
CREATE MEMBER CURRENTCUBE.[Current Month] AS
DatePart("mm",Now())),
VISIBLE = False;
SCOPE ([Time View].[Time View].[YTD], [Time].[Year - Quarter - Month - Day].[Year].Members);
this = Aggregate(CrossJoin([Time View].[Time View].[Regular], PeriodsToDate([Time].[Year - Quarter - Month - Day].[Year],
[Time].[Year - Quarter - Month - Day].CurrentMember.FirstChild.FirstChild.Lead([Current Month] – 1))));
Wat doet dit script functioneel:
De scope voor de assignment die er onder staat wordt beperkt tot de YTD plak in de Time View dimensie en tot de members in de tijddimensie op jaarniveau. Vervolgens wordt via de PeriodsToDate functie de set van maanden opgehaald tot en met de maand die gedefinieerd is via de Current Month berekening. Via een trucje wordt vervolgens voor ieder jaar binnen de scope (via CurrentMember op de tijd hierarchie) het eerste kind opgehaald (in mijn voorbeeld is dat het eerste kwartaal) en daar weer het eerste kind van (de eerste maand binnen het eerste kwartaal). Daar wordt vervolgens een Lead op gedaan met als aantal maanden vooruit het aantal in Current Month – 1. Bij het getal 10 krijg je zo met de PeriodsToDate functie de maanden januari t/m oktober terug voor ieder jaar in de scope. Die set van maanden wordt gecombineerd (via de CrossJoin) met de Regular member in de TimeView dimensie. En de zo verkregen set wordt geaggregeerd (de Aggregate) zodat je door de hele kubus heen een YTD krijgt op jaar niveau.
Mijn oplossing heeft één beperking: je krijgt alleen een YTD op jaarnviveau, maar dit wilde ik ook juist. Een voorbeeld OLAP database waarin dit is geïmplementeerd, kun je hier downloaden. Hierin heb ik overigens hard de maand op 7 gezet gezien de voorbeeld database die data tot juli 2004 bevat.