Mastering Enterprise BI: Introduction to Key Performance Indicators

Monday May 8th 2006 by William Pearson
Share:

Architect Bill Pearson introduces a powerful feature that debuts in Analysis Services 2005: Key Performance Indicators (KPIs). In this article, we create a KPI, based partially upon a calculated member we construct to support it.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.

Note: To follow along with the steps we undertake within this series, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine

  • Microsoft SQL Server 2005 Analysis Services

  • Microsoft SQL Server 2005 Integration Services

  • Business Intelligence Development Studio

  • Microsoft SQL Server 2005 sample databases

  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:

    • the Administrators local group on the Analysis Services computer

    • the Server role in the instance of Analysis Services

  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, as appropriate.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

About the Mastering Enterprise BI Articles ...

Having implemented, and developed within, most of the major enterprise BI applications for over for fourteen years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I have become convinced that the components of the Microsoft integrated BI solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of Cognos (and other) enterprise BI to the Microsoft solution. In addition to converting formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel those found in the more "mature" enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost. The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the dominant enterprise BI vendors, to date, represents a serious "undersell" of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves – and, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the Microsoft solution will commoditize Business Intelligence.

For more information about the Mastering Enterprise BI articles, see the section entitled "About the Mastering Enterprise BI Articles" in my article Relative Time Periods in an Analysis Services Cube, Part I.

Introduction

A Key Performance Indicator ("KPI") is a performance measure that specifies an emphasis intended to produce a desired result. As quantifiable measurements that gauge business success, KPIs can be diverse in nature, but they typically represent values, statuses, trends, or goals. KPIs often combine two or more of these into a single indicator. Because their purpose is to present high level, summary information to executives or other managers of the enterprise, who then use the information to steer an integrated organization, we typically limit the number of these vital measures to only a few.

KPIs are usually graphic in nature, and are collected into a dashboard, scorecard, or other central point of reference, where they can be easily accessed by the management team, to obtain a quick and accurate summary of business success or progress toward success. KPIs make it possible for management to make decisions and take action. Based upon the information the indicators convey, managers can pull the processes and activities that the KPIs represent into alignment with strategic goals and objectives.

KPIs can range from simple to complex, and they are often evaluated over time. A simple KPI (represented, say, by a smiling or frowning "Smiley" face image) might indicate a status of monthly employee turnover or headcount, either of which might represent a single, but important, key measure. A more complex KPI might combine multiple perspectives (a status, with regard to corporate market share for a given product, coupled with a trend, such as competitor market share over the past few months for a similar product) into a single graphic (say a directional arrow or a traffic signal), indicating that the enterprise is leading in share, that share is eroding, or even that it has been overtaken by its competition. KPIs can be used alone or in combination to support rapid management decisions and actions, and to provide an indication of progress toward the accomplishment of intended strategic outcomes.

In this article, we will examine KPIs, and get hands-on exposure to the process of adding them to a sample cube, within the Business Intelligence Development Studio. We will overview the creation of KPIs based upon measures and calculated measures, and discuss ways in which to use them to offer robust decision support capabilities. As a part of our examination of the steps, we will:

  • Discuss the concepts behind KPIs, and how we can leverage them within our business environments for decision support, analysis and reporting purposes;

  • Create a clone Analysis Services Database in SQL Server Management Studio, within which to conduct our practice exercises;

  • Access the Analysis Services Database from within Business Intelligence Development Studio to perform our practice exercises;

  • Create a calculated measure to partially support the KPI we create in our practice session;

  • Create a KPI, demonstrating examples of input for various expressions within its designer form;

  • Browse the KPI, to verify operation and overview basic features.

Introducing Key Performance Indicators

Overview and Discussion

In Analysis Services 2005, Key Performance Indicators are composed of calculations that are associated with a measure group (or, alternatively, all measure groups) within a cube. MDX expressions, regular measures and calculated measures, often in combination, are used to generate KPIs. Because KPIs in Analysis Services contain additional metadata, they can direct the way that the results of their underlying calculations can be displayed in client applications.

Different client applications can consume Analysis Services KPIs because they are server-based, offering advantages in presentation flexibility. Other strengths of the server-based indicators include the obvious performance benefits that accrue through the performance of what are often complex calculations on the server rather than on the client machines, as well as the fact that they clearly provide a "single version of the truth" versus separate, potentially differing versions in separate client applications.

Analysis Services KPIs are supported by measures or calculated measures, and, as we shall see in the practice section, they have multiple properties which we define as part of their creation. KPI properties include a Value Expression, a Goal Expression, a Status Indicator and a Status Expression, and a Trend Indicator, and a Trend Expression. (We will discuss each of these as we add them in our practice KPI later.) We can also define a Parent KPI, a Current Time Member, a Weight, and a Description. Moreover, we can logically group our KPIs through the use of a Display Folder.

The Goal and Value are usually the two most important properties of the KPI. The Value represents the "current state" of the measure, whereas the Goal represents the "desired state" of the measure, or the state toward which the organization is striving. As we shall see, the use and comparison of these two properties helps us to derive the Status and Trend of the Key Performance Indicator.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. These samples include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW Analysis Services database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed. The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation.

Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in the references I have noted.

Hands-On Procedure

Let's get some hands-on practice with the construction and use of KPIs. To prepare, we will create a new Analysis Services database, based upon the existing Adventure Works DW sample, to insulate the original sample database from modifications we will make. We will accomplish the creation of the "clone" database from within the SQL Server Management Studio, and then work with our new database from the Business Intelligence Development Studio.

Preparation

Create a Clone Analysis Services Database in SQL Server Management Studio

We will begin our preparation within SQL Server Management Studio, where we will create a clone of the sample Adventure Works DW database, which can be installed by anyone implementing MSSQL Server 2005 Analysis Services.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.


Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name / instance, if appropriate) into the Server name selector.

6.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears, with the appropriate input for our local environments, similar to that depicted in Illustration 2.


Illustration 2: The Connect to Server Dialog, with Representative Settings

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  Within the Object Explorer (the leftmost pane of the Studio, by default), expand the server in which we are working, if necessary, by clicking the "+" sign to its immediate left.

9.  Expand the Databases folder that appears underneath the expanded server.

10.  Right-click the Adventure Works DW database.

11.  Select Back Up... from the context menu that appears, as shown in Illustration 3.


Illustration 3: Right-click the Adventure Works DW Database – Select Back Up ...

The Backup Database – Adventure Works DW dialog appears.

12.  Replace the default name that appears in the Backup file box with the following:

ANSYS047 Adventure Works DW.abf

13.  Uncheck the Apply compression setting in the Options section.

14.  Uncheck the Encrypt backup file setting that immediately follows.

The relevant portion of the Backup Database – Adventure Works DW dialog appears, as depicted in Illustration 4.


Illustration 4: The Backup Database – Adventure Works DW Dialog Appears

15.  Click OK to begin the backup.

The Backup Database – Adventure Works DW dialog grays, as the Executing symbol in the Progress pane (lower left corner of the dialog) becomes active. The process may run several minutes depending upon the resources available on the local system. Once completed, the dialog closes, returning us to the Management Studio.

We will now restore the same backup under a different name, to create a copy of the existing sample database – a copy wherein we can make modifications without impairing the existing sample, which we may wish to use to complete tutorials included with MSSQL Server 2005 or elsewhere.

16.  Within the Object Explorer, right-click the Databases folder underneath the Adventure Works DW database.

17.  Select Restore... from the context menu that appears, as shown in Illustration 5.


Illustration 5: Right-click the Databases Folder – Select Restore ...

The Restore Database dialog appears.

18.  Click the Browse button to the right of the box (second from the top) labeled From backup file.

The Locate Database Files dialog appears.

19.  Navigate to the following backup file (where we located it in our backup steps above):

ANSYS047 Adventure Works DW.abf

20.  Click the file within the Select the file window, to place the file name into the File name box, as depicted in Illustration 6.


Illustration 6: Locate Database Files Dialog with Our Input ...

21.  Click OK to accept the file path / name, and to close the Locate Database Files dialog.

We return to the Restore Database dialog, where we see the file we have selected appear in the From backup file box.

22.  Type the following into the Restore database box immediately above the From backup file box:

ANSYS047 Adventure Works DW

23.  Leave all other settings at default.

The affected portion of the Restore Database dialog, with our input, appears as shown in Illustration 7.


Illustration 7: The Completed Restore Database Dialog

24.  Click OK to initiate the restoration.

The Restore Database dialog grays, as the Executing symbol in the Progress pane, once again, becomes active. The process runs, and, once it completes, the dialog closes, returning us to the Management Studio. Here we see the new ANSYS047 Adventure Works DW database appear in the Object Browser, as depicted in Illustration 8.


Illustration 8: The New Database Appears ...

NOTE: If the new database does not appear immediately, right-click the Databases folder and select Refresh from the context menu that appears, as shown in Illustration 9.


Illustration 9: Refreshing as Required ...

Having created the ANSYS047 Adventure Works DW database, we can now proceed with the practice portion of our session, and get some hands-on experience with KPIs in Analysis Services 2005. To do so, we will move to the Business Intelligence Development Studio, closing SQL Server Management Studio for the present.

25.  Select File -> Exit from the main menu in the SQL Server Management Studio.

Preparation

Access the Analysis Services Database in Business Intelligence Development Studio

We will access the new Analysis Services database we have created within the Business Intelligence Development Studio, where we can best accomplish design and development work in general. While we certainly might begin within the BI Development Studio by creating an Analysis Services project, building all the components from scratch, we will take a few shortcuts here to more rapidly get to the subject matter of our session, KPIs.

1.  Click the Start button, once again.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 10.


Illustration 10: Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 11.


Illustration 11: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4.  Close the Start Page tab.

5.  Select File -> Open on the Visual Studio main menu.

6.  Select Analysis Services Database from the cascading menu, as depicted in Illustration 12.


Illustration 12: Opening an Analysis Services Database ...

The Connect to Database dialog appears.

7.  Ensure that the radio button to the immediate left of Connect to existing database is selected.

8.  Type the name of the server / server with instance into the Server box. (Mine is MOTHER1\MSSQL2K5 as seen in various illustrations throughout my articles)

9.  Select the ANSYS047 Adventure Works DW database in the Database selector underneath the Server box.

10.  Click the Browse button to the right of the box labeled New Solution at the bottom of the Connect to Database dialog.

The Browse for directory ... dialog opens.

11.  Navigate to a convenient place to store the objects created within our solution.

12.  Within the Browse for directory ... dialog, once it rests at a convenient place, right-click the white space within the dialog.

13.  Select New from the context menu that appears.

14.  Select Folder from the cascading menu that branches off the New selection, as shown in Illustration 13.


Illustration 13: Select New -> Folder

A folder, with edit-ready title New Folder, appears.

15.  Name the new folder as follows:

ANSYS047 KPIs

The new folder appears as depicted in Illustration 14.


Illustration 14: New Folder to House Our Solution Files ...

16.  With the folder selected, click the Open button.

The Browse for directory ... dialog closes, and we return to the Connect to Database dialog, which appears, with our input, similar to that shown in Illustration 15.


Illustration 15: The Connect to Database Dialog with our Input

17.  Click OK to accept our input, and to connect to the Analysis Services database.

The Connect to Database dialog closes, and we see our clone Analysis Services database open, complete with all member objects, in Solution Explorer (which appears, by default, in the upper right corner of the BI Development Studio), as partially depicted in Illustration 16.


Illustration 16: The Analysis Services Database Appears in Solution Explorer (Partial View)

We are now positioned to get some practice with defining a KPI within a cube. In the next section, we will work within the Adventure Works cube to create a calculated measure, upon which we will partially base our KPI.

Create a Calculated Measure to Support KPI

Let's say that we have been tasked with creating a KPI to add to other indicators that are currently presented within a digital dashboard in use at Adventure Works. Management has called upon us to define and create a KPI, to be called Growth in Internet Orders, which will act as an indicator of the ratio of the Growth in Internet Orders in the current period to that of the previous period. Because the client is unfamiliar with all except the most high-level concepts surrounding Analysis Services KPIs, we will briefly discuss the nature of the various property settings as we proceed.

Before we create our KPI, we will create a Growth in Internet Orders calculated measure, upon which we will partially base the new indicator. While we often have the choice of doing all calculations within the KPI itself, using a calculated measure allows us to make the expressions within the KPI more straightforward, as well as to create reusable building blocks for similar uses, to foster both efficiency and consistency within KPIs and additional cube objects.

Let's open the Adventure Works cube, within the clone Analysis Services database we have created, and begin our practice with the creation of these calculated measures.

1.  Right-click the Adventure Works cube in the Solution Explorer (within the Cubes folder of the tree).

2.  Select Open from the context menu that appears, as shown in Illustration 17.


Illustration 17: Open the Adventure Works Cube ...

The Cube Designer, one of several specialized variants within the BI Intelligence Development Studio (among which this is a member of the Analysis Services project subset) opens, defaulted to the Cube Structure tab. For purposes of this section, we will be working largely within the Calculations tab, where calculated members / measures are conveniently constructed and maintained.

NOTE: I perform in-depth examinations of many of the specific capabilities and features of the SQL Server Business Intelligence Studio (which provides significant support in helping us to work with Analysis Services projects, and to integrate Analysis Services projects with Reporting Services and Integration Services), as well as the SQL Server Management Studio, in other articles of my series' here at Database Journal. In the interest of time and focus, we will limit our discussion in this article to relevant portions of this sophisticated development environment. For more on the features of each of the studios, I invite you to visit my series index page.

3.  Click the Calculations tab atop the Cube Designer, as depicted in Illustration 18.


Illustration 18: Switching to the Calculations Tab ...

The Calculations tab loads.

4.  If necessary, click Form View, whose icon appears just beneath the Calculations tab label, as shown in Illustration 19.


Illustration 19: Click Form View, If Necessary ...

5.  Click the New Calculated Member button appearing atop the Calculations tab, as depicted in Illustration 20.

Illustration 20: Click the New Calculated Member Button ...

The Calculations form appears.

6.  Type the following into the Name text box of the Calculations form.

[Growth in Internet Orders]

7.  Type (or cut and paste) the following MDX syntax into the Expression box underneath the Name box (leaving the settings in between at default):


CASE
  WHEN [Date].[Fiscal].CURRENTMEMBER.LEVEL.ORDINAL = 0
  THEN "NA"
  WHEN ISEMPTY(  
    ([Date].[Fiscal].CURRENTMEMBER.PREVMEMBER, 
        [Measures].[Internet Order Count])) 
  THEN Null
  ELSE( 
    ( [Date].[Fiscal].CURRENTMEMBER, [Measures].[Internet Order Count] ) -
       ( [Date].[Fiscal].PREVMEMBER, [Measures].[Internet Order Count] ))/
           ( [Date].[Fiscal].PREVMEMBER,[Measures].[Internet Order Count])
  END

8.  Select Percent in the Format string box, underneath the Expression box.

9.  Select True in the Visible box, just beneath the Format string box.

The Calculations form, with our input, appears as shown in Illustration 21.


Illustration 21: The Calculation Form for the New Calculated Measure

For purposes of our practice exercise, we are creating a calculated measure that will generate a ratio of the Growth in Internet Orders in the current period to that of the previous period. The advantages of using calculated members / measures include the facts that 1) we can present our calculations compactly, with "friendly" names, and 2) we can ensure that the calculation underneath (in this case, the change in Internet Order Count measure over the prior fiscal period, divided by the Internet Order Count of the prior period – that is, the growth since the previous fiscal year, etc.) is consistently applied for anyone using the calculation.

NOTE: For detailed information on the .CurrentMember and .PrevMember functions, see MDX Member Functions: "Relative" Member Functions, a member of my monthly MDX Essentials series at Database Journal.

10.  Click the Check Syntax button, as depicted in Illustration 22, to check the Expression syntax of the new calculated measure.


Illustration 22: Checking Calculated Measure Expression Syntax ...

The Check syntax message box appears, indicating that "the syntax check was successful," assuming we have typed the MDX expression correctly, as depicted in Illustration 21.


Illustration 23: Message Box: "The Syntax Check Was Successful"

11.  Click OK to dismiss the message box.

12.  Click-select the new Growth in Internet Orders calculated measure in the Script Organizer pane, in the left upper corner of the Calculations tab.

13.  Using the Move down button atop the tab, move the new calculated measure to just below the Expense to Revenue Ratio calculated measure (on or about Line 29 in the Script Organizer), as depicted in Illustration 24.


Illustration 24: Moving the New Calculated Measure below Another Ratio ...

14.  Select File -> Save All from the main menu, to save our work thus far, as shown in Illustration 25.


Illustration 25: Saving Our Work ...

Let's process the Adventure Works cube so that it reflects our changes to its metadata, so that we can proceed with constructing a KPI that is partially reliant upon the new Growth in Internet Orders calculated measure.

15.  Right-click the Adventure Works cube in the Solution Explorer.

16.  Select Process ... from the context menu that appears, as depicted in Illustration 26.


Illustration 26: Select Process ... from the Context Menu

The Process Cube dialog for the Adventure Works cube appears next, as shown in Illustration 27.


Illustration 27: Process Cube Dialog – Default Settings

17.  Click the Run button, at the bottom of the dialog, to begin processing.

Processing begins, and we see the individual steps logged in the Process Progress viewer. Processing soon completes, and we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as depicted in Illustration 28.


Illustration 28: Cube Processing Successfully Completes ...

18.  Click Close to dismiss the Process Progress viewer.

19.  Click Close to dismiss the Process Cube dialog that re-appears.

Having updated cube metadata, we are ready to begin constructing a new KPI.

Procedure

Create a New KPI in the Adventure Works Cube

Let's get some hands-on exposure to the various components of an Analysis Services KPI.

1.  Click the KPIs tab atop the Cube Designer - View pane, as shown in Illustration 29.


Illustration 29: KPIs Tab – Cube Designer, View Pane

2.  Click the New KPI button, atop the newly opened KPIs view, as depicted in Illustration 30.


Illustration 30: New KPI Button – Atop the KPIs View

A new KPI form appears within the KPI Designer. It is here that we will define the new KPI, via several expression fields.

3.  Replace the default ("KPI") in the Name text box, atop the form, with the following:

Growth in Internet Orders

4.  Select Internet Orders within the Associated measure group box underneath the Name box.

5.  Click the Metadata tab, as necessary, to bring it to the front, within the Calculation Tools pane in the bottom left corner (default position) of the KPIs view.

6.  Expand the Measures folder to expose the new Growth in Internet Orders calculated measure, currently freestanding underneath the folders within the Measures folder.

7.  Click and drag the Growth in Internet Orders calculated measure into the Value Expression field of the KPI form, as shown in Illustration 31.


Illustration 31: Adding Growth in Internet Orders Calculated Measure to Value Expression

The Value Expression field, which (unsurprisingly) comprises the value of the KPI, is the only field that forces input.

8.  Type the following into the Goal Expression field:


CASE
   WHEN [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Year]
   THEN .30
   WHEN [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Semester]
   THEN .15
   WHEN [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Quarter]
   THEN .075
   WHEN [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Month]
   THEN .025
   ELSE "NA"
END

The optional Goal Expression field defines a goal, or a target, for the value. With The MDX expression above, we are employing the CASE statement to establish different growth percentage goals for different fiscal periods – "straight-lining" the percentages in a way that they add up to the annual goal of 30 %.

The KPI form, with our input through the Goal Expression field, appears as depicted in Illustration 32.


Illustration 32: The KPI Form through the Goal Expression Field ...

9.  Select the Traffic light image in the drop-down down selector labeled Status indicator under Status.

The Status Indicator selection simply determines the image that will be used, within the KPI Browser, to represent the Status as we define it in the Status Expression in the next step.

10.  Type (or cut and paste) the following syntax into the optional Status expression box underneath the Status indicator selector box.


CASE
   WHEN KpiValue( "Growth in Internet Orders" ) >=
      KpiGoal ( "Growth in Internet Orders" ) 
   THEN 1
   WHEN 
      KpiValue( "Growth in Internet Orders" ) >= 
         .90 * KpiGoal ( "Growth in Internet Orders" )
      AND
         KpiValue( "Growth in Internet Orders" ) < 
            KpiGoal ( "Growth in Internet Orders" ) 
    THEN 0
    ELSE -1
END

The KPI form, with our input for the Status section, appears as shown in Illustration 33.


Illustration 33: The KPI Form: Input for Status Section ...

Analysis Services uses the Status Expression (an MDX expression) to evaluate the current status of the Value Expression compared to the Goal Expression. The values required to drive the Status Indicator are -1 ("very bad"), 0, or 1 ("very good"). The idea, as with all visual KPIs, is to allow an information consumer to reach rapid conclusions about the Value. (It becomes apparent, therefore, that the expression underlying the Value must support precisely the indication that is intended to be present).

Our MDX expression above supports the evaluation of "progress toward the goal." If Growth in Internet Orders is equal to, or more than, the goal itself, a value of 1 ("very good") is used to populate the chosen graphic. This translates, within the context of the Traffic light indicator, as a "Green" light, "illuminated" within the presentation graphic. Moreover, a value of 0 (assigned by the MDX expression when Growth in Internet Orders is equal to, or more than, 90 % of the goal, and yet less than the goal itself), will result in a "Yellow" light. Finally, any Growth in Internet Orders value that is less than management's minimal desired target (those stipulated within the Goal Expression we created earlier) will return a "Red" light.

11.  Select the Status arrow image in the drop-down selector labeled Trend indicator under Trend.

The Trend Indicator selection specifies the image that will be used, within the KPI Browser, to represent the Trend as we define it for the Trend Expression in the next step. Because the Trend represents a KPI within the context of time, an implicit comparison of some sort is virtually always performed between the current Value and (usually) the same Value at some point in the past.

12.  Type (or cut and paste) the following syntax into the optional Trend expression box underneath the Trend indicator selector box.


CASE
    WHEN 
       [Date].[Fiscal].CURRENTMEMBER.LEVEL Is [Date].[Fiscal].[(All)]
    THEN 0
    WHEN 
       VBA!ABS(

           KpiValue( "Growth in Internet Orders" ) - 
             (KpiValue ( "Growth in Internet Orders" ),
                 PARALLELPERIOD([Date].[Fiscal].[Fiscal Year], 1,
                    [Date].[Fiscal].CURRENTMEMBER)) /
                 ( KpiValue ( "Growth in Internet Orders" ),
             PARALLELPERIOD([Date].[Fiscal].[Fiscal Year], 1,
                  [Date].[Fiscal].CURRENTMEMBER))  
        ) <=.02
    THEN 0
    WHEN 
       KpiValue( "Growth in Internet Orders" ) - 
          (KpiValue ( "Growth in Internet Orders" ),
              PARALLELPERIOD([Date].[Fiscal].[Fiscal Year], 1,
                 [Date].[Fiscal].CURRENTMEMBER)) /
                    (KpiValue ( "Growth in Internet Orders" ),

                 PARALLELPERIOD([Date].[Fiscal].[Fiscal Year], 1,
              [Date].[Fiscal].CURRENTMEMBER)
           )  >.02
    THEN 1
    ELSE -1
END

The KPI form, with our input for the Trend section, appears as depicted in Illustration 34.


Illustration 34: The KPI Form: Input for Trend Section ...

Analysis Services uses the KPIs Trend Expression (another MDX expression) to evaluate the current trend of the Value Expression, relative to the Goal Expression. The Trend Expression helps the business user to quickly determine whether the Value Expression is approaching, or getting further from, the Goal Expression. The Trend Expression, like the Status Expression, is designed to return a value ranging between -1 and 1, particularly if we are intending to use the Analysis Services 2005 indicators. (These indicators are designed for use exclusively within the KPI Browser.)

We will wrap up the construction of our new KPI with a couple of final settings.

13.  In the Additional Properties section, just below the Trend Expression field, select Customer Perspective\Expand Customer Base within the dropdown selector labeled Display folder.

14.  In the Description field, type the following description of the KPI:

The ratio of operating expenses to gross profit.

15.  Leave other settings at default.

The Additional Properties section of the KPI form, with our input, appears as shown in Illustration 35.


Illustration 35: The KPI Form: Additional Properties Section

We can place KPIs in folders that match our business units, processes, functions, and other classifications – we can even nest the folders when that is useful. Organization of this sort can help authors, developers and consumers alike find KPIs in which they have an interest.

While we will not go further than the basics in this session, we can define several additional properties. In addition to selecting a display folder, we can specify a Parent KPI if the KPI is computed from other KPIs, a Current Time Member if appropriate, Weight information for the KPI in scenarios where we wish to employ weightings, and a Description of the KPI. We will encounter most of these properties multiple times in other, more concentrated articles within this and other of my series.

16.  Select File -> Save All to preserve our work to this point.

Verification

View the New KPI in the KPI Browser

Let's process the cube again, and then view the new KPI in the KPI Browser.

20.  Select File -> Save All from the main menu, to save our work thus far, as we did earlier.

21.  Right-click the Adventure Works cube in the Solution Explorer.

22.  Select Process ... from the context menu that appears, as before.

The Process Cube dialog for the Adventure Works cube appears next, as it did earlier.

23.  Click the Run button, as before, to begin processing.

Processing begins, and we see the individual steps logged in the Process Progress viewer, once again. Processing soon completes, and we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as we noted earlier.

Having updated cube metadata, we are ready to browse the cube's KPIs, including our new Growth in Internet Orders KPI.

24.  Click the Browser View button atop the KPIs view, as depicted in Illustration 36.


Illustration 36: Click the Browser View Button ...

Our new KPI, Growth in Internet Orders, appears among the rest, where it also appears to be functioning as expected. The general KPI Browser view appears as shown in Illustration 37.


Illustration 37: General KPI Browser View (Compressed)

The small blue icon to the right of each KPI (with an "i" on it to signify "information") serves dual purposes. If it is a blue "information" icon, we can display the KPI description we have assigned with a mouseover, as a tool tip. If, however, we encountered errors in generating the KPI (say, one of the MDX expressions we employed within the various expression fields was incorrect, incomplete or both), a red error icon would appear instead.

We can see with the KPI that we have a "green light" status ("very good"), and a "stable" trend. The Growth in Internet Orders KPI appears alone in Illustration 38, along with the tool tip caption that appears on the mouseover of the "i" icon.


Illustration 38: General KPI Browser View

We can visualize an array of decision support, analysis and reporting capabilities at this point, once we add in filters at various dimensional levels. In addition, we can go beyond the KPI Browser, and display KPIs in various ways in client applications. (I will be doing so within my Reporting Services series and elsewhere, to present examples of "KPIs beyond the KPI Browser"). For now, we will conclude our article, having focused upon the construction of a basic KPI to demonstrate the "multidimensional" information that these indicators can convey.

25.  Examine other features of the KPI Browser with our Growth in Internet Orders KPI, as well as the pre-existing sample KPIs, as desired.

26.  Select File -> Save All from the main menu, to save our work through this point, as we have done in earlier steps.

27.  Select File -> Exit, when ready, to leave the Business Intelligence Development Studio.

Conclusion

In this article, we examined Key Performance Indicators. We discussed the concepts behind KPIs, and ways that we can leverage them within the business environment for decision support, analysis and reporting purposes. Once we overviewed the features and operations of these high level, summary indicators, we set about getting some hands-on experience creating a working KPI to meet a hypothetical client need. We prepared for our practice session by "cloning" an Analysis Services Database in SQL Server Management Studio. We then shifted to Business Intelligence Development Studio, from which we accessed our database and conducted our practice exercises.

We next created a calculated measure to partially support a KPI we constructed, (the focus of our practice session). We then discussed some advantages of using calculated measures for this purpose. Next, we created a KPI, providing examples of input for various expressions within its design form, and explaining the intended action of each. Finally, we examined the KPI in operation from within the KPI Browser, focusing on its operations and basic features.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved