Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy

  • Article

As you have already learned in this tutorial, you can organize attribute hierarchies into levels within user hierarchies to provide navigation paths for users in a cube. A user hierarchy can represent a natural hierarchy, such as city, state, and country, or can just represent a navigation path, such as employee name, title, and department name. To the user navigating a hierarchy, these two types of user hierarchies are the same.

With a natural hierarchy, if you define attribute relationships between the attributes that make up the levels, Microsoft SQL Server 2005 Analysis Services (SSAS) can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, Analysis Services will aggregate all non-key attributes from the key attribute. Additionally, as you have already learned, when you define such a relationship, you can specify that the relationship is either flexible or rigid. If you define a relationship as rigid, Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as rigid actually changes, Analysis Services generates an error during processing unless the dimension is fully processed. Specifying the appropriate relationships and relationship properties increases query and processing performance. For more information, see Defining and Configuring an Attribute Relationship, and Configuring User-Defined Hierarchy Properties.

In the tasks in this topic, you define attribute relationships for the attributes in the natural user hierarchies in the Analysis Services Tutorial project. These include the Customer Geography hierarchy in the Customer dimension, the Sales Territory hierarchy in the Sales Territory dimension, the Product Model Lines hierarchy in the Product dimension, and the Fiscal Time and Calendar Time hierarchies in the Time dimension. These user hierarchies are all natural hierarchies.

Defining Attribute Relationships for Attributes in the Customer Geography Hierarchy

To define attribute relationships for attributes in the Customer Geography hierarchy

  1. Switch to Dimension Designer for the Customer dimension, and then click the Dimension Structure tab.

    In the Hierarchies and Levels pane, notice the levels in the Customer Geography user-defined hierarchy. This hierarchy is currently just a drill down path for users - no relationship between levels or attributes have been defined.

  2. In the Attributes pane, expand Geography.

    Notice the four attribute relationships that link the non-key attributes from the Geography table to the key attribute from the Geography table.

  3. In the Attributes pane, expand Full Name.

    Notice that the Geography attribute is related to the Full Name attribute. Notice also that the Postal Code attribute is indirectly linked to the Full Name attribute through the Geography attribute, because the Postal Code is linked to the Geography attribute and the Geography attribute is linked to the Full Name attribute.

  4. Drag the Postal Code attribute relationship from the Geography attribute to the <new attribute relationship> tag for the Full Name attribute.

    The Postal Code attribute is now directly related to the Full Name attribute. In the Properties window, notice that the RelationshipType property for this attribute is set to Flexible. This is appropriate because the relationship between a customer and a postal code may change over time.

  5. In the Attributes pane, expand the Postal Code attribute.

    The City attribute is currently related to the Postal Code attribute through the Geography attribute, rather than being directly related.

  6. Drag the City attribute relationship from the Geography attribute to the <new attribute relationship> tag for the Postal Code attribute.

    The City attribute is now directly related to the Postal Code attribute. In the Properties window, notice that the RelationshipType property for this attribute is set to Flexible. This is appropriate because the relationship between a city and a postal code may change over time.

  7. In the Attributes pane, expand City.

    The State-Province attribute is currently related to the City attribute through the Full Name and Geography attributes.

  8. Drag the State Province Name attribute relationship from the Geography attribute to the <new attribute relationship> tag for the City attribute, and then change the value of the RelationshipType property for this attribute relationship to Rigid.

    The value of the RelationshipType property for the attribute relationship should be set to Rigid because the relationship between a city and a state will not change over time.

  9. In the Attributes pane, expand State-Province, drag the Country-Region attribute relationship from the Geography attribute to the <new attribute relationship> tag for the State-Province attribute, and then change the value of the RelationshipType property for this attribute relationship to Rigid.

    The value of the RelationshipType property of this attribute relationship should be set to Rigid because the relationship between a state-province and a country-region will not change over time.

  10. In the Attributes pane, delete the Geography attribute.

    This attribute is no longer needed.

Note

In this task, you moved the attribute relationships from the Geography attribute to the other attributes, instead of creating new attribute relationships for each of those attributes. Defining redundant relationships generally adds no value and unnecessarily increases processing time.

Defining Attribute Relationships for Attributes in the Sales Territory Hierarchy

To define attribute relationships for attributes in the Sales Territory hierarchy

  1. Open Dimension Designer for the Sales Territory dimension, and then click the Dimension Structure tab.

  2. In the Hierarchies and Levels pane, click the Sales Territories hierarchy and then expand Sales Territory Region and Sales Territory Country.

    Notice that Sales Territory Group is linked directly to Sales Territory Region, the key attribute, and is not linked to the Sales Territory Country attribute.

  3. Drag the Sales Territory Group attribute relationship from the Sales Territory Region attribute to the <new attribute relationship> tag for the Sales Territory Country attribute.

    Sales Territory Group is now linked to Sales Territory Country, and Sales Territory Country is now linked to Sales Territory Region. The RelationshipType property for each of these relationships should be set to Flexible because the groupings of regions within a country might change over time and because the groupings of countries into groups might change over time.

    Note   You can set attribute relationships for user-defined hierarchies in either the Attributes pane or the Hierarchies and Levels pane.

Defining Attribute Relationships for Attributes in the Product Model Lines Hierarchy

To define attribute relationships for attributes in the Product Model Lines hierarchy

  1. Open Dimension Designer for the Product dimension, and then click the Dimension Structure tab.

  2. In the Attributes pane, expand the Model Name attribute and the Product Name attribute.

  3. Drag the Product Line attribute relationship from the Product Name attribute to the <new attribute relationship> tag for the Model Name attribute.

    The value of the RelationshipType property for this attribute relationship should be set to Flexible because the relationship between a product line and a model name might change over time.

Defining Attribute Relationships for Attributes in the Fiscal Time Hierarchy

To define attribute relationships for attributes in the Fiscal Time hierarchy

  1. Switch to Dimension Designer for the Time dimension, and then click the Dimension Structure tab.

  2. In the Attributes pane, expand the following attributes:

    • Date
    • Month Name
    • Fiscal Quarter
    • Fiscal Semester

  3. Drag the Fiscal Quarter attribute relationship from the Date attribute to the <new attribute relationship> tag for the Month Name attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

  4. Drag the Fiscal Semester attribute relationship from the Date attribute to the <new attribute relationship> tag for the Fiscal Quarter attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

  5. Drag the Fiscal Year attribute relationship from the Date attribute to the <new attribute relationship> tag for the Fiscal Semester attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

Defining Attribute Relationships for Attributes in the Calendar Time Hierarchy

To define attribute relationships for attributes in the Calendar Time hierarchy

  1. In the Attributes pane, expand Month Name, Calendar Quarter, and Calendar Semester.

  2. Drag the Calendar Quarter attribute relationship from the Date attribute to the <new attribute relationship> tag for the Month Name attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

  3. Drag the Calendar Semester attribute relationship from the Date attribute to the <new attribute relationship> tag for the Calendar Quarter attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

  4. Drag the Calendar Year attribute relationship from the Date attribute to the <new attribute relationship> tag for the Calendar Semester attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

Defining Attribute Relationships for Attributes in the Geography Hierarchy

To define attribute relationships for attributes in the Geography hierarchy

  1. Open Dimension Designer for the Geography dimension, and then click the Dimension Structure tab.

  2. In the Attributes pane, expand the following attributes:

    • City
    • Geography Key
    • Postal Code
    • State-Province

  3. Drag the City attribute relationship from the Geography Key attribute to the <new attribute relationship> tag for the Postal Code attribute.

    Because postal codes within a city may change over time, the appropriate value for the RelationshipType property for this attribute is Flexible.

  4. Drag the State -Province attribute relationship from the Geography Key attribute to the <new attribute relationship> tag for the City attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

  5. Drag the Country-Region attribute relationship from the Geography Key attribute to the <new attribute relationship> tag for the State-Province attribute, and then set the value for the RelationshipType property for this attribute to Rigid.

  6. Set the Geography Key attribute to not visible, not optimized, and not ordered.

  7. Deploy the Analysis Services Tutorial project.

Next Task in Lesson

Defining the Unknown Member and Null Processing Properties

See Also

Other Resources

Defining and Configuring an Attribute Relationship
Configuring User-Defined Hierarchy Properties

Help and Information

Getting SQL Server 2005 Assistance