In the 7th article of this series, I will discuss how to manage your partitions with XMLA. We will be focused on the partition structures as opposed to partition processing which was covered in the previous post.
Creating or altering a partition
I have already discussed object creation with XMLA in part 3 of this series. Remember that when altering the partition you need to have all of the partition definition you wish to keep as well as what you are changing in the XMLA. If you have a subset of the definition, SSAS will assume you want to remove the missing parts.
Adding a partition to an existing measure group
One area where a partition is different from other objects is that you can add a partition to an existing measure group. This is often done to improve processing performance or to match the underlying data architecture (especially if table partitioning is already in place for the source tables). Often the partitions are based on date and correspond to the date the data is loaded. For instance, you may have separate partitions for each year. So at the beginning of the next year you need to add a partition. In this case, you create a new partition with a specific data source definition for that partition.
If the measure group only has one partition that is based on a single table, you may need to change the source definition so you can properly add a new partition. There are two common ways to manage the data sources to support partitions. The first is to create views that map to the partition structure in the cube and then the data source for each partition can point directly to a particular table or view. The second way is to specify a query definition that usually points at one table and filters the data based on one or more keys.
In either case, the XMLA is constructed as a Create command with the ParentObject specifying the measure group that the partition will be added to. The Object definition contains the ID, Name, Source and other properties that make up the partition. The example below shows the definition for adding a partition with that covers the month of January using a filter in the QueryDefinition for the Source.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
</ParentObject>
<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" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<ID>Internet_Sales_Jan2011</ID>
<Name>Internet_Sales_Jan2011</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>Adventure Works DW</DataSourceID>
<QueryDefinition>SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey BETWEEN 20110101 AND 20110131</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>60398</EstimatedRows>
<AggregationDesignID>Internet Sales</AggregationDesignID>
</Partition>
</ObjectDefinition>
</Create>
Merging partitions
In some cases, you will find the need to merge partitions. In our example above we added a partition for the month of January. However, as the your partition count becomes larger there is benefit to merging partitions into historical or archive partitions. Or you may even decide that merging will help with maintenance or processing. In either case, the logic is fairly simple. You have one or more source partitions being merged into target partition. The XMLA for this is pretty straightforward.
There are a couple of nuances to be aware of including the fact that the structure and the aggregation designs need to be the same to support merging. If you plan to merge, you should take this into account when building partitions and applying aggregation designs. Furthermore, if you use partition slices you will likely need to modify the target’s slice after the merge is account for the new data.
Here is the code required to merge two source partitions into a target partition. The result will be one partition.
<MergePartitions xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Sources>
<Source>
<DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_Jan2011</PartitionID>
</Source>
<Source>
<DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_Feb2011</PartitionID>
</Source>
</Sources>
<Target>
<DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales</PartitionID>
</Target>
</MergePartitions>
As you can see in the example above, all that you need to specify is the full address for each partition and identify which are the sources and which one is the target.
Setting the Slice Property
Before digging into the XMLA a couple of notes on the data slice in cubes. First, slices are not required for MOLAP partitions, however, they can help with query performance. While in current versions of SSAS (2005 and later), the system will determine the slice based on the data, it is not always accurate. Keep in mind that a slice does not affect processing or the source data for a partition. That is determined by the Source attribute and related definition.
Refer to the following blog posts Jesse Orosz and Mosha Pasumanky for more information as the details around the value of assigning scope which is beyond the scope of this post:
http://jesseorosz.wordpress.com/?s=slices
http://sqlblog.com/blogs/mosha/archive/2008/10/14/get-most-out-of-partition-slices.aspx
Assuming you need to set the slice or if you need to change it due to a merge here is the XMLA to alter a partition’s slice property.
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_Feb2011</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" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<ID>Internet_Sales_Feb2011</ID>
<Name>Internet_Sales_Feb2011</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>Adventure Works DW</DataSourceID>
<QueryDefinition>SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey BETWEEN 20110102 AND 20110228</QueryDefinition>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<Slice>{[Date].[Calendar].[Month].&[2011]&[2]}</Slice>
<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>60398</EstimatedRows>
<AggregationDesignID>Internet Sales</AggregationDesignID>
</Partition>
</ObjectDefinition>
</Alter>
If we were to merge the January and February partitions we created previously, we could change the slice definition to include both months.
<Slice>{[Date].[Calendar].[Month].&[2011]&[1],[Date].[Calendar].[Month].&[2011]&[2]}</Slice>
Setting the Storage Location
Partitions also support the ability to change the storage location for each partition further capitalizing on hardware investments such as SSDs and low cost disks. This is set in the XMLA using the StorageLocation element with a valid path.
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_Jan2011</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" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<ID>Internet_Sales_Jan2011</ID>
<Name>Internet_Sales_Jan2011</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>Adventure Works DW</DataSourceID>
<QueryDefinition>SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey BETWEEN 20110101 AND 20110131</QueryDefinition>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<StorageLocation>C:\Data\SSASOptimized</StorageLocation>
<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>60398</EstimatedRows>
<AggregationDesignID>Internet Sales</AggregationDesignID>
</Partition>
</ObjectDefinition>
</Alter>
As you can see, there are many partition management operations that can be done using XMLA. As you work to develop a management strategy keep in mind that these operations can be scripted for automated execution when managing your Analysis Services solution.
Like this:
Like Loading...
A little over six months ago, I wrote an article on Magenic.com about ![powerpivotprotoday[1]](http://www.powerpivotpro.com/wp-content/uploads/2010/06/powerpivotprotoday11.png "powerpivotprotoday[1]")
