Chapter 7: Designing the Analysis Services OLAP Database

The tip of the iceberg

Overview

Youve designed, built, and loaded the relational data warehouse database; now its 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. Theres a substantial component of dimensional and physical design and a modest extension of the ETL system for populating the database.

Figure 7.1: Business Dimensional LifecycleThe Data Track

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

Itll 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 up-to-date. But youre starting from a clean and conformed dimensional model, so its 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: Its substantially easier, and lots more fun, to deliver fast query performance and complex analytics in Analysis Services than in a relational database. Analysis Services works well. Especially when you consider its price, its the obvious choice of OLAP technology on the Microsoft platform.

We spend most of the chapter discussing how to develop your Analysis Services dimensions and measures. We end with a discussion of physical design considerations, including pre-computed aggregations and partitions, along with an overview of the approaches for keeping your Analysis Services database up-to-date.

In Chapter 10, we look at the other part of Analysis Services functionality: data mining.