We have designed a fairly big cube - and now we want to add a view aggregates, but the aggregation design wizzard does not built aggregates.
after a view minutes of "Initializing..." the wizard returns with zero aggregates (even if i select 99% performance).
I have already checked some other posts - custom assemblies and so on... but did not find andy hint.
SSAS2005 SP2
Profiler does not show any error...
this may be related to the correct record counts (see post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)
Thanks for Help, HANNES
Could this be because of the huge amount of attributes in the cube?
- Cube 5 Dimensions
Time: 10 Attributes|||
Its really hard to say exactly why the Aggregation Design Wizard is not building any aggregations. It may be due to the record counts being wrong, the Aggregation Usage property on too many of the attributes being set incorrectly, etc.
I'd suggest you read through the SSAS performance guide as it discusses aggregation design quite extensively and you'll likely have a better idea of what you might need to change in order to get the wizard to consider building aggregations.
HTH,
Dave Fackler
PS. The SSAS performance guide can be found here:
http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc
|||Hey Hannes,
i've got the same Problem since a few days, but without a Solution. I've also a huge Cube with 30 Dims and 23 MeasureGroup. It works fine so far, until this Problem appear. In my Cube there is a second Time Dim, all MeasureGroups without this Dim in Referenz, the Aggregation Design Wizard works.
If i've found a Solution for that Situation i'll write it down.
Kind Regard
Andy L?wen
|||Hello!
30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.
And each attribute that you put into a dimension will increase the size of the cube. Count the members for each individual attribute in each dimension and multply them with all the number of members of each other attribute in the same dimension
I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.
It would be interesting to know more about the business case behind this design.
Regards
Thomas Ivarsson
|||Bug? I don't know, it may see your Dimension and see 150 attributes (is that even right?) and...die? That's huge and aggregation's would not even help at all for performance.
Edit: Second thought, that cube IMO really isn't aggregatable, maybe the wizard agrees with me. I suggest turning those attrributes into measures. Only structures should be in the Dimension.
|||Thomas Ivarsson wrote:
Hello!
30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.
I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.
It would be interesting to know more about the business case behind this design.
Regards
Thomas Ivarsson
I agree, anymore than 10-15 attributes for a dimension and i'd suggest your cube design is off, let alone 200 attributes. Can you give examples of what attributes you have?
|||Even when you specify 99% the aggregation design wizard will not design 99% of all possible aggregations, it has some pretty smart (most of the time) algorithms which try to figure out how many aggregations would be "reasonable" for the wizard to consider and then it will design 99% of those. So in your case there must be something in your design that is causing the wizard to conclude that there are no "reasonable" aggregations.
The aggregation design wizard relies heavily on the estimated row counts and on the attribute relationships and other settings in your dimensions. The following are some of the factors that can contribute to aggregations not being designed by the wizard.
lack of or incorrectly defined attribute relationships|||
Hey Thomas,
there are many Dims, MG and so on, that's right. But only a few attributes are Aggregateable.
My Cube has a size of 3GB, and we did not have a Problem with Performance. So that is not the Problem.
My Problem is, that the Aggregation Design Wizard will not work this the last change on the Design of the Cube.
But give me some Hours, i think i'm on the right way.
Kind Regards
Andy L?wen
|||If only a few attributes are aggregatable, then that means that any aggregations that could be built would be pretty large - they will always have to be at the leaf levels of the non-aggregatable attributes. This in turn means that AS is likely to have decided they are too large to be useful (see the AS Performance Guide, P37, on the 'one-third rule') so it hasn't designed any at all. If you do want to built aggregations then you should either build them manually or deliberately set the counts for your attributes to artificially small values.
HTH,
Chris
|||Andy could you please provide an example of attributes you have? I'm still very curious how it's possible for so many to exist.|||Hi,
About setting the count to a lower value, dont set it too low, because
Another tip which I got from an microsoft insider is that,
the Aggregtion Wizard does not create aggregations if the count is somewhere near 1024 rows (if I remember correctly).
Regards
|||You're thinking of the fact that AS won't build aggregations or indexes on a partition with fewer than 4096 rows, by default.|||Hi,
Oh! yes, you are right.
I also believe that this threshold value can be set somewhere in a configuration file.
But then how is the number of rows different from the count?
|||To answer my question... we have selectivly defined some attributes with aggregation usage "non" (for those attributes which are not propably often used) - while we have set half of the attributes to aggregation usage non - the aggregation wizzard has begun to design aggregates...
Maybe there is a relation to the not correct set estimated rows... - as long as I do not find any code to proper set estimeded rows its impossible for me to say whats the reason... (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)
Best Regards, HANNES
No comments:
Post a Comment