Excel OLAP pivot tables are a relatively new. Most advanced Excel users are familiar with Pivot Tables but do not realize that Excel now has a new and more powerful type of Pivot Table. You’ll find out a lot more about the difference between the different Pivot Table types in the article below.
Most Excel users will be able to broadly understand the article but to actually use OLAP pivot tables requires a lot of background knowledge. You may prefer to begin with my Excel Power Pivot 2-minute overview article first. That puts the whole subject of OLAP into context.
This article is an unedited lesson from one of the 68 short focused lessons in my Excel Expert Skills book/e-book that seek to empower Excel users with a mastery of OLAP skills.
Leave me a comment if you find the article useful.
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: 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.
Lesson 12-7: Understand Excel OLAP pivot tables
In: Lesson 9‑4: Understand the pivot table data cache you learned that 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.
In: Lesson 11‑30: Load a query directly into the PivotTable cache, you learned that 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.
In: 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 created a data model and then used it as the data source for a new type of pivot table called an OLAP pivot table.
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.
Excel 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). You’ll learn more about MDX queries later, 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.
Excel 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: Lesson 11‑30: Load a query directly into the PivotTable cache, you learned 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.
This lesson is excerpted from the above book.
This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.
It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Lessons in Session 12
|Session Twelve: Power Pivot, Data|
Modeling, OLAP and Business Intelligence
|Lesson 12‑1: Understand data model|
|Lesson 12‑2: Install the Power Pivot|
|Lesson 12‑3: Add tables to a data|
|Lesson 12‑4: Understand the Power|
|Lesson 12‑5: Add a relationship to a|
data model using Power Pivot
|Lesson 12‑6: Use an OLAP pivot table|
to analyze data residing in a data model
|Lesson 12‑7: Understand OLAP pivot|
|Lesson 12‑8: Understand modern data|
|Lesson 12‑9: Create a data model|
directly from a relational database
|Lesson 12‑10: Understand many-to-many|
|Lesson 12‑11: Understand OLTP|
|Lesson 12‑12: Understand OLAP|
|Lesson 12‑13: Understand how to|
resolve many-to-many relationships
|Lesson 12‑14: Create a snowflake data|
model from a relational database
|Lesson 12‑15: Convert a snowflake|
data model into a star data model
|Lesson 12‑16: Hide, remove and rename|
data model columns
|Lesson 12‑17: Add a calendar table to|
a data model
|Lesson 12‑18: Format values in a data|
|Lesson 12‑19: Understand MDX queries|
and OLAP pivot table limitations
|Lesson 12‑20: Use the CUBEVALUE|
function to query a data model
|Lesson 12‑21: Convert CUBEVALUE|
functions to include MDX expressions
|Lesson 12‑22: Create an asymmetric|
OLAP pivot table using Named Sets
|Session 12: Exercise|
|Session 12: Exercise Answers|
Power Pivot causes great confusion. This simple 2-minute overview completely de-mystifies Power Pivot and its associated technologies.
This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.
This lesson discusses the Power Pivot window and how it differs from the regular Excel window. A sample file is included.
A Power Pivot data model is an OLAP database. OLAP databases can be modeled as a Snowflake or Star schema. This article explains both designs.
A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.
This lesson shows how to use the CUBEVALUE function to make an OLAP pivot table directly access the data model. Includes sample file.