Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 8, 2006

Mastering Enterprise BI: Introduction to Key Performance Indicators - Page 5

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date