Problem
What is the difference between Parallel and Sequential Processing in SQL Server Analysis Services?
Solution
Even though tabular OLAP cubes have been quite the rage of late, there still exists a larger number of folks running Multi-Dimensional OLAP cubes. These cubes require processing to keep their data current. The basic processing settings center around what data to update; however, the processing order option controls how SSAS executes the actual queries that are sent to the database to update both the dimensional and measure data.
Two options exist for completing this task; either the job can execute the queries in a sequential or in a parallel fashion. Of course, advantages and disadvantages exist for both options, which we will discuss below. Additionally, as you will see below, some of our assumptions based on the name of the option may not be fully correct. Finally, note that these processing options are used irrespective of other processing options, such as Process Full or Process Update.
Parallel Processing in SQL Server Analysis Services
Parallel processing is the default option when you start processing an object in SSAS, as shown below.
To change the procession objects, you simply click the Change Settings button.
With the parallel option, by default the SSAS processing engine issues what it considers to be an optimal number of database queries to be executed by the database server. Furthermore, the processing engine issues the query commands as a single job and transactions wait until all the processed objects are finished successfully before committing the changes to the OLAP cube objects. This option is often the fastest option as many objects are processed in tandem. However, you run the risk of running into issues such as out of memory conditions or performance issues when running too many queries as the same time.
As shown below, you can manually decide the number of parallel statements to be executed at the same time.
The Maximum parallel tasks setting lets you set the granularity of the number of the transactions, but you still run into the same risks as letting the processing engine decide.
To summarize, parallel processing can speed up performance because you are maximizing the number of threads executing at the same time; however, if you have long running processing times, the cube and related objects are taken offline at the beginning of the process and remain offline (“unprocessed”) until the entire job completes successfully.
Sequential Processing in SQL Server Analysis Services
Different from parallel processing, the sequential processing option allows for the selection of two sub-options: One Transaction and Separate transactions. The one transaction option works in a way similar to the Parallel processing order in that either all the batch jobs are committed, or nothing is committed. However, the individual objects are not taken offline immediately.
If the Separate Transactions option is selected, then each object’s processing is treated as its own batch job. If one object experiences an error, the remaining jobs will continue and commit their processing success (or failure) as they are completed.
Let us show an example of what happens in each of the different scenarios. Using the Wide World Importer DW analysis services cube (and the related WideWorldImportersDW database), we can see the different results of processing the cube’s dimensions. To affect an error in the processing, I changed a customer key in the Dimension.Customer table so that a duplicate key resulted.
When processing the objects using the parallel options, the job fails completely. In fact, the job does not even get to the all the dimensions to begin processing. In the illustration below, the error plainly tells us about the duplicate key.
Using the Sequential, One Transaction option, the below screen prints show a similar error and similar result of all processing being rolled back, but you may also notice more objects were processed.
Finally, the Sequential, Separate Transaction option produces a very different result. Notice in the response area, that multiple responses are listed, but only one transaction is showing an error.
One item that I have found helpful in determining the best option to use as it truly does depend on your hardware, cube and database sizes, and your processing window, is to use the SSAS profiler which is shown below. This works very similar to the profiler available for SQL Server.
The flexibility to adjust the processing order options in SSAS provides several layers of control for your processing needs.
Next Steps
