KPI MDX Fact, per person, per month

Question

Hi all,

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):

FactUsage

UserId

Training Duration done in Minutes

Number of Sessions

DimEmployee

UserId

Name

Department

Team

DimAccessDate

DateKey

Year

Half Year

Quarter

Month

Week

This is what I've tried so far:

Value Expression

([Measures].[Duration In Minutes], [Date].[Year -  Half Year -  Quarter -  Month -  Date].CURRENTMEMBER ,
 [Employee].[Department - Division - Section - Team - Name].CURRENTMEMBER)

Goal Expression

(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.

Many thanks,

David

---

David

Answer 1

Hello DavidC,
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.

Greetings