In the SSAS attribute relationships, what is the difference between Rigid and Flexible Relationships?
In simple terminology, by Rigid the following terms will come into our mind, something which is strict, something which is not so flexible, something which is fixed, firm or stiff and so on. So, even in Analysis Services, it has the same meaning, we use these for dimension attribute which do not change their values over a period of time, for example, if we take Time Dimension, the month May 2010 would always remain under the year 2010 (it’s true, you can believe me on thisJ, and any doubts, check calendar…), the same thing applies to a week in a month or a day in a week, the relationships never change.
So, with the same analogy, flexible is something that has a possibility of change (This is what we have to deal with most of the times, remember the rule, “Change is only constant”), in other terms, there is a possibility of members moving between dimension, taking a standard example for more clarity, let us take Organization -> Employee Department, an employee may be in Sales Department today, he or she can move to Marketing department, or vice versa or what ever other possibility… And another good example would be with respect to customer, customer’s address has a possibility to change, so this relationship between customer name and customer address is definitely going to be a Flexible one!!!
Now, with respect to analysis services, the default value for a relationship type property is flexible, so what does it really mean for us, flexible relationships require Analysis Services to drop and re-compute any existing aggregations during incremental dimension. If we change or define relationship between two attributes to Rigid, the analysis services by default takes the relationship as fixed and does not actually go and re-compute existing relationships and in other words lets the existing relationships remain without clearing them, thereby reducing the incremental process time.
In the above, the hard lines with dark arrows are the once representing the rigid relationships and the lighter once with transparent arrows are the flexible once.
If looked from other angle, with respect to MDX Query’s on the Cube with say… 30% aggregation, if we fire a MDX and say the numbers we need are not there in the aggregations present, so analysis services in turn has to calculate the numbers that we need from the details, now, it takes in to consideration all the relationships, suppose I need a number @ semester level which is not there in my current aggregations, so if I design a relationship in the time dimension like Year points to semester, semester point to quarter , quarter to month, month to week and week to day. So, here if analysis services calculate the required data for date level, by default it has the required information for returning the data that you need by doing proper summation. So, thus we can say that, the hierarchies with rigid relationships can be queried faster than those with flexible relationships. But, the issue that we would face is, changing any dependent attribute that has a rigid relationship within any hierarchy requires full process of the entire dimension.
Note:this should be present in a checklist for cube development process, because these settings really count for processing as well as query performance.
If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!
{ Comments on this entry are closed }
