2012年7月24日 下午 12:36
I have created a SSAS Cube, based on our training database. I've been trying to get to grips with MDX and KPI's, and struggling.
The training manager wants to set a target KPI of 60 minutes training (CBT) / per month / per employee. I have been trying to write the query but it never compiles. I feel I am getting it horribly wrong and guessing in the dark. I tried using the example template HR/Training Hours/Employee but can't seem to adapt it. Could anyone give me some pointers?
And as for the trend section, that's like black magic to me at the moment, I can't seem to get that anything like correct!
Also, I expected that when I then looked at the data per person it would evaluate automatically, so that if I looked higher up the hierachy at the year instead of month, it would automatically make it 60*12 months, so 720/Year. And likewise if the Department or team hierachy was collapsed, it would automatically calculate it against the amount of employees. So if the team had 5 employees, then the monthly KPI for that team would be 5 * 60 = 300. Am I expecting too much?
I have the following (Trimmed):
Training Duration done in Minutes
Number of Sessions
This is what I've tried so far:
([Measures].[Duration In Minutes], [Date].[Year - Half Year - Quarter - Month - Date].CURRENTMEMBER , [Employee].[Department - Division - Section - Team - Name].CURRENTMEMBER)
(60, [Date].[Year - Half Year - Quarter - Month - Date].[Month] , [Employee].[Department - Division - Section - Team - Name].CURRENTMEMBER)
And for now I've just been keeping it simple with the Status Expression as a 3 state indicator
IIf ( KPIValue( "My Training KPI" ) - KPIGoal( "My Training KPI" ) <=0, 1, -1 )
Any help would be appreciated as I have a feeling I'm getting it very wrong.
2012年7月24日 下午 06:22
At first, did you try to evaluate your Expressions in a MDX Query?
I guess your Goal Expression is wrong.
Try the following:
// Value_Help_Measure: create member [Measures].[MinutesPerEmployee] as sum( ( existing [Employee].[Department - ... - Name].[Employeelevel] ,existing [Date].[Year - ... - Date].[Month] ) ,[Measures].[Duration in Minutes] ); // Goal_Help_measure: Create member [Measures].[EmployeeMonthCount] as countdistinct( nonempty( ( existing [Employee].[Department - ... - Name].[Employeelevel] ,existing [Date].[Year - ... - Date].[Month] ) ,[Measures].[Duration in Minutes] ); // Goal: [Measures].[EmployeeMonthCount]*60 // Value: [Measures].[MinutesPerEmployee]
There is a messy fact, the Goal will work correct on Date level, the Value not. So Perharps it would be a good idea to scope the KPI below the month level on N/A :)
Try the Code, if it can help you.