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 Apr 29, 2008

Support Parameterization from Analysis Services - Parameter Defaults - Page 2

By William Pearson


Practice: Add Parameter Default Support Objects to the Analysis Services Layer

We will next add parameter default 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 shown in Illustration 6.

Illustration 6: Opening the Cube Designer ...

Add Calculated Members in Analysis Services to Support Parameter Defaults 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, as we have seen, 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 in Reporting Services: Customize Automatically Created Parameter Support Objects we had the Report Parameters in place, ready to then be pointed to the calculated members we created in Analysis Services in Support Parameterization from Analysis Services. In this article, we will save time in like manner, creating support objects in Analysis Services which we will then reference in a dataset, and then tie to the appropriate Report Parameters in Reporting Services, as we shall see. We will continue with the somewhat intuitive date-related parameters we have already established, as we create the calculated members we will need in Analysis Services to support parameter defaults. We could, of course, include additional custom calculated members that are entirely different, should the need arise in our local business environments.

We review the parameter default 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, as an early example, 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 across different reports.

From within the now open Cube Designer, we will begin creating Analysis Services objects, specifically a named set and several calculated members, to support parameter defaults in our practice report. Keep in mind that there are multiple ways to accomplish this effort: we will focus upon the creation of simple calculated members that we can easily pull into our existing report datasets to illustrate a straightforward approach that might be handled in more sophisticated ways.

1.  Select the Calculations tab within the Cube Designer.

The Calculations tab opens.

2.  Click the bottom entry in the Script Organizer pane (the name of the bottom item will likely differ in your own environment) to anchor the cursor, as depicted in Illustration 7.

Illustration 7: Click the Bottom Entry in the Script Organizer to Position the New Calculation

3.  Click the New Named Set button atop the Calculations tab, as shown in Illustration 8.

Illustration 8: Click the New Calculated Member Button

Clicking the New Named Set button adds a new named set to the Script Organizer pane (by default named “[Named Set]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane.

At this stage, let’s review what we already have in place in the report datasets. The focus is to build upon what we have previously put in place, and leverage the existing datasets to simply include, as part of the data they entrain from Analysis Services, the calculated members we employ there, in later steps, to generate our parameter defaults.

4.  Leaving the Cube Designer open at the Calculations tab, return to the DBJ_OLAP_Report where we left it open in Layout view earlier.

5.  Click the Data tab of the report to expose the Query pane.

6.  Click the downward selector arrow on the right side of the Dataset selector.

7.  Select the AS_DateYrParams_Support dataset (which we created in Support Parameterization from Analysis Services), as shown in Illustration 8.

Illustration 9: Select the AS_DateYrParams_Support Dataset ...

The MDX for the dataset, which we added as a part of our steps in aligning parameter support to the respective Analysis Services calculated members in our earlier article, appears as depicted in Illustration 10.

Illustration 10: MDX for the AS_DateYrParams_Support Dataset

We note that the query simply selects calculated members for parameter caption (what the information consumer sees in the parameter selector at runtime) and parameter value (the qualified MDX value that is passed to Analysis Services to query and retrieve data at runtime, based upon the selection of the information consumer). We can certainly use this logic to propel our existing solution - we need only extend the solution to include support for parameter defaults. To do so we will return to Analysis Services, and create a named set and additional calculated members within our cube calculations, whose mission will be to specify the default for this, and each of the other two, date-related parameters, as we shall see. Once we have created the additional calculated members, we will return to reference them in a separate dataset through which we align the newly created Analysis Services objects with the respective report parameters.


For more information about Named Sets, particularly from an MDX syntax perspective, please see Named Sets Revisited, a member of my Introduction to MSSQL Server Analysis Services series at Database Journal.

For an 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.

8.  Return to the Calculations tab of the Cube Designer.

9.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Named Set]”).

[Last Period Sales]

10.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

            NOT ISEMPTY ([Measures].[Internet Sales Amount])

The new [Last Period Sales] calculation appears in the Calculations Expressions pane, as shown in Illustration 11.

Illustration 11: The [Last Period Sales] Named Set in the Calculations Expressions Pane

11.  Click the Check Syntax button atop the Calculations tab, as depicted in Illustration 12.

Illustration 12: Click the Check Syntax Button

The Check Syntax message box appears, indicating that the syntax check was successful, as shown in Illustration 13.

Illustration 13: Our Syntax Appears to Have Passed Checking ...

12.  Click OK to dismiss the message box.

We have successfully added support, via the named setLast Period Sales,” for what will become a row axis in a Reporting Services dataset, as we shall see in short order. We have, in effect, specified that Analysis Services use the logic within the named set to determine the “most recent period” (including Year, Quarter, and Month) for which our cube contains data. The logic accomplishes this by identifying the most recent Period for which the measure Internet Sales Amount is not empty. We use the MDX Tail() function to accomplish selection of “the last non-empty” period - by employing the MDX Filter() function to filter out any except periods that are “occupied” by an Internet Sales Amount.

Because we are retrieving data at the Month level, the corresponding row set returned by Reporting Services will also include, as we shall see, higher levels within the Date dimension (Calendar hierarchy) - a characteristic of Reporting Services that produces a dataset ideal for many purposes.

NOTE: For more details surrounding the MDX Tail() function, see Basic Set Functions: Subset Functions: The Tail() Function. For more information about the Filter() function, see Basic Set Functions: The Filter() Function. Examples of these popular and useful functions in action can also be found in numerous other member articles throughout my MDX Essentials series at Database Journal.

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