Rigid Relationship affects on SSAS Update Process Dimension – ASP Forum
In Microsoft SQL Server Analysis Services, the cardinality property has no effect members do not change over time, or Flexible, which means that relationships If you define a relationship as rigid, Analysis Services retains. Rigid relationships are defined where you are shre that members will not change levels or their respective attribute relationships. For example. 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.
This process is maintained by creating natural hierarchies within a cube; these hierarchies allow you to easily navigate up and down the various levels of attributes within a dimensions. Of course, one of the most common hierarchies which gets created is a date hierarchy. Day rolls up to month which rolls up to quarter which rolls up to year. These hierarchies help both the user understand how to move through a dimension, but they also serve a second, and potentially more important role as a way to improve performance of aggregations and queries.
Improve SQL Server Analysis Services Performance with the Usage Based Optimization Wizard An integral part of creating performant aggregations is to create and maintain the appropriate attribute relationships for your hierarchies.
The reason the relationship is so important is because the hierarchy, the attribute relationship, and any related aggregations work in conjunction with each other to produce faster results for a query. Furthermore, because the relationship has been established, the measure rollup by year can look to see if there is an aggregation at the year level. If that level is not available and if the attribute relationship is setup properly which we show later in the tipthen instead of having to look all the way down to the date key value, SSAS can just try to find an aggregate at the next level of the relationship, which would be quarter in our example.
Attribute Relationships: Settings and Properties - Page 2
Summating the data at the quarter level would be much faster than having to drill down to the date key level. Even if no aggregation exists at the quarter level, then the query could look to the month level which again would be faster than summing the data at the date key level. To show this process in practice, let us take a look at an example using a date field. These databases are available for download, free, on CodePlex at: For our date example, I actually create a new data dimension, Date TEST, based on the existing date view in the data source view.
As shown below, year, quarter, month, week, and date were added as attributes to the dimension. As noted in the second screen print, SSAS displays a caution triangle and displays a warning that says that no attribute relationships exists in the hierarchy and as such performance may be impacted.
Thus, we now know we should create an attribute relationship, but it is not required. To create the relationship we need to click on the Attribute Relationship tab. 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. Click Open on the context menu that appears, as shown in Illustration 3.
Opening the Dimension via the Dimension Designer The tabs of the Dimension Designer open. Click the Dimension Structure tab, if we have not already arrived there by default. The attributes belonging to the Customer dimension appear as depicted in Illustration 4.
Configure Attribute Relationship Properties | Microsoft Docs
We can also see, within the Hierarchies and Levels pane, four levels in the Customer Geography user hierarchy. This hierarchy currently exists simply as a drill down path for information consumers, and appears as shown in Illustration 5. Hierarchies and Levels Pane, Customer Dimension 4. We note that four member properties link the non-key attributes from the Geography table to the key attribute from the Geography table, as depicted in Illustration 6.
In the Attributes pane, expand the Full Name attribute. We see that the Geography attribute is related to the Full Name attribute. We also note that the Postal Code attribute is indirectly related to the Full Name attribute through the Geography attribute, because Postal Code is linked to the Geography attribute and the Geography attribute is linked to the Full Name attribute.
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.
SQL Server Analysis Services Attribute Relationships
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. 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.
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. 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.
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. We will next directly relate the City attribute to the Postal Code attribute.
The City attribute is now directly related to the Postal Code attribute.