SSAS: Comparing Actuals and Targets at different granularity when you cant use IsLeaf()

Disclaimer! : Ive purposely simplified a lot of the examples here for readability. It may seem as though i am not using best practice such as surrogate IDs in dimensions etc.. this is not the case.

Yesterday I inadvertantly come up with an interesting solution (in SSAS2005) to a common problem! For arguments sake say I have a simple cube containing two measure groups – one for Actuals and one for Targets. The Actuals fact contains a measure “Actual_Days_Elapsed” i.e. the number of days an event took. There are number of dimensions such as Time, Location, and Event_Group – all are related to the Actuals via regular relationships using the dimension key attribute.

The Event_Group dimension contains a hierarchy that is of particular interest in this example:

The Actuals fact is related to every dimension, however, the Targets fact is only related to the Event_Category dimension and at the “group Code” level i.e. We only have a Target_Days_Elapsed per event Category.

I dealt with this by relating the Event_Category dimension to the Targets fact using an attribute that was not the dimension key. This worked as expected.

Then things got interesting.. i was later asked to report on the percentage of facts that were within target. My first thought was to add a calculated member called “Within Target” along the lines of:

iif(Actual_Days_Elapsed <= Target_Days_Elapsed), TRUE, FALSE)

I quickly realised that this didnt work because it is performing the calc on the aggregation and at the leaf level. To be clear we want SSAS to perform the calc at the leaf level and then aggregte normally.

Some research led me to this informative post by Ajit Singh. This and a related thread on the MSDN forums led me a potential solution. I tried to implement the IsLeaf() solution which is quite elegant. However i got an error saying i coulnd tuse isleaf in my script because i had used a non-key attribute to link Event_Category to the Targets fact. This does makes sense.. but whoa.. what to do now?

After racking my brains for a little while i came up with the following solution:

1. Alter the DSV and replace the Actuals fact with a named query
2. The named query (simplified for the example) is:
   
SELECT
Fact_Actuals.*
, CASE WHEN Fact_Actuals.Actual_Days_Elapsed <= Fact_Targets.Target_Days_Elapsed
THEN 1 else 0 END AS Within_Target

   FROM
   Fact_Actuals
   LEFT OUTER JOIN Fact_Target
   ON Fact_Actuals.Group_Code = Fact_Targets.Group_Code

3. Add a new measure in the Actuals measure group on the new Within_Target which has a Sum aggregation type.
4. Add a calculated member to do the percentage with target now that we have a measure giving us the number that were within target. Assumption here is that we have a default Count measure on the Actuals fact.