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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 10, 2002

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

By William Pearson


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




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