http://www.sqlservercentral.com/blogs/salvoz-sql/2013/01/14/sql-server-analysis-services-kpis/

Printed 2014/08/23 02:08PM

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.

image

Then click the ‘New KPI’ button.

image

You should now see the KPI form displayed below:

image

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.

image

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

image

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

image

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:

image

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:

image

The complete MDX expression is displayed below:

image

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-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.