How to automate cube processing

After creating a cube, a first question strike to the professionals is "How to automate cube processing?” So I thought let's share some methods by which one can automate daily cube processing. There are several methods present by using those you can automate cube processing but I am going to discuss few of them which are most widely used across the organizations.

Following are the 2 most widely used approaches; 
(Note :- Following example is demonstrated using Adventure-works sample database)

1) Using XMLA:-

By using following approach you can Process any object of Analysis services database i.e. SSAS database itself, Measure Group, Cube, Partitions etc....

I am going to explain the approach for Processing “SSAS database” and following are the steps which one can follow to accomplish the same.

a)      Connect to Database engine and expand “SQL Server Agent” node, right click on the “Jobs” folder and select “New Job” option.

b)      When you click on “New Job” option, it opens “New Job” wizard, under “General” tab, mention all the required details like name of Job, Owner etc…

c)      Click on the “Steps” tab and click on “New…” option.

d)      Under “New Job Step” wizard, give the appropriate "Step name" and select “SQL Server Analysis Services Command” option under “Type”.

e)      Mention the server instance name under “Server” option and paste the following XMLA script under “Command”. (Note :- Following sample XMLA is for Adventure-works but you can simply use the name of your SSAS database name instead of "Adventure Works DW 2008R2" in the following XMLA).

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process 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">

      <Object>

        <DatabaseID>Adventure Works DW 2008R2</DatabaseID>

      </Object>

      <Type>ProcessFull</Type>

      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

    </Process>

  </Parallel>

</Batch>

Click on the OK button of “New Job Step” wizard and then click on the OK button of “New Job” wizard.

So now your SQL Server Job for processing SSAS database is ready and you can schedule the job as per your requirement.

2) Using AMO (Analysis Management Objects).

In this approach, SSIS package needs to be created using “Script Task”. You can follow following steps for achieving the same.

a)      Create a new SSIS package using “Script Task”; refer following screen shot for the same. (I have given the name of Script task as “Process SSAS database” in my sample and that’s why same is appearing in the screen shot). Right click on “Process SSAS database” named script task and select “Edit” option, when you click on “Edit” option, it will open “Script Task Editor” wizard. Under ScriptLanguage you can select "Visual C# or Visual Basic". I have demonstrated the sample using "Visual Basic" and that's why I have selected the same in the "ScriptLanguage". After selecting ScriptLanguage, click on "Edit Script" button.

b)   When you click on “Edit Script” option, it will open VB script code window, open “Project Explorer” (press “Ctrl+R”for the same). Click on “Show All files” option. Right click on “References” node and click on “Add Reference” option.

c)   Under “Add Reference” window, Select “Analysis Management Objects” under “.Net” tab and click on OK button.

d)  When you click on OK button, you will find the reference of "Microsoft.AnalysisServices" dll under the References. For using the classes and objects of referenced dll write,            Imports Microsoft.AnalysisServices at the top of .VB code file.

e)  Write following AMO code under Main() procedure, save and close the file.



Public Sub Main()
Dim objServer As Server
Dim objDatabase As Database

objServer = New Server
objServer.Connect("localhost") 'Write the instance name of SSAS server"  
objDatabase = objServer.Databases.FindByName("Adventure Works DW 2008R2")
objDatabase.Process(ProcessType.ProcessFull)

objServer.Disconnect()
Dts.TaskResult = ScriptResults.Success
End Sub

f)   Now your SSIS package is ready, you can create a SQL Server Job for scheduling SSIS package.
      Follow same steps a,b,c from "Using XMLA" (i.e. 1st approach)  and in step d, under "Type", instead of Analysis Services command, select "SQL Server Integration Services Package" and mention the package path (if you want to select "Package source" as File system).Click on OK button of New job step wizard and then OK button of New job and your SQL Server job for processing SSAS database using SSIS package is ready.You can schedule a job as per your requirement.

      You can also create a SSIS package using "Analysis Services Processing Task" and create a SQL Server Job for scheduling SSIS package.