This lesson is excerpted from the Expert Skills book
This lesson is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002.
This lesson is contained in the session: Power Pivot, Data Modeling, OLAP and Business Intelligence.
A normal pivot table does not access the source data directly. Instead, a pivot table data cache is created from the data source. The pivot table then takes its data from the cache. This is explained in depth in the Expert Skills Book: Lesson 9‑4 :Understand the pivot table data cache.
A pivot table data cache doesn’t need to use an Excel worksheet as its data source. It is possible to construct the cache directly from an external data source. This technique is taught in the Expert Skills book: Lesson 11‑30: Load a query directly into the PivotTable cache, you learned that
A data model (that is fundamentally different to a pivot table data cache) can be used as the data source for a new type of pivot table called an OLAP pivot table. This is covered in depth in the Expert Skills book: Lesson 12‑3: Add tables to a data model, Lesson 12‑5: Add a relationship to a data model using Power Pivot and Lesson 12‑6: Use an OLAP pivot table to analyze data residing in a data model.
You may wonder why a different type of pivot table is needed when you create a pivot table from a data model rather than a single Excel table.
Normal and OLAP Pivot Tables
In the above diagram you can see that both the regular pivot table and the OLAP pivot table look identical.
The difference between them is that the OLAP pivot table obtains its data from a Data Model created from one or more related tables. The ability to select fields from more than one related table is one of the biggest advantages of working with OLAP pivot tables.
The regular pivot table obtains its data from a pivot table data cache created from a single table residing in an Excel worksheet or external data source.
OLAP pivot tables are standards-based
The PivotTable data cache can be thought of as a non-standard OLAP implementation that is restricted to a single table data source.
In contrast, when data resides in a data model it can be retrieved directly from the data model using an industry-standard query language called MDX (Multi-Dimensional eXpressions). The Expert Skills Book provides more information about MDX in: Lesson 12 19: Understand MDX queries and OLAP pivot table limitations.
MDX is also used by many non-Microsoft products such as Crystal Reports.
An OLAP pivot table automatically generates the MDX queries it needs to return values to display in the OLAP pivot table.
OLAP pivot tables can work with big data
Excel worksheets are limited to approximately one million rows. OLAP pivot tables obtain their data from a Data Model. Data Models can contain about two thousand million data rows.
In the Expert Skills book (Lesson 11 30: Load a query directly into the PivotTable cache) students are taught how a regular pivot table can work with big data by using Get & Transform to load data (from an external non-Excel data source) directly into the PivotTable Data Cache.
While it is true that a regular pivot table can also overcome the million-row limitation in this way, the PivotTable Data Cache is still restricted to 2.1 thousand million data items (rather than rows) and is unable to work with more than one table.
What is Business Intelligence?
Business Intelligence is a very broad term that was first used in 1865 to describe how the banker Sir Henry Furnese prospered by receiving and acting upon information before his contemporaries.
When IT professionals talk about Business Intelligence (BI) today, they usually mean the analysis and presentation of data sourced from an OLAP database (data model).
Dr E.F. Codd (the inventor of OLAP) wrote a paper in 1985 that proposed 12 rules for defining OLAP. Two of his rules were:
- The tool (and not the user) should be concerned with where the physical data comes from.
- Reporting facilities should present information in any way the user wants to view it.
You can see how Microsoft’s BI solution complies with Codd’s rules by separating the data modeling (difficult) and reporting (easy) tasks and by enabling complete reporting flexibility via the OLAP pivot table.
Additional information (shown in the sidebar of the Expert Skills book)
What is self-service BI?
In the recent past it was very expensive to implement a Business Intelligence solution using server-based products such as Microsoft SQL Server Analysis Services.
BI projects would often take many months to complete and involve large numbers of highly trained IT professionals to design (and extract and transform data into) an OLAP cube. An OLAP cube is the earliest implementation of an OLAP database – for more on this see Expert Skills book: Lesson 12 11: Understand OLTP database design (sidebar).
The vision for the Excel data model is that ordinary Excel users can instantly and automatically create a ready-to-go data model (OLAP database).
An OLAP pivot table or OLAP pivot chart can then be used to analyze and present the contents of the data model.
Microsoft sometimes call this concept: Self Service BI.