Expert SQL Server™ 2005 Integration Services

6.3. Advanced Processing and Partition Management Examples

When it comes to an enterprise BI solution, rarely is the status quo applicable for processing objects. Usually, there are unique precedent requirements or complicated partition management and processing needs to get the performance and agility essential for successful BI applications. Earlier, this chapter discussed the different ways that SSAS objects could be processed and managed within SSIS. The prior section looked at the basic out-of-the box functionality. So, now it's time to consider leveraging some of the other approaches and learning how to take advantage of the Analysis Services Execute DDL Task by making the XMLA dynamic.

6.3.1. Dimension Processing

Dimension processing is generally straightforward, except for some potential unique precedence handling or transaction management. Because of this, even though many of the prescribed approaches would work for dimension processing, the keep-it-simple corollary dictates that some of the approaches would be overkill.

Leveraging the Analysis Services Processing Task is often sufficient, and has already been described. The other straightforward approaches involve creating an XMLA script for processing the dimensions. Rarely do dimension structures change, and, in most scenarios, a process update is performed on the dimensions. In most cases, the XMLA script doesn't need to be dynamic. Therefore, the XMLA can be generated and then just executed either by using the ...