Extending and Customizing Cubes - SQL Server Analysis Services 2012 Cube Development - page 147

Defining key performance indicators
You can define a KPI within the SSDT tab called KPIs by specifying the value, goal,
status, and trend expressions along with optional properties. A KPI's value is a meas-
ure, its goal is the ideal value of the calculation, the status shows the relative condition
of the measure compared to the goal, and the trend indicates whether the business is
moving closer to the target. For example, the Adventure Works sample database
has a KPI called Growth in Customer Base . This KPI is built upon the [meas-
ures].[growth in customer base] measure which compares the current time
span's customer count with that of the previous time span: the current year is com-
pared to last year, the current quarter is compared to last quarter, and so on. The goal
is arbitrarily set to 30 percent increase for each fiscal year, 15 percent for semesters,
7.5 percent for quarters, and 2.5 percent for months. The KPI's status is shown as a
green check mark if the goal is met or with a red X sign if we're below target. Lastly,
the trend displays an upward arrow if the value is increasing, a downward arrow if de-
creasing, and a horizontal arrow if the number remains flat.
In this recipe you will create internet order sales growth KPI to monitor Adventure
Works internet order traffic health. Let's set modest goals of 20 percent growth per
calendar year, 10 percent per calendar semester, 5 percent per quarter, and 2.5 per-
cent per month. We'll consider values within 80 percent as a decent progress.
How to do it...
To define internet order sales growth KPI:
1. Open the Adventure Works cube within the sample database (existing on
your SSAS instance) in SSDT and navigate to the Calculations tab.
2. Create a new calculated member by entering the following expression on the
Script View , and save the solution. The expression simply compares the or-
der count for the current time span with the previous time span, reports N/A
if we're at the top level (all calendar time) and returns null if the previous time
span had no orders:
CREATE MEMBER
CURRENTCUBE.[Measures].[Internet Order
Count Growth]