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.
» 
Comment and Contribute
