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

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

Let's first copy the existing cube to provide a disposable work environment.

The Duplicate Name dialog appears.

Exp_Finance

The Duplicate Name dialog appears as depicted in Illustration 3.

The new Exp_Finance cube appears in the tree as shown in Illustration 4.