Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 10, 2002

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

By William Pearson

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.

Illustration 19: The Save Button

Page 5: Building and Managing a Multi-Table Dimension

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM