BI Blogs

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.

Yes folks, in conjunction with the UK SQL Server Community I’m proud to announce another BI Evening on the 26th of April and - by popular demand - it’s going to be in central London. We’ve got a star-studded line-up of speakers too: David Francis  talking about PerformancePoint Monitoring and Analytics; the legendary Allan Mitchell on dimension and fact table ETL methods with SSIS; and all the way from the US of A (though not just for this event, he was coming here anyway) Reed Jacobson on PerformancePoint Planning and Budgeting.  You’ll detect a bit of  a PerformancePoint theme going on here…

 

Anyway, here’s the link to register:

Spaces are limited so you’d better hurry. There’s also a non-BI SQL Server event going on at the same time, and since both are being sponsored by those nice people at Red-Gate software I’m told there will be all sorts of goodies to give away.

Great post here from Erik Veerman on a way of optimising some many-to-many relationships:

I blogged about something similar, but applicable in slightly different scenarios, here:

In general the idea is that if you can reduce the size of any dimensions and measure groups involved in a m2m relationship then you’ll get better performance. I believe partitioning your intermediate measure group appropriately can also help.

 

Via Darren Gosbell, I see that the best practices included in the forthcoming SQL2005 Best Practices Analyzer have been turned into a white paper which you can download here:

 

Most of the recommendations are fairly obvious, some were new to me, some provoked a ‘they need to fix that’ response, and some I wasn’t sure I agreed with 100%. For example:

 

I quite often find myself designing diamond-shaped relationships in my attributes; the example they give in the text isn’t particularly good, but Day->Month->Year and Day->Week->Year is very common. OK most of the time I design user hierarchies along these paths anyway, but this need to design user hierarchies just for the sake of performance rather than because users actually want to see these hierarchies (and this isn’t the first time I’ve heard this recommended) is something that I don’t feel particularly happy about. It would be nice if we could make the choice between using attribute hierarchies and user hierarchies based on ease-of-use alone.

 

This is the multiple measure groups and one cube vs multiple cubes with one measure group question that has been kicking around for at least a year. We were promised it was going to be addressed in the AS2005 Performance Guide but it wasn’t; when are we going to get some details published? In my experience I have seen a slight improvement in performance when you split cubes up in this way but nothing major, yet clearly there must be some reason for MS to keep recommending this so perhaps I’ve not come across the right scenario yet.

 

Obviously having large intermediate measure groups and/or dimensions in m2m relationships is going to slow things down, but in my experience I’ve always been pleasantly surprised with the performance of m2m dimensions. I’ve seen intermediate measure groups with much more than 1 million members in perform really well…

 

As an aside, I recently noticed that in Eric Jacobsen’s recently blog entry on partitioning here he says you can also think about 2Gb (rather than a number of rows) as a rough guideline for the maximum size of a partition, and more interestingly that you should not have more than about 2000 partitions in your measure group. I asked him about this and it turns out that there are some internal limitations in AS at the moment, which hopefully will be fixed soon, that mean that slow down performance when you have large numbers of partitions. This does suggest that at the moment roughly speaking there’s a maximum size for a MOLAP cube to perform well of around 4Tb or 30 billion rows… not that I’ve ever seen a AS2005 MOLAP cube that big, but interesting to note.

 

Based on Eric’s article mentioned in the last point, I think it’s also a good idea to set the Slice property on MOLAP partitions where possible too given that the automatic detection of which members appear in which partitions gets confused by overlapping slices. Greg Galloway recently did some experiments on this which I saw where he showed that if you set the slice on a MOLAP partition the auto-slice information isn’t used.

 

I quite frequently find myself designing aggregations which fail the one-third rule, although not by much, and for certain queries and calculations it can be a good idea. But I agree you should only do so as a last resort.

Via Darren Gosbell, I see that the best practices included in the forthcoming SQL2005 Best Practices Analyzer have been turned into a white paper which you can download here:

 

Most of the recommendations are fairly obvious, some were new to me, some provoked a ‘they need to fix that’ response, and some I wasn’t sure I agreed with 100%. For example:

 

I quite often find myself designing diamond-shaped relationships in my attributes; the example they give in the text isn’t particularly good, but Day->Month->Year and Day->Week->Year is very common. OK most of the time I design user hierarchies along these paths anyway, but this need to design user hierarchies just for the sake of performance rather than because users actually want to see these hierarchies (and this isn’t the first time I’ve heard this recommended) is something that I don’t feel particularly happy about. It would be nice if we could make the choice between using attribute hierarchies and user hierarchies based on ease-of-use alone.

 

This is the multiple measure groups and one cube vs multiple cubes with one measure group question that has been kicking around for at least a year. We were promised it was going to be addressed in the AS2005 Performance Guide but it wasn’t; when are we going to get some details published? In my experience I have seen a slight improvement in performance when you split cubes up in this way but nothing major, yet clearly there must be some reason for MS to keep recommending this so perhaps I’ve not come across the right scenario yet.

 

Obviously having large intermediate measure groups and/or dimensions in m2m relationships is going to slow things down, but in my experience I’ve always been pleasantly surprised with the performance of m2m dimensions. I’ve seen intermediate measure groups with much more than 1 million members in perform really well…

 

As an aside, I recently noticed that in Eric Jacobsen’s recently blog entry on partitioning here he says you can also think about 2Gb (rather than a number of rows) as a rough guideline for the maximum size of a partition, and more interestingly that you should not have more than about 2000 partitions in your measure group. I asked him about this and it turns out that there are some internal limitations in AS at the moment, which hopefully will be fixed soon, that mean that slow down performance when you have large numbers of partitions. This does suggest that at the moment roughly speaking there’s a maximum size for a MOLAP cube to perform well of around 4Tb or 30 billion rows… not that I’ve ever seen a AS2005 MOLAP cube that big, but interesting to note.

 

Based on Eric’s article mentioned in the last point, I think it’s also a good idea to set the Slice property on MOLAP partitions where possible too given that the automatic detection of which members appear in which partitions gets confused by overlapping slices. Greg Galloway recently did some experiments on this which I saw where he showed that if you set the slice on a MOLAP partition the auto-slice information isn’t used.

 

I quite frequently find myself designing aggregations which fail the one-third rule, although not by much, and for certain queries and calculations it can be a good idea. But I agree you should only do so as a last resort.