Data Warehousing and Business Intelligence

15 July 2014

Process Update of a Dimension

Filed under: Analysis Services — Vincent Rainardi @ 5:05 am

If the relationship between attributes of a dimension is rigid, the dimension Process Update will fail when a row in the dim table is deleted.

 

The error message is:

 

Rigid relationships between attributes cannot be changed during incremental processing of a dimension

 

This is because the absence of that deleted row forces Process Update to delete the member from the dimension. SSAS will not allow this deletion, because we specify the relationship as rigid, meaning can not change.

 

When we specify a relationship as rigid, SSAS doesn’t recalculate the aggregation. When we specify a relationship as flexible, SSAS recalculates the aggregation.

 

These are examples of attribute relationships:

 

  1. [15 Jan 2014] (date attribute) to [Jan 2014] (month attribute)
  2. [Jan 2014] (month attribute) to [2014 Q1] (quarter attribute)

 

If we delete 15 Jan 2014 from the DimDate, then do a process update on the date dimension, we will get the above error message. This is because we changes the relationship between date attribute and month attribute, and between month and quarter. The relationship #1 and #2 above will not exist any more. That’s a change. Hence SSAS will say “Rigid relationship between attributes cannot be changed during incremental processing of a dimension”.

 

Why does SSAS say this? Because if SSAS changes the 2 relationships above, it will have to process the aggregation. For example:

 

  • 14th Jan: Overhead Cost = £10,000
  • 15th Jan: Overhead Cost = £15,000
  • 16th Jan: Overhead Cost = £20,000
  • 1st Feb: Overhead Cost = £30,000

 

Aggregate at Jan 2014 = £45,000. Aggregate at 2014 Q1 = £75,000.

 

When 15th Jan row is deleted, the aggregates changes to: Jan 2014 = £30,000 and 2014 Q1 = £60,000.

 

But in the rigid relationship SSAS does not reprocess the aggregation! Hence when updating the dimension SSAS says Rigid attribute relationship cannot be changed during incremental processing of a dimension.

 

Process Update of a dimension can delete a member. Repeat: Process Update of a dimension can delete a member. But the relationship must be flexible. It can not be rigid.
Of course Process Update of a dimension can create a member, like Process Add. (SSMS does not allow Process Add for a dimension, but we can do it using XMLA).

 

References:

 

  1. Prav provides explanation in great details in his blog, with regards to how to replicate this problem: link
  2. Aniruddha Thengadi: link
  3. Hasan Humayun: link
  4. Rigid vs Flexible Relationship in MSDN: link

 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: