Introduction to SQL Server 2000 Analysis Services: Working with Dimensions - Page 4

Dragging the sales_district column onto the Region dimension makes it appear beneath the Region level, as shown below. This is because dropping the sales_district column onto the Region dimension adds the column at the dimension's lowest level. Our intent is to make sales_district lower in the hierarchy than Region; hence this is a good way to start.

Click to Enlarge
Illustration 14: Adding the Sales District Level to the Dimension Tree

11. Rename the new level to District (see Step 7 above for guidance).

We next want to add sales_country as a level above Region.

12. Drag the sales_country column from the Region table onto the Region level.

Dragging the sales_country column onto the Region level makes it appear above the Region level, as shown below.

Illustration 15: Adding the Sales Country Level to the Dimension Tree

13. Rename the new Sales Country level to Country.
14. Drag the sales_state_province column from the Region table onto the District level.

Dragging the sales_state_province column onto the District level makes it appear above the District level, (it becomes the parent level of the level upon which it is dropped) as shown below.

Illustration 16: Adding the Sales State Province Level to the Dimension Tree

15. Rename the new Sales State Province level to State.
16. Drag the sales_city column from the Region table onto the Region dimension.

Dragging the sales_city column onto the Region dimension makes it appear beneath the District level (the lowest level before). It drops to the lowest level of the dimension, as explained earlier.

17. Rename the new Sales City level to City.
18. Compare the Region Dimension tree hierarchy to the partial view of the hierarchy depicted below.

Illustration 17: The Region Dimension Hierarchy

We can review our work by performing another browse of the dimension, this time seeing the members that exist at each of the levels we have just created.

19. Click the Data tab (for location, see Illustration 10 above). This retrieves a hierarchical representation of the Region dimension. We can expand the Region dimension to see the hierarchical levels that we have added in the foregoing steps, as depicted below.

Illustration 18: The New Hierarchical Levels of the Region Dimension

Simply because we can, let's change the Dimension Members List's "All Region" top level name to "World" in our Region Dimension Members list.

20. In the Dimension tree, click / highlight the Region dimension.
21. Click the Advanced tab (Properties pane below the Dimension tree).
22. Click the All Caption property value to highlight it.
23. Change the Caption to read "World".

The top level name will change to "World" when any refresh action takes place (for example, clicking the Schema tab, then clicking the Data tab).

24. Click the Save Button (shown below), or select File --> Save, to save the single-table dimension that we have created.

Illustration 19: The Save Button

Page 5: Building and Managing a Multi-Table Dimension