Ssas flexible and rigid relationship

Thinking: Rigid & Flexible relationship of attributes in SSAS

ssas flexible and rigid relationship

A flexible attribute relationship (customer-city) is one where members can move around during dimension updates, and; A rigid attribute. Flexible and Rigid Relationship Types in SSAS In SSAS, when building dimensions you'll notice that in the “Attribute Relationships” tab of a. How do you create attribute relationships in SQL Server Analy. For attribute values that are changing, this option should be Flexible. setting this option to Rigid will tell SSAS during processing that these values will not.

These relationships are circled in Illustration 7. In the Properties window which appears for the highlighted Postal Code attribute, by default in the right bottom corner of the design environmentwe can observe 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. The RelationshipType property defines rules for the modification of the key value of the members of the related, dependent attribute in our example, the Full Name attribute is the current attribute, whereas the Postal Code attribute is the related attribute. When we define an attribute relationship, we use the RelationshipType property to specify that the relationship is one of the following types: The key values of the related attribute and the current attribute have a fixed association, and cannot change without a full reprocessing of the dimension.

The key of the related, dependent attribute, and therefore the entire member of the dependent attribute, can be changed anytime.

ssas flexible and rigid relationship

In our example above, the Postal Code attribute is dependent upon the Full Name Customer attribute with a Flexible relationship, since the postal code can change anytime a customer moves to another location. If we 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, as we noted in Introduction to Attribute Relationships in MSSQL Server Analysis Services.

Flexible and rigid relationship

We noted that the RelationshipType property in our example is set to Flexible: In our example above, the Postal Code attribute is dependent upon the Full Name Customer attribute with a Flexible relationship, since the postal code can change anytime a customer moves. While we will not make modifications here, we also see that the Cardinality setting for the Postal Code attribute relationship is set to Many.

ssas flexible and rigid relationship

Cardinality defines the nature of the relationship of the key of related attributes and their members when those members are used as member properties of the current attribute and its members. The Cardinality setting can have one of two possible values: One, and only one, member of the current attribute is associated with each member of the related attribute.

A given member of a related attribute can be associated with multiple members of the current attribute.

SQL Server Analysis Services Attribute Relationships

Finally, we can see that the Visible setting for the Postal Code attribute relationship is set to True. The Visible setting specifies whether the related attribute is accessible, as a member property of the current member, to the information consumer. The Visible setting can have either of two possible values: The related attribute is not visible to the information consumer, and therefore cannot be used as a member property of the current member.

The related attribute is visible to, and can be accessed by, the information consumer as a member property of the current member. For purposes of our immediate example, we will leave the Visible property as its current setting of True.

The Properties window for the Postal Code attribute relationship appears as depicted in Illustration 9. 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. As shown in the following illustration, we currently do have attribute relationships setup; however all of them directly link each attribute back to the Date Key, the lowest level.

No intermediate relationships exist to efficiently rollup the hierarchy levels. You will also notice that the attribute relationship tab contains two views. The graphical view is on the top of the below screen print while the detail list view is shown at the bottom.

In order to create a relationship, we can either use the drag and drop method or manual add method via the tool bar button. As shown below, to create our relationship, we actually start at the lowest level and then drag our attribute to the next higher level value.

In our example, the Date key is already related to each of the four attributes in the hierarchy. As such, our first level of the relationship, which is Date Key to Week, has already been established. As you can see in the below illustration, we now have established the Date Key to Calendar Week to Calendar Month relationships. We could continue to use the drag and drop method to complete our relationship setup or we can use the New Attribute Relationship button.

ssas flexible and rigid relationship

As displayed below, once you click the New Attribute Relationship button, the Create Attribute Relationship window opens. On the left side you select the "lower" attribute level to be added to the higher rollup value which appears on the right side.

ssas flexible and rigid relationship

This screen also shows the Relationship Type option. For attribute values that are changing, this option should be Flexible. However for values that do not change, setting this option to Rigid will tell SSAS during processing that these values will not change with future reprocesses of this dimension.

Cross products between levels for example the cross-product between Subcategory and Category in the hierarchy do not need to go through the key attribute, saving CPU time during queries.

Aggregations built on attributes can be reused on related attributes, saving resources during processing and for queries. Auto-exits can be more efficient to eliminate attribute combinations that do not exist in the data.

Attribute Relationships: Settings and Properties - Page 2

If the attribute relationship is defined, the Analysis Services engine knows beforehand which category each subcategory belongs to via indexes built at process time. Processing flexible attribute relationship is expensive: When a change is detected during process in a flexible relationship, all indexes for partitions referencing the affected dimension including the indexes for attribute that are not affected must be invalidated.

This is an expensive operation and may cause Process Update operations to take a very long time. Indexes invalidated by changes in flexible relationships must be rebuilt after a Process Update operation with a Process Index on the affected partitions; this adds even more time to cube processing.

Flexible relationships are the default setting. Carefully consider the advantages of rigid relationships and change the default where the design allows it. Considering unnatural user hierarchies There are two types of user hierarchies, natural and unnatural hierarchy.

Unnatural hierarchies have at least two consecutive levels that have no attribute relationships, commonly created for drill-down paths of commonly viewed attributes that do not follow any natural hierarchy. An example is gender-education relationship.