How to process SSAS objects in SSIS (single task or multiple sub tasks)?

1
votes

I'm working on SSIS and SSAS to build a BI plateform.

I would like to develop a SSIS worflow to process all my SSAS objects. So, I want to use an 'Analysis Services Processing Task` to do all the job.

enter image description here

In my case, there are a 8 cubes using more thant 15 dimensions (a dimension can be used by few cubes).

Should I process all the SSAS objets by using one Analysis Services Processing Task or can I split them into different sub tasks. For example, one package for each sub task including an 'Analysis Services Processing Task'.

So for example :

  • Package 1 - Task 1 - Processing dimensions
  • Package 2 - Task 2 - Processing cube 1
  • Package 3 - Task 3 - Processing cube 2
  • ...

Is this approach will be more efficient even if one ASP task can use parallel process ?

Thanks !

1
ssissql-server-2012ssassql-server-data-tools
Your approach will insure that there a no missing keys when the cubes are processed. Generally, it's a good idea. If processing time is taking too long, consider incremental processing and lazy aggregations. – Mark Wojciechowicz

1 Answers

2
votes

Normally, I would go the simplest way of running a "Process Full" of the complete database. Then Analysis Services takes care of dependencies, and uses multiple parallel threads to process everything. And by default, everything is part of one single transaction, i. e. if anything fails, everything stays in the original, consistent state, and users see the original state during processing, and the new state after processing completes successfully. There is no intermediate, half-new and half-old state visible to users.

Only of there are reasons to deviate from this simple approach, I would use a different approach. Reasons could be:

  • It takes to long to completely re-process everything, and you know which parts of the source data changed and which not.
  • You want to avoid to loose several hours of processing due to a problem that occurs in a late processing step, and you can live with the inconsistency caused by some parts being processed, and some not.
  • Your server has limited resources, and the default processing of Analysis Services is to ambitious and stops due to trying to parallelize too much, and then gets stuck as there are too few resources. Similarly, you do not have enough disk space on the server to host two copies of the data necessary for the transaction processing.
  • ...

The first one is the most likely one.