Printed 2017/08/19 06:49PM

SQL Server Analysis Services KPIs

By Jennifer Salvo, 2013/01/14

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:

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:


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:


The complete MDX expression is displayed below:


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

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

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.