Microsoft® SQL Server® 2008 Integration Services: Problem-Design-Solution

9

SSAS Processing Architecture

This final chapter of the Business Intelligence chapter trio targets the integration points between SSIS and SQL Server 2008 Analysis Services (SSAS). Business Intelligence (BI) solutions that involve SSAS cubes typically include a method for processing the objects from the data warehouse or data mart database sources into the Online Analytical Processing (OLAP) cube structures. With the focus of Chapters 7 and 8 on handling dimension and fact table ETL, the final step in the process is to load the related SSAS objects, which, in most cases, is the responsibility of the ETL process. This chapter contains one Problem-Design-Solution to discuss SSAS processing and partition management techniques and best practices. The “Problem” section discusses the integration points between SSIS and SSAS, and some of the considerations to look at during that integration. The “Design” section looks at the out-of-the-box basics, and how to handle more complicated situations. Lastly, the “Solution” section shows how to create a set of packages to utilize the tasks and components, and manage the entire SSAS solution.

Problem

After you’ve designed your data warehouse and loaded it with data, you must provide the users with a way to access the data. By creating an Analysis Services database with cubes and dimensions, you give the users accessibility, and you can encapsulate business terms and business logic within the cube. Analysis Services will also contain aggregations ...