Calculations aggregeren met Power BI/Analysis Services

Veel solutions van Ensior maken gebruik van de Microsoft BI Suite. Hierbij wordt het model gemaakt in Analysis Services. Dit kan zowel een multidimensioneel model zijn of een tabular model. Een erg krachtige functie binnen deze modellen is het aanmaken van calculations. Dit zijn berekende velden die binnen de modellen kunnen worden gedefinieerd. Het mooie aan deze berekeningen is dat je hier bijvoorbeeld heel makkelijk percentages mee kunt berekenen, welke dan in de front-end tools 'on-the-fly' worden berekend.

Een veel voorkomende vraag is vaak of berekeningen ook op een ander niveau dan het laagste niveau of het totaalniveau kunnen worden berekend. Bijvoorbeeld een berekend die op projectniveau wordt berekend en daarna wordt opgeteld om tot een totaal voor de gehele organisatie te komen.

Verschillende types berekeningen

In bovenstaande afbeelding is goed te zien wat er gebeurt met de resultaten als een berekening op verschillende niveaus wordt berekend. Alle berekeningen zijn gebaseerd op A*B.
• De berekening in het rood is een simpele calculation, welke dus op elk niveau wordt berekend. Dit geeft dus een totaal van (26*25) = 650
• De blauwe variant wordt op regelniveau berekend en geeft een resultaat van 87 (A*B op regelniveau en dan opgeteld)
• De berekening in het groen wordt tot op projectniveau uitgevoerd en dan opgeteld tot een totaal. Dit geeft dus 32(8*4) + 154(11*14) + 49(7*7) = 235

De eerste twee varianten zijn natuurlijk simpel te realiseren door respectievelijk een calculation toe te voegen of de berekening in de query uit te voeren of een calculated column op datasetniveau aan te maken. De 3e zal ik hieronder voor de verschillende type modellen toelichten. Deze methode is ook goed toepasbaar voor cross-fact calculations. Deze berekeningen zijn namelijk gebaseerd op meetwaarden uit verschillende meetwaardengroepen, waardoor het niet mogelijk is om de berekening aan de query toe te voegen.

In onderstaande tabel is goed te zien welke methode in welke situatie het beste gebruikt kan worden. Tevens is er een voorbeeld van een berekening toegevoegd, alhoewel dat natuurlijk altijd sterk afhankelijk is van de inrichting van het model.

Multidimensional model
Binnen een multidimensioneel model moet, om een berekening op projectniveau te bereiken, aan SSAS eigenlijk worden aangegeven op welk niveau een berekening moet worden gedaan. Dit kan met behulp van het SCOPE statement. Hiermee wordt aangegeven op welk niveau de meetwaarde moet worden gevuld.

Werkwijze:
1. Maak een lege "Named Column" of een leeg veld aan in de Data Source View
2. Voeg het veld als measure toe aan de kubus
3. Geef in de calculations tab door middel van scope aan op welk niveau de berekening moet worden uitgevoerd. In onderstaand voorbeeld is de measure [AB SCOPE] aangemaakt en wordt deze op projectniveau berekend door de volgende code toe te voegen:
    SCOPE([Measures].[AB SCOPE]);
    SCOPE([PROJECT].[ID].[ID].MEMBERS);
    THIS = [Measures].[A] * [Measures].[B];
    END SCOPE;
    END SCOPE;

 

Tabular Model
In een tabular model is de berekening iets makkelijker toe te voegen. Binnen een tabular model kan de functie SumX hiervoor worden gebruikt. De berekening kan zowel op rapportniveau als op kubusniveau worden toegevoegd.
• Voeg een nieuwe meetwaarde toe aan je rapportage met de volgende formule; SumAB_DAX = sumx(values('Fact'[PROJECT]),[SumA]*[SumB])
• Voeg een nieuwe meetwaarde toe aan je model met de volgende syntax; sumx(values('Fact'[PROJECT]),[SumA]*[SumB])

Conclusie
De mogelijkheden om zelf formules en berekeningen toe te voegen aan de verschillende modellen zijn erg krachtig. Al zal het soms wel redelijk wat MDX- of DAX-kennis vergen. In dit geval zijn de berekeningen simpel te realiseren en kunnen ze van grote waarde zijn voor de organisatie. Let echter wel goed op welke berekening je op welk moment het beste kan inzetten. Over het algemeen geldt:
• Berekeningen op het laagste niveau in de query of calculated column
• Berekeningen die achteraf berekend moeten worden met een calculation (bijvoorbeeld percentages)
• Cross-fact calculations die achteraf berekend moeten worden met een calculation
• Berekeningen die op een bepaald niveau berekend moeten worden of cross-fact calculations die niet achteraf berekend moeten worden met een calculation met scope of sumx.

Deze blog is eerder gepubliceerd op de website van Ensior.