How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?