Vidas Matelis Analysis Services Blog

I recently posted a new PowerShell script to process all dimensions and cubes in the specified Analysis Services database. This script has a few additional parameters that give you more control for your SSAS DB processing. First of all, you can specify the MaxParallel processing parameter to control the level of parallelism. You can also specify how many processing commands you want to execute per batch. 

The MaxParallel processing parameter controls the level of parallelism – the number of threads to run commands in parallel. When the MaxParallel value is not specified, or set to 0, then Analysis Services chooses a value based on the number of CPU cores of the server. I found a few reasons why I want to set MaxParallel parameter value:

• If I allow SSAS to choose the number of threads, in most cases during my cube processing I see close to 100% CPU usage. When I know that other databases will be queried during processing, I choose a lower MaxParallel value and leave some CPU power to answer other SSAS database queries.
• In my post “SSAS database processing sometimes gets stuck” comments Leandro Tubia posted that reducing the MaxParallel value helps with the “SSAS getting stuck” problem.

Another new parameter is CmdPerBatch. There is a reason why sometimes I want to set CmdPerBatch parameter to anything else than 999. Here a value of 999 means that all processing commands will be executed in one batch (I have less than 999 objects). But when all objects (dimensions, partitions) are processed in one batch, even one object failure will rollback ALL processed objects. So sometimes it is very frustrating when after nightly job I find that because of a small mistake in one object my development database is not processed. So I prefer in the development environment process in batches of 4 or 8. In such case if one object processing fails, the next morning I’ll find 4 or 8 unprocessed objects that I can deal with manually. Note: Analysis services allows you to ignore dimension key errors, but here I am talking about different processing failures – for example a fact table structure change that is not reflected in the SSAS DB. I use this parameter just on my development server, in production I usually process objects in one batch.

So here are my processing time test results for different parameter values:

Here are some things I learned from this:

• The fastest way of processing is when you have all commands executed in one batch
• The slowest way of processing is when you have each object processed in a separate batch. In my database this was 4 times (!) slower than the fastest processing configuration.
• A higher MaxPrallel value will not necessary give you better performance. 
• Letting the server decide the MaxParallel value could be the best option for the fastest processing in most cases.
• More objects (dimensions, partitions) in your SSAS database mean that the processing time will increase even on the same data volumes

I would like to point out that processing time depends on many factors and you will see different behaviour on your database structure, your specific data and your server configuration. But you should be aware that these parameters will affect your processing time.