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

May 8, 2006

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.

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

Growth in Internet Orders

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

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.

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.

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.

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.

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.

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.

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.

The ratio of operating expenses to gross profit.

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

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.