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

Chapter 7. Designing the Analysis Services OLAP Database

OLAP Database

The tip of the iceberg

You've designed, built, and loaded the relational data warehouse database; now it's time to think about building your Analysis Services OLAP database. This part of the project straddles the data track of the Lifecycle, as illustrated in Figure 7.1. There's a substantial component of dimensional and physical design and a modest extension of the ETL system for populating the database.

The business process dimensional model drives the design of the Analysis Services database (start with an OLAP database design that's nearly indistinguishable from the underlying relational dimensional database). Use Analysis Services' wizards to get started, and you can develop a decent prototype in a few days.

It'll take more than a few days to polish that prototype, adding complex calculations and other decorations, making the physical design decisions, and setting up and testing the process to keep the Analysis Services database upto-date. But you're starting from a clean and conformed dimensional model, so it's really not that hard. The investment in building and populating the OLAP database is typically measured in person-weeks, not person-months.

This chapter starts with a plug for why you should include an Analysis Services OLAP database in your DW/BI system. What it comes down to is this: It's substantially easier, and lots more fun, to deliver fast query performance and complex analytics in Analysis ...