Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 15, 2006

Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 5

By William Pearson


Reporting with Analysis Services KPIs

Add KPI Values to the Report

Our clone of the Sales Reason Comparisons report, the sole OLAP report in the sample set, will serve as a quick "framework," since it is already in place and has several attributes that will allow us to modify it quickly. We can then add KPI values.

1.  Click the Data tab to return to Data view.

We arrive at the MDX Query Designer, where we will be working in Design View for this part of our session.

2.  Select the sole item (Product) in the Filter pane, in the upper right corner of the Query Designer.

3.  Click the Delete button to remove the filter, as shown in Illustration 32.

Illustration 32: Deleting the Product Filter ...

The filter disappears, and the grid updates (unless Auto Execute is disabled) in the Results pane below.

4.  Right click a point with the Results pane.

5.  Select Clear Grid to begin a fresh start with the DataSet, as depicted in Illustration 33.

Illustration 33: Clearing the Results Pane Grid

6.  In the Metadata Browser pane, expand the Product dimension in the metadata tree by clicking the "+" sign to its immediate left.

7.  Drag the Subcategory attribute from the Metadata Browser pane over to drop on the main Results pane, as shown in Illustration 34.

Illustration 34: Adding Product Subcategory to the Data Set

8.  In the Metadata Browser pane, expand KPIs in the tree.

9.  Drag the Internet Revenue KPI from the Metadata Browser pane over to right of the new Subcategory column, dropping it when the red line appears, as depicted in Illustration 35.

Illustration 35: Adding the KPI to the Data Set

We see that the KPI, which represents a collection of calculations that we can use to support decisions and evaluate success, and which are associated with a given measure group (or, alternatively, all measure groups) within the cube, has brought along four separate members. The member components of the Internet Revenue KPI are as follows:

  • Value – an MDX numeric expression that returns the actual value of the KPI.
  • Goal - an MDX numeric expression that typically returns the targeted (budgeted, forecast, etc.) value of the KPI.
  • Status - an MDX expression representing the state of the KPI at a specified point in time. The Status expression returns normalized values: a "-1" (interpreted as "bad" or "low"), a "0" (interpreted as "acceptable" or "medium," etc.) or a "1" (interpreted as "good" or "high").
  • Trend - an MDX expression evaluating the value of the KPI over time. The Trend allows information consumers to ascertain progress / improvement or lack of same over any business-relevant, time-based criterion.

The Goal and Value components are typically the most significant and common, although Status and Trend, which are based upon Goal and Value, are often in demand, as well. Other calculations are available besides the above examples. For an in-depth discussion of Analysis Services 2005 KPIs, see my article Mastering Enterprise BI: Introduction to Key Performance Indicators.

Let's add a couple of filters at this point. While we might select multiple years' operations for analysis, we will limit our practice report to Fiscal Year 2004. In addition, we will filter our null values to generate a more compact report.

10.  In the Filter pane, select Date in the Dimension selector.

11.  Select Date.Fiscal Year in the Hierarchy selector of the Filter pane, as depicted in Illustration 36.

Illustration 36: Select Date.Fiscal Year in the Hierarchy Selector

12.  Select Equal in the Operator selector for the Filter.

13.  Select FY 2004 in the Filter Expression selector of the Filter pane as shown in Illustration 37.

Illustration 37: Select FY 2004 in the Filter Expression Selector

The Filter pane, with our selections, appears as depicted in Illustration 38.

Illustration 38: Filter Pane with All Selections in Place

We are now ready to modify the matrix data region on the Layout tab, where the layout remains the same even though we have effectively redesigned the underlying DataSet.

14.  Click the Layout tab.

15.  Select the left-most Data field within the matrix to select it, as shown in Illustration 39.

Illustration 39: Select the Left-most Data Field in the Matrix

16.  Press the DELETE key on the PC.

17.  Delete the second and third Data fields in the matrix in similar fashion.

The second Data field (along with the respective column) disappears and the third Data field clears, leaving the Data watermark exposed, as depicted in Illustration 40.

Illustration 40: Data References Have Been Removed

Next, we will eliminate the Report Parameter for Product Category (which referenced the Product filter we removed earlier in the Filter pane of the Data tab.)

18.  Select Report --> Report Parameters ... from the main menu, as shown in Illustration 41.

Illustration 41: Select Report --> Report Parameter from the Main Menu

19.  Select the sole Product Category parameter appearing in the Parameters list on the left side of the Report Parameters dialog, which appears next.

20.  Click the Remove button underneath the Parameters list, as depicted in Illustration 42.

Illustration 42: Remove the Pre-Existing Report Parameter ...

21.  Click OK to accept the modification and dismiss the Report Parameters dialog.

We return to the Layout tab. Next, we will modify some properties of the matrix data region.

22.  Click any point within the matrix to cause its gray header and row borders to appear.

23.  Right-click the upper left corner of the matrix, as shown in Illustration 43.

Illustration 43: Getting to the Matrix Properties ...

24.  Select Properties from the context menu that appears (the gray borders disappear, and the matrix data region assumes a semi-transparent outline), as depicted in Illustration 44.

Illustration 44: Select Properties from the Context Menu

The Matrix Properties dialog opens, defaulted to the General tab.

25.  Click the Groups tab.

26.  In the Rows list in the top half of the Groups tab, select the sole group (matrix1_Sales_Reason).

27.  Click the Edit button, as shown in Illustration 45.

Illustration 45: Editing the Rows Group ...

The Grouping and Sorting dialog opens, defaulted to the General tab.

28.  Replace the contents of the Name field with the following:


29.  In the Group on section, select the following to replace the current contents of the top row of the Expression list:


The Grouping and Sorting dialog - General tab appears as depicted in Illustration 46.

Illustration 46: Grouping and Sorting Dialog for Row Group – with Modifications

30.  Click OK to accept the modifications and to return to the Matrix Properties dialog – Groups tab.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM