Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Processing Order Options


By:   |   Read Comments   |   Related Tips: > Analysis Services Administration

FREE Webcast > 5 Easy SQL Server Query Performance Boosters


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.

process object - Description: process objects
process all dimensions - Description: process all dimensions

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.

parallel processing - Description: parallel processing

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.

sequential processing - Description: sequential processing

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.

change customer key - Description: change customer key

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.

view details

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.

sequential one transaction - Description: sequential one transaction result

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.

sequential separate transactions - Description: sequential separate transactions

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.

SSAS Profiler - Description: SSAS Profiler

The flexibility to adjust the processing order options in SSAS provides several layers of control for your processing needs.

Next Steps


Last Update:



next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Learn more about SQL Server tools