How can we get the aggregation information including the level of aggregations?

Hi, All experts here,

I am trying to fetch all aggregations' information including their aggregations' levels.

What I tried was typing in :<Aggregations> in the xml file of the cube, but all I can see from there is the aggregations ID and dimension ID related to the aggregations? Cant see any information like the levels of aggregations?

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

How do you want to see this information? There was an Aggregation Manager application which was released with SP2 that presents this in a UI where you can also edit the aggregations manually (which can be dangerous if you don't know what you are doing). We also built an improved version of this application into BIDSHelper www.codeplex.com/bidshelper which lets you right click on a cube and edit it's aggretations.|||

Hi,

Yes, I have got this BIDSHelper in my SSAS2005 and I am able to right click on the cube and open up the 'Aggregation Manager' to edit the aggregations. But it does not include any aggregation levels there?

What you can do with this 'Aggregation Manager' is to 'eliminate redundancies' and 'eliminate duplicates' and add more aggregations?

But the question is: there is a strange option there which enables you to right click on the interface and 'add aggregation', but I have no clue what aggregation this is produced from here?

I am very confused now and if any of you have a clear idea about it please shed me any light on it.

Thank you very much and I am looking forward to hearing from you for your helpful advices.

With best regards,

Yours sincerely,

|||

Helen999888 wrote:

What you can do with this 'Aggregation Manager' is to 'eliminate redundancies' and 'eliminate duplicates' and add more aggregations?

But the question is: there is a strange option there which enables you to right click on the interface and 'add aggregation', but I have no clue what aggregation this is produced from here?

To your first question, yes that is all you can do. You can either get the query log to generate the design or manually create the design, this kind of leads me onto your second question. If you right click and add aggregation, it produces a blank one, where you have to check the attributes you want to use in this aggregation. With this method you will have to know which attributes are going to produce the best performance gains for your query/queries. And the only way to really do that, apart from guessing, is to look at the queries people are running and the profiler.

Hope that helps

Matt

|||

Hi, Matt,

Thanks a lot. Your advices have been really helpful for me.

With best regards,

Yours sincerely,

|||

I agree with Matt, don't go creating aggregations blindly. The best practice is to get the Wizard to build some for you at about 20-30%. Then make sure the query logging is switched on and log a period of standard user activity and the use the Usage Based Optimization wizard.

Only after this, based on evidence from profiler would you consider manually creating aggregations. Adding the wrong or too many aggregations can not only slow down processing of your cubes, the can also slow down query performance. With the original AggManager sample it was even possible to add aggregations that would produce incorrect figures (only if you had not materialized reference dimensions and we fixed this issue in the BIDSHelper version)

When you said you were after the aggregation level, were you talking about the levels in your hierarchies or about the percentage level that the aggregation wizard uses? The aggregation wizard "level" is specific to the wizard and is not stored anywhere.

|||

Hi, the leve I am talking about is the 'hierarchy' level of a dimension which is aggregated.

Thanks for your advices.

Kind regards,