none
Can't get the right sum for top level KPI RRS feed

  • Question

  • Context

    In my scorecard I got an KPI: [Logged Billable Project Hours Percentage versus Target]

    The following definition is stored in SSAS to define it:

     

    Value:

    Code Snippet
    [Measures].[Logged Billable Project Hours]/[Measures].[Expected Logged Hours]

     

     

    Goal:

    Code Snippet
    [Measures].[Logged Billable Hours Green Target]/[Measures].[Expected Logged Hours]

     

     

    Status:

    Code Snippet

    Case

    When [Measures].[Logged Billable Project Hours]/[Measures].[Expected Logged Hours] < [Measures].[Logged Billable Hours Amber Target]/[Measures].[Expected Logged Hours]

    Then -1

    When [Measures].[Logged Billable Project Hours]/[Measures].[Expected Logged Hours] >= [Measures].[Logged Billable Hours Amber Target]/[Measures].[Expected Logged Hours]

    And [Measures].[Logged Billable Project Hours]/[Measures].[Expected Logged Hours] < [Measures].[Logged Billable Hours Green Target]/[Measures].[Expected Logged Hours]

    Then 0

    When [Measures].[Logged Billable Project Hours]/[Measures].[Expected Logged Hours] >= [Measures].[Logged Billable Hours Green Target]/[Measures].[Expected Logged Hours]

    Then 1

    End

     

     

    I got a scorecard like this:

     

    + Logged Billable Project Hours versus Target Roll up calculation is wrong, should only include for Developers and Project Managers, but it's calculating for all roles

    +--- Developers Rollup works fine

    +------ Active Developers (=named set and will be populated with all members after hitting refresh) Rollup works fine

    +--- Project Managers Rollup works fine

    +------ Active Project Managers (=named set and will be populated with all members after hitting refresh) Rollup works fine

     

    The percentages are calculated on SSAS.

     

    A MDX query is sent including all the different employees from the named set. SSAS returns the right calculations per employee.

    A MDX query is sent including for Developers and Project Managers . SSAS returns the right calculation.

     

    Problem

    However, for the top level, a MDX query is send to get the value withput specifying the role (so all roles are included), which is not desired. There are only [Logged Billable Project Hours] for Developers and Project Managers, so this number is still fine. But: the [Expected Logged Hours] are also stored for all Employees, so this number is too high. It should only include the expected hours for Project Managers and Developers, but all Employee Roles are used in the calculation.

     

    Does anyone has any ideas how I can manipulate the calculation for the top level so I only get the calculation for the Developers and Project Managers in stead of all Employee Roles?

     

    Things I tried

    When you create a scorecard, you can select the Measure Filters in the wizard. However, it didn't seem to have influence. An annoying thing is also that you can not edit the filter after using the wizard. (Same with importing KPI's from SSAS, which can only be added by using the wizard of the scorecard and not afterwards?). When I set a filter to a specific role, I could not find it back in the XML of the workspace by using a text search, so I am still wondering where it is stored.

    What I want is to use a WHERE clause when the query for the Top Level KPI is send, because that will allow me to get the value for Developers and Project Managers only.

     

    Any feedback is appreciated.

     

    Thanks,

    Chris.

     

    Tuesday, September 23, 2008 4:47 PM

Answers

  • My current workaround is to add a named calculation in the Employee Dimension for every KPI. Like this:

     

    Code Snippet

    CASE
     WHEN [Employee Role] = 'Developer'
      THEN 'Total'
     WHEN [Employee Role] = 'Project Manager'
      THEN 'Total'
     WHEN [Employee Role] = 'Support'
      THEN 'Total'
     ELSE 'No Total'
    END

     

     

    Later I can drag the "Total" member to the scorecard and this will only include Developers, Project Managers and Support. It's not neat to adjust the cube in order to let the scorecard works properly, but I can't think of a better solution now.

    Thursday, October 30, 2008 10:57 AM

All replies

  • You would have to create a calculated member for the dimension that only included the roles you wanted in your query then choose that member in the filter for the KPI.  Or, write an MDX expression to gather what you need.  If you want your calcs to come from SSAS, you have to have the right rollup member over there that we can pull from. 

    Hope this helps.

    Thanks,

    Alyson

     

     

     

    Wednesday, October 1, 2008 2:56 PM
  • Hello Alyson,

     

    Thanks for your reply and my apologies fo such late reaction.

     

    The problem I have now is that when you use MDX expression you can't use the WHERE clause. That's what I want, because enables filtering.

    The MDX sent to Analysis Services is generated by PerformancePoint. Isn't there any possibility to add a WHERE clausule in the generated code?

     

    I tried your advice to create a calculated member especially for the scorecard. However this is still without success because then you can only use MDX expression and not a full query with WHERE clause.

     

    I tried the following to filter by using an MDX expression (without success):

     

    Code Snippet

    WITH

    MEMBER Measures.ExpectedLoggedHoursSum AS

    (

    [Employee].[Employee Role].&[Developer]

    ,

    [Measures].[Expected Logged Hours]

    )

    +

    (

    [Employee].[Employee Role].&[Support]

    ,

    [Measures].[Expected Logged Hours]

    )

    +

    (

    [Employee].[Employee Role].&[Project Manager]

    ,

    [Measures].[Expected Logged Hours]

    )

    SELECT ([Time].[Month].&[2008-01-01T00:00:00]) on columns,

    (Measures.ExpectedLoggedHoursSum,[Employee].[Employee].&[3]) on rows

    FROM [Timesheet DW]

     

     

    This returns the hours for Employee with ID = 3. If the Employee is not in one of the three roles in the member definition, then (null) is returned, which is expected.

    If I use ExpectedLoggedHoursSum, then the total will be correct (so that problem is solved, see my previous post), because only relevant roles are included.

     

    But if I want to request the hours for particular role, some MDX like this will be generated by PerformancePoint:

     

    Code Snippet

    SELECT ([Time].[Month].&[2008-01-01T00:00:00]) on columns,

    (Measures.ExpectedLoggedHoursSum,[Employee].[Employee Role].&[Developer]) on rows

    FROM [Timesheet DW]

     

    Then the total of Developer + Support + Project Manager is agian returned in stead of role Developers. So the scorecard is still not correct.

     

    So the only way how I know to do it in MDX is using the WHERE clause, but as stated before I can't use that.

     

    Any help or ideas are welcome.

     

    Thanks,

    Chris.

    Monday, October 27, 2008 4:37 PM
  • For KPIs you can only enter a tuple expression in the KPI definition so unless you can get to the right data by using just a tuple you will not be able to get what you are after.  Is there any way to write a tuple to get what you are after? Or add together multiple tuple values?

     

    Monday, October 27, 2008 6:40 PM
  • In Dashboard Designer I can define a MDX tuple formula that must work for all levels in the scorecard. For KPIValue (KPIStatus and KPI Goal works similarly and are further ignored) I have got following definition:

    Code Snippet

    (KPIValue("Logged Billable Project Hours versus Target"),[Employee].[Is Active].&[Active])

    There are three levels in my scorecard and I will show the generated MDX which I captured with SQL Profiler:

    Lowest level: employee (works fine)

    Code Snippet

    WITH

    MEMBER [Measures].[CUSTOM_GeneratedGUID] as '((KPIValue("Logged Billable Project Hours versus Target"),[Employee].[Is Active].&[Active]))'

    MEMBER [Measures].[GeneratedGUID] as '([Measures].[CUSTOM_GeneratedGUID])'

    SELECT {[Measures].[GeneratedGUID]} ON AXIS(0),

     

    {([Time].[Year to Month].[Month].&[2008-01-01T00:00:00],[Employee].[Employee].&[8],[Employee].[Employee Role].&[Developer])}

     

    ON AXIS(1)

    FROM [Timesheet DW]

     

     

    Middle level: role (works fine)

    Code Snippet

    WITH

    MEMBER [Measures].[CUSTOM_GeneratedGUID] as '((KPIValue("Logged Billable Project Hours versus Target"),[Employee].[Is Active].&[Active]))'

    MEMBER [Measures].[GeneratedGUID] as '([Measures].[CUSTOM_GeneratedGUID])'

    SELECT {[Measures].[GeneratedGUID]} ON AXIS(0),

     

    {([Time].[Year to Month].[Month].&[2008-01-01T00:00:00],[Employee].[Employee Role].&[Developer])}

     

    ON AXIS(1)

    FROM [Timesheet DW]

     

     

    Highest level: total of roles in scorecard (Works not fine)

    Code Snippet

    WITH

    MEMBER [Measures].[CUSTOM_GeneratedGUID] as '((KPIValue("Logged Billable Project Hours versus Target"),[Employee].[Is Active].&[Active]))'

    MEMBER [Measures].[GeneratedGUID] as '([Measures].[CUSTOM_GeneratedGUID])'

    SELECT {[Measures].[GeneratedGUID]} ON AXIS(0),

     

    {([Time].[Year to Month].[Month].&[2008-01-01T00:00:00])}

     

    ON AXIS(1)

    FROM [Timesheet DW]

     

     

    Because the scorecard doesn't understand the hierarchies, on the highest level it doesn't ask the total for the specific roles in the scorecard, but it asks for all roles (no roles specified as you can see in last snippet means all roles).

    I have only control over the tuple expression which I marked as orange, because that's what I can edit in Dashboard Designer.

     

    However I can not think of a tuple expression that shows the right result for all levels based on the MDX generation of PerformancePoint.

    Tuesday, October 28, 2008 11:16 AM
  • My current workaround is to add a named calculation in the Employee Dimension for every KPI. Like this:

     

    Code Snippet

    CASE
     WHEN [Employee Role] = 'Developer'
      THEN 'Total'
     WHEN [Employee Role] = 'Project Manager'
      THEN 'Total'
     WHEN [Employee Role] = 'Support'
      THEN 'Total'
     ELSE 'No Total'
    END

     

     

    Later I can drag the "Total" member to the scorecard and this will only include Developers, Project Managers and Support. It's not neat to adjust the cube in order to let the scorecard works properly, but I can't think of a better solution now.

    Thursday, October 30, 2008 10:57 AM