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 2

By William Pearson

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


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


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

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