An Easy Way for SSAS Batch Processing

Batch processing is available in Analysis Services. Which means we can send multiple processing commands to the server in just a single SQL Server Job. With SSAS batch processing we can control which objects to be processed and in what order in a batch. As batch processing reduces the amount of time taken to commit changes it offers better data availability. We can easily generate XMLA codes for batch processing through SSMS (SQL Server Management Studio). You might see lots of discussions about this in other websites and lots of them are saying you need to right click on the objects one by one and generate the scripts. Then put all scripts together in another XMLA script. But it is such a pain when you have lots of objects that should be selected one after another to generate the batch processing XMLA. Sadly, it is not the end of the story. You need put all scripts together by copying and pasting the scripts several times. Today I want to show you a very easy to the job which saves lots of your time.

I’m using “Adventure Works 2012 Multidimensional” as an example and I’m going to batch process some dimensions.

Connect to the SSAS server from Management Studio

Expand the database

Expand dimensions

Press F7 to open Object Explorer Details

Now you can multi-select dimensions

Right click on selected dimensions from Object Explorer Details and click Process

Select all dimensions from the object list. You should know this, press shift and select the dimensions from the list.

Right click on the list and select “Process full”

Click on change settings and tick “Process affected objects”. This will force processing all objects that have dependencies to the selected objects. Click OK.

Click on the script button then click Cancel

As you can see a batch processing XMLA script is generated in a new query

Select all script an copy it

Connect to a SQL Server Database Engine to create a SQL Server Job for the batch processing

Expand Jobs

Right click on Jobs and select New Job

In General page type a name for the job and select its owner

Click on Steps page and click New

Key in a step name

Select “SQL Server Analysis Services” as type

Enter the server name

Paste the XMLA scripts you copied before in the command text box

Click “Advanced” then select “Quit the job reporting success” then click OK

Click OK

Now run the job by right clicking on it and selecting “Start Job at step…”

All done!

Like this: Like Loading...