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.

Weve 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 doesnt 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 youve 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 packages control flow. Once the table is correctly updated in the relational database, kick off the associated Analysis Services processing. You can even define an Integration Services transaction to roll back the relational changes if the Analysis Services processing encounters a severe error.

An alternative, and usually better, approach is to create one package to perform all Analysis Services processing. Execute this package as the final step in the master packages control flow. This way you can easily bind all the Analysis Services work together into a single transaction.

If you have SQL Server Enterprise Edition, you could set up the Analysis Services database with Proactive Caching. Proactive Caching, which we discuss at greater length in Chapter 17, is a new feature of Analysis Services 2005 that basically monitors the relational database and automatically populates the Analysis Services database. With this approach, the Integration Services packages would ignore Analysis Services. Proactive Caching is most useful for low latency scenarios, where youre adding data to the cube throughout the day.