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 Jul 26, 2004

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

By William Pearson

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.

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.

Illustration 1: Select Copy from the Context Menu

8.  Right-click the Cubes folder.

9.  Select Paste from the context menu that appears, as shown in Illustration 2.

Illustration 2: Select Paste, after Right-clicking the Cubes Folder

The Duplicate Name dialog appears.

10.  Rename the new database as follows:



This is also a good way to rename MSAS objects for which a "Rename" option does not exist. We simply create the new object in the manner shown above, give it the desired name, and discard the original object, as appropriate.

Keep in mind that, in the case of cubes and other structural objects, this will mean reprocessing before the clone will be fully usable.

The Duplicate Name dialog appears as depicted in Illustration 3.

Illustration 3: Duplicate Name Dialog with Our Input

11.  Click OK to create the clone cube.

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

Illustration 4: The New Cube Appears

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