Designing Aggregations

I have a fairly large partition (100M rows, 30 measures) and am attempting to create aggregations through the Aggregation Design Wizard. Regardless of the option I select, either percent or file size, the wizard always completes after designing only about 50 aggregtions for 0% and only 200kb. I cannot believe that to be right. On a much smaller partition I created about 600 aggregations to 30% for 2GB.

I've double checked to ensure I have all of my dimension attribute relationships defined.

Any ideas why I cannot get the large partition to build the aggregations or why it completes after building about 50? I have plenty of disk space and and cube processing time is not an issue, so I would like to create up to 25-30GB of aggregations.

Thanks

Have you established your attribute relationships?

WIthout them the system does not know that natural hierarchies may be available and by default it only creates aggregations along natural hierarchies.

_-_-_ Dave

|||

Thanks Dave, but I have established the attribute relationships. What confuses me is that the wizard shows that it will create about 250 aggregations, 0%, 125kb. I use the option to manually stop the aggregation design (but never do. I let if finish on its own) and it always comes out roughly the same.

When I process the partition I can see that the aggregations are being created. When I look in the file system I see a bunch of .tmp files being created (e.g. AggMerge_5800_54_8s64d_28.tmp), all of them for about 1.5GB. I eventually run out of space on the drive and the processing fails.

All together these .tmp files take about 150GB. The source database is only about 200GB.

Any thoughts? Ideas? The aggregation design wizard tells me the aggregations will take up about 125kb, but the .tmp files tell me something different.

Design Aggregations using XMLA

How can I automate aggregation design of a cube in Analysis Services

using XMLA.

I tried following XMLA.

I am not getting any examples for XMLA for Aggregation

design, please help me

===================================================

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

<Command>

<DesignAggregations>

<Object>

<DatabaseID>Adventure

Works DW</DatabaseID>

<CubeID>Adventure

Works</CubeID>

<MeasureGroupID>Internet

Sales</MeasureGroupID>

<PartitionID>Inernet_Sales_2001</PartitionID>

</Object>

<Time>10</Time>

<Steps>4</Steps>

<Optimization>30</Optimization>

<Storage>100</Storage>

<Materialize></Materialize>

<Queries></Queries>

</DesignAggregations>

</Command>

<Properties>

<PropertyList>

</PropertyList>

</Properties>

</Execute>

===================================================

Regards

SK

Here's a couple of scripts i was mucking around with. i've split them into 1, 2 and 3 (ie numbered in the orderr in which i ran them). They may give you some pointers to where you want to go.

Sorry about the formatting, i've just pasted them in here and it seems to go on forever.....

Script 1 - Create Aggregation Design for Measure Group --

<!--

XMLA_Creates_Aggregation_Design_for_Measure_Group

This script adds the aggregation design to the measure group

-->

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Aggregation Testbed</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Internet Sales</MeasureGroupID>
</ParentObject>
<ObjectDefinition><AggregationDesign>

<ID>AggregationDesign 1</ID>

<Name>AggregationDesign 1</Name>

<!-- Parameterize the following element-->

<EstimatedRows>1013</EstimatedRows>

<!-- Load the dimensions and levels and level counts from the Cube_Levels table-->

<Dimensions>

<Dimension>

<CubeDimensionID>Order Date Key - Dim Time</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Date</AttributeID>

<EstimatedCount>1158</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Year</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Semester</AttributeID>

<EstimatedCount>7</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Quarter</AttributeID>

<EstimatedCount>13</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Month Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Calendar Month Name</AttributeID>

<EstimatedCount>38</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Fiscal Year</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Fiscal Semester</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Quarter</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Month Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Month Name</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Ship Date Key - Dim Time</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Date</AttributeID>

<EstimatedCount>1158</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Year</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Semester</AttributeID>

<EstimatedCount>7</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Quarter</AttributeID>

<EstimatedCount>13</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Month Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Calendar Month Name</AttributeID>

<EstimatedCount>38</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Fiscal Year</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Fiscal Semester</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Quarter</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Month Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Month Name</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Delivery Date Key - Dim Time</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Date</AttributeID>

<EstimatedCount>1158</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Year</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Semester</AttributeID>

<EstimatedCount>7</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Quarter</AttributeID>

<EstimatedCount>13</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Calendar Month Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Calendar Month Name</AttributeID>

<EstimatedCount>38</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Fiscal Year</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Fiscal Semester</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Quarter</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Month Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Fiscal Month Name</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Customer</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Full Name</AttributeID>

<EstimatedCount>18484</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Country-Region</AttributeID>

<EstimatedCount>6</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>State-Province</AttributeID>

<EstimatedCount>71</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>City</AttributeID>

<EstimatedCount>587</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Postal Code</AttributeID>

<EstimatedCount>646</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Education</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Email Address</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Gender</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Marital Status</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Occupation</AttributeID>

<EstimatedCount>6</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Phone</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Product</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Product Name</AttributeID>

<EstimatedCount>606</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Product Line</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Model Name</AttributeID>

<EstimatedCount>119</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Subcategory</AttributeID>

<EstimatedCount>37</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Category</AttributeID>

<EstimatedCount>4</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>List Price</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Size</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Weight</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Currency</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Currency</AttributeID>

<EstimatedCount>105</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Currency Code</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Sales Reason</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Sales Reason</AttributeID>

<EstimatedCount>10</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Sales Reason Type</AttributeID>

<EstimatedCount>3</EstimatedCount>

</Attribute>

</Attributes>

</Dimension>

<Dimension>

<CubeDimensionID>Internet Sales Order Details</CubeDimensionID>

<Attributes>

<Attribute>

<AttributeID>Sales Order Key</AttributeID>

<EstimatedCount>60398</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Sales Order Number</AttributeID>

<EstimatedCount>27659</EstimatedCount>

</Attribute>

<Attribute>

<AttributeID>Sales Order Line</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Carrier Tracking Number</AttributeID>

</Attribute>

<Attribute>

<AttributeID>Customer PO Number</AttributeID>

</Attribute>

</Attributes>

</Dimension>

</Dimensions>

</AggregationDesign>

</ObjectDefinition>

</Create>

-- End of script 1 :)

Script 2. - Add/link the aggregation Design to the Measure Group --

<!--

XMLA_Adds_Aggregation_Design_To_Partition

This script adds the aggregation design to the partition. It requires the aggregation design to have been

created for the associated Measure Group

-->

<Alter xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Aggregation Testbed</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Internet Sales</MeasureGroupID>

<PartitionID>Internet_Sales_914</PartitionID>

</Object>

<ObjectDefinition>

<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>Internet_Sales_914</ID>

<Name>Internet_Sales_914</Name>

<Annotations>

<Annotation>

<Name>LastOrderDateKey</Name>

<Value>914</Value>

</Annotation>

</Annotations>

<Source xsi:type="QueryBinding">

<DataSourceID>AdventureWorksDW</DataSourceID>

<QueryDefinition>SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey &lt;= '184'</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<ProactiveCaching>

<SilenceInterval>-PT1S</SilenceInterval>

<Latency>-PT1S</Latency>

<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

<ForceRebuildInterval>-PT1S</ForceRebuildInterval>

<Source xsi:type="ProactiveCachingInheritedBinding" />

</ProactiveCaching>

<EstimatedRows>1013</EstimatedRows>

<AggregationDesignID>AggregationDesign 1</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Alter>

-- END OF SCRIPT 2 :)

Script 3. - Run the designer to build the aggregation designs themselves (ie the aggregations, but not populated with data, this happens when you build the partition and/or cube).

<!--

XMLA_Runs_Aggregation_Engine_to_Determine_Aggregations

This script runs the aggregation engine that uses the supplied parameters to build an aggregation design

-->

<DesignAggregations xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Aggregation Testbed</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Internet Sales</MeasureGroupID>

<AggregationDesignID>AggregationDesign 1</AggregationDesignID>

</Object>

<!--Time>00h00m05s</Time-->

<Steps>0</Steps>

<Optimization>25.0</Optimization>

<Materialize>true</Materialize>

</DesignAggregations>

-- END OF SCRIPT 3 :)

Hope you find these of use.

Steve.

Design Aggregation Problem

I have some problem. design aggregation cannot be made on several cubes that have a lot of data, and

have a lot of measures and dimension (the number of fields in the table more or

less 150 fields). They return 0% optimization level when we run design

aggregation.

This link have been published a few posts down but I repeat it here again. This is a good guide of how to create good aggregations: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH

Thomas Ivarsson