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

September 10, 2002

Summary- and Detail- Level Properties

Now let's look at summary- and detail-level member properties. Member properties can exist at any level of the hierarchy, as we have seen from the above tables and at other points in the lesson. There are myriad ways to use this circumstance to add valuable information to our dimensional levels. Moreover, many tables we might use in dimension building contain columns that, while housing valuable information, might not warrant placement as a discreet level in the dimensional hierarchy. Let's gain some exposure to potential uses for summary- and detail-level member properties to accommodate these scenarios.

First, let's consider a situation in our current multi-table dimension build where a summary-level member property might be used to add value to our design. The product_class table contains a column called product_family. Let's assume, in the case of the current example, that management wants to be able to easily display the product_family information but does not want to introduce the information as a new level in the somewhat simple but effective hierarchy that we have already constructed for the Product dimension (any given department can only belong to one family, after all, they reason). They do not intend to routinely report summaries based upon the product_family attribute with the cube that will be driven from the model we are building, but only want the information on an "as needed" basis. We will take the following steps to assign the product_family column to a new product_department level, creating a summary-level member property in the process.

16. Add a product_department level to the hierarchy, immediately above the Category level, by dragging the product_department column from the product_ class table (you may need to click the Schema tab to access the table) onto the Category level in the Dimension tree.
17. Click / highlight the new product_department level, then click the Basic tab in the Properties pane below the Dimension tree.
18. Rename the product_department level to Department by making the appropriate change to the Name value in the Properties pane.
19. Press Enter.

The Dimension tree should now appear as illustrated below.

Illustration 30: The Dimension Tree with the new Department Level

20. With the Department level highlighted, select Insert --> Member Property at the top menu.

The Insert Property dialog appears, as shown in Illustration 31 below.

Illustration 31: The Insert Member Property Dialog

21. Double-click product_family in the Member Property dialog.

The Insert Property dialog closes, and we return to the Dimension Editor, where we can see the new Product Family addition to the Member Properties folder, under the Department level in the Dimension tree, as shown below.

Illustration 32: The Added Member Property Appears in the Dimension Tree

22. Select File --> Save from the top menu to save the modifications to the Product dimension.

We have added a summary-level member property to accommodate the needs of the Information Consumers to be able to associate each Product Family attribute with its respective Product Department in their reporting functions.

Page 10: Conclusions