MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist

This article is a member of the series MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services; our primary focus is the manipulation of multidimensional data sources, using MDX expressions, in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first lesson of this series: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, with respect to any MS Office components presented in a given article.

Along with MSAS, of which we have made repeated use in the previous articles of the series, additional application considerations apply for this article, because it introduces another Microsoft application, MSSQL Server 2000 Reporting Services ("Reporting Services").

For those joining the series at this point because of a desire to work with Reporting Services and its components from the perspective of authoring or managing reports, it is assumed that, along with Reporting Services (with Service Pack 1), MSSQL Server 2000, Visual Studio.NET and any other appropriate support applications are accessible to / installed on your PC, with the appropriate access rights to the associated environments, to parallel the steps of the article. If this is the first time Reporting Services is being accessed on your machine, you may need to consult the Reporting Services ReadMe files, and any associated online documentation, for installation and configuration instructions. In addition, my Reporting Services series at Database Journal offers a growing body of guidance in various aspects of using Reporting Services, which may be of assistance.

Overview

As most of us who work in the Business Intelligence community are aware, parameters (sometimes known as "prompts" or "parameter prompts") are a staple of enterprise reporting, because they enable information consumers to quickly find the information they need from a report. These filters can be put in place "on the fly," and are typically enacted when the consumer types or selects a value, or a series of values, at run time.

There are two primary types of parameters, type-in and picklist, which can be mechanized through various means. Type-in parameters accept directly typed user input for the value upon which the report is based. Alternatively, the picklist presents a selection of choices to a consumer based upon a static file, a dataset from a larger data source, or through other means. The picklist is often the tool of choice, because of its inherent elimination of typing errors. A well-constructed picklist makes selection easy for the consumer, who is not often pleased with a long scrolling process, or other cumbersome method, as the initial step in generating a commonly requested report.

Every enterprise level reporting system of which I am aware allows parameterization, most in various forms. The mechanics behind parameters differ between them, at least to some degree, but all provide the capability for prompting the consumer for filter information at runtime. The skill and forethought with which parameterization is built into a report is a critical matter, and deficiencies in this arena can ruin the user experience, no matter how capable the underlying system with which we are creating business intelligence applications.

Because it is important to always anticipate consumer desires, I maintain an "inventory" of successful approaches to meeting the "need for user friendliness." I come across such nuances frequently as a BI architect and consultant. In working with MSAS, I have found countless opportunities to "embed" support for such instrumentality at the MSAS level.

In this article, I will provide an option for the support of a picklist that we leverage within the parameters of the reporting environment. After constructing its foundation within the components of the cube, I will show the use of the picklist in Reporting Services, primarily because it is free and readily available to anyone with an MSSQL Server license. (A 120-day evaluation of both Reporting Services and MSSQL Server / Analysis Services can also be downloaded free by anyone with access to the Microsoft site, at the time of writing). It has also been my reporting tool of choice since I began beta-testing it last year. The concepts involved, however, extend to any enterprise reporting package designed to report from common OLAP data sources, and even some of the more proprietary ones, like Cognos, Business Objects, and others that provide "one-way" connectivity to MSAS cubes. It is especially applicable in the cases of tools like Crystal Analysis Pro, ProClarity and other advanced, yet relatively "open," OLAP reporting applications.

If you can successfully designate an MSAS cube as a data source, regardless of the reporting application you have, you can probably use the concepts we will be discussing here. In this article, we will:

Create a Cube-Based Hierarchical Picklist

Objective and Business Scenario

In the following sections, we will perform the steps required to create calculated members and a named set within a sample cube, to illustrate cube-level support for an organizational reporting function. Once we have accomplished the simple structural additions to MSAS, we will construct a parameter within a basic report that we have created in Reporting Services. We will base the parameterization of the report upon the calculated members and a named set that we have created in MSAS.

For purposes of our practice procedure, we will assume that information consumers within the Finance department of the FoodMart organization have expressed the need for a single, prompted parameter, based upon geographical location of their stores, within a basic report that focuses on total expense for a given location. While the consumers wish the capability to narrow the report to a view of the expenses of individual stores at any time, they have also expressed that a "nice to have" would be the capability to select, within the same report, upon various other hierarchical levels in location, such as city, state and so forth.

We immediately recognize that such a hierarchical picklist might be valuable in many other reports, and we decide to make the investment in creating the support for this functionality in the MSAS cube. We can then reuse the underlying structure easily in prospective efforts, by simply referencing it in any report we author.

We realize that other parts of the organization, who report from MSAS cubes through various applications, will be able to leverage the structure we provide in this manner: Any application that can use a calculated member / named set can take advantage of these components with ease. Moreover, we can see that the requested selection capability is applicable within other dimensions, as well, and that the concepts involved can be put in place elsewhere.

Obvious benefits are many, and include easier, more consistent reporting (to a degree, perhaps, "managed authoring...") as well as greater functionality centralized within a given report. The reports can be used to retrieve a wider range of information within a single report, meaning a dramatic reduction in the number of individual reports in the library that, in essence, accomplish the same objectives at different "rollup," and other, levels.

Considerations and Comments

For purposes of this exercise, we will prepare a copy of the Budget cube in the FoodMart 2000 sample database, which accompanies the installation of MSAS, along with other samples. The "clone" will allow us to leave the original sample cube in its pristine (or otherwise existing) condition, as we might have saved various settings, structures, and so forth, for referential or other reasons. There will therefore be no need to remember to return and remove settings that we modify for purposes of the lesson, or otherwise restore the original sample to its previous state. We can simply discard our clone upon the conclusion of our session, or at any convenient time thereafter.

While the cloning process is simple, ensure that you have the authority, access and privileges needed to accomplish the process, and that the copy of an existing MSAS cube within the FoodMart 2000 database presents no other issues in your environment. After the session, the clone can be deleted or used for another purpose, whatever is convenient.

If the sample database was not installed, or was removed prior to your beginning this article, please see the MSAS documentation, including the Books Online, for the straightforward procedure to restore the database from the archive (.cab) file containing the samples. As of this writing, a copy of the archive can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Hands-On Procedure

1. Open Analysis Manager.

2. Expand the Analysis Server folder in the management console.

3. Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4. Expand the FoodMart 2000 database.

5. Expand the Cubes folder inside the FoodMart 2000 database.

6. Right-click the Budget cube.

7. Click Copy from the context menu that appears, as shown in Illustration 1.

While the name of the calculated member can obviously be anything useful in the environment in which we are creating it, I typically use a name similar to the above, in order to make it apparent, both inside Analysis Services and in the reporting application (Reporting Services in this article), that the calculated member exists for use in the reporting ("RS") application, that it has been created to support picklists ("PX"), and that this particular calculated member exists to generate a picklist display name ("PLName") in our reports for the various levels of the Store dimension.

6. Type the following MDX into the Value Expression section of the Calculated Member Builder:

The MDX expression above returns, via our calculated member, a display name that we will use to generate the picklists that the information consumers see, as part of being prompted, each time they attempt to generate a report in which we insert the picklist to support a parameter. An explanation of the components of the above expression appears in Table 1.

Expression

Meaning

Space([Store].Currentmember.Level.Ordinal * 6)

The Space() VBA function is used together with the level ordinal property of the current member to generate a "space size." We then multiply that size value by six (6).

Our objective here is cosmetic - we are simply making the geographical levels of the Store dimension appear more intuitively hierarchical for the consumers, by "indenting" each of the various levels of the Store dimension in accordance with its levels in the hierarchy.

+ [Store].CurrentMember.Name

This section of our expression actually generates the name of the current member ...

Table 1: Expression Components Summary

The Calculated Member Builder appears with our input as depicted in Illustration 7.

The new Calculated Member appears in the tree within the Calculated Members folder.

We are again using a naming convention for the member to make it apparent, both inside Analysis Services and in the reporting application, that the calculated member exists for use in the reporting ("RS") application, that it has been created to support picklists ("PX"), and that this particular calculated member exists to generate a unique name (the "qualified name") within MDX.

The MDX expression will return, via our calculated member, the unique name (a member property) that we will use as the value field in dataset query, upon which we base our ultimate report. In other words, it generates the qualified / full name that corresponds to the "user-friendlier" picklist name that the user selects at runtime.

The Calculated Member Builder appears, with our input, as depicted in Illustration 8.

The new Calculated Members appear in the tree within the Calculated Members folder as shown in Illustration 9.

Now that we have the two calculated members in place, let's create a named set to contain them conveniently for use in the reporting application.

As we saw to be the case with the calculated members earlier, the name of the set can obviously be anything useful in the environment in which we are creating it. I typically use a name similar to the above, in order to make it apparent, both inside Analysis Services and from the reporting application (Reporting Services in this article), that the named set exists for use in the reporting ("RS") application, that it has been created to support picklists ("PX"), and that this particular named set exists to support picklists in our reports for the various levels of the Store dimension.

The set we specify above will return, via our named set, both the picklist name (which the consumers will see) and the "qualified" MSAS name. The former name provides the list from which the intended audience can select the Store levels they wish to appear in the report; the latter name supplies the MDX- "qualified" name required to filter the report appropriately.

A confirmation dialog should appear, indicating that Syntax is OK, as shown in Illustration 11.

NOTE: The cube must be processed before we can designate it as a data source in the next section.

The Process a Cube dialog appears, as depicted in Illustration 14, with the processing method defaulted to Full Process. Full processing is the only option, as this is the first processing cycle of our cloned cube.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 15.

We will leave Analysis Manager opened, as we begin a report in Reporting Services next.

We can verify the adequacy of our MSAS picklist support structures using the named set (and thus the calculated members that compose it) in a simple report, as we will do in the following steps.

Create the Report Project

First, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

The New Project dialog appears. We note that Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services (the folder was added by the installation of Reporting Services, as it established the Report Designer in Visual Studio .NET).

5. Click Business Intelligence Projects in the Project Types list, if necessary.

6. Click Report Project in the Templates list.

7. Navigate to a location in which to place the Report Project files.

8. Type the following into the Name box, leaving other settings at default:

MXAS017

The New Project dialog appears, with our addition, as shown in Illustration 18.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 19.

We have now created a Report Project, and are ready to proceed with creating a Report File.

About the Series ...

Overview

Create a Cube-Based Hierarchical Picklist

Objective and Business Scenario

Considerations and Comments

Hands-On Procedure

Create the Report Project