SSAS TUTORIAL : Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy

In this task, you define attribute relationships for the attributes in the natural user hierarchies in the Analysis Services Tutorial project. These include the Customer Geographyhierarchy in the Customer dimension, the Sales Territory hierarchy in the Sales Territorydimension, the Product Model Lines hierarchy in the Product dimension, and the Fiscal Dateand 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 theDimension 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 theGeography table to the key attribute from the Geography table. The Geographyattribute is related to the Full Name attribute. The Postal Code attribute is indirectly linked to the Full Name attribute through the Geography attribute, because thePostal 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 clickDelete.

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 typelist, 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 theAttribute 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 theRelationship 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 might change over time and because the groupings of countries 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 typelist, 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 theAttribute 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 typelist, 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 theAttributeHierarchyOrdered property to False, and set theAttributeHierarchyVisible property to False.

13.   On the File menu, click Save All.

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