Excel® 2007 PivotTables and PivotCharts

Chapter 11. Building a PivotTable from an Olap Cube

Understanding OLAP

So far in this book you have worked with relatively small data sources such as the Northwind sample database that comes with Microsoft Access. In the business world, however, it is common to work with data sources that are much larger — from hundreds of thousands of records to millions, even billions, of records. You cannot place such a huge data source on a worksheet, and even trying to manipulate all that data via a regular external data source is extremely time‐consuming and resource intensive. Fortunately, such huge data sources often reside on special servers that use a technology called Online Analytical Processing, or OLAP. OLAP enables you to retrieve and summarize immense and complex data sources. When combined with Excel, OLAP enables you to view the data in a PivotTable or PivotChart report and manipulate the data quickly and easily.

Data Warehouse

In a traditional relational database management system, or RDBMS, such as Access, multiple tables are related using common fields. In the Northwind sample database, for example, the Customers table is related to the Orders table based on the common Customer ID field, and the Orders table is related to the Order Details table on the common Order ID field. You can use a query to pick and choose fields from each table and return them in a dataset. However, this can be a very slow process with a massive data source, so OLAP uses a different concept called the ...