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 19, 2004

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

By William Pearson


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:


{[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.




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.

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