Key Performance Indicators (KPIs) | Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

Key Performance Indicators, most often called KPIs, may also be referred to as Key Success Indicators (KSIs). Regardless of what you call them, they can help your organization define and measure quantitative progress toward organizational goals. Business users often manage organizational performance using KPIs. Many business application vendors now provide performance management tools (namely dashboard applications) that collect KPI data from source systems and present KPI results graphically to end business users. Microsoft Office Business Scorecard Manager 2005 is an example of a KPI application that can leverage the KPI capabilities of Analysis Services 2005.

Analysis Services 2005 provides a framework for categorizing the KPI MDX expressions for use with the business data stored in cubes. Each KPI uses a predefined set of data roles — actual, goal, trend, status, and weight — to which MDX expressions are assigned. Only the metadata for the KPIs is stored by an Analysis Services instance, while a new set of MDX functions for applications is available to easily retrieve KPI values from cubes using this metadata.

The Cube Designer provided in Business Intelligence Development Studio (BIDS) also lets cube developers easily create and test KPIs which you learn in the following section. Figure 9-25 shows the KPIs in the Adventure Works cube using the KPI browser in the Cube Designer. You can get to the KPI browser by clicking on the KPI tab in the Cube Designer and then clicking on the KPI browser icon (second icon in the tool bar in the KPI tab).

image from book
Figure 9-25

KPI Creation

Consider the following scenario: The Adventure Works sales management team wants to monitor the sales revenue for the new fiscal year. Sales revenue for prior fiscal years is available in the Adventure Works cube. The management team has identified the goal of 15% growth for sales revenue year over year. If current sales revenue is over 95% of the goal, sales revenue performance is satisfactory. If, however, the sales revenue is within 85% to 95% of the goal, management must be alerted. If the sales revenue drops under 85% of the goal, management must take immediate action to change the trend. These alerts and calls to action are commonly associated with the use of KPIs. The management team is interested in the trends associated with sales revenue; if the sales revenue is 20% higher than expected, the sales revenue status is great news and should be surfaced as well — it's not all doom and gloom.

Use the following steps to design the KPIs for the sales management team:

Open the Adventure Works DW sample project located at C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise.
Double-click the Adventure Works cube in Solution Explorer to open the Cube Designer.
Click the KPIs tab to open the KPI editor.
Click the New KPI icon in the KPI toolbar to open a template for a new KPI. As you can see in Figure 9-26, there are several properties to fill in.

Figure 9-26
Type Sales Revenue KPI in the Name text box and then choose Sales Summary in the drop-down box for Associated Measure Group. The revenue measure is Sales Amount which is included in the Sales Summary measure group.
Type the following MDX expression in the Value Expression text box.
```
     [Measures].[Sales Amount] 
```
When managers browse the KPI, the value of Sales Amount value will be retrieved from the cube.
Now you need to translate the sales revenue goal to increase 15% over last year's revenue into an MDX expression. Put another way, this year's sales revenue goal is 1.15 times last year's sales revenue. Use the ParallelPeriod function to get the previous year's time members for each current year time member. Type the resulting MDX expression, shown below, in the Goal Expression text box.
```
     1.15 *         (           [Measures].[Sales Amount],           ParallelPeriod           (             [Date].[Fiscal].[Fiscal Year], 1,             [Date].[Fiscal].CurrentMember           )         ) 
```
In the Status section of the KPI template, you can choose a graphical indicator for the status of the KPI to display in the KPI browser. You can see several of the available indicators in Figure 9-27. For your own KPI applications, you must programmatically associate the KPI status with your own graphical indicator. For now, select the Traffic Light indicator. The MDX expression that you define for status must return a value between -1 and 1. The KPI browser displays a red traffic light when the status is -1 and a green traffic light when the status is 1. When the status is 0, a yellow traffic light displays.

Figure 9-27
Type the following expression in the Status Expression text box.
```
     Case       When KpiValue ("Sales Revenue KPI")/KpiGoal ("Sales Revenue KPI" )>=.95         Then 1       When KpiValue ("Sales Revenue KPI")/KpiGoal ("Sales Revenue KPI")<.95         And           KpiValue ("Sales Revenue KPI")/KpiGoal ("Sales Revenue KPI")>=.85         Then 0         Else -1     End 
```
The above expression uses the Case MDX statement available for use with Analysis Services 2005. In addition, you now have a set of MDX functions to use with KPI metric values. In the previous MDX expression, the KpiValue function retrieves the value of Sales Revenue KPI, and the KpiGoal function retrieves the goal value of Sales Revenue KPI. More precisely, the KpiValue function is a member function that returns a calculated measure from the Measures dimension. By using KPI functions, you can avoid a lot of typing if your value or goal expression is complex. This Status expression will return one of three discrete values — 1 if revenue exceeds 95% of goal, 0 if revenue is between 85%-95% of goal, and -1 if revenue is below 85% of goal.

Choose the default indicator (Standard Arrow) for Trend indicator. Type the following MDX expression in the Trend Expression text box. This expression compares current KPI values with last year's values from the same time period to calculate the trend of the KPI.

     Case         When (               KpiValue ( "Sales Revenue KPI" )-               (                 KpiValue ( "Sales Revenue KPI" ),                 ParallelPeriod                 (                     [Date].[Fiscal].[Fiscal Year],                     1,                     [Date].[Fiscal].CurrentMember                 )              )) /              (                KpiValue ( "Sales Revenue KPI" ),                ParallelPeriod                (                  [Date].[Fiscal].[Fiscal Year],                  1,                  [Date].[Fiscal].CurrentMember                )              )             <=-.02         Then -1         When ( KpiValue ( "Sales Revenue KPI" ) -              (                 KpiValue ( "Sales Revenue KPI" ),                ParallelPeriod                (                  [Date].[Fiscal].[Fiscal Year],                  1,                  [Date].[Fiscal].CurrentMember                )               )) /               (                 KpiValue ( "Sales Revenue KPI" ),                 ParallelPeriod                 (                   [Date].[Fiscal].[Fiscal Year],                   1,                   [Date].[Fiscal].CurrentMember                 )             ) >.02         Then 1         Else 0     End

Expand the Additional properties section at the bottom of the KPI template to type a name in the Display Folder combo box for a new folder, or to pick an existing display folder. The KPI browser will show all KPIs in a folder separate from other measures and dimensions, but you can further group related KPIs into folders and subfolders. A subfolder is created when the folder names are separated by a backslash, "\." In the Display Folder combo box, type SampleKPI\RevenueFolder as shown in Figure 9-28.

image from book
Figure 9-28

You can also choose to set Parent KPI so that the KPI browser displays KPIs hierarchically. Using the Parent KPI setting is for display purposes only and doesn't actually create a physical relationship between parent and child KPIs. You could, however, design a Parent KPIs that uses values from child KPIs via KPI functions; there is even a Weight expression to adjust the value of a Parent KPI. The display folder setting is ignored if you select a Parent KPI because the KPI will display inside its parent's folder. To complete your KPI, leave the Parent KPI as (None).

Congratulations, you just created your first KPI! Deploy the project to an instance of Analysis Services 2005 so you can view the KPI values. To deploy, select the Build menu item and then select Deploy Adventure Works DW. Like MDX scripts, KPI definitions are only metadata, so changing and saving the KPI definitions will only update the metadata store. A cube reprocess is not required, allowing you to use a KPI right after deploying it to the Analysis Services instance.

To view the KPI, follow these steps:

In the cube designer, click the Browser View icon in the KPI toolbar, as shown in Figure 9-29.

Figure 9-29

Your new KPI is at the bottom of the view window and should look like Figure 9-30

Figure 9-30
The KPI browser supports the standard slicer window at the top of the browser. You can select specific members to narrow down the analysis to areas of interest. For example, suppose you are interested in the sales revenue KPI for August 2003. In the slicer window, select the Date dimension, Fiscal hierarchy, and August 2003 (found in semester H1 FY 2004 and quarter Q1 FY 2004) as shown in Figure 9-31.

image from book
Figure 9-31

You will notice the KPI values have changed as shown in Figure 9-32, as have the Goals — August beats the goal!

image from book
Figure 9-32

KPIs In Depth

Every Analysis Services cube can have an associated collection of KPIs, and each KPI has five properties as its set of metadata. These properties are MDX expressions that return numeric values from a cube as described in the following table.

KPI Specific Properties	Description
Value	An MDX expression that returns the actual value of the KPI. It is mandatory for a KPI.
Goal	An MDX expression that returns the goal of the KPI.
Status	An MDX expression that returns the status of the KPI. To best represent the value graphically, this expression should return a value between -1 and 1. Client applications use the status value to display a graphic indicator of the KPI trend.
Trend	An MDX expression that returns the trend of the KPI over time. As with Status, the Trend expression should return a value between -1 and 1. Client applications use the trend value to display a graphic indicator of the KPI trend direction.
Weight	An MDX expression that returns the weight of the KPI. If a KPI has a parent KPI, you can define weights to control the contribution of this KPI to its parent.

Analysis Services 2005 creates hidden calculated members on measure dimensions for each KPI metric (value, goal, status, trend, and weight). However, if a KPI expression directly references a measure, Analysis Services optimization uses the measure directly instead of creating a new calculated measure. You can query the calculated measure used for KPIs in an MDX expression, even though it's hidden.

To see how this works, open SSMS, and connect to Analysis Services. Click the Analysis Services MDX Query icon in the toolbar to open a new MDX query window. Make sure you're connected to the Adventure Works DW database in the Available Databases list box, type the following query in the MDX query window, and then click the Execute button.

     SELECT {Measures.[Sales Revenue KPI Goal] } ON 0,     [Date].[Fiscal].[Fiscal Quarter].members on 1     FROM [Adventure Works]

Figure 9-33 shows the results of executing the query.

image from book
Figure 9-33

Using ADOMD.NET to Query KPIs

The Analysis Services instance hosting the database cubes also maintains the KPI definition metadata. As you learned in the previous section, you can access KPI values directly by using KPI functions. Client applications can also access this KPI metadata information and retrieve values programmatically through an Analysis Services client-side component ADOMD.NET.

ADOMD.NET provides native support for KPIs. It includes a KPI class that contains a method called Kpi.Properties ("KPI_XXX"), which is used to retrieve properties of each KPI. This method returns a string of unique measures for the developer to use in the construction of MDX queries that retrieve the KPI values. The following code example demonstrates how to access a KPI using ADOMD.NET and how to construct a parameterized MDX query. Because KPI metrics are just calculated measures, you execute a KPI query with ADOMD.NET the same way you execute regular MDX queries.

     using System;     using System.Collections.Generic;     using System.Text;     using Microsoft.AnalysisServices.AdomdClient;     namespace QueryKPIs     {         class Program         {             static void Main (string[] args)             {                 string connectionString = "Provider = MSOLAP.3;Data     Source=localhost;Initial Catalog=Adventure Works DW";                 AdomdConnection acCon = new AdomdConnection (connectionString);                 try                 {                     acCon.Open ();                     CubeDef cubeObject = acCon.Cubes["Adventure Works"];                     foreach (Microsoft.AnalysisServices.AdomdClient.Kpi cubeApi in     cubeObject.Kpis)                     {                         string commandText = @"SELECT { strtomember (@Value),     strtomember (@Goal), strtomember (@Status), strtomember (@Trend) }           ON COLUMNS FROM [" + cubeObject.Name + "]";                         AdomdCommand command = new AdomdCommand (commandText, acCon);                         foreach (Microsoft.AnalysisServices.AdomdClient.Kpi kpi in     cubeObject.Kpis)                         {                             command.Parameters.Clear ();                             command.Parameters.Add (new AdomdParameter ("Value",     kpi.Properties["KPI_VALUE"].Value));                             command.Parameters.Add (new AdomdParameter ("Goal",     kpi.Properties["KPI_GOAL"].Value));                             command.Parameters.Add (new AdomdParameter ("Status",     kpi.Properties["KPI_STATUS"].Value));                             command.Parameters.Add (new AdomdParameter ("Trend",     kpi.Properties["KPI_TREND"].Value));                             CellSet cellset = command.ExecuteCellSet ();                             Console.WriteLine ("KPI Name:" + kpi.Name);                             Console.WriteLine ("Value:" +     cellset.Cells[0].FormattedValue);                             Console.WriteLine ("Goal:" +     cellset.Cells[1].FormattedValue);                             Console.WriteLine ("Status:" +     cellset.Cells[2].FormattedValue);                             Console.WriteLine ("Trend:" +     cellset.Cells[3].FormattedValue);                         }                     }                 }                 finally                 {                     acCon.Close ();                 }             }         }     }

Note that this example uses a parameterized MDX query and the StrToMember function to avoid MDX injection. The developer of a client-side application needs to be cautious with user input; a simple string concatenation would allow a malicious user to input and run harmful code. You can create a new C# program called QueryKPI, copy the above code, add the Microsoft.AnalysisServices.AdomdClient DLL as a reference and run the program. We recommend you explore the .NET Adomd client object model by writing client programs that leverage the object model.