Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member - Page 2
July 19, 2004
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).
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.
6. Right-click the Analysis Server.
7. Select Paste from the context menu that appears, as shown in Illustration 2.
The Duplicate Name dialog appears.
8. Rename the new database as follows:
The Duplicate Name dialog appears as depicted in Illustration 3.
9. Click OK to create the clone database.
The new Exp_FoodMart 2000 database appears in the tree as shown in Illustration 4.