Automate creation of cube partitions

In most of the SSAS solutions, developers create cube partitions as per Years, Semesters,Quarters or Months but that totally depends upon the volume of data your fact table contains. Now consider a case in which you have created cube partitions for the previous and current Year but the next question strikes in your mind is that "how to create a new partition before new year begins ?" and then probably you will think that you have to create a partition manually in the SSAS project file and deploy that project before new year begins but Is it really a efficient solution as you will have to create a new partition and deploy the code before every year begins and then probably you will think, "Can I automate the creation of partitions before every Year begins ?", and the answer to this question is "Yes" and this post is all about the same. So lets discuss this in details.......I am going to explain you the case where we are going to create partition on yearly basis and if your cube contains partitions per year then probably you can create that manually before new year starts but if your cube contains partitions as per quarters or months then it's really a separate maintenance task to modify the code manually in order to create a new partition before every quarter or month begins. So lets discuss this in details.....

You can automate the creation of partitions by writing some AMO code under "Script Task" of SSIS package. Step-by-step instructions for the same are as follows;

1. Create a SSIS project. I have created a project and package with the name "CreateCubePartitions" but you can create with the name which you want.

2. Drag and drop "Script Task" from the Toolbox on to the "Control Flow" tab of the package.

3. I have renamed "Script task" to "Create Cube Partitions" but you can rename it as per your need. Right click on script task and click on "Edit" option.

4. When you click on "Edit" option, it will open a "Script Task Editor" form. From the "ScriptLanguage" option ,select "Microsoft Visual Basic 2008" ( as I am going to demonstrate this example using VB script and that's why I have selected "Microsoft Visual Basic 2008" option" but you can use Microsoft Visual C# 2008 option too). After selection of script language, click on "Edit Script" button. when you click on "Edit Script" button, it will open VB script console. Click on "Show All Files" option under "Project Explorer". You can refer following snapshot for the same.

5. Right click on "References" node and click on "Add Reference" option.

6. When you click on "Add Reference" option, it will open "Add Reference" form. Under ".Net" tab click on "Analysis Management Objects" and click OK button and you will find "Microsoft.AnalysisServices.dll" under the references node which I have highlighted in the following snapshot (this example is based on SSAS 2008 version and that's why I have selected 10.0.0.0 version dll but you will need to select the proper version of dll as per the version of SSAS installed on your server). 

7. After referencing the dll, you will need to Import the same, so you can use the classes and objects of AnalysisServices namespace. For importing, you just need to write Imports Microsoft.AnalysisServices in the following way at the top of your class.

8. Now you can write your AMO code using different classes of AnalysisServices namespace. Following is the sample code which you can write under Sub Main( ). I have given sample code. you can modify that as per your requirements.



Public Sub Main()


        Dim objServer As Server
        Dim objDatabase As Database
        Dim objCube As Cube
        Dim objFindPartition As Partition
        Dim objNewPartition As Partition
        Dim intCurrentYear As Integer
        Dim strQuery As String
        Dim objSource As QueryBinding
        Dim strQueryForNewPartition As String
        Dim strNewPartitionName As String


        intCurrentYear = Now.Year


        objServer = New Server
        objServer.Connect("localhost")


        objDatabase = objServer.Databases.FindByName("Adventure Works DW 2008R2")
        objCube = objDatabase.Cubes.FindByName("Adventure Works")


        For Each objMeasureGrp As MeasureGroup In objCube.MeasureGroups


            strQuery = String.Empty


            For Each objFindPartition In objMeasureGrp.Partitions


                If objFindPartition.Name.Contains(intCurrentYear) Then
                    objSource = objFindPartition.Source
                    strQuery = objSource.QueryDefinition
                    strQueryForNewPartition = strQuery.Replace(intCurrentYear, intCurrentYear + 1)


                    strNewPartitionName = objFindPartition.Name.Replace(intCurrentYear, intCurrentYear + 1)
                End If
            Next


            objNewPartition = New Partition
            objNewPartition.Name = strNewPartitionName
            objNewPartition.StorageMode = StorageMode.Molap
            objNewPartition.Source = New QueryBinding(objSource.DataSourceID,   strQueryForNewPartition)
            objMeasureGrp.Partitions.Add(objNewPartition)
            objMeasureGrp.Update(UpdateOptions.ExpandFull)
            objNewPartition.Process(ProcessType.ProcessFull)
        Next


        objCube.Update(UpdateOptions.Default)
        objDatabase.Update(UpdateOptions.Default)


        Dts.TaskResult = ScriptResults.Success
End Sub



9. So you are done with your package creation and you can schedule the package using SQL Server Job Agent. Create a new job and under Schedule tab and then under New job schedule, select Frequency as Monthly and then set a appropriate day and then recurs every 12 months. You can schedule a job quarterly, monthly if you want to create cube partitions on quarterly,monthly basis respectively.