Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 19, 2004

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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