The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

5.3. Updating Analysis Services Databases

We recommend that you use Analysis Services as the main presentation server for your DW/BI system. All the hard ETL work goes into populating the relational dimensional data warehouse database. The Analysis Services database is then built from the relational data warehouse database, and should always consume cleansed and conformed data.

We've seen many installations where Analysis Services is treated not as part of the DW/BI system, but instead as a kind of client tool that doesn't need to be managed in a professional way. This attitude sets you up for trouble. Your team should clearly understand that Analysis Services is a server, with significant requirements for availability and recoverability.

The first thing to understand about Analysis Services is that there are two basic units of processing: dimension processing and fact processing. Just as you have already defined the structure of your relational database before you run the ETL job to populate it, you have already defined your Analysis Services database structure and are ready to add the same data you've just added to the relational data warehouse database.

The standard method of updating the Analysis Services database is to use the Analysis Services Processing task in Integration Services. You could add this task to the end of each package's control flow. Once the table is correctly updated in the relational database, kick off the associated Analysis Services processing. You can ...