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

image

  • Press F7 to open Object Explorer Details
  • Now you can multi-select dimensions

image

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

image

  • Select all dimensions from the object list. You should know this, press shift and select the dimensions from the list. Smile
  • Right click on the list and select “Process full”

image

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

image

  • Click on the script button then click Cancel

image

  • 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

image

  • 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

image

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

image

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

image

  • All done!

image

Leave a Reply