Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

SQL Server Analysis Services KPIs

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.

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:

  • 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.

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:

  • 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

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:

  • 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

Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...