Key Performance Indicators (KPIs) are measurements used to gauge business performance against quantifiable goals.  SQL Server Analysis Services provides functionality to define KPI calculations and associate KPIs to measure groups in a multidimensional cube.

SQL Server Analysis Services KPIs consist of the following properties:

• Value Expression: An MDX expression that returns the KPIs actual value.
• Goal Expression: An MDX expression that returns the KPIs target value.
• Status Expression: An MDX expression that returns the KPIs state at a point in time.  The MDX expression must return a value between 1 and –1.  Values of –1 or lower are interpreted negatively. A value of 0 is interpreted as neutral. Values of +1 or higher are interpreted positively.
• Trend Expression: An MDX expression that evaluates the KPIs performance over time. 

Several other properties are associated with KPIs, but the four listed above are the most crucial.

To create a KPI, click the ‘KPIs’ tab in BIDS or SSDT.

Then click the ‘New KPI’ button.

You should now see the KPI form displayed below:

First provide a name for the KPI and select the appropriate measure group.  In this example, my KPI is named ‘Sales Quota’ and it is associated to a measure group with the same name.

You must now specify a ‘Value Expression’.  In this example, my value expression is simply the ‘Sales Amount’ measure.

Now specify a ‘Goal Expression’.  The goals are already stored in a measure called ‘Sales Quota’ in this example.

Once you’ve specified Value and Goal expressions, you may define the Status Expression.

The following business rules apply to the status calculation in this example:

• If no KPI goal exists, then the status is neutral.
• If the KPI value divided by the KPI goal is greater than or equal to 100%, the status is positive.
• If the KPI value divided by the KPI goal is greater than or equal to 90% but less than 100%, then the status is neutral.
• If the KPI value divided by the KPI goal is less than 90%, the status is negative.

In addition to specifying a Status Expression, you must also select a ‘Status indicator’ that specifies the types of icons that will be used to indicate the status.

After specifying the Status expression and indicator, you may now specify a Trend expression and indicator.

The following business rules apply to the trend calculation in this example:

• If any of the following criteria are true, then we cannot evaluate a trend and return neutral.
  • No KPI goal exists.
  • The prior date period falls within the prior fiscal year.
  • No date period is specified (the user has not sliced by Date in the cube).
• If the KPI value divided by the KPI goal in the current date period is less than the KPI value divided by the KPI goal for all prior date periods within the same fiscal year (not including the current date period), then the trend is negative.
• If the KPI value divided by the KPI goal in the current date period is equal to the KPI value divided by the KPI goal for all prior date periods (not including the current date period), then the trend is neutral.
• If the value divided by the goal in the current period is greater than the value divided by the goal for all prior periods (not including the current period), then the trend is positive

The complete MDX expression is displayed below:

The ‘Additional Properties’ section contains the following properties that can be configured if necessary or desired:

• Display Folder: Categorizes the KPI for client application display purposes.
• Parent KPI: Defines an existing KPI under which the current KPI is categorized.  If this property is set, then ‘Display Folder’ is ignored.
• Current Time Member: An MDX expression that returns a time member that specifies the temporal context of the KPI.
• Weight: A weighting factor that is applied to the KPI.
• Description: Description of the KPI.

If you’d like to learn more about SSAS KPIs, another excellent article can be found here.

comments powered by Disqus