Dimension and Member Properties and Considerations
While an understanding of the workings of the Dimension Editor is key to the successful design and building of our dimensions and, ultimately, cubes, a good working knowledge of the data itself is another essential part of designing a useful structure. We will explore our data as we design dimensions to ensure sound planning and construction of hierarchical levels. In the real world, this is an important stage; it is a phase within which we often adjust properties to be consistent with our design as we determine the business requirements for the dimensions and cube, and compare these desired states (of the Information Consumers) with the existing state (of the data itself).
Analysis Services makes a quick review of the data simple. One of the niftiest features of the Dimension Editor, from a designer's/developer's perspective, is the capability it offers us to do an ad hoc browse of a sample of the data in any table that we have in our designer's crosshairs. Let's do a quick browse of the Product table, and see how useful the information returned can be in helping us consider dimension property setpoints within the context of our data.
Browsing the Data
10. Right-click on the product_class table with the mouse pointer on the caption bar (the blue bar in the picture below, which contains the table name, at the top of the table).
Illustration 27: Right-click on the Caption Bar to Initialize a Data Browse of the Table
1. Select Browse Data.
2. The Browse Data Viewer appears - with the first 1,000 rows of data in the table, as shown below.
Illustration 28: The Browse Data Viewer
3. Close the Browse Data viewer.
4. Double-click the product_name column in the product table.
Product_name is added as a new bottom level to the Dimension tree. The effect is much the same as dragging a column name onto the Product dimension, after the manner of our single-table dimension build in the earlier section.
5. Drag the product_subcategory column from the product_class table onto the product_name level in the Dimension tree.
6. Drag the product_category column from the product_class table onto the product_subcategory level.
As we might have expected from working with the Region hierarchy in the Dimension tree in the Single-table Dimensions section earlier, this creates new levels for the dragged columns, each above the levels onto which they have been dropped.
7. Rename the Product Category and Product Subcategory levels in the Dimension tree to Category and Subcategory, respectively. (For guidance on renaming levels, see the earlier section, Building and Managing a Single-Table Dimension.)
The Dimension tree should appear as illustrated below.
Illustration 29: The Newly Defined Product Hierarchy - Dimension Tree View
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