Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member - Page 3
July 19, 2004
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.
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.
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.
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.
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.
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.
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.
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.
20. Click Close to close the Process viewer.