I'm struggling with trying to create a calculation in my cube. I have an accumulating snapshot fact table with some information including:
LocationKey (Foreign key to the location dimension) Process Identifier (unique key to identify the individual process grain) Process start date (Foreign key to date dimension) Process Days Overdue (A column calculated at ETL time for the number of days the individual process is overdue by)
When I build my cube, the measures I get are: Process Count (added automatically by SSAS for the count of all proceses) Process Days Overdue
What I'd like to be able to do is add a calculation for: Count of Overdue processes (i.e. number of processes where 'Days overdue' > 0) Count of On Target processes (i.e. number of processes where 'Days overdue' = 0)
I've had some limited success so far. I've managed to get the counts coming out correctly in relation to the total number of processes, but they fail to calculate correctly when I start slicing the data.
If I have 100 processes, 10 are overdue and 90 on target at the highest level in my cube I can get the information correctly. i.e.:
Total Count: 100 Overdue: 10 OnTarget: 90
However when I, for example split the data by location instead of getting:
Total Count (Location A): 80 Overdue (Location A) : 8 **OnTarget (Location A) :**72 Total Count (Location B) : 20 Overdue (Location B) : 2 OnTarget (Location B) : 18
my results are not splitting correctly and I'm getting: **Total Count(Location A):**80 **Overdue (Location A):**10 **OnTarget (Location A):**90 **Total Count (Location B) :**20 Overdue (Location B): 10 OnTarget (Location B): 90
Any help would be really appreciated. I'm not sure if I'm just doing something wrong, or if my whole understanding is incorrect and what I'm trying to do is impossible...
asked Dec 05, 2012 at 11:39 AM in Default