We now wish to complete the addition of the other levels to our Region dimension. We will follow the common-sense hierarchy for the table members, based upon geography in our example, while acknowledging that relationships might not be intuitive in many real-world scenarios. As most of us know, understanding the relationships of the table members to each other, as well as understanding their reporting significance, becomes vital to an effective design process.
9. Switch to the Schema tab in the Dimension Editor (See Illustration 10 above for a "map," if necessary).
10. Drag the sales_district column from the Region table onto the Region dimension.
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.
Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.xReceive news via our XML/RSS feed
IE 7
Firefox 2.0