SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: Need XMLA example to add or drop existing aggregate from partition

A: To drop aggregation from partition using XMLA you can execute ALTER XMLA statement and set empty value between AggregationDesignID tags:

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object><DatabaseID>Adventure Works DW</DatabaseID>
 <CubeID>Adventure Works DW</CubeID>
  <MeasureGroupID>Fact Sales Summary</MeasureGroupID>
    <PartitionID>Total_Sales_2004</PartitionID>
</Object>
<ObjectDefinition>
 <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
<Source xsi:type="QueryBinding">
 <DataSourceID>Adventure Works DW</DataSourceID>
 <QueryDefinition>SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct,
DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber,
'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM FactResellerSales
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'
UNION
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct,
DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber,
'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM FactInternetSales
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'</QueryDefinition>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<ProactiveCaching>
 <SilenceInterval>-PT1S</SilenceInterval>
 <Latency>-PT1S</Latency>
 <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
 <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
 <AggregationStorage>MolapOnly</AggregationStorage>
 <Source xsi:type="ProactiveCachingInheritedBinding">
  <NotificationTechnique>Server</NotificationTechnique>
 </Source>
</ProactiveCaching>
<EstimatedRows>51201</EstimatedRows>
<AggregationDesignID></AggregationDesignID>

     </Partition>
</ObjectDefinition>
</Alter>

Note: in <ObjectDefinition> part you have to specify all important partition parameters. If, for example you will not specify Source property, then this partition will have empty source property. I would recommend that you use scripting functionality of Microsoft SQL Server Management studio to create initial partition script and then adjust it.

To create aggregation you need to specify Aggregation Design ID between AggregationDesignID tags:

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
 <DatabaseID>Adventure Works DW</DatabaseID>
  <CubeID>Adventure Works DW</CubeID>
   <MeasureGroupID>Fact Sales Summary</MeasureGroupID>
    <PartitionID>Total_Sales_2004</PartitionID>
</Object>
<ObjectDefinition>
 <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
<Annotations>
 <Annotation>
  <Name>AggregationPercent</Name>
  <Value>20</Value>
 </Annotation>
</Annotations>
<Source xsi:type="QueryBinding">
 <DataSourceID>Adventure Works DW</DataSourceID>
 <QueryDefinition>SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct,
DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber,
'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM FactResellerSales
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'
UNION
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey,
SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct,
DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber,
'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM FactInternetSales
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'</QueryDefinition>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<ProactiveCaching>
 <SilenceInterval>-PT1S</SilenceInterval>
 <Latency>-PT1S</Latency>
 <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
 <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
 <AggregationStorage>MolapOnly</AggregationStorage>
 <Source xsi:type="ProactiveCachingInheritedBinding">
  <NotificationTechnique>Server</NotificationTechnique>
 </Source>
</ProactiveCaching>
<EstimatedRows>51201</EstimatedRows>
<AggregationDesignID>AggregationDesign</AggregationDesignID>  </Partition>
</ObjectDefinition>
</Alter>

Done