Excel® 2007 VBA Programmer's Reference

Chapter 23. Browsing OLAP Data Sources with Excel

The dominant database type in most organizations is the OLTP (On-line Transaction Processing) database. Indeed, most of you are probably working with some form of an OLTP database as you read this. The main characteristics of this type of database are: they typically contain many tables, each table usually contains multiple relationships with other tables, and records within any given table can be routinely added, deleted, or updated.

Although OLTP databases are effective in gathering and managing data, they typically don't make for effective data sources for reporting, for three main reasons:

• Complexity: The large number of tables and relationships that can exist in an OLTP database can leave you wondering exactly which tables to join and how the tables relate to each other.

• Volume: OLTP databases normally contain individual records. Lots of them. To create any number of aggregate reports and views, you would have to run views that group, aggregate, and sort records on the fly. The sheer volume of data in the database could very well inundate you with painfully slow reporting.

• Consistency: By its very nature, the records in a transactional database are ever-changing. Building a reporting solution on top of this type of database will inevitably lead to inconsistent results from month to month, or even from day to day.

Some organizations avoid these woes by building their reporting solutions on top of OLAP (On-Line Analytical Processing) ...