Practice
Practice:
Add Parameter Support Objects to the Analysis Services Layer
We will
next add parameter support within the Analysis Services database upon which our sample report is based. To do so, we will access the Cube
Designer within the Business Intelligence Development Studio, taking the following steps:
1.
Right-click the
Adventure Works cube within the Solution Explorer.
2.
Select Open
from the context menu that appears, as depicted in Illustration 14.
Illustration 14: Opening
the Cube Designer ...
Add Calculated
Members in Analysis Services to Support Parameters within Reporting Services
As we learned in Reporting Services: Customize Automatically Created
Parameter Support Objects and Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets,
as well as in other recent articles within our series, when we create parameterized filters within our dataset(s),
Reporting Services automatically creates support objects for self-contained
parameterization at the reporting layer. These support objects include Report
Parameters, such as those we have examined in our preparation steps above,
as well as datasets to support each of those Report Parameters.
The datasets use the WITH MEMBER keywords to create calculated
members at runtime, when the datasets are instantiated in Reporting
Services, which are then referenced, by default, in the Value and Label
fields within the associated Report Parameter settings page.
Because we took the shortcut approach of having Reporting
Services generate the support objects automatically in our creation of the
date-related parameters earlier, we not only have the Report Parameters
in place, ready to be pointed to the calculated members we will create
in Analysis Services, but we also have a useful set of calculated
member definitions already in place upon which we can easily model our Analysis
Services calculated members. Let’s take a look at the DateCalendarYear
dataset for starters, as we create the calculated members we will need
in Analysis Services. In our practice session, we will only create
those we actually intend to use in the associated parameters of our report,
ignoring the additional calculated members that are provided in the automatically
created datasets. We could, of course, include all, or create custom calculated
members that are entirely different, should the need arise in our local
business environments.
We review the requirements with
the client representatives, reiterating that, while parameterization can
certainly be managed using the automatically created objects provided within Reporting
Services, several benefits accrue to us in creating the support objects
within the Analysis Services layer. Positing the intelligence within
the cube will, we assert, mean that the same logic can be carried forward to
multiple reports by simply referencing the calculations in their respective data
sources, versus performing the calculations for every affected measure in the
report (and adding to processing time, etc.). Moreover, we emphasize that
maintaining the logic in a single location in the Analysis Services
layer means a single point of maintenance: we can modify the logic in one
place and rely upon the changes to “ripple through” to all reports that
reference that logic, versus having to mechanically modify each individual
report. This also ensures consistency of application of the logic to all
affected reports: None will be overlooked for prospective modifications in the
logic, for instance, when modifications become necessary, nor will different
logic be accidentally applied to different reports.
From within the now open Cube
Designer, we will begin creating calculated members to support
parameterization in our practice report.
1.
Select the Calculations
tab within the Cube Designer.
The Calculations tab opens.
2.
Click the
bottom entry in the Script Organizer pane, to anchor the cursor, as shown
(the name of the bottom item will likely differ in your own environment) in Illustration
15.
Illustration 15: Click
the Bottom Entry in the Script Organizer to Position the New Calculation
3.
Click the New
Calculated Member button atop the Calculations tab, as depicted in Illustration
16.
Illustration 16: Click
the New Calculated Member Button
Clicking the New Calculated Member button adds a new calculation
to the Script Organizer pane (by default named “[Calculated Member]”)
and displays fields for its definition in the calculations form in the Calculations
Expressions pane.
4.
Leaving the Cube
Designer open at the Calculations tab, return to the Data tab
of the report with which we are working in Reporting Services.
5.
Click the
downward selector arrow on the right side of the Dataset selector.
6.
Select the DateCalendarYear
dataset within the selector.
The MDX for the dataset, which was
automatically generated as one of a chain of events triggered by our creation
of the parameterized filter for Year in the report’s primary
dataset (ProductData), appears (rearranged, so as to make examination of
the various sections easier), as shown in Illustration 17.
Illustration 17: MDX for
the DateCalendarYear Dataset
Many will recognize the WITH MEMBER clauses as
representing the definition of various calculated members, which we also
specify within our SELECT clause for the purposes of supporting our parameter
defaults. These work the same as the calculated members that
appeared within the automatically generated dataset queries that we examined in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Pt. II.
NOTE: For and introduction to Calculated
Members, particularly from an MDX syntax perspective, please see the
following articles:
Each of these articles is a member
of my MDX in Analysis
Services series at Database Journal.
In this dataset, the following calculated members
are created:
-
ParameterCaption
-
ParameterValue
-
ParameterLevel
We will, as we have already noted, not be referencing
these fields in the DateCalendarYear Report Parameter
definitions, but the underlying MDX for the parameter caption and value
can be used as a template to create a calculated member in Analysis
Services, as we shall see. The following portion of the MDX is of
immediate use to us in our next steps.
WITH
MEMBER
[Measures].[ParameterCaption]
AS
'[Date].[Calendar Year]. CURRENTMEMBER.MEMBER_CAPTION'
MEMBER
[Measures].[ParameterValue]
AS
'[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME'
7.
Return to the Calculations
tab of the Cube Designer.
8.
Type the
following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the
default name of “[Calculated Member]”).
YearParamCaption
9.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Calendar Year]. CURRENTMEMBER.MEMBER_CAPTION
The new YearParamCaption calculation
appears in the Calculations
Expressions pane,
with our input, as depicted in Illustration 18.
Illustration 18: The YearParamCaption
Calculation in the Calculations Expressions Pane
10.
Click the Check
Syntax button atop the Calculations tab, as shown in Illustration
19.
Illustration 19: Click
the Check Syntax Button
The Check Syntax message
box appears, indicating that the syntax check was successful, as depicted in Illustration
20.
Illustration 20: Our
Syntax Appears to Have Passed Checking ...
11.
Click OK to
dismiss the message box.
We
have successfully added support for the caption display of the Year
Report Parameter. We will now add a calculated member to house the
logic behind the value (the “qualified” MDX name) we wish to pass for
the same Report Parameter, based upon our caption selection at runtime.
12.
From our
current position within the Calculations tab, once again, click the
bottom entry in the Script Organizer pane, to anchor the cursor.
NOTE: Once a calculation is created, we can always move it up or
down within the Script Organizer pane using the Move Up or Move
Down buttons, respectively. These buttons appear together atop the Calculations
tab, as shown in Illustration 21.
Illustration 21: Move Up
and Move Down Buttons, atop the Calculations Tab
13.
Click the New
Calculated Member button, once again.
14.
Type the
following into the Name box of the currently open calculations form in the Calculations
Expressions pane (replacing the default name of “[Calculated Member]”).
YearParamValue
15.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME
The new YearParamValue calculation
appears in the Calculations
Expressions pane,
with our input, as depicted in Illustration 22.
Illustration 22: The
YearParamValueCalculation in the Calculations Expressions Pane
16.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the first calculated member we added.
17.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
We
have successfully added support for the value display of the Year
Report Parameter. This means we have now added support for both the caption
and value fields of the Report Parameter within our report for Year.
We will next add, while we are “under the hood” within Analysis Services,
a similar pair of calculated members for each of the two remaining,
date-related Report Parameters we have selected for our practice
exercise, DateCalendarQuarterofYear and DateMonthofYear.
18.
Click the New
Calculated Member button, once again.
19.
Type the
following into the Name box of the currently open calculations form in the Calculations
Expressions pane (replacing the default name of “[Calculated Member]”).
QtrParamCaption
20.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION
The new QtrParamCaption calculation appears in the Calculations Expressions
pane, with our input,
as shown in Illustration 23.
Illustration 23: The QtrParamCaption
Calculation in the Calculations Expressions Pane
21.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the first pair of calculated members we
added.
22.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
23.
Click the New
Calculated Member button, once again.
24.
Type the
following into the Name box of the currently open calculations form in the Calculations
Expressions pane (replacing the default name of “[Calculated Member]”).
QtrParamValue
25.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME
The new QtrParamValue calculation appears in the Calculations Expressions
pane, with our input,
as depicted in Illustration 24.
Illustration 24: The
QtrParamValue Calculation in the Calculations Expressions Pane
26.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the calculated member we added earlier.
27.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
28.
Click the New
Calculated Member button, once again.
29.
Type the
following into the Name box of the currently open calculations form in the Calculations
Expressions pane (replacing the default name of “[Calculated Member]”).
MoParamCaption
30.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION
The new MoParamCaption calculation appears in the Calculations Expressions
pane, with our input,
as shown in Illustration 25.
Illustration 25: The
MoParamCaption Calculation in the Calculations Expressions Pane
31.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the other calculated members we added.
32.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
33.
Click the New
Calculated Member button, once again.
34.
Type the
following into the Name box of the currently open calculations form in the Calculations
Expressions pane (replacing the default name of “[Calculated Member]”).
MoParamValue
35.
Type (or cut
and paste) the following into the Expression box of the expanded Expression
section below:
[Date].[Month of Year].CURRENTMEMBER.UNIQUENAME
The new MoParamValue calculation appears in the Calculations Expressions
pane, with our input,
as depicted in Illustration 26.
Illustration 26: The MoParamValue
Calculation in the Calculations Expressions Pane
36.
Click the Check
Syntax button atop the Calculations tab to ascertain syntactically
correct input, as we did for the first calculated member we added.
37.
Click OK to
dismiss the message box indicating the successful syntax check has occurred.
We have completed the addition of
the Analysis Services objects required to support the targeted
date-related Report Parameters in the Reporting layer.
» 
Comment and Contribute
