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.
» 
Comment and Contribute
