Reporting Services Server Aggregates

Je maakt een rapport op de AdventureWorks Analysis Services database. Je neemt omzet, kosten en de marge per product op en je groepeert de gegevens op categorie en subcategorie. Voor deze groeperingen neem je totalen op in het rapport. Het eindresultaat ziet er op categorieniveau als volgt uit:

Je ziet natuurlijk meteen dat de marges niet kloppen. Dom, want Reporting Services heeft standaard een Sum functie gebruikt om de totalen te bepalen. Prima voor de omzet en kosten, maar natuurlijk niet voor de marge. Je verandert de expressies in de group headers en gebruikt de Avg functie.

Het rapport lijkt nu in orde. Totdat iemand je er op wijst dat de cijfers voor de marge niet kloppen. Deze persoon heeft namelijk met een ander gereedschap rechtstreeks in de Analysis Services database gekeken en ziet de volgende cijfers:

Terwijl jouw rapport de volgende cijfers laat zien:

Met het schaamrood op je kaken, constateer je natuurlijk snel dat jouw cijfers inderdaad niet kloppen. Omzet – kosten / kosten is voor de Clothing categorie inderdaad 40.15%… Reporting Services neemt het gemiddelde van de marges van de onderliggende subcategorieën in plaats van op ieder niveau opnieuw de berekening uit te voeren.

Je krabt je eens op je hoofd, probeert zo slim mogelijk te kijken en beloofd de brenger van het slechte nieuws snel een oplossing te bieden. In gedachten ben je al bezig nieuwe formules te bedenken, maar wacht eens… In de Analysis Services database zitten de cijfers al correct op ieder niveau. Waarom gebruik je die dan niet??

De oplossing: de Aggregate functie

Kortom, hoe voorkom je dat Reporting Services zelf cijfers aggregeert terwijl Analysis Services daar nu juist zo goed in is? Het antwoord: gebruik eenvoudigweg de Aggregate functie in plaats van Sum of Avg.

En inderdaad, na deze kleine wijziging zijn de cijfers in de nieuwe kolom helemaal correct.

Zodra je de Aggregate functie gebruikt, verandert er ook iets in de MDX query designer in Report Builder. Plotseling doet de toolbar knop Show Aggregations iets!

Met deze knop ‘ingedrukt’, laat de designer nu de zogenaamde server aggregates zien, de cijfers uit de database die nu rechtstreeks worden gebruikt. Voordat je de Aggregate functie gebruikt, slaat Reporting Services de resultaten uit de MDX query ‘plat’. Dat wil zeggen dat alle hiërarchische informatie feitelijk verdwijnt en er alleen records overblijven op het laagste niveau, in dit voorbeeld op productniveau. Door het gebruik van de Aggregate functie, dwing je de data provider records toe te voegen op de hiëarchische niveaus en met de Show Aggregations toolbar knop worden deze toegevoegde records, de server aggregates, getoond.

Moraal van dit verhaal: bouw je rapporten op Analysis Services databases, denk dan eens wat vaker aan de Aggregate functie. Hoewel de functie vooral toegevoegde waarde heeft bij niet standaard te aggregeren cijfers (zoals prijzen, gemiddelden, ratio’s, etc. of wanneer je met SSAS technieken als scoped assignments of custom member formulas resultaten in een kubus berekend), kun je de functie ook gebruiken voor standaard te aggregeren cijfers. Op grote databases, kan dit een betere performance geven dan Reporting Services laten aggregeren.