Key Performance Indicators, Actions, and the DRILLTHROUGH Statement - Microsoft SQL Server 2008 Analysis Services - page 265

Listing 15.6 shows example of KPI being created by CREATE KPI statement.

TABLE 15.2

Properties Supported by CREATE KPI Statements

Name Description

Specifies a graphical representation of status to provide
easy visualization of the KPI. The value of this property is a
string that maps to a set of bitmaps.

Status_Graphic

Specifies a graphical representation of a trend to provide
easy visualization of the KPI. The value of this property is a
string that maps to a bitmap.

Trend_Graphic

Enables a hierarchical organization of KPIs. For example,
you could define a Customer Scorecard KPI to have the
children Customer Satisfaction and Customer
Retention .

Parent_KPI

Defines a measure group associated with this KPI, which is
used to specify dimensionality of the KPI. This property is
optional. If it is not specified, the KPI will have the dimen-
sionality of all the measure groups in the cube.

Associated_Measure_Group

An MDX expression that defines the current member of the
Time dimension that is relevant for the KPI. If this property
is not specified, the default member of the Time dimension
is used.

Current_Time_Member

LISTING 15.6

CREATE KPI Statement

Create KPI [Warehouse and Sales].[Growth in Sales] as
Case
When [Time].[Time].CurrentMember.Level.Ordinal = 0
Then “NA”
When
IsEmpty ( ( [Time].[Time].PrevMember, [Measures].[Sales Count] ) ) Then Null
Else ( [Measures].[Sales Count] - ( [Time].[Time].PrevMember,
[Measures].[Sales Count] ) ) / ( [Time].[Time].PrevMember, [Measures].
[Sales Count]) End,
GOAL=
Case