Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

Create New SSAS Partition dynamically: XML Task

with one comment

There is a good number of posts exist on how to create and manage new SSAS partitions dynamically, but almost all of them based on AMO coding and may look intimidating to some folks especially with little .NET skills in the past but expert DBA skills presently J. Knowing my programming background, I was "kindly" asked to provide a framework for dynamic partition creation with as little code implementation as possible, so it can be maintained by a person with a different skill set. Let’s think a bit: SSAS partition object can be scripted as XMLA script, some XMLA tags can be replaced dynamically to ensure partition uniqueness, SSIS XML Task has Operation Type – Merge… Ok, enough thinking J


Assume, that we want to create a new partition for each new year of data. We will need a simple cube with at least one QueryBinding partition already created and having WHERE clause, just to generate our version of partition XMLA (original partition can be deleted after we are done), something like this:


As you can see, there are just a few dynamic parts that can be replaced on the fly to make partition script fully dynamic, the list is highlighted: Partition ID, Partition Name, WHERE clause and Partition Slice. SSIS XML Task Merge operation can add new tabs to a XML document, so basically, idea here is to add all dynamic parts in the run time and send the final script to the SSAS for execution. Let’s try to do that. First, we create a Master XMLA partition script template by deleting all dynamic tags and store it somewhere in the share folder. So, the Master script template will look like this:



As you can see, there is no Partition ID, Name or Slice tags in it, as well, WHERE clause got modified by replacing actual Year value with "####" mask. Additionally, <Create> tag had xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" attribute, but looks like XML Task can’t read the entire XML when this attribute is present, so I was forced to remove it while building dynamic XMLA with XML Task and restore it right before sending final XMLA script to the SSAS, since SSAS won’t process <Create> command without it. Next, I built a simple SSIS package with 5 tasks and 5 variables.



Package Variables are set up and configured in the following way



For example, Node_PartiitonID variable’s Expression property is set to "<ID>Transaction_" + @[User::CurrentYear] + "</ID>" to build partition ID based on the current Year.


[Add Partition ID] XML Task is configured as follows and self-explanatory.



Here, I connected to my Master XMLA template, went to /Create/ObjectDefinition/Partition XPath and inserted value taken from [User::Node_PartitionID] variable. After all this, I saved results of generated XMLA script to [User::XMLA] variable. In the next two tasks, I am sequentially adding Partition Name and Partition Slice to a in-variable script, each time modifying and saving final version of XMLA variable contents. Configuration for the [Add Partition Slice] looks like this.



Here, source is [User::XMLA] variable of course, not initial version of the Master.xmla file. The task before the last one is the Script Task. We need to add some final details to the almost-ready-for-the-run XMLA script. Couldn’t avoid using .NET completely, J


      Public Sub Main()


        ‘year mask

        Const YEAR_MASK As String = "####"

        ‘get current year

        Dim sCurrentYear As String = _


        ‘replace #### mask with current year value

        Dim sXML As String = _

             Dts.Variables("User::XMLA").Value.ToString.Replace(YEAR_MASK, sCurrentYear)

        ‘add before droped xmlns attribute back to <Create> tag

        sXML = sXML.Replace("<Create>", _

             "<Create xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">&quot;)

        ‘store modified script back to var

        Dts.Variables("User::XMLA").Value = sXML


        Dts.TaskResult = Dts.Results.Success

      End Sub


The last task sends our final XMLA script to the SSAS server.


All this framework (5 tasks) can be placed inside a For Each Loop container, which will loop over all years that require new partition creation. The logic on how to find these years may be implemented differently, depending on overall system requirements and SSAS version. This can be done by reading a list of existing partitions from DMV, or SSIS can be configured not to fail if partition already exists, etc.


Of course, this solution has it’s own drawbacks: partitions can be created sequentially only and not in parallel, but if requirements for partition creation are moderate, like in our case, then it’s good enough. Another one is a maintenance of the Master XMLA template, but my answer to this: if DBA’s are already managing thousands of scripts, then one or few more is not a big deal at all J. Nonetheless, the main demand is achieved: no or little of .NET "nightmare". 



Written by Konstantin Gorakine

October 30, 2009 at 1:41 pm

Posted in SSAS

One Response

Subscribe to comments with RSS.

  1. Here is this and some other articles on SSAS Partitions: http://ssas-wiki.com/w/Articles#Partitions

    Sam Kane

    February 23, 2011 at 3:07 pm

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: