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!
Hi Soheil,
I read your article. it’s very nice explanation. i have one problem in my SSAS db. we have some cubes in our project which are automatically run daily wise by using batch file. but we have three cubes in a single script. daily this script calls runs in as a single cube.could you please explain me how to split those 3 cubes individually and create batch files each of them. the problem is in that 3 cubes if one cube fails.. we didn’t see that in front end.. until we check the log in the server. . if it fail also the remaining 2 cubes running. then we can’t identified that cube failure.. in front end we are using TWS console to watch these cubes in the form of jobs scheduling. please reply me ASAP.
Hi Nagaraju,
Thanks for your feedback. Regarding splitting the cube processes, it is very easy to resolve your issue.
You just right click on each cube, then click “Process”. Select a desired processing option then click “Script” button on top of the window.
Put the scripts in separate SQL Server Agent jobs and you’re done.
Hope that helps.
Cheers