Mastering OLAP Reporting: Prototype KPIs in Reporting Services

Monday Aug 21st 2006 by William Pearson
Share:

Need a quick KPI prototype, using Reporting Services / Analysis Services 2000 or 2005? BI Architect Bill Pearson leads hands-on practice in presenting simple KPIs to management with Reporting Services.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with tips and techniques for real-world use. For more information on the series, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy, Hyperion, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Note: To follow along with the steps we undertake within the articles of this series, the following components, samples and tools are recommended, and should be installed / accessible, according to the respective documentation that accompanies MSSQL Server 2005:

Server Requirements

  • Microsoft SQL Server 2005 Reporting Services

  • Microsoft SQL Server 2005 Database Services

  • The AdventureWorks sample databases

  • Microsoft SQL Server 2005 Analysis Services

  • The AdventureWorks OLAP cube

Client Requirements

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

Sample Files

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005. The samples with which we are concerned include, predominantly, the Adventure Works DW database. This database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Note: Current Service Pack updates are assumed for the operating system, along with the applications and components listed above and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application commoditizes business intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

In Mastering OLAP Reporting: Reporting with Analysis Services KPIs, a member of my Database Journal MSSQL Server Reporting Services series, we introduced an exciting new feature within MSSQL Server 2005 Analysis Services ("Analysis Services"), Key Performance Indicators ("KPIs"). In that article, we focused upon using Analysis Services KPI structures within Reporting Services. Our practice exercises centered upon preparing a scenario within which we used KPIs that existed in an Analysis Services cube, presenting them within a report we created within Reporting Services.

NOTE: For detailed information about Analysis Services 2005 KPIs (from an Analysis Services, versus Reporting Services, perspective), see my introductory article Mastering Enterprise BI: Introduction to Key Performance Indicators, a part of the Introduction to MSSQL Server Analysis Services series at Database Journal.

In response to the article, I received several e-mails that, in varied ways and with diverse objectives, touched upon a common thread: the need to generate KPIs at the presentation layer without creating KPI structures within Analysis Services. The reasons ranged from a need to generate KPIs from relational data sources to the requirement for presenting KPIs based upon an Analysis Services 2000 cube, where KPI capabilities, per se, do not exist. Moreover, I come across the need to prototype KPIs on a regular basis for prospective clients, as well as for clients who have yet to implement them (as an organization in general, or for various organizational units that do not currently make use of KPIs). At times, client representatives wish to develop the concepts behind prospective KPIs on the fly – when they may not have the capability to make ad hoc modifications within a cube that is currently in place – with the intent of backfilling the structures in Analysis Services once the concepts are tested and perfected.

Other reasons exist for a desire to create "quick and dirty" KPIs at the presentation layer, and so I suspect that an introduction to an easy approach for creating them within Reporting Services might be useful. The concepts behind the steps we take to do so in this article can be extrapolated to many similar strategies. While the best place for KPI support is likely to ultimately be the Analysis Services layer (or perhaps the relational database layer) of the integrated BI solution, the highly flexible Reporting Services environment lends itself to prototyping in this regard, as we shall see.

As always, performance optimization and other (often competing) factors need to be weighed in deciding "where to put the intelligence," and, for this reason, a "reporting specialist," an "Analysis Services expert," an "MSSQL DBA," or other "single-layer practitioner," no matter how experienced, might not serve as the best architect for an overall BI solution. For more of my observations on this subject, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

In this article, we will focus upon the generation of "prototype" KPIs, housed solely within Reporting Services, to meet the business requirements of a hypothetical client. As a part of our examination of the steps involved in prototyping KPIs within Reporting Services, we will:

  • Create a new Analysis Services Project within a Business Intelligence Development Studio solution, so as to make our entire solution freestanding (perhaps to ease further independent exploration);
  • Ascertain Connectivity of the Relational Data Source within the Analysis Services Project;
  • Deploy the Analysis Services Project;
  • Add a new Reporting Services Project to the solution;
  • Add an Analysis Services Shared Data Source;
  • Create a new, basic OLAP Report to work with a sample cube;
  • Add measures and associated KPIs to the report;
  • Verify the correct operation of our prototype KPIs within the report;
  • Discuss, at appropriate junctures, the results obtained within the development techniques that we exploit throughout our practice session.

Prototyping KPIs in Reporting Services

Objective and Business Scenario

As I discuss in Mastering Enterprise BI: Introduction to Key Performance Indicators and in Mastering OLAP Reporting: Reporting with Analysis Services KPIs, a Key Performance Indicator ("KPI") is a performance measure that specifies an emphasis intended to produce a desired result. As quantifiable measurements that gauge business success, KPIs can be diverse in nature, but they typically represent values, statuses, trends, or goals. KPIs often combine two or more of these into a single indicator. Because their purpose is to present high level, summary information to executives or other managers of the enterprise, who then use the information to steer an integrated organization, we typically limit the number of these vital measures to only a few.

KPIs are usually graphic in nature, and are collected into a dashboard, scorecard, or other central point of reference, where they can be easily accessed by the management team, to obtain a quick and accurate summary of business success, or of progress toward success. KPIs make it possible for management to make decisions and take action. Based upon the information the indicators convey, managers can pull the processes and activities that the KPIs represent into alignment with strategic goals and objectives. KPIs can range from simple to complex, and they are often evaluated over time. These indicators can be used alone or in combination to support rapid management decisions and actions, and to provide an indication of progress toward the accomplishment of intended strategic outcomes.

Our focus in this article is the generation of ad hoc KPIs in Reporting Services. While we would ultimately design and implement KPI support in Analysis Services (or even elsewhere), we will do so within Reporting Services within this session to illustrate an approach to prototyping KPIs in a scenario that supports a hypothetical client need. The concepts we apply will work equally well within an environment whose data source is Analysis Services 2000 or Analysis Services 2005, in combination with Reporting Services 2000 or Reporting Services 2005. The basic ideas can even be extrapolated to relational data sources, the use of MS Excel as a presentation medium, and elsewhere, with only a minimal amount of imagination.

Let's say that representatives of the Controller's Group of our client, the Adventure Works organization, have asked us to consult with them to meet a new reporting requirement. Management, having become excited by the sheer volume of information in the recent media about KPIs, has asked that the Controller's Group investigate the use of KPIs in general, and that they present a working prototype, based upon existing OLAP data, to show that they can support the Controller with KPIs. We ask the group members for the details, and note the requirements.

Members of the Controller's Group explain that they wish to be able to present two basic KPIs for Fiscal Year 2004. They would like to generate, based upon existing data, a Gross Profit Margin KPI and a Growth in Customer Base KPI for each respective Territory Sales Country. Instead of presenting any of the common images for the indicators (which include everything from Smiley faces in various stages of apparent happiness, to meters, gauges and so forth with "readings" ranging from "low" - or "bad" - to "high," "good," etc.), the requirement for the Controller is simple. Based upon his highly traditional background, the Controller wishes simply to see a small square, containing one of three colors, for each KPI for each country.

For the Gross Profit Margin KPI, the specification is for a Gross Profit Margin of less than 41 percent to be represented by a red indicator; for a Gross Profit Margin equal to, or greater than, 42 percent to be represented by a green indicator; and for a Gross Profit Margin between the lower and upper thresholds (equal to, or greater than, 41 percent, but less than 42 percent) to be represented by a yellow indicator.

The Growth in Customer Base KPI works in a similar fashion, only with the growth factor the supporting measure. The specification is for a Growth in Customer Base value of less than 4 to be represented by a red indicator; for a Growth in Customer Base value equal to, or greater than, 5 to be represented by a green indicator; and for a Growth in Customer Base value between the lower and upper thresholds (equal to, or greater than, 4, but less than 5) to be represented by a yellow indicator.

Detailed discussion with the client representatives reveals that, although we can access a cube (as a report data source) that contains the data to support the KPIs requested, we cannot modify the existing cube, which is used for critical reporting support, in any way. We realize that this means no KPI support at the cube level (via KPI structures, calculated members, etc. in Analysis Services), and that the entire prototype solution must be based within Reporting Services. Because we have found the flexible Reporting Services environment to be ideal for such efforts in similar situations in the past, we agree to prototype the two proposed KPIs, once we have ascertained the existence, in the cube, of the data we need to serve as the bases for our KPI calculations. Moreover, we agree that, once we have successfully prototyped the required KPIs, we will offer advice to the team about a more optimal approach for their creation and maintenance, within Analysis Services.

Hands-On Procedure

We will begin by creating a new Report Server Project, within the SQL Server Business Intelligence Development Studio, wherein we will establish a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as in another of my Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the Studio features relevant to our immediate practice exercise, to allow us to get to the focus of our session more efficiently.

Preparation

Create a New Analysis Services Project within a New Solution

For purposes of our practice session, we will create a copy of the Adventure Works Analysis Services project, one of several samples that are available with (albeit installed separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a "clone" of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Analysis Services and other components of the Microsoft integrated business intelligence solution in general.

To begin, we'll launch the SQL Server Business Intelligence Development Studio.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

The equivalent on my PC appears as depicted in Illustration 1.


Illustration 1: Launching SQL Server Business Intelligence Development Studio

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File --> Open from the main menu.

5.  Click Project / Solution ... from the cascading menu, as shown in Illustration 2.


Illustration 2: Selecting a Project ...

The Open Project dialog appears.

6.  Browse to the location of the Adventure Works Analysis Services sample project folder.

Two versions of the Adventure Works Analysis Services project samples are installed, by default (and, of course, subject to be installed in a different location (or locations) on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project

Of the two samples, we will select the Enterprise project, as it is a more evolved sample than the Standard project. We will copy the Enterprise folder to another location, and work with it within our practice session. (If the Standard project is all that is available in the local environment, use that instead.)

7.  Right-click the Enterprise folder, containing the sample Adventure Works solution, project and associated objects.

8.  Select Copy from the context menu that appears, as depicted in Illustration 3.


Illustration 3: Copy the Enterprise Project Folder ...

9.  Navigate, from within the Open Project dialog box, to the location of preference for storing the folder we have copied, and from which we will create a "clone" project.

10.  Upon arrival within the desired folder, right-click the area within the folder.

11.  Select Paste from the context menu that appears, as shown in Illustration 4.


Illustration 4: Pasting the Copy of the Enterprise Project Folder in a Selected Location

The Enterprise folder appears, via the Open Project dialog, in the new location, as depicted in Illustration 5.


Illustration 5: Enterprise Project Folder in Its New Location ...

12.  Right-click the Enterprise folder, within the Open Project dialog.

13.  Select Rename from the context menu that appears, as shown in Illustration 6.


Illustration 6: Renaming the Folder ...

14.  Type the following replacement name into the folder's activated caption box:

RS032 Simple KPIs in RS

15.  Click outside the caption box to accept the new name, which then appears as depicted in Illustration 7.



Illustration 7: Newly Named Folder ...

16.  Click the new folder, to select it.

17.  Click the Open button on the Open Project dialog.

The solution file, Adventure Works.sln, and the project file, Adventure Works DW.dwproj, appear at this point, along with two folders containing associated objects.

18.  Select the Adventure Works.sln file, and then click the Open button, as shown in Illustration 8.


Illustration 8: Opening the Adventure Works.sln File

The Adventure Works solution opens, complete with Adventure Works DW Analysis Services project, and we see the various associated objects appear in Solution Explorer, as depicted in Illustration 9.


Illustration 9: The New Solution, Project and Objects within the Solution Explorer

19.  Right-click the Adventure Works solution, within the Solution Explorer.

20.  Select Rename from the context menu that appears, as shown in Illustration 10.


Illustration 10: Renaming the Solution ...

21.  Type the following replacement name into the solution's activated caption box:

RS032_Adventure Works

22.  Click outside the caption box to accept the new name.

23.  Right-click the Adventure Works DW Project, underneath the newly renamed solution, within the Solution Explorer.

24.  Select Rename from the context menu that appears, as depicted in Illustration 11.


Illustration 11: Renaming the Analysis Services Project ...

25.  Type the following replacement name into the project's activated caption box:

RS032_Adventure Works DW

26.  Click outside the caption box to accept the new name.

27.  Click Yes on the dialog box that appears next, asking if we would like to change the object name as well, as shown in Illustration 12.


Illustration 12: Click Yes to Change the Object Name, As Well ...

The newly renamed solution and project appear within the Solution Explorer, as depicted in Illustration 13.


Illustration 13: The Newly Renamed Solution and Project Appear ...

Ascertain Connectivity of the Relational Data Source

Let's ensure we have a working data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone (the default for the Adventure Works DW project sample's connection is localhost).

1.  Double-click Adventure Works.ds, within the Data Sources folder seen in Solution Explorer.

The Data Source Designer opens, defaulted to the General tab, and appears with default settings as shown in Illustration 14.


Illustration 14: The Data Source Designer with Default Settings ...

2.  Click the Edit button on the Data Source Designer dialog.

The Connection Manager opens, and appears with default settings depicted in Illustration 15.


Illustration 15: The Connection Manager with Default Settings ...

We note that the default Server name is "Localhost." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

3.  If appropriate, type the correct server / instance name into the Server name box of the Connection Manager. (Mine is MOTHER1\MSSQL2K5, as shown in Illustration 16.)


Illustration 16: The Connection Manager, with Corrected Settings ...

4.  Ensure that AdventureWorksDW is selected in the box labeled Select or enter a database name in the Connect to a database section of the Connection Manager dialog.

5.  Ensure that authentication settings are correct for the local environment.

6.  Click the Test Connection button.

A Connection Manager message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are proper. The message box appears as depicted in Illustration 17.


Illustration 17: Testing Positive for Connectivity ...

7.  Click OK to dismiss the message box.

8.  Click OK to accept changes, as appropriate, and to dismiss the Connection Manager.

The Data Source Designer appears, with our modified settings, similar to that shown in Illustration 18.


Illustration 18: The Data Source Designer with Modified Settings ...

9.  Click OK to close the Data Source Designer, and to return to the development environment.

Deploy the Analysis Services Project

Before going further, let's deploy our Analysis Services project. This will ensure issue-free access for Reporting Services as we progress within our practice preparation and procedures.

First, let's ensure alignment of the deployment server, as well as changing the name of the destination Analysis Services database, to prevent write over of the Analysis Services DW database if we have already installed it for education and testing purposes.

1.  Right-click the Adventure Works DW Analysis Services project in the Solution Explorer.

2.  Select Properties from the context menu that appears, as depicted in Illustration 19.


Illustration 19: Select Properties for the Project ...

The Properties Pages dialog opens.

3.  Within the left-hand pane, under Configuration Properties, select Deployment.

4.  In the Target section of the right-hand pane, ensure that the correct server / server instance combination is in place in the Server box.

5.  Within the Database section, replace the existing Database name, Adventure Works DW, with the following:

RS032_Adventure Works DW

The Properties Pages dialog appears, with our modified settings, similar to that shown in Illustration 20.


Illustration 20: The Properties – Deployment Page, with Modified Settings ...

6.  Click OK to accept changes and close the dialog.

We can now deploy the project.

7.  Right-click the Adventure Works DW Analysis Services project in the Solution Explorer, once again.

8.  Select Deploy from the context menu that appears, as depicted in Illustration 21.


Illustration 21: Initiating Analysis Services Project Deployment ...

9.  Click Yes on the dialog that appears next, asking if we would like to build and deploy the project first, as shown in Illustration 22.


Illustration 22: Click Yes to Rebuild and Deploy ...

Deployment completes, and the Process Database dialog appears, defaulted to Process Full.

10.  Click Run ... on the Process Database dialog that appears next, as depicted in Illustration 23.


Illustration 23: Click Run to Process the Database ...

The Process Progress viewer appears, indicating steps of processing as they transpire. Once processing finishes, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 24.


Illustration 24: "Process Succeeded" Message

11.  Click Close to dismiss the Process Progress viewer.

12.  Click Close to dismiss the Process Database dialog.

We are now ready to create a report project, and proceed with the practice exercise. To prepare for this, we will add a new Report Server project to our existing solution.

Add a New Reporting Services Project

We will add a new Report Server project, within which we will craft a basic report to present an approach for rapid KPI creation.

1.  Right-click the Solution 'RS032_Adventure Works level, atop the tree within the Solution Explorer.

2.  Select Add --> New Project ... from the cascading context menus that appear, as depicted in Illustration 25.


Illustration 25: Adding a Project to the Solution ...

The Add New Project dialog appears.

3.  Within the Templates pane, select Report Server Project.

4.  Type the following into the Name box underneath the Templates pane:

RS032 Simple KPIs

5.  Navigate to a desired location, using the Browse button to the right of the Location box, as necessary, for the new Project file.

The Add New Project dialog appears, with our input, similar to that shown in Illustration 26.


Illustration 26: Adding a New Report Server Project ...

6.  Click OK to create the new Report Server Project.

The RS032 Simple KPIs Report Server Project appears, with associated object folders, in Solution Explorer, as depicted in Illustration 27.


Illustration 27: The New Report Server Project within the Solution Explorer

We will next create a shared Data Source within the new Report Server Project. This will be the last of our preparatory steps for working with KPIs in a new report.

Create an Analysis Services Shared Data Source

1.  Within the Solution Explorer tree, just underneath the new RS032 Simple KPIs Report Server Project, right-click the Shared Data Sources folder.

2.  Select Add New Data Source ... from the cascading context menus that appear, as shown in Illustration 28.


Illustration 28: Adding a New Shared Data Source

The Shared Data Source dialog opens, defaulted to the General tab.

3.  Type the following into the Name box on the General tab.

RS032_Adventure Works DW

4.  Select the following in the Type box, just beneath the Name box:

Microsoft SQL Server Analysis Services

5.  Click the Edit button to the right of the Connection string box, underneath the Type box.

6.  Click the Edit button on the Shared Data Source dialog.

The Connection Properties dialog opens.

7.  Type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\MSSQL2K5, as before).

8.  In the Connect to a database section in the bottom half of the dialog, in the selector box labeled Select or enter a database name, select RS032_Adventure Works DW.

The Connection Properties dialog appears, with our settings, similar to those depicted in Illustration 29.


Illustration 29: The Connection Properties Dialog with Example Settings ...

9.  Ensure that authentication settings are correct for the local environment, as we did for the relational data source above

10.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 30.


Illustration 30: Testing Positive for Connectivity ...

11.  Click OK to dismiss the message box.

12.  Click OK to accept changes, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with modified settings, similar to that shown in Illustration 31.


Illustration 31: The Shared Data Source Dialog with Modified Settings ...

13.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

We are now ready to create a basic report, and to gain some exposure to creating KPIs in Reporting Services.

Procedure

Prototype KPIs in Reporting Services

Add a New, Blank Report

Let's create a basic OLAP report that contains data upon which we will base examples of KPIsKPIs we can generate quickly and solely within Reporting Services, to fit the hypothetical business requirement we have outlined. We won't spend a great deal of time with formatting and other nuances of presentation – the point here is to illustrate a conceptual option for generating KPIs on a limited scale.

1.  In Solution Explorer, right-click the Reports folder, within the RS032_Simple_KPIs project that we added earlier

2.  Select Add from the context menu that appears.

3.  Select New Item ... from the context menu that cascades from the first, as depicted in Illustration 32.


Illustration 32: Select Add --> New Item ...

The Add New Item dialog appears.

4.  Click Report in the Templates pane, as required, to select it.

5.  Type the following into the Name box at the foot of the dialog:

RS032_KPI_Matrix

6.  Click the Add button in the bottom right corner of the dialog, which should appear as shown in Illustration 33.


Illustration 33: Creating a New, Blank Report

RS032_KPI_Matrix.rdl, currently a blank canvas, opens within the design environment, and appears in the Solution Explorer, as depicted in Illustration 34.


Illustration 34: The New Report Appears within Solution Explorer

Create a Dataset to Support KPIs

Let's create a basic Dataset, upon which to base our new OLAP report.

1.  Click the Data tab within Report Designer, to open the Data view.

2.  Using the "down arrow" button to the right of the Dataset selector, atop the Data tab, click <New Dataset...>, as shown in Illustration 35.


Illustration 35: Adding a New Dataset ...

The Dataset dialog appears.

3.  Type the following into the Name box at the foot of the dialog:

RS032_KPI_Support

The Dataset dialog appears, as depicted in Illustration 36.


Illustration 36: The Dataset Dialog for Our New Dataset

4.  Click OK to accept the new Dataset definition, and to dismiss the Dataset dialog.

The MDX Query Designer opens in Design view.

5.  Within the Metadata pane to the left of the design area, expand the Sales Territory dimension by clicking the "+" sign to its immediate left.

6.  Expand the Sales Territory hierarchy that appears underneath the dimension (bottom item within the dimension).

The Sales Territory hierarchy expands, exposing its members within the Metadata tree, as partially shown in Illustration 37.


Illustration 37: The Expanded Sales Territory Dimension and Hierarchy ...

7.  Drag the newly exposed Sales Territory Country into the Results pane of the Dataset design area, as depicted in Illustration 38.


Illustration 38: Adding Sales Territory Country to the Dataset ...

Sales Territory Country appears as a column heading in the design area. Let's add additional data fields that we will need in our query.

8.  Within the Metadata pane, once again, expand the Date dimension.

9.  Expand the Fiscal folder that appears within the Date dimension.

10.  Expand the Fiscal hierarchy that appears within the Fiscal folder (bottom item within the folder).

11.  Drag the newly exposed Fiscal Year into the Results pane area, dropping it to the right of the Sales Territory Country column, as shown in Illustration 39.


Illustration 39: Adding Fiscal Year to the Dataset ...

Fiscal Year now appears as a second column heading in the Results pane area. Let's add the measures we need to support the required KPIs.

12.  Within the Metadata pane, expand Measures.

13.  Expand the Internet Sales folder that appears within Measures.

14.  Drag the following two measures (actually calculations), in the order shown, into the Results pane area:

Internet Gross Profit Margin
Growth in Customer Base

Drop each measure, one after the other, to the right of the columns already in place, as depicted in Illustration 40.


Illustration 40: Adding Two Measures ...

15.  In the Filter pane, located in the upper right corner of the Query Designer, select Date via the Dimension column selector button.

16.  Select Date.Fiscal Year in the Hierarchy column, to the immediate right of the Dimension column, within the Filter pane.

17.  Select Equal in the Operator column.

18.  Select FY 2004, via the checkbox within the selector for the Filter Expression column, as shown in Illustration 41.


Illustration 41: Filtering by Fiscal Year 2004 ...

19.  Click OK to accept the setting for the Filter Expression and to close the selector.

The Query Designer appears, with our settings, as depicted in Illustration 42.


Illustration 42: The Query Designer with our Settings

NOTE: If the Results pane has not populated, as shown in the illustration above, click the Execute Query ("!") button in the toolbar atop the Query Designer, shown circled in Illustration 43.


Illustration 43: The Execute Query Button in the Toolbar

We now have the 2004 data we need to support our immediate KPI requirement in the steps that follow. While our requirement for this practice session is, of course, quite simple, we are aware that we can support more elaborate requirements by adding parameterization and other features, both here and elsewhere within Reporting Services.

Add KPIs in the Report Layout

In this section, we will add a matrix data region, along with data, to the report canvas on the Layout tab. While our focus is the creation of basic KPIs, and not a "traditional" report, we will find that adding the supporting data to the matrix, and then adding the KPIs (which are based upon the data), will leave the data in place to allow for easy verification of KPI accuracy. (We can always remove the data after the KPIs are in place and verified as to accuracy of presentation.)

We will add a matrix data region to the Layout tab first, which will serve to house our data and KPI calculations.

1.  Click the Layout tab.

2.  From the Toolbox, drag a matrix data region to the Layout tab, as depicted in Illustration 44.


Illustration 44: Adding a Matrix Data Region ...

NOTE: If the Toolbox is not visible, in the View menu, select Toolbox.

The matrix appears on the report canvas, as shown in Illustration 45.


Illustration 45: The Matrix Appears on the Report Canvas

3.  Within the Datasets window, expand the RS032_KPI_Support Dataset by clicking the "+" sign to its immediate left.

NOTE: If the Datasets window is not visible, select Datasets in the View menu.

The data fields within the RS032_KPI_Support Dataset appear in the Datasets window, as depicted in Illustration 46.


Illustration 46: The Data Fields Appear within the Datasets Window

4.  From the Datasets window, drag the Sales_Territory_Country field into the bottom left corner of the new matrix data region (the box watermarked "Rows"), as shown in Illustration 47.


Illustration 47: Dragging the First Data Field into the Matrix Data Region

5.  Drag the Fiscal_Year field into the top right corner of the new matrix data region (the box watermarked "Columns").

6.  Drag the Internet_Gross_Profit_Margin field into the bottom right corner of the new matrix (the box watermarked "Data").

7.  Drag the Growth_in_Customer_Base field to the right side of the bottom right corner box, where we dropped the Internet_Gross_Profit_Margin field in the previous step, dropping the field when the white "bracket" figure appears, as depicted in Illustration 48.


Illustration 48: Drop the Data Field when the "Bracket" (Circled Above) Appears ...

8.  Click the gray bar atop the leftmost column of the matrix, to highlight the column.

9.  Holding down the SHIFT key, click the bars atop the second and third columns, so as to highlight all columns.

10.  Select "8" in the Font Size selector in the toolbar, atop the design environment, as shown in Illustration 49.


Illustration 49: Highlight All Four Columns, and Change the Font to "8"

11.  Right-click the bottom right box, currently containing the expression =Sum(Fields!Growth_in_Customer_Base.Value).

12.  Select Add Column from the context menu that appears, as depicted in Illustration 50.


Illustration 50: Adding a Column to the Matrix ...

13.  Right-click the new bottom right box, the one we added in the last step (currently unoccupied).

14.  Select Add Column from the context menu that appears, once again, to add another empty column to the matrix.

15.  Click the gray bar atop the first new column of the matrix, to highlight the column.

16.  Holding down the SHIFT key, click the bar atop the second new column, so as to highlight both new, blank columns.

17.  In the Properties window, select the Width box, within the Layout properties group.

NOTE: If the Properties window is not visible, select Properties Window in the View menu.

18.  Set the Width property to .5, as shown in Illustration 51.


Illustration 51: Narrowing the Width of the New Columns ...

19.  Click outside the Width property box to accept the new setting.

Both columns narrow. These will be our KPI columns.

20.  Click the bottom row in the first (leftmost) of the two newly added columns, simply to select the box.

21.  Holding down the SHIFT key, click the bottom row in the second of the two newly added columns (the bottom right box in the matrix), so as to highlight the bottom row in both blank columns.

22.  Expand the BorderWidth property group (within the Appearance group) within the Properties window.

23.  Set the individual BorderWidth properties as presented in Table 1.

Physical Dimension

Width Setting

Default

1

Left

20

Right

20

Top

7

Bottom

7


Table 1: BorderWidth Properties Settings

24.  In the BorderStyle property setting (just above the BorderWidth property group, which we have just set, in the Properties window), select Solid.

25.  In the BorderColor property setting (just above the BorderStyle property group setting in the Properties window), select White.

The BorderColor, BorderStyle, and BorderWidth settings, with our input, appear as depicted in Illustration 52.


Illustration 52: Border Properties Settings in the Properties Window

26.  Right-click the heading box (the box above the box for which we changed width properties above) in the first (leftmost) of the two newly added columns.

27.  Select Expression from the context menu that appears, as shown in Illustration 53.


Illustration 53: Select Expression for the Left KPI Heading ...

The Expression Editor appears.

28.  Type the following into the upper input box of the Editor, as heading for the Internet Gross Profit Margin KPI:

="GP"& vbcrlf &"Margin"

The relevant portion of the Expression Editor appears, with our input, as depicted in Illustration 54.


Illustration 54: Expression Editor with Our Input

29.  Click OK to accept our input, and to dismiss the Expression Editor.

30.  Right-click the heading box for the second new column (to the immediate right of the box in which we placed the title in the last step), to select it.

The Expression Editor appears, once again.

Type the following into

31.  Type the following into the upper input box of the Editor, as heading for the Growth in Customer Base KPI:

="Cust"& vbcrlf &"Base Growth"

The relevant portion of the Expression Editor appears, with our input, as shown in Illustration 55.


Illustration 55: Expression Editor with Our Input

32.  Click OK to accept our input, and to dismiss the Expression Editor.

33.  Select both heading boxes (the ones we populated in our last few steps).

34.  Select Center in the Text Align setting of the Properties window (a member of the Appearance group), as depicted in Illustration 56.


Illustration 56: Centering the New Headings ...

We are now ready to add the KPIs. Since the client representatives have stated that they simply wish to see "color indicators," our task is even more simple than if they wished to see images that reflected status of the indicators, and so forth, as we presented in Mastering OLAP Reporting: Reporting with Analysis Services KPIs. We have only to vary the colors of our specially sized matrix boxes. We can accomplish this effect rapidly with the BackGroundColor property setting of the respective KPI box, as we shall see in the steps that follow.

35.  Click the Gross Profit Margin KPI box (in the bottom row underneath the heading box in which we have typed ="GP"& vbcrlf &"Margin"), to select it.

36.  Click <Expression...> from the dropdown selector obtained by clicking the BackGroundColor setting in the Properties window (the first selection in the Appearance group, atop the window), as shown in Illustration 57.


Illustration 57: Select <Expression...>, Instead of A Color ...

The Expression Editor appears, once again.

37.  Replace any existing contents, by typing (or cutting and pasting) the following into the upper input box of the Editor:

=IIF(SUM(Fields!Internet_Gross_Profit_Margin.Value)
< .41, "Red", IIF(SUM(Fields!Internet_Gross_Profit_Margin.Value)
>= .42, "Green", "Yellow"))

In effect, we are conditionally formatting the Background Color of the specially sized box, based upon the magnitude of the Gross Profit Margin value for the respective Territory Sales Country. In accordance with the specifications we have obtained from the representatives of the Controller's Group, we have directed, via our expression, that a Gross Profit Margin of less than 41 percent be represented by a red indicator, that a Gross Profit Margin equal to, or greater than, 42 percent be represented by a green indicator, and that a Gross Profit Margin between the lower and upper thresholds (equal to, or greater than, 41 percent, but less than 42 percent) be represented by a yellow indicator.

The relevant portion of the Expression Editor appears, with our input, as depicted in Illustration 58.


Illustration 58: Expression Editor with Our Input

38.  Click OK to accept our input, and to dismiss the Expression Editor.

Now all that remains in our series of steps for KPI creation is the addition of the second KPI, Growth in Customer Base KPI.

39.  Click the Growth in Customer Base KPI box (the bottom row underneath the heading box in which we have typed ="Cust"& vbcrlf &"Base Growth"), to select it.

40.  Click <Expression...> from the dropdown selector obtained by clicking the BackGroundColor setting in the Properties window (the first selection in the Appearance group, atop the window), as we did after selecting the first KPI earlier.

The Expression Editor appears, as before.

41.  Replace any existing contents, by typing (or cutting and pasting) the following into the upper input box of the Editor:

=IIF(SUM(Fields!Growth_in_Customer_Base.Value)
< 4, "Red", IIF(SUM(Fields!Growth_in_Customer_Base.Value) >= 5,
"Green", "Yellow"))

After the manner that we followed with the first KPI, we are conditionally formatting the Background Color of the specially sized box for the second KPI. This time, we are basing color upon the magnitude of the Growth in Customer Base value for the respective Territory Sales Country. In accordance with the specifications we have obtained from the representatives of the Controller's Group, we have crafted our expression to direct that a Growth in Customer Base value of less than 4 be represented by a red indicator, that a Growth in Customer Base value equal to, or greater than, 5 be represented by a green indicator, and that a Growth in Customer Base value between the lower and upper thresholds (equal to, or greater than, 4, but less than 5) be represented by a yellow indicator.

The relevant portion of the Expression Editor appears, with our input, as shown in Illustration 59.


Illustration 59: Expression Editor with Our Input

42.  Click OK to accept our input, and to dismiss the Expression Editor.

Having completed the steps of adding and formatting our basic KPIs, we will verify their operation in the next section.

Verification

Preview the Report and Verify Accuracy of KPIs

We will next take a look at the results of our handiwork. Recall that we left the actual values, upon which the conditional formatting behind our KPIs is based, within the matrix data region, to provide a means of easy verification at this point within our practice session.

1.  Click the Preview tab.

The Analysis Services KPIs report executes momentarily, and then returns our KPIs as expected, as depicted in Illustration 60.


Illustration 60: Basic KPIs at Work in the Analysis Services KPIs Report ...

We can agree the results of the KPIs for the respective country / value combinations easily, and gain comfort that our straightforward conditional Background Color formatting is working properly in providing the indications specified by the Controller's Group representatives. Having ascertained that the KPIs are accurate, we might develop the report further by eliminating the two measures, by designing in additional KPIs to present other critical performance indicators, and so forth. In prototyping KPI solutions for clients using similar approaches, I have taken these concepts much further, including the addition of values to the matrix boxes (conditionally formatted themselves to change to a color that contrasts best with the respective Background Color), the addition of logic to various conditional formatting schemes to do nested IIF statements that vary the results based on parameter selections, and many other variations upon these themes.

Moreover, I've added drilldown / drillthrough features into KPI presentations, and so forth, although I often find myself reminding clients that it is easy to lose sight of the general purposes of Key Performance Indicators if we become lost in chasing the virtually unlimited options that we can accomplish within a tool as versatile as Reporting Services. Typically, once we have a working prototype and / or have selected the KPIs (with underlying logic) that we want to display in dashboards or even elaborate reports, I recommend the creation of KPIs within Analysis Services 2005 (or calculated members within Analysis Services 2000 implementations), or perhaps other approaches, upon which we might base the ultimate KPIs at the presentation layer, for obvious performance, and other design, reasons.

2.  Select File --> Save All on the main menu, to save our work to this point.

3.  Select File --> Exit, to leave the Business Intelligence Development Studio, when ready.

Conclusion ...

In this article, we concentrated upon the generation of "prototype" KPIs, housed solely within Reporting Services, to meet the business requirements of a hypothetical client. After discussing reasons that prototyping KPIs in this fashion might be useful, we detailed a simple KPI objective, and set about meeting the requirements in a hands-on practice exercise. We prepared for our practice exercise by creating a new Analysis Services Project within a Business Intelligence Development Studio solution, so as to make our entire solution freestanding (perhaps to ease further independent exploration); by ascertaining Connectivity of the Relational Data Source within the Analysis Services Project; and then by deploying the Analysis Services Project.

Next, we concluded preparation by adding a new Reporting Services Project to the solution, within which we added an Analysis Services Shared Data Source to access the cube we had deployed within the Analysis Services Project. We then created a basic OLAP Report within which to work with our sample cube data.

We added measures and associated KPIs to the report, performing minimal formatting along the way to enhance our demonstration of the prototype we had constructed. Finally, we verified the correct operation of our prototype KPIs within the report. Throughout the steps of our practice session, we discussed, at appropriate junctures, various settings and techniques involved in achieving our objectives.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved