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
RevenueKPI 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:
matrix1_ProductSubcategory
29. In the Group on section, select
the following to replace the current contents of the top row of the Expression
list:
=Fields!Subcategory.Value
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.
» 
Comment and Contribute
