The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition

Chapter 8. The Core Analysis Services OLAP Database

The tip of the iceberg.

Once you've designed, built, and loaded the relational data warehouse, it's time for the fun part: delivering information to the business user community. In order for users to be able to consume that information — especially for ad-hoc use — you need to make sure they can formulate queries and get responses quickly. The Analysis Services OLAP database is a popular and effective tool for meeting these goals, and should be a core part of your strategy to enable self-service business intelligence. OLAP stands for On Line Analytic Processing, to distinguish this analytic database from the more familiar OLTP transaction processing database.

Designing and developing the Analysis Services database straddles the data track of the Kimball Lifecycle, as illustrated in Figure 8-1. The OLAP design process begins with modeling. If you've followed the Kimball Method to implement the dimensional model in the relational database, the OLAP design step is a straightforward translation from that existing design. There are some physical design decisions to be made later in the development process, and a modest extension of the ETL system for populating the OLAP database.

Start the process of building the OLAP database by sourcing it from a robust, cleanly populated, relational dimensional model, as described in the previous chapters. From a solid starting point, you can develop a decent prototype in a few days.

It'll take more ...