SQL Server™ 2005 Bible

Chapter 43. Business Intelligence with Analysis Services

In This Chapter

• Data warehousing overview

• Building an Analysis Services database

• Configuring dimensions

• Dimension checklist

• Proactive caching configuration

• Data integrity configuration

Having worked with various organizations and data systems, over time I've noticed a progression of reporting and analysis solutions. First queries are run directly against the online transactional processing (OLTP) database, but this approach conflicts with production use of the database and generally limits access to very few due to security concerns.

Often the next step is to make a nightly copy of the OLTP database for the express purpose of running analytical queries. These attempts at using an OLTP database for online analytical processing (OLAP) are problematic on a number of fronts:

• OLTP data structures are optimized for single, atomic transactions, whereas OLAP queries summarize large volumes of data. Thus, queries are painfully slow.

• OLTP data may reflect limited history, whereas OLAP tends to be interested in historical trends.

• OLTP data structures are understood by a relatively small population of experts in the organization, whereas OLAP is most effective when exposed to the widest possible audience.

A common refinement on querying the OLTP database is to create a new database that contains tables of summary data. When done carefully, this approach can address some speed and history issues, but it is still understood by a relatively small population. ...