Explanation Behind the Incorrect Query Results

After about a week and a half of ongoing discussions we’ve come up with an explanation as to why the queries were returning bad numbers. Thank goodness!
 
To make a long story short, I had manually designed aggregations that I shouldn’t have. We have a reference dimension with materialized set to False.  This reference dimension should not have been included in any aggregations. The Aggregation Design Wizard is smart enough not to do this, however you can hack the XMLA to do so yourself. The Aggregation Manager Utility included in SP2 also allows one to add aggregations to these type of dimensions.
 
The reason I did not set materialize to True is because when a sales person moves between business groups that revenue needs to follow them to that new group.
 
So if you have a reference dimension with materialized set to False – DO NOT INCLUDE IT IN ANY AGGREGATIONS.
 
Below is the explanation from the support engineer.
 
 
****************************************************************************************

Reference dimension

Dimension that indirectly relates to fact data through intermediate dimension. In this case, [Business Unit] is reference dimension through [Sales] dimension as intermediate dimension

Materialize setting

Materialize=True for reference dimension will put the key attribute for the reference dimension in the fact data during processing. Therefore the reference dimension is the same as regular dimension with this setting. 

Materialize=False will not include attribute from reference dimension to the fact data during processing. So access reference dimension members will happen on the fly by going through intermediate dimension. As one can see, this may impact query performance. Therefore the default setting is have Materialize=True for reference dimension

What the correct behavior should be

When Materialize is set to False for reference dimension, aggregation should not include any attribute from the reference dimension. The correct behavior should be the processing would raise an metadata validation error. A design change request to implement this has been filed to the next version of Analysis Services.  

What is the problem

With this aggregation included in the cube, the aggregation creation routine does not know how to aggregate the measure from fact and the reference dimension attribute as the fact does not have the attribute from the reference dimension, therefore the measure is null for this aggregation. In the pool of aggregations created, there is another aggregation which is a good one that also covers this query, but the good aggregation is at the upper level. After full processing, both aggregations are created. When the query is executed, the query execution engine starts from the top level and goes down to find the covering aggregation, in this case, it picks good aggregation (Aggregation 34) and returns the correct result.

After Process Update on Sales dimension, Process Update will drop all the aggregations that would be affected by the data change, in this case, good agg Aggregation 34 got dropped, but the “bad” aggregation stayed as it is deeper than the good aggregation. So now the query would start picking the “bad” aggregation as the good one was gone, and returned null for the measure. 

Solutions:

The direct solution to this problem is to remove this aggregation (Aggregation 30). To keep this aggregation, one would need to set Materialize to True for the reference dimension.

Since Process Update will always drop affected aggregations, it is recommended to rebuild the aggregation by running Process Index after Process Update. In this case, Process Update will recreate the good aggregation and avoid the problem for this particular query.

We will be working with content team on a KB article for this behavior as well.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Explanation Behind the Incorrect Query Results

  1. Pingback: Gotchas With Referenced Dimensions « SQL with Dustin Ryan

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