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
- Check out the following:
I have a passion for crafting Business Intelligence Solutions for my user groups. My experience includes almost 15 years of SQL Server involvement with the last 12 years focused specifically on Business Intelligence, SharePoint, OLAP, SSRS, and Decision Support solutions. Currently, I am a Business Intelligence Architect in the healthcare industry, and I also teach database and analytics classes for Kennesaw State University, Southern New Hampshire University, and Reinhardt University. My education includes an MBA and an undergraduate in Accounting (yes I am a reformed accountant!), both from Kennesaw State University. I enjoy every day by trying to grow my faith and spend precious time with my family. I have been happily married to my wife of over 20 years, and we have two teenagers one who we home school with the help of a University Model School, Cornerstone Prep in Acworth, GA (cornerstoneprep.org). Our other child is a Construction Management major at KSU’s Southern Poly / Marietta campus. We are a soccer and Cross Country (XC) family who play, coach, and referee soccer or run for fun most every day. For several years, our family has volunteered (and played with the dogs and cats) at Etowah Valley Humane Society in Cartersville, GA.
- MSSQLTips Awards: Champion (100+tips) – 2016 | Author of the Year – 2015 | Author Contender – 2014, 2016-2021