Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 2
May 15, 2006
Reporting with Analysis Services KPIs
Objective and Business Scenario
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 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. A simple KPI (represented, say, by a smiling or frowning "Smiley" face image) might indicate a status of monthly employee turnover or headcount, either of which might represent a single, but important, key measure. A more complex KPI might combine multiple perspectives (a status, with regard to corporate market share for a given product, coupled with a trend, such as competitor market share over the past few months for a similar product) into a single graphic (say a directional arrow or a traffic signal), indicating that the enterprise is leading in share, that share is eroding, or even that it has been overtaken by its competition. KPIs 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.
Because the graphical output of Analysis Services KPIs can be directly consumed only from the KPI Browser in Analysis Services, one is initially challenged with the apparent fact that they are of limited use in external applications, among which reporting applications figure considerably. In this article, we will demonstrate not only a means of reporting upon the various values contained within Analysis Services KPIs, but we will also provide a flexible means for generating graphics that reflect KPI values in Reporting Services, with a result that resembles closely the presentation afforded in the Analysis Services KPI Browser.
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.
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 (and installable 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 while retaining the original sample in a pristine state perhaps 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.
While the preparation might seem cumbersome, creating all the objects to get us to the point of working with KPIs would require far too many steps to be practical in an article of this size. By using existing project samples (particularly an Analysis Services project that already has KPIs created), and a modified copy of a sample report, we can get a "head start" on solution creation, so that we can get to the focus matter of our session, KPI reporting. Though the steps are numerous, making preparatory modifications, and then making the enhancements to the solution to add the functionality to support the subject of our lesson, can be done easily within the Business Intelligence Studio environment. The end result of our efforts will be to leave us a working example of the specific approach we took, with all the "working parts" in place, to which we can refer in our individual business environments.
To begin, 'we will 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.
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.
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, therefore, subject to be installed in a different location 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.
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.
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 our 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.
The Enterprise folder appears, via the Open Project dialog, in the new location, as depicted in Illustration 5.
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.
14. Type the following replacement name into the folder's activated caption box:
RS029 MSAS KPIs in ReportServices
15. Click outside the caption box to accept the new name, which then appears as depicted in Illustration 7.
16. Click the new folder, to open it.
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.
17. Select the Adventure Works.sln file, and then click the Open button, as shown in Illustration 8.
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.