Automatically Generate Aggregation Designs for cube partitions?

Hi Everyone,

Is there a way to automatically generate aggregations for cube partitions?

We're using the SyncAdvWorksPartition as our basis for dynamically creating measure group partitions. Our poblem is that newly created partitions does not have any aggregation designs implemented on it. We would like to place some aggregation designs on each of our partitions. Is there a way to automatically create aggregation designs on a partition just by using XMLA or other means?

Thanks,

Joseph

Sitting with the same thing myself right now . Using AMO the core of it is shown on the bottom of this page:

http://msdn2.microsoft.com/en-us/library/ms345091.aspx

It works fine. My current problem is that I get complaints from the aggregation designer about attributes containing zero values. I'm guessing this means one has to set the EstimatedCount property for them before calling the aggregation wizards. Wondering if anyone can confirm this?

|||

> My current problem is that I get complaints from the aggregation designer about attributes containing zero values. I'm guessing this means one has to set the EstimatedCount property for them before calling the aggregation wizards. Wondering if anyone can confirm this?

Yes, you need to set the following properties before designing aggregations:

- EstimatedCount property (= estimated number of members) for all the DimensionAttributes of the Dimension used in the MeasureGroup (the MeasureGroup containing the AggregationDesign you are creating)

- EstimatedRows property of the MeasureGroup

You can run SQL queries manually to get the values for these properties. Or you can get them programatically: generate and run the SQL statement that does a SELECT DISTINCT; given a DimensionAttribute, use its KeyColumns to see what table and columns is based on; given a MeasureGroup, use the Sources of its Measures to get the fact table. An AMO code sample is being written for exactly this and it will be released in Books Online + MSDN.

Once you setup those properties, save the Dimensions and the MeasureGroup (with .Update method) before calling InitializeDesign.

Adrian Dumitrascu

|||

Hi Adrian,

you mention the AMO code sample - do you know when this will be available? I am unable to programmatically let my end user simply select a cube (or measure-group) via my app and choose the % aggregation they want... I ran into the EstimatedCount problem as above and then when I have this (via the WIzard), how to create and save aggregations for a partition is proving very difficult (following the MS sample code, it looks like I can create aggregations, but I have no idea how to save them in the partition I am working with) Really frustrating... if I know the counts, how can I simply replace the Aggregation Design Wizard with a "aggregate to X%" function? Also - if I don't know the counts, sample code for getting and setting them would be helfpul too.

Thanks!

-cf

|||

Hi,

the AMO code sample to assign the EstimatedCount and EstimatedRows has been released?

Can you point me to it?

Thank you

Marco Bergonzini

|||

I, too, would like to see the official Microsoft AMO sample code. Until that arrives, BIDS Helper has such a feature:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Update%20Estimated%20Counts&referringTitle=Home

Feedback is welcome as we just added it a few days ago.