Expand the new
Exp_FoodMart 2000 database.
Expand the Shared
the Time dimension.
from the context menu that appears, as depicted in Illustration 5.
Illustration 5: Select
Edit from the Context Menu
the Properties pane is open to view, click the Advanced tab.
Click the Default
Member property on the Advanced tab to highlight / select it.
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
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.
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
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).
Click the ellipses
button to the right of the input box, as depicted in Illustration 7.
Illustration 7: Click
the Ellipses Button ...
following into the MDX expression box, using the MDX Builder features as
you find them helpful:
An explanation of the components of the above expression
appears in Table 1.
Selecting all months ...
Filter out those months that are "not unpopulated",
Out of the filtered subset, select the Current Member
Wrap the expression in a Tail() function to
return the "last" (most recent in time) member of the returned
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
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.
Builder appears with our input as depicted in Illustration 8.
Illustration 8: MDX
Builder with Complete MDX Expression
Click OK to
accept the input and close the MDX Builder.
to close the Set Default Member dialog.
return to the Dimension Editor, opened where we left it within the Time
--> Save from the main menu to save our changes to the Time
--> Exit to close the Dimension Editor, and to return to Analysis
Expand the Cubes
folder, within the Exp_FoodMart 2000 database.
the Warehouse cube.
from the context menu that appears, as shown in Illustration 9.
Illustration 9: Select
a Cube - Select the processing method dialog appears, with the radio button
likely to be defaulted to Full process for this cube.
Ensure that Full
process is, indeed, selected, as depicted in Illustration 10.
Illustration 10: Full
Process Mode Selected
Click OK to
begin processing of the Warehouse cube.
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.
Completion of Processing is Indicated
to close the Process viewer.