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

A client application performs a few simple steps to retrieve KPI data, as shown in Figure 15.3.

Analysis
Services

Client
Application

Query KPI List

Prepare to
Display

Cube

KPI

KPI

Query KPI Values

KPI

Display
Results

FIGURE 15.3

Steps to retrieve KPI data.

To retrieve KPI data, first the client application retrieves the list of KPIs available on the
server/session. To do this, it issues a schema rowset request for MDSCHEMA_KPIS . This
schema's rowset has a list of columns that repeat the properties of the KPI object. The
schema rowset is used to enumerate the names of available KPIs and to retrieve the KPI
properties, such as StatusGraphic and TrendGraphic . Other columns of the schema's
rowset, such as Value , Goal , Trend , Status , and Weight , contain the names of the calcu-
lated measures associated with Value , Goal , Status , Trend , and Weight expressions,
respectively.

Although the client application can use those names to generate an MDX query for the
actual values of those calculated members, we recommend a simpler way to access values
associated with KPI expressions. Client applications can use the helper MDX functions
provided by Analysis Services— KPIGoal , KPIStatus , KPITrend , KPIValue , KPIWeight , and
KPICurrentTimeMember —to access the values of the KPI properties. For example, a client
application could issue the simple MDX request shown in Listing 15.7.

LISTING 15.7 An MDX Statement That Requests KPI Property Values

SELECT
{KPIValue(“Growth in Sales”), KPIGoal(“Growth in Sales”),
KPIStatus(“Growth in Sales”),KPITrend(“Growth in Sales”)}
ON COLUMNS,
Store.Store.members on ROWS
FROM [warehouse and sales]
WHERE [Time].[Time].[Year].[1998]

This request returns the results shown in Figure 15.4.