Michael Mukovskiy

Sometimes you want to backup the partition definitions of your cube and be able to (re)create them later easily. In another scenario you could want to mimic the production partitions on your test cube or vice versa.

Here we show the way of generating the XMLA batch command for partition creation using a simple XSL transformation against the XMLA cube definition. We consider MSAS 2008 R2 as our environment here.

The source XMLA that contains partition definitions can be obtained by scripting out the scope of interest (AS database, cube or measure group) in SQL Management Studio or just using the build result in Visual Studio: .asdatabase file or using WriteScript() in AMO in case it fits your infrastructure.

Instead of CREATE command for partitions we generate an ALTER with the option AllowCreate=true. It is more flexible than CREATE in many situations because it ignores the existing partitions with same definition and “fixes” the deviating attributes of partition with the same ID.

Now the whole cycle looks like following:

  1. Obtain the source XMLA with partition definitions (where <Partition> elements can be found)
  2. Apply the XSL transformation using the XSLT document below
  3. Optionally change the destination <DatabaseID> values in result XMLA script
  4. Run XMLA script against the destination Cube

 

Here is the XSLT definition:

<xsl:stylesheet version="1.0" 
                xmlns:xsl="
http://www.w3.org/1999/XSL/Transform" 
                xmlns:as="
http://schemas.microsoft.com/analysisservices/2003/engine">
  <xsl:output omit-xml-declaration="yes"/>
  <xsl:template match="/">
    <Batch Transaction="true" 
           xmlns="
http://schemas.microsoft.com/analysisservices/2003/engine">
      <xsl:for-each select="//as:Partition">
        <Alter ObjectExpansion="ExpandFull" AllowCreate="true" 
               xmlns="
http://schemas.microsoft.com/analysisservices/2003/engine">

          <Object>
            <DatabaseID>
              <xsl:value-of select="ancestor::as:Database/as:ID"/>
            </DatabaseID>
            <CubeID>
              <xsl:value-of select="ancestor::as:Cube/as:ID"/>
            </CubeID>
            <MeasureGroupID>
              <xsl:value-of select="ancestor::as:MeasureGroup/as:ID"/>
            </MeasureGroupID>
            <PartitionID>
              <xsl:value-of select="as:ID"/>
            </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"
             <xsl:copy-of select="child::*"/>
            </Partition>
          </ObjectDefinition>

        </Alter>
      </xsl:for-each>
    </Batch>
  </xsl:template>
</xsl:stylesheet>

Note that the transformation searches for <Partition> ignoring its hierarchical position (select=”//as:Partition”). This unbinds you from the general structure of source XMLA definition and respectively from the method you obtain it!

Note also that we copy one to one all partition elements: <xsl:copy-of select="child::*"/>

Here is the sample output – a batch command for partition creation which can be executed in SQL Management Studio for example:

<Batch Transaction="true"
       xmlns="
http://schemas.microsoft.com/analysisservices/2003/engine"
       xmlns:as="
http://schemas.microsoft.com/analysisservices/2003/engine">
  <Alter ObjectExpansion="ExpandFull" AllowCreate="true">

    <Object>
      <DatabaseID>My_DB_ID</DatabaseID>
      <CubeID>My_Cube_ID</CubeID>
      <MeasureGroupID>My_MG_ID</MeasureGroupID>
      <PartitionID>My Fact 2012</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">
        <ID>My Fact 2012</ID>
        <Name>My Fact 2012</Name>
        <Source xsi:type="QueryBinding">
          <DataSourceID>MyDS</DataSourceID>
          <QueryDefinition>SELECT * from dbo.FactTable where Year=2012</QueryDefinition>
        </Source>
        <StorageMode>Molap</StorageMode>
        <Slice>[Date].[Year].&amp;[2012]</Slice>
        <ProcessingMode>Regular</ProcessingMode>
        <ProactiveCaching>
          <SilenceInterval>-PT1S</SilenceInterval>
          <Latency>-PT1S</Latency>
          <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
          <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
          <Source xsi:type="ProactiveCachingInheritedBinding" />
        </ProactiveCaching>
      </Partition>
    </ObjectDefinition>

   </Alter>

</Batch>

You can run the XSL transformation in many ways: XML Notepad, SSIS Package, XML editor in Management Studio or Visual Studio. For details on using XSLT and other scenarios for MSAS see our post:

    XPath and XSLT for the metadata analysis of Analysis Services objects.