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

When KpiValue(“Growth in Sales”) - (KpiValue(“Growth in Sales”),
ParallelPeriod ([Time].[Time]. [Year], 1,
[Time].[Time].CurrentMember ))/(KpiValue(“Growth in Sales”),
ParallelPeriod([Time].[Time].[Year], 1, [Time].[Time].CurrentMember ) ) >.02
Then 1
Else -1
End
</Trend>
<StatusGraphic>Gauge - Ascending</StatusGraphic>
<TrendGraphic>Standard Arrow</TrendGraphic>
<Weight />
<ParentKpiID />
<CurrentTimeMember/>
</Kpi>
</Kpis>
During the deployment of a KPI object, under the covers Analysis Services analyzes the
expressions associated with the Value , Goal , Status , Trend, and Weight properties and
creates hidden calculated members on the Measure dimension associated with each prop-
erty. If an expression just references a calculated measure, Analysis Services uses the exist-
ing calculated measure and won't create a new one. For example, if your expression for
the Value property refers to the calculated measure MyKPIValue that you have already
created in an MDX script, Analysis Services won't create a new calculated measure for the
value expression, but will instead use the existing MyKPIValue calculated measure.
Session-Based KPIs
To allow you to create KPIs on your own machine, which you won't have to deploy to the
server, Analysis Services 2008 supports session-based KPIs. Session-based KPIs are similar to
session-based calculated members. They are created by a CREATE KPI statement and can be
deleted by a DROP KPI statement.
A CREATE KPI statement has the following syntax:
CREATE MEMBER [< cube_name >.][< KPI name ] AS < KPI Value >[,<Property
➥ Name>=<Property_Value>]
In this statement, you specify cube name and name of the KPI you create. As KPI Value ,
you can specify MDX expression that returns the actual value of the metric. In the CREATE
KPI statement, you can also specify one or more of optional properties. The properties are
similar to ones you use to create a KPI object on the server (see Table 15.1). Properties that
have slightly different names are described in Table 15.2.