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)
(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).
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.
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.
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.SuccessEnd SubFollow 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.
