Define Attribute Relationships for Better Performance

When you're architecting a Unified Dimensional Model (UDM) to enhance processing or query performance, one item to consider is how to define (or not define) the relationships between key and non-key attributes in your dimensions. If you build a dimension on a single table, as you would with a star schema, all of your non-key attributes will automatically be related to the key attribute. If you build a dimension on multiple tables, as you would with a snowflake schema, the non-key attributes will automatically be related to the key attribute in each table and to the key attribute and foreign key attribute. Attribute relationships can become complex, so it's important to understand how they operate so that you can make the necessary changes.

One reason to redefine attribute relationships is for natural hierarchy. In a product database, you might have product group, product line, product, and SKU. In such a case, you would define the product group as a member property of product line, product line as a member property of product, and so on.When you create a dimension, several problems can occur if you leave attributes related to one another only through the key attribute (as they are by default). For example, during the dimension and cube processing, you could have discovered that you needed more memory, aggregations weren't created based on efficient relationships between different attributes, and dimension members weren't stored physically in an optimized manner with related attributes stored together.

Attributes that have undefined relationships can cause a problem when you want to sort one attribute by another. A classic example of a dimension that sorts attributes that have undefined relationships is a time dimension. To sort data by month, you need to enter the name of the month in the dimension, but the months must sort by the month number. If you didn't define an attribute relationship between the month name and month number attributes (as Figure 1 shows), the month level-of-time dimension won't sort correctly.

This topic explains the general concept of defining attribute relationships but not the sorting aspect. For more information about how to define attribute relationships, read the Microsoft article "Specifying Attribute Relationships Between Attributes in a Multilevel Hierarchy" at http://msdn2.microsoft.com/en-us/library/ms166553(SQL.90).aspx.