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
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
first copy the existing database to provide a disposable work environment.
Expand the Analysis
Server folder in the management console.
Expand the Analysis
Server with which you are working by clicking the "+" sign to its
the FoodMart 2000 database.
from the context menu that appears, as shown in Illustration 1.
Illustration 1: Select
Copy from the Context Menu
the Analysis Server.
from the context menu that appears, as shown in Illustration 2.
Illustration 2: Select
Paste, after Right-clicking the Analysis Server
Name dialog appears.
Rename the new
database as follows:
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.
Name dialog appears as depicted in Illustration 3.
Duplicate Name Dialog with Our Input
Click OK to create the
Exp_FoodMart 2000 database appears in the tree as shown in Illustration
Illustration 4: The New