Mastering Enterprise BI: Introduction to Key Performance Indicators - Page 4
May 8, 2006
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.
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.
The Calculations tab loads.
4. If necessary, click Form View, whose icon appears just beneath the Calculations tab label, as shown in Illustration 19.
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.
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.
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.
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.
14. Select File -> Save All from the main menu, to save our work thus far, as shown in Illustration 25.
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.
The Process Cube dialog for the Adventure Works cube appears next, as shown in Illustration 27.
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.
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.