Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member

Monday Jul 19th 2004 by William Pearson
Share:

Create a dynamic default member in this hands-on article. Author Bill Pearson introduces default members, and leads a walkthrough of the steps required to meet a hypothetical business need through the definition of a custom default member.

About the Series ...

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: 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, in cases where MS Office components are presented in the article.

Overview

As most of us that work with MSAS are aware, dimensions as defined in Analysis Services contain many properties, one of which is the default member. The default member can have far-reaching effects from the perspective of information consumers, because they are often not even aware of the property, usually cannot control it, and would not likely relish controlling it even if they could. Yet the default member affects the results of their reporting and analysis ambitions because it "fills in the blanks" with regard to the setting of any unspecified dimensions in the MDX queries that they generate, be it from enterprise reporting systems, the Pivot Table Service (for example, within the new Excel OLAP add-in, which I plan to discuss in a subsequent article), or through any other vehicle they use to query an MSAS cube.

The WHERE clause that lies within every MDX query (whether it is explicit or not) is the fulcrum of the default member in reporting and analysis. In its job of describing slicer dimensions, the WHERE clause is "subsidized" by MSAS itself, because MSAS supplements any dimensions that the WHERE clause leaves unspecified, and which do not appear in an axis assignment, with the default member it derives from the dimension properties settings inside the cube structure. All dimensions are thus accounted for, allowing for precision in the data retrieval from the OLAP cube.

The default member, by default, is typically the All level for a given dimension, unless the "default" empty state of the property is changed. If the property is empty and there is no All level in place for the dimension, the default member is an assigned member inside the highest level of the dimension.

Regardless of the setting of the default member property, its use in the WHERE clause of MDX queries is not often clear to information consumers, many of which understand little more about MultiDimensional eXpressions than how to spell "MDX." I constantly encounter cases where those who query cubes do not realize that all dimensions are specified, whether they name them or not, and that the impact of MSAS' "assumptions" can affect the outcome of the results they obtain in their querying efforts.

This becomes particularly noticeable with the Time dimension(s), which, as in the sample cubes that accompany MSAS, is typically without an All level in its structure(s). The effects of the default member within the Time dimension are likely to have to be managed in most business environments. While we can certainly maintain the default member manually, from an administrative perspective, this only adds overhead to our already resource-challenged environments.

In this article, we will explore one approach to enhancing the behavior of the default member. We will:

  • Determine the existing default member setting for the dimension
  • Provide a simple solution through customization of the default member for a Time dimension;
  • Provide a more elaborate solution to provide a completely dynamic default member
  • Explain the results we obtain from the MDX that we use to accomplish each solution.

Create a Dynamic Default Member

Objective and Business Scenario

In the following section, we will perform the steps required to convert the default member for the Time dimension in a sample cube, from its installed setting of empty to a completely dynamic state. Along the way, we will examine the placement and construction of the property setting entries needed to accomplish this, as well as discussing the workings of the underlying MDX we use to bring about the desired results.

For purposes of our practice procedure, we will assume that the information consumers in our business have expressed frustration with having to manually select the current period anytime they query the cube. They specify that the business need is for the "current" period, specifically month in this scenario, to be automatically assumed by MSAS to be the latest month to which data has been posted. They want MSAS to "default" to the perspective of the current month in the MSAS Data view, as well as in their appropriately designed client applications

Considerations and Comments

For purposes of this exercise, we will prepare a copy of the FoodMart 2000 sample database that accompanies the installation of MSAS, along with other samples. The "clone" will allow us to leave the original sample database 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.

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 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

Preparation

Let's first copy the existing database 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.  Right-click the FoodMart 2000 database.

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


Illustration 1: Select Copy from the Context Menu

6.  Right-click the Analysis Server.

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


Illustration 2: Select Paste, after Right-clicking the Analysis Server

The Duplicate Name dialog appears.

8.  Rename the new database as follows:

Exp_FoodMart 2000

TIP:

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 likely 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

9.  Click OK to create the clone database.

The new Exp_FoodMart 2000 database appears in the tree as shown in Illustration 4.


Illustration 4: The New Database Appears

Procedure

1.  Expand the new Exp_FoodMart 2000 database.

2.  Expand the Shared Dimensions folder.

3.  Right-click the Time dimension.

4.  Select Edit from the context menu that appears, as depicted in Illustration 5.


Illustration 5: Select Edit from the Context Menu

The Dimension Editor opens.

5.  Ensuring that the Properties pane is open to view, click the Advanced tab.

6.  Click the Default Member property on the Advanced tab to highlight / select it.

An ellipses ("...") button appears to the far right of the Default Member property, as shown in Illustration 6.


Illustration 6: Advanced Properties Tab, Default Member Property Setting

7.  Click the ellipses button.

The Set Default Member dialog appears. As we can see, no custom default is set, assuming the "out of the box" condition for the Time dimension in the sample MSAS database from which our database is cloned. We can either select any member of the Time hierarchy to fill the default position, or type in an MDX expression to meet our needs.

Our business requirement, for purposes of this lesson, is not only to select a custom default member (versus the setting that accompanies installation for the Time dimension, as noted above), but also to enable that custom default to dynamically adjust, based upon the existence of data in our database over time. To do this, we need to supply an MDX expression that 1) ascertains the last month that houses data (a reasonable criterion for "current month", in many businesses, from the perspective of the database), and 2) flexes to automatically make that month the default month for easy access by information consumers.

8.  Click the radio button above the input box labeled Enter MDX formula to specify default member for dimension (in the lower part of the dialog).

9.  Click the ellipses button to the right of the input box, as depicted in Illustration 7.


Illustration 7: Click the Ellipses Button ...

The MDX Builder appears.

10.  Type the following into the MDX expression box, using the MDX Builder features as you find them helpful:

Tail(Filter([Time].[Month].Members,
NOT IsEmpty(Time.CurrentMember)),1).Item(0)

An explanation of the components of the above expression appears in Table 1.

Expression

Meaning

[Time].[Month].Members,

Selecting all months ...

Filter([Time].[Month].Members,

NOT IsEmpty

Filter out those months that are "not unpopulated", and ...

Filter([Time].[Month].Members,

NOT IsEmpty(Time.CurrentMember))

Out of the filtered subset, select the Current Member

Tail(Filter([Time].[Month].Members,

NOT IsEmpty(Time.CurrentMember)),1)

Wrap the expression in a Tail() function to return the "last" (most recent in time) member of the returned subset

Tail(Filter([Time].[Month].Members,

NOT IsEmpty(

Time.CurrentMember)),1).Item(0)

Convert the subset resulting from the Tail() function to a member with the .Item() function, which means the first (as indicated by the zero-based index) member in the Month level (the members of the subset) of the Time dimension.

NOTE: For an explanation of the Tail() function, see my article MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function in the MDX Essentials series at Database Journal. At this writing, the .Item() function is the subject of an article scheduled to be published in the near future, within the same series.

The MDX Builder appears with our input as depicted in Illustration 8.


Illustration 8: MDX Builder with Complete MDX Expression

11.  Click OK to accept the input and close the MDX Builder.

12.  Click OK to close the Set Default Member dialog.

We return to the Dimension Editor, opened where we left it within the Time dimension.

13.  Select File --> Save from the main menu to save our changes to the Time dimension.

14.  Select File --> Exit to close the Dimension Editor, and to return to Analysis Manager.

15.  Expand the Cubes folder, within the Exp_FoodMart 2000 database.

16.  Right-click the Warehouse cube.

17.  Select Process from the context menu that appears, as shown in Illustration 9.


Illustration 9: Select Process ...

The Process a Cube - Select the processing method dialog appears, with the radio button likely to be defaulted to Full process for this cube.

18.  Ensure that Full process is, indeed, selected, as depicted in Illustration 10.


Illustration 10: Full Process Mode Selected

19.  Click OK to begin processing of the Warehouse cube.

The cube rapidly processes, as evidenced by the Process viewer that appears next. Processing ends with a green message, indicating successful completion, as shown in Illustration 11.


Illustration 11: Completion of Processing is Indicated

20.  Click Close to close the Process viewer.

Verification

We can verify the immediate effects of our new default member setting by taking the steps that follow:

1.  Right-click the Warehouse cube again.

2.  Select Edit from the context menu that appears, as shown in Illustration 12.


Illustration 12: Select Edit ...

The Cube Editor opens.

3.  Click the Data tab to view the data.

Atop the Data View, we can see that the Time dimension now defaults to "12," the last month in the Warehouse cube with data present - indeed, the last month member of our sample cube structure. The relevant portion of the Data view appears as depicted in Illustration 13.


Illustration 13: Default Month Appears in Selector

We can verify this from an independent perspective by taking the following steps:

4.  Start the MDX Sample Application.

Note: For instruction in using the MDX Sample Application, see the MDX Sample Application Online Help. I also provide detailed steps in most of my MDX Essentials Series articles.

5.  Select the Exp_FoodMart 2000 database.

6.  Select the Warehouse cube.

7.  Type the following into the Query pane:


SELECT 

{[Time].DefaultMember} ON AXIS(0), 

NON EMPTY {[Measures].[Supply Time]} ON AXIS(1) 

FROM [Warehouse]

An explanation of the .DefaultMember function appears in Table 2.

Function

Explanation

.DefaultMember

Returns the default member, specified in the Default Member property of the dimension or hierarchy to which it is appended within an appropriate MDX expression.

8.  Execute the query.

The results pane appears as shown in Illustration 14.


Illustration 14: Default Month Appears in Selector

I selected the measure, Supply Time, simply to make sure that data was returned on both axes for visual effect. The focus of the results in the context of this article is the fact that the default member of the Time dimension is indeed 12. Its appearance in our results dataset allows us to verify independently that, by creating a dynamic default member via the definition of a custom default member in the Time dimension, we have met the business need as expressed by the information consumers.

Conclusion and a Look Ahead ...

In this article, we explored an approach for enhancing the behavior of the default member. After determining the existing default member setting for the dimension, we examined a simple customization of the default member for a Time dimension. Next, we exposed a more elaborate solution, providing a completely dynamic default member. In both cases, we examined operation of the MDX involved, and the results that the respective expressions produced.

In our next article, we will look at derived measures, and explore scenarios where their use can be a viable alternative to reliance upon the more common calculated measure.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved