In the current blog, there is a summary of Type 1 and Type 2 attribute dimension changes in terms of their implementation of attribute relationships in Analysis Services 2008. Attribute relationships are an important part of dimension design for a number of reasons:
-
Correct attribute relationships help the server to optimize data storage
-
Define referential integrity within the dimension itself
-
Organise member properties
According to the SQL Server Best Practices Whitepaper, there are some optimisation guidelines for dimensions:
-
Create attribute relationships that follow the natural hierarchies found in the data
-
Set the RelationshipType property of the attribute relationships to Rigid when the data does not change between related attributes, and to Flexible where the data does change between related attributes.
When you set the property RelationshipType correctly, then the server is able to do its job of optimising the change processing methods, and aggregation re-building. In terms of aggregations, SSAS will persist aggregations during cube processing if the attribute relationship is considered to be fixed, or Rigid. If it is set to Flexible, then the aggregations will need to be processed again.
Briefly, where the relationships between attributes are not expected to change, the property RelationshipType should be set to Rigid. In contrast, RelationshipType should be set to Flexible where the attribute relationship is considered to be subject to change. By default, the RelationshipType is set to Flexible. So, for example, the relationship between Year and Quarter is not likely to change, so the RelationshipType would be set to Rigid. On the other hand, the marital status of a customer may be subject to change, so this RelationshipType would be set to Flexible.
This sounds straightforward, but becomes more complex when we consider Type 1 and Type 2 attribute changes, as you will see below.
If you’re tracking Type 1 historical changes of an attribute, then this is normally set to Flexible if the key and the name are changed at the same time, normally during ETL load. So, if a ProductCategory key and name change occurs at the same time, then this would be Flexible. One exception of this is that if the name changes, but the key does not, then it could be set to Rigid. For example, if the ProductCategory name changes, but its key does not, then this will set to Rigid; the name will simply be the new name for the ProductCategory.
If you’re tracking Type 2 historical changes of an attribute, such as Product Name that’s tied directly to the key attribute, then you change the RelationshipType property to Rigid.
If your Type 2 historical change is happening to an attribute that’s not directly related to the key attribute but is related instead to a higher level attribute, then the RelationshipType property needs to remain Flexible. Otherwise, you’ll generate an error when the dimension is re-processed.
To summarise, although the Rigid/Flexible definition appears straightforward at first, it is important to keep in mind the type of attribute change that is being made.