locked
Importing KPI from SSAS doesn't use status expression? RRS feed

  • Question

  • I have the following KPI defined in SSAS:

     

    Value expression:

    [Measures].[Billable Time Percent of Total]

     

    Status expression:

    Case

    When [Measures].[Billable Time Percent of Total] <= .5

    Then -1

    When [Measures].[Billable Time Percent of Total] > .5 And

    [Measures].[Billable Time Percent of Total] <= .65

    Then 0

    When [Measures].[Billable Time Percent of Total] > .65

    Then 1

    End

     

    When you create a scorecard and select a SSAS cube as data source, then you are able to import a KPI from SQL Server Analysis Services.

     

    When I take a look at the thresholds,  it seems that it doesn't copy the thresholds which I defined. Is this simply not included in teh import or am I doing something wrong?

     

    Thanks in advance for your help.

    Tuesday, June 17, 2008 11:00 AM

Answers

  • Hi Chris,

     

    It's a little hard to find, I'll admit. The Band by stated score (advanced) is used here because it allows us to use values from two different data sources. The first gets the KPIGoal value, this is the number that will be visible to the user (or the Target value). This banding method also allows a completely separate data source to use to drive the behaviour of the indicator - that's the KPIStatus query. The number that is brought back by this query is compared to the threshold boundaries that you see.

     

    Using the Band by stated score (advanced) method we can show the valus of the KPIGoal and have the indicator driven by the value of KPIStatus.

     

    Follow these steps to find where the KPIStatus formula is configured within your KPI

    1. Select the Goal and Status metric
    2. Click on the Set scoring pattern and indicator button in the thresholds area
    3. Click Next twice
    4. Click the Specify Data Mapping button
    5. The MDX tuple formula for Status should be at the bottom of the screen

    Cheers,

    Nick

    Wednesday, June 18, 2008 9:47 AM

All replies

  • Hi Chris,

     

    The "Import" part of the name is a little misleading here. What actually happens when you run this wizard is it simply creates PPS KPIs that point towards (or reference) the definition in your SSAS cube. The PPS version is not meant to be a copy of the SSAS KPI that can then be further developed (like changing threshold values). The threshold settings and all other parts of the KPI definition will remain in the SSAS object.

     

    You'll notice that each of the metrics within the PPS KPIs that have been generated by the wizard all use Custom MDX formulas to reference the values from SSAS e.g. KPIGoal("Channel Revenue"), KPIStatus("Channel Revenue"). A common gotcha: the Goal and Status metric that is created references both the Goal and Status portions of the SSAS KPI. The KPIStatus formula is tucked away within the threshold settings by virtue of the Band by stated score (advanced) banding method.

     

    Hope this helps.

     

    Cheers,

    Nick

    Tuesday, June 17, 2008 8:26 PM
  • Hi Nick,

     

    Thanks for your reply.

     

    When I use SQL Profiler, I see indeed that KPIGoal, KPIStatus and KPIValue formulas are executed. Also the thresholds from SSAS are used!! I got confused, because when I click on "Goal and Status" in the KPI, I see the standard thresholds

    Best: 1

    Threshold 2: 0.5

    Threshold 1 : -0.5

    Worst: -1

    It seems that these are not used, because it gets the actual status by sending a KPIStatus call to SSAS.

     

    When I look for Data Mapping for "Value", I see the formula KPIValue. When I look under "Goal and Status" I see only the formula KPIGoal and not KPIStatusWhere can I find the definition of KPIStatus in Dashboard Designer which will be sent to SSAS?

     

    Cheers,

    Chris

     

    Wednesday, June 18, 2008 9:15 AM
  • Hi Chris,

     

    It's a little hard to find, I'll admit. The Band by stated score (advanced) is used here because it allows us to use values from two different data sources. The first gets the KPIGoal value, this is the number that will be visible to the user (or the Target value). This banding method also allows a completely separate data source to use to drive the behaviour of the indicator - that's the KPIStatus query. The number that is brought back by this query is compared to the threshold boundaries that you see.

     

    Using the Band by stated score (advanced) method we can show the valus of the KPIGoal and have the indicator driven by the value of KPIStatus.

     

    Follow these steps to find where the KPIStatus formula is configured within your KPI

    1. Select the Goal and Status metric
    2. Click on the Set scoring pattern and indicator button in the thresholds area
    3. Click Next twice
    4. Click the Specify Data Mapping button
    5. The MDX tuple formula for Status should be at the bottom of the screen

    Cheers,

    Nick

    Wednesday, June 18, 2008 9:47 AM
  • Hi Nick,

     

    Thank you very much!

     

    I think it's still misleading that the "standard" thresholds are displayed (see my previous post), but they are not used at all! Anyway I now know how it works :-)

     

    Cheers,

    Chris.

    Wednesday, June 18, 2008 10:09 AM
  • Hi Chris,

     

    The threshold boundaries are indeed used but the default values in Dashboard Designer (DD) are not generated from the KPI MDX defined in the cube. Remember that the number you see is the KPIGoal. The KPIStatus number drives the indicator under the covers. Try experimenting by changing the threshold boundaries within DD.

     

    Cheers,

    Nick

    Wednesday, June 18, 2008 10:26 AM