Edit

Share via

4-6-Specifying Attribute Relationships in User-Defined Hierarchy

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

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/region, 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, SQL Server Analysis Services can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, SQL Server Analysis Services will aggregate all non-key attributes from the key attribute. Therefore, if the underlying data supports it, you should define attribute relationships between attributes. Defining attribute relationships improves dimension, partition, and query processing performance. For more information, see Define Attribute Relationships and Attribute Relationships.

When you define attribute relationships, you can specify that the relationship is either flexible or rigid. If you define a relationship as rigid, SQL Server Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as rigid actually changes, SQL Server 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 Define Attribute Relationships, and User Hierarchy Properties.

In the tasks in this topic, you define attribute relationships for the attributes in the natural user hierarchies in the SQL Server 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 Date and Calendar Date hierarchies in the Date dimension. These user hierarchies are all natural hierarchies.

Defining 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 pane, notice the levels in the Customer Geography user-defined hierarchy. This hierarchy is currently just a drill-down path for users, as no relationship between levels or attributes have been defined.

  2. Click the Attribute Relationships tab.

    Notice the four attribute relationships that link the non-key attributes from the Geography table to the key attribute from the Geography table. The Geography attribute is related to the Full Name attribute. 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. Next, we will change the attribute relationships so that they do not use the Geography attribute.

  3. In the diagram, right-click the Full Name attribute and then select New Attribute Relationship.

  4. In the Create Attribute Relationship dialog box, the Source Attribute is Full Name. Set the Related Attribute to Postal Code. In the Relationship type list, leave the relationship type set to Flexible because relationships between the members might change over time.

  5. Click OK.

    A warning icon appears in the diagram because the relationship is redundant. The relationship Full Name -> Geography-> Postal Code already existed, and you just created the relationship Full Name -> Postal Code. The relationship Geography-> Postal Code is now redundant, so we will remove it.

  6. In the Attribute Relationships pane, right-click Geography-> Postal Code and then click Delete.

  7. When the Delete Objects dialog box appears, click OK.

  8. In the diagram, right-click the Postal Code attribute and then select New Attribute Relationship.

  9. In the Create Attribute Relationship dialog box, the Source Attribute is Postal Code. Set the Related Attribute to City. In the Relationship type list, leave the relationship type set to Flexible.

  10. Click OK.

    The relationship Geography-> City is now redundant so we will delete it.

  11. In the Attribute Relationships pane, right-click Geography-> City and then click Delete.

  12. When the Delete Objects dialog box appears, click OK.

  13. In the diagram, right-click the City attribute and then select New Attribute Relationship.

  14. In the Create Attribute Relationship dialog box, the Source Attribute is City. Set the Related Attribute to State-Province. In the Relationship type list, set the relationship type to Rigid because the relationship between a city and a state will not change over time.

  15. Click OK.

  16. Right-click the arrow between Geography and State-Province and then click Delete.

  17. When the Delete Objects dialog box appears, click OK.

  18. In the diagram, right-click the State-Province attribute and then select New Attribute Relationship.

  19. In the Create Attribute Relationship dialog box, the Source Attribute is State-Province. Set the Related Attribute to Country-Region. In the Relationship type list, set the relationship type to Rigid because the relationship between a state-province and a country-region will not change over time.

  20. Click OK.

  21. In the Attribute Relationships pane, right-click Geography-> Country-Region and then click Delete.

  22. When the Delete Objects dialog box appears, click OK.

  23. Click the Dimension Structure tab.

    Notice that when you delete the last attribute relationship between Geography and other attributes, that Geography itself is deleted. This is because the attribute is no longer used.

  24. On the File menu, click Save All.

Defining Attribute Relationships for Attributes in the Sales Territory Hierarchy

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

  2. In the diagram, right-click the Sales Territory Country attribute and then select New Attribute Relationship.

  3. In the Create Attribute Relationship dialog box, the Source Attribute is Sales Territory Country. Set the Related Attribute to Sales Territory Group. In the Relationship type list, leave the relationship type set to Flexible.

  4. Click OK.

    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 is set to Flexible because the groupings of regions within a country/region might change over time and because the groupings of countries/regions into groups might change over time.

Defining Attribute Relationships for Attributes in the Product Model Lines Hierarchy

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

  2. In the diagram, right-click the Model Name attribute and then select New Attribute Relationship.

  3. In the Create Attribute Relationship dialog box, the Source Attribute is Model Name. Set the Related Attribute to Product Line. In the Relationship type list, leave the relationship type set to Flexible.

  4. Click OK.

Defining Attribute Relationships for Attributes in the Fiscal Date Hierarchy

  1. Switch to Dimension Designer for the Date dimension, and then click the Attribute Relationships tab.

  2. In the diagram, right-click the Month Name attribute and then select New Attribute Relationship.

  3. In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set the Related Attribute to Fiscal Quarter. In the Relationship type list, set the relationship type to Rigid.

  4. Click OK.

  5. In the diagram, right-click the Fiscal Quarter attribute and then select New Attribute Relationship.

  6. In the Create Attribute Relationship dialog box, the Source Attribute is Fiscal Quarter. Set the Related Attribute to Fiscal Semester. In the Relationship type list, set the relationship type to Rigid.

  7. Click OK.

  8. In the diagram, right-click the Fiscal Semester attribute and then select New Attribute Relationship.

  9. In the Create Attribute Relationship dialog box, the Source Attribute is Fiscal Semester. Set the Related Attribute to Fiscal Year. In the Relationship type list, set the relationship type to Rigid.

  10. Click OK.

Defining Attribute Relationships for Attributes in the Calendar Date Hierarchy

  1. In the diagram, right-click the Month Name attribute and then select New Attribute Relationship.

  2. In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set the Related Attribute to Calendar Quarter. In the Relationship type list, set the relationship type to Rigid.

  3. Click OK.

  4. In the diagram, right-click the Calendar Quarter attribute and then select New Attribute Relationship.

  5. In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Quarter. Set the Related Attribute to Calendar Semester. In the Relationship type list, set the relationship type to Rigid.

  6. Click OK.

  7. In the diagram, right-click the Calendar Semester attribute and then select New Attribute Relationship.

  8. In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Semester. Set the Related Attribute to Calendar Year. In the Relationship type list, set the relationship type to Rigid.

  9. Click OK.

Defining Attribute Relationships for Attributes in the Geography Hierarchy

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

  2. In the diagram, right-click the Postal Code attribute and then select New Attribute Relationship.

  3. In the Create Attribute Relationship dialog box, the Source Attribute is Postal Code. Set the Related Attribute to City. In the Relationship type list, set the relationship type to Flexible.

  4. Click OK.

  5. In the diagram, right-click the City attribute and then select New Attribute Relationship.

  6. In the Create Attribute Relationship dialog box, the Source Attribute is City. Set the Related Attribute to State-Province. In the Relationship type list, set the relationship type to Rigid.

  7. Click OK.

  8. In the diagram, right-click the State-Province attribute and then select New Attribute Relationship.

  9. In the Create Attribute Relationship dialog box, the Source Attribute is State-Province. Set the Related Attribute to Country-Region. In the Relationship type list, set the relationship type to Rigid.

  10. Click OK.

  11. In the diagram, right-click the Geography Key attribute and then select Properties.

  12. Set the AttributeHierarchyOptimizedState property to NotOptimized, set the AttributeHierarchyOrdered property to False, and set the AttributeHierarchyVisible property to False.

  13. On the File menu, click Save All.

  14. On the Build menu of SQL Server Data Tools, click Deploy Analysis Services Tutorial.

Next Task in Lesson

Defining the Unknown Member and Null Processing Properties

See Also

Define Attribute Relationships
User Hierarchy Properties