Data Analytics: Exploiting the Data Warehouse - Data Warehouse Systems: Design and Implementation - page 361

this number is evolving during the current year. For this, we use the Sales
fact table. For simplicity, we assume that the employee's quota is computed
as a 15% increase over last year's sales. The goal is that the salespersons
reaching quota must be at least of 75% .

We next show how we can define KPIs using Microsoft Analysis Services.

9.2.4 KPIs in Analysis Services

Analysis Services provides a framework for defining KPIs that exploit the
business data stored in cubes. Each KPI uses a predefined set of properties
to which MDX expressions are assigned. Only the metadata for the KPIs are
stored by Analysis Services, whereas a set of MDX functions is available to
applications for retrieving KPI values from cubes using these metadata. The
Cube Designer provided in SQL Server Data Tools enables cube developers
to create and test KPIs.
In Analysis Services, a cube can have a collection of KPIs. Each KPI has
five properties, which are MDX expressions that return numeric values from
a cube, as described next:

￿ Value , which returns the actual value of the KPI. It is mandatory for a
KPI.
￿ Goal , which returns the goal of the KPI.
￿ Status , which returns the status of the KPI. To best represent the value
graphically, this expression should return a value between 1 and 1. Client

applications use this value to display a graphic indicator of the KPI value.
￿ Trend , which returns the trend of the KPI over time. As with Status ,it
should return a value between

−

1 and 1. Client applications use this value
to display a graphic indicator of the KPI trend direction.
￿ Weight , which returns the weight of the KPI. If a KPI has a parent KPI,
we can define weights to control the contribution of this KPI to its parent.

−

Analysis Services creates hidden calculated members on the Measures
dimension for each KPI property above. Nevertheless, these calculated
measures can be used in an MDX expression, even though they are hidden.
We show next how the Sales performance KPI defined in the previous
section can be implemented using Analysis Services. Recall that we want to
monitor the sales amount by year with respect to the goal of achieving 15%
growth year over year. Let us now give more detail about what the users want.
If the actual sales amount is more than 95% of the goal, the performance is
considered satisfactory. If, however, the sales amount is within 85-95% of the
goal, management must be alerted. If the sales amount drops under 85% of
the goal, management must take immediate action to change the trend. These
alerts and calls to action are commonly associated with the use of KPIs. We
are also interested in the trends associated with the sales amount; if the