0 comments
Business Intelligence 
Een tijdje terug vroeg één van mijn collega's mij wat de betekenis is van ByAccount als waarde voor de AggregateFunction property bij een Analysis Services measure. En ook al werk ik op het Kenniscentrum, ik weet (gelukkig) niet alles. Gelukkig, want zo valt er ook voor mij nog steeds wat te leren! Ik had in dit geval de klok wel horen luiden want ik wist dat het iets te maken had met Account dimensies, maar waar de klepel precies hing, wist ik niet. Toen ik vandaag ter voorbereiding van een cursus volgende week door het boek SQL Server 2005 Analysis Services – Step by Step van Jacob Reed en Stacia Misner zat te bladeren, kwam ik een heel hoofdstuk tegen over dit onderwerp: hoofdstuk 6 – Working with a Finance Measure Group. Tijd om kort samen te vatten wat die ByAccount aggregate functie nu doet.
Normaal gesproken wordt een measure over alle dimensies, inclusief de tijd dimensie, geaggregeerd met de default Sum aggregatie functie. Bij veel measures helemaal prima, maar niet wanneer één van je dimensies een dimensie is waarin een rekeningschema is geïmplementeerd. Netto winst is bijvoorbeeld gelijk aan bruto winst + overige inkomsten – onkosten – belastingen. Een mix dus van optellen en aftrekken, terwijl de bedragen in de database allemaal positief zijn. Door de bewuste dimensie van het type Account te maken, kun je een extra kolom in de onderliggende dimensie tabel opnemen waarmee je aan kunt geven hoe een bepaalde rekening bijdraagt aan de rekeningen op een hoger niveau. Onderstaande figuur toont zo'n dimensie tabel.
Via de UnaryOperatorColumn property van de Parent attribuut in de Account dimensie, map je de Operator kolom in bovenstaande tabel en wordt de betreffende rekening naar zijn parent geaggregeerd overeenkomstig de betreffende operator (+, -, * of /; ~ betekent helemaal niet aggregeren). In de kubus ziet dit er nu als volgt uit:
Nu hebben we het nog helemaal niet over de ByAccount aggregatie functie gehad. Tot nu toe werkt alles met de default Sum aggregatie functie. Maar, wat gebeurt er nu wanneer we de tijd erbij halen en er in het rekeningschema rekeningen voorkomen die over de tijd niet geaggregeerd mogen worden maar over alle andere dimensies wel? Denk bijvoorbeeld aan activa. Binnen één maand mogen alle activa gewoon worden geaggregeerd, maar wat is de stand aan het eind van het jaar? Niet de som van alle maanden in het jaar! Maar dit geldt alleen voor de activa, niet voor inkomsten en uitgaven die weer wel geaggregeerd mogen worden over de tijd. En daar komt de ByAccount aggregatie functie in beeld. Want ook hier bepaald de rekening hoe er geaggregeerd moet worden, ditmaal over de tijd. We breiden daartoe de dimensie tabel uit met een extra kolom:
Deze extra kolom AccountType geeft aan wat voor soort rekening het is. Je kunt hier overigens je eigen labels voor gebruiken. Bij het maken van de account dimensie via de wizard, map je de verschillende waarden op de ingebouwde account typen in Analysis Services. Ga je niet via de wizard of wil je dit achteraf nog herzien, kun je dat via de Add Business Intelligence toolbar button in de dimension designer doen en vervolgens Define Account Intelligence kiezen.
Nu zegt het boek van Jacob Reed en BOL dat voor het built-in account type Asset de aggregatie functie LastNonEmpty gebruikt wordt, maar dat is niet het geval. Volgens een blog artikel van Joe Kasprzak, zit er een bug in Analysis Services waardoor er voor alle account types toch gewoon gesommeerd wordt… Maar gelukkig kun je de mapping tussen account type en aggregatie functie aanpassen. Je doet dat door in de solution explorer met rechts op je solution te klikken en dan Edit Database te kiezen. Je krijgt dan oa. het volgende te zien:
Door hier de juiste aggregatie functie per rekeningtype te kiezen, kun je iedere rekening correct over de tijd aggregeren. Joe Kasprzak vermeld dat je voor alle account typen behalve Income en Expense de aggregatie functie op LastNonEmpty moet zetten. Let er wel op dat het gebruik van de ByAccount aggregatie functie één dimensie van het type Account vereist (en er kan maar één Account dimensie in een kubus bestaan), dat van de parent key in die dimensie de Type property ook op Account wordt gezet en de Usage property op Parent staat. Als je de dimensie met de wizard maakt, gaat dit vanzelf allemaal goed.
En zo kun je met een Account dimensie en het juiste gebruik van enkele properties een flexible dimensie ten behoeve van rekeningschema's maken waarin rekeningen correct worden geaggregeerd binnen het schema én met de ByAccount aggregatie functie óók over de tijd. Zie voor meer informatie SQL Server 2005 Analysis Services – Step by Step hoofdstuk 6, of in BOL Adding Account Intelligence to a Dimension. Tevens heb ik een voorbeeld Visual Studio solution beschikbaar gesteld die ik heb gebruikt tijdens dit onderzoekje.