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.
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 (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.
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, 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.
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 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.
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:
RS029 MSAS KPIs in ReportServices
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 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.
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
» 
Comment and Contribute
