Create
a Calculated Measure to Support KPI
Let's say that we have
been tasked with creating a KPI to add to other indicators that are
currently presented within a digital dashboard in use at Adventure Works.
Management has called upon us to define and create a KPI, to be called Growth
in Internet Orders, which will act as an indicator of the ratio of
the Growth in Internet Orders in the current period to that of the
previous period. Because the client is unfamiliar with all except the most
high-level concepts surrounding Analysis Services KPIs, we will briefly
discuss the nature of the various property settings as we proceed.
Before we create our KPI,
we will create a Growth in Internet Orders calculated measure, upon
which we will partially base the new indicator. While we often have the choice
of doing all calculations within the KPI itself, using a calculated measure
allows us to make the expressions within the KPI more
straightforward, as well as to create reusable building blocks for similar
uses, to foster both efficiency and consistency within KPIs and
additional cube objects.
Let's open the Adventure
Works cube, within the clone Analysis Services database we have
created, and begin our practice with the creation of these calculated measures.
1.
Right-click
the Adventure Works cube in the Solution Explorer (within the Cubes
folder of the tree).
2.
Select Open
from the context menu that appears, as shown in Illustration 17.
Illustration 17: Open
the Adventure Works Cube ...
The Cube Designer,
one of several specialized variants within the BI Intelligence Development
Studio (among which this is a member of the Analysis Services project
subset) opens, defaulted to the Cube Structure tab. For purposes of this
section, we will be working largely within the Calculations tab, where calculated
members / measures are conveniently constructed and maintained.
NOTE: I perform in-depth examinations of many of the specific
capabilities and features of the SQL Server Business Intelligence Studio
(which provides significant support in helping us to work with Analysis
Services projects, and to integrate Analysis Services projects with Reporting
Services and Integration Services), as well as the SQL Server
Management Studio, in other articles of my series' here at Database
Journal. In the interest of time and focus, we will limit our discussion
in this article to relevant portions of this sophisticated development
environment. For more on the features of each of the studios, I invite you to
visit my series
index page.
3.
Click the Calculations
tab atop the Cube Designer, as depicted in Illustration 18.
Illustration 18:
Switching to the Calculations Tab ...
The Calculations
tab loads.
4.
If necessary,
click Form View, whose icon appears just beneath the Calculations
tab label, as shown in Illustration 19.
Illustration 19: Click
Form View, If Necessary ...
5.
Click the New
Calculated Member button appearing atop the Calculations tab, as depicted
in Illustration 20.
Illustration 20: Click
the New Calculated Member Button ...
The Calculations
form appears.
6.
Type the
following into the Name text box of the Calculations form.
[Growth in Internet Orders]
7.
Type (or cut
and paste) the following MDX syntax into the Expression box underneath
the Name box (leaving the settings in between at default):
CASE
WHEN [Date].[Fiscal].CURRENTMEMBER.LEVEL.ORDINAL = 0
THEN "NA"
WHEN ISEMPTY(
([Date].[Fiscal].CURRENTMEMBER.PREVMEMBER,
[Measures].[Internet Order Count]))
THEN Null
ELSE(
( [Date].[Fiscal].CURRENTMEMBER, [Measures].[Internet Order Count] ) -
( [Date].[Fiscal].PREVMEMBER, [Measures].[Internet Order Count] ))/
( [Date].[Fiscal].PREVMEMBER,[Measures].[Internet Order Count])
END
8.
Select Percent
in the Format string box, underneath the Expression box.
9.
Select True
in the Visible box, just beneath the Format string box.
The Calculations form,
with our input, appears as shown in Illustration 21.
Illustration 21: The
Calculation Form for the New Calculated Measure
For
purposes of our practice exercise, we are creating a calculated measure that will
generate a ratio of the Growth in Internet Orders in the current period to that of
the previous period.
The advantages of using calculated members / measures include the facts that 1)
we can present our calculations compactly, with "friendly" names, and
2) we can ensure that the calculation underneath (in this case, the change
in Internet Order Count measure over the prior fiscal period, divided by
the Internet Order Count of the prior period – that is, the growth since
the previous fiscal year, etc.) is consistently applied for anyone using the
calculation.
NOTE: For detailed information on the .CurrentMember and
.PrevMember functions, see MDX
Member Functions: "Relative" Member
Functions, a member of my monthly MDX Essentials
series at Database Journal.
10. Click the Check Syntax
button, as depicted in Illustration 22, to check the Expression
syntax of the new calculated measure.
Illustration 22:
Checking Calculated Measure Expression Syntax ...
The Check syntax message
box appears, indicating that "the syntax check was successful,"
assuming we have typed the MDX expression correctly, as depicted in Illustration
21.
Illustration 23: Message
Box: "The Syntax Check Was Successful"
11. Click OK to dismiss the
message box.
12. Click-select the new Growth in
Internet Orders calculated measure in the Script Organizer pane, in
the left upper corner of the Calculations tab.
13. Using the Move down button
atop the tab, move the new calculated measure to just below the Expense to
Revenue Ratio calculated measure (on or about Line 29 in the Script
Organizer), as depicted in Illustration 24.
Illustration 24: Moving
the New Calculated Measure below Another Ratio ...
14. Select File -> Save All from the main menu, to save our
work thus far, as shown in Illustration 25.
Illustration 25: Saving
Our Work ...
Let's
process the Adventure Works cube so that it reflects our changes to its
metadata, so that we can proceed with constructing a KPI that is
partially reliant upon the new Growth in Internet Orders calculated
measure.
15. Right-click the Adventure Works
cube in the Solution Explorer.
16. Select Process ... from the
context menu that appears, as depicted in Illustration 26.
Illustration 26: Select
Process ... from the Context Menu
The Process Cube
dialog for the Adventure Works cube appears next, as shown in Illustration
27.
Illustration 27: Process
Cube Dialog – Default Settings
17. Click the Run button, at
the bottom of the dialog, to begin processing.
Processing begins, and we
see the individual steps logged in the Process Progress viewer.
Processing soon completes, and we receive a Process Succeeded message in
the Status bar at the bottom of the viewer, as depicted in Illustration
28.
Illustration 28: Cube
Processing Successfully Completes ...
18. Click Close to dismiss the Process
Progress viewer.
19. Click Close to dismiss the Process
Cube dialog that re-appears.
Having updated cube
metadata, we are ready to begin constructing a new KPI.
» 
Comment and Contribute
