Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

Excel OLAP pivot tables

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.

note

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

Excel Power Pivot 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.

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

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
memory requirements
Lesson 12‑2: Install the Power Pivot
add-in
Lesson 12‑3: Add tables to a data
model
Lesson 12‑4: Understand the Power
Pivot window
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
tables
Lesson 12‑8: Understand modern data
analysis
Lesson 12‑9: Create a data model
directly from a relational database
Lesson 12‑10: Understand many-to-many
relationships
Lesson 12‑11: Understand OLTP
database design
Lesson 12‑12: Understand OLAP
database design
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
model
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

Related Articles

Understand modern data analysis

Excel modern data analysis

This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.

Understand the Power Pivot window

Excel Power Pivot window

This lesson discusses the Power Pivot window and how it differs from the regular Excel window. A sample file is included.

OLAP Star Schema

Excel data model Design

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.

OLTP database example

Understand OLTP database design

A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.

Excel CUBEVALUE function

This lesson shows how to use the CUBEVALUE function to make an OLAP pivot table directly access the data model. Includes sample file.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

thirteen − 5 =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader