Creating Manual Custom Aggregations in SSAS2005 using XMLA

 
Analysis Services 2000 has a tool named the Aggregation Partition Utility (see my post on 10/24/06) which allows a developer to design custom aggregations in addition to whatever aggregations may already exist. So far no similar tool exists for SSAS2005, although rumors abound that one is in the works.

We used the Design Wizard to create the original set of aggregations which resulted in 75 aggregations. After looking at the aggregations (right click on Measure Groups – Script Measure Group as – Collapse Measures/Dimensions/Partitions – Look at AggregationDesign) only about 40 were of any use. The end users always slice by the Date dimension which ended up not being in about half of the aggregations created by the Design Wizard. We removed these aggregations. Keeping aggregations which won’t be queried is harmful since they have to be calculated and recalculated (sometimes) when dimensions and partitions are processed.

A measure group can have more than one aggregation design. For example a measure group may have two partitions, last years data and this years. Last years data may not need as many aggregations as the current year so these two partitions would need two different aggregation designs.

Sadly there is no way to assign a specific aggregation design to a partition using the BI Studio. You’ll have to change it manually via Management Studio. Right click on the partition, Script Partition As Alter, look for the AggregationDesignID and change it to the newly created AggregationDesignID. The partition must be reprocessed in order for the new aggregations to take effect.

Personallly, I prefer to let the Design Wizard take the first hack at the aggregations them go through them myself and remove any which won’t be of use. I then add aggregation by aggregation as I feel necessary. Once it’s created I assign the DesignID to the appropriate template partition (Hourly, Daily, Monthly) and process. We have a set of template partitions that we copy whenever a new partition is created.

Most Analysis Services cubes won’t need custom aggregations. We have a large cube with 400 partitions and growing (one new partition per day, 15 million rows per partition) and some large dimensions (three with excess of five million members). At first iteration the cube was pretty much unusable with the Design Wizard aggs. It wasn’t until we started adding our own aggregations the thing actually became useful.

A few basics:
An AggregationDesign can have one or more Aggregations
A Measure Group can have one or more AggregationDesigns
A Partition can have only one AggregationDesign

The following code has two aggregations for a single AggregationDesign. The Measure Group has four dimensions: Date, Sales Type, Location, and Item with each of those dimensions having several attributes. 

Sample XMLA code to Create a Custom Aggregation

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>Sales</
DatabaseID>
        <CubeID>Location</
CubeID>
        <MeasureGroupID>Item by Location</
MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <AggregationDesign xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <ID>AggregationDesignCustom01</ID>
          <Name>AggregationDesignCustom01</Name>
         
The estimated counts are used by the Aggregation Design Wizard 
          <EstimatedRows>500000</EstimatedRows>
          <Dimensions>
            <Dimension>
              <CubeDimensionID>dimDate</CubeDimensionID>
              <Attributes>
                <Attribute>
                  <AttributeID>Year</AttributeID>
                  <EstimatedCount>1</EstimatedCount>
                </Attribute>
                <Attribute>
                  <AttributeID>Month</AttributeID>
                  <EstimatedCount>1</EstimatedCount>
                </Attribute>
                <Attribute>
                  <AttributeID>Date</AttributeID>
                  <EstimatedCount>1</EstimatedCount>
               </Attribute>
              </Attributes>
            </Dimension>
            <Dimension>
              <CubeDimensionID>dimSalesType</CubeDimensionID>
              <Attributes>
                <Attribute>
                  <AttributeID>Sales Type</AttributeID>
                  <EstimatedCount>5</EstimatedCount>
                </Attribute>
              </Attributes>
            </Dimension>
            <Dimension>
              <CubeDimensionID>dimLocation</CubeDimensionID>
              <Attributes>
                <Attribute>
                  <AttributeID>State</AttributeID>
                  <EstimatedCount>48</EstimatedCount>
                </Attribute>
                <Attribute>
                  <AttributeID>County</AttributeID>
                  <EstimatedCount>100</EstimatedCount>
                </Attribute>
                <Attribute>
                  <AttributeID>City</AttributeID>
                  <EstimatedCount>2000</EstimatedCount>
                </Attribute>
              </Attributes>
            </Dimension>
           
            <Dimension>
              <CubeDimensionID>dimItem</CubeDimensionID>
              <Attributes>
                <Attribute>
                  <AttributeID>Item</AttributeID>
                  <EstimatedCount>100000</EstimatedCount>
                </Attribute>
                <Attribute>
                  <AttributeID>Item Category</AttributeID>
                  <EstimatedCount>200</EstimatedCount>
                </Attribute>
             </Attributes>
            </Dimension>
          </Dimensions>
          <Aggregations>
           
This aggregation is: dimDate.Date / dimLocation.State / dimItem.Item Category
            <Aggregation>
              <ID>Aggregation_01</ID>
              <Name>Aggregation_01</Name>
              <Dimensions>
                <Dimension>
                 <CubeDimensionID>dimDate</CubeDimensionID>
                  <Attributes>
                    <Attribute>
                      <AttributeID>Date</AttributeID>
                    <Attribute>
                  </Attributes>
                </Dimension>
                <Dimension>
                  <CubeDimensionID>dimSalesType</CubeDimensionID>
                </Dimension>
                <Dimension>
                  <CubeDimensionID>dimLocation</CubeDimensionID>
                  <Attributes>
                    <Attribute>
                      <AttributeID>State</AttributeID>
                    </Attribute>
                  </Attributes>
                </Dimension>
                <Dimension>
                  <CubeDimensionID>dimItem</CubeDimensionID>
                  <Attributes>
                    <Attribute>
                      <AttributeID>Item Category</AttributeID>
                    </Attribute>
                  </Attributes>
                </Dimension>
              </Dimensions>
            </Aggregation>
           
This aggregation is: dimDate.Month / dimSalesType.Sales Type / dimLocation.State / dimLocation.County
            <Aggregation>
              <ID>Aggregation_02</ID>
              <Name>Aggregation_02</Name>
              <Dimensions>
                <Dimension>
                  <CubeDimensionID>dimDate</CubeDimensionID>
                  <Attributes>
                    <Attribute>
                      <AttributeID>Month</AttributeID>
                    </Attribute>
                  </Attributes>
                </Dimension>
                <Dimension>
                  <CubeDimensionID>dimSalesType</CubeDimensionID>
                  <Attributes>
                    <Attribute>
                      <AttributeID>Sales Type</AttributeID>
                    </Attribute
                  </Attributes>
                </Dimension>
               
You can aggregate more than one attribute per dimension
                <Dimension>
                  <CubeDimensionID>dimLocation</CubeDimensionID>
                  <Attributes>
                    <Attribute>
                      <AttributeID>State</AttributeID>
                    </Attribute>
                    <Attribute>
                      <AttributeID>County</AttributeID>
                    </Attribute>
                  </Attributes>
                </Dimension>
                <Dimension>
                  <CubeDimensionID>dimItem</CubeDimensionID>
                </Dimension>
              </Dimensions>
            </Aggregation>
    
          </Aggregations>
        </AggregationDesign>
    </ObjectDefinition>
</Create>

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s