Use of attribute relationship in ssas

Attribute relationship example | ORAYLIS

use of attribute relationship in ssas

With a natural hierarchy, if you define attribute relationships between the attributes that make up the levels, Analysis Services can use an. Attribute Relationships tab in Analysis Services I still saw some cases recently where the improper use of attribute relationship settings caused wrong query results (which might even be worse.

Specifying the appropriate relationships and relationship properties increases query and processing performance.

ssas attribute relationships tab

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 user hierarchies are all natural hierarchies. 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.

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.

SQL Server Analysis Services Attribute Relationships

Next, we will change the attribute relationships so that they do not use the Geography attribute. Set the Related Attribute to Postal Code.

use of attribute relationship in ssas

In the Relationship type list, leave the relationship type set to Flexible because relationships between the members might change over time. A warning icon appears in the diagram because the relationship is redundant.

SQL Server Analysis Services Attribute Relationships

When the Delete Objects dialog box appears, click OK. Set the Related Attribute to City. In the Relationship type list, leave the relationship type set to Flexible.

use of attribute relationship in ssas

In the diagram, right-click the City attribute and then select New Attribute Relationship. 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. Right-click the arrow between Geography and State-Province and then click Delete. 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. 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. For Analysis Services, this specifies a natural relationship and potential aggregation. In the Customer dimension, a natural hierarchy exists for the Country, Region, City, and Customer attributes.

  • Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy
  • Attribute Relationships
  • Introduction to Attribute Relationships in MSSQL Server Analysis Services - Page 2

The Country attribute as an attribute relationship to the Region attribute. The Region attribute as an attribute relationship to the City attribute.

The City attribute as an attribute relationship to the Customer attribute. For navigating data in the cube, you can also create a user-defined hierarchy that does not represent a natural hierarchy in the data which is called an ad hoc or reporting hierarchy.

use of attribute relationship in ssas

Users do not see any difference in how the two hierarchies behave, although the natural hierarchy benefits from aggregating and indexing structures - hidden from the user - that account for the natural relationships in the source data.

The SourceAttribute property of a level determines which attribute is used to describe the level. The KeyColumns property on the attribute specifies the column in the data source view that supplies the members. The NameColumn property on the attribute can specify a different name column for the members.