Friday, December 4, 2009

Where Clause and SSAS Calculations

Today I had to change business rule in our SQL Server Analysis Services 2005 solution. It would be hard to explain the scenario so I use an example.
Scenario: A class contains boys and girls. We need to calculate the passing percentage of students that are boys.

Previously the rule was to get passing percentage of boys.
Total number of boys that pass / Total number of students in class
Correct rule should have been:
Total number of boys that pass / Total number of boys
Yeah, I know this can be done in a giffy in SQL Server but Analysis services is a totally different ball game.

Constraint was that we had no dimension for gender like boys, girls. Instead two columns in fact table (yup, not good implementation) boys and girls with value 0,1.
To work around our constraint we created  Named Query in cube which returned all the dimensions along with every record where boys = 1.
Finally we had to filter measure based upon result dimension (P meaning Pass). So in Calculations tab of cube created a calculation as:

Round(([Measures].[Vw Get All Boys Count], [Result Dim].[Result].&[P])  / [Measures].[Vw Get All Boys Count] * 100,0)

And got the result we were expecting.

No comments:

Post a Comment