Microsoft® SQL Server® 2008 Bible

Chapter 70. BI Design

IN THIS CHAPTER

• Differences between OLTP and OLAP

• Data warehousing concepts

• Warehouse structures and relationships

• Loading dimensions and fact tables

• Managing changing dimension data

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 a very few staff due to security concerns.

Often the next step is to make a copy of the OLTP database for the express purpose of running analytical or reporting 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. Consistent interpretation ...