While building and deploying an SSAS OLAP cube, there are two processing orders that you can choose from when you create a process operation:
- Parallel (All Objects will be processed in a single transaction): Used for batch processing, all tasks run in parallel inside one transaction.
- Sequential (Transaction mode):
- One Transaction: All Tasks are executed in one transaction
- Separate Transactions: Every Task is executed in one transaction
As mentioned above, when choosing a parallel processing order, tasks are processed in parallel (specific degree of parallelism) but if one task fails all operations are rolled back, even if it was the last one. This may lead to a serious problem when processing a huge OLAP cube that contains lots of partitions. In a similar case, using a sequential processing order will decrease the processing performance since tasks are processed sequentially which delays delivery. One of the best workarounds would be processing partitions using a parallel option but in batches; each process operation (batch) will contain a specific number of partitions (tasks). In case that an error occurs during processing phase, only one batch tasks are rolled back. I proposed this approach for the first time as a solution to a similar problem on stackoverflow.com. Since it addresses a wide audience, I decided to improve it and write it as a separate article. To learn to build a cube from scratch using SSAS, I would recommend you to run through this interesting article, How to build a cube from scratch using SQL Server Analysis Services (SSAS). This article contains a step-by-step guide for implementing this processing logic within a SQL Server Integration Services package.