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 Power Pivot – 2 minute overview

Excel Power Pivot enables a technology called OLAP (OnLine Analytical Processing) to be used by Excel.  OLAP is sometimes also referred to as Business Intelligence, but you’ll find out a lot more about what that actually means in the article below.

I’ll introduce this article with a little anecdote.  

Many years ago (in the early 90’s) I was contracted to engineer a pan-European business intelligence solution for one of the world’s largest car manufacturers.  At that time OLAP was a big new game-changing technology that every blue chip company just had to have.

OLAP was so new that none of the directors really understood what it was but they did appreciate the benefits.  I had to travel to many countries to give presentations explaining benefits such as “this report that used to take four hours will print out in a few seconds using OLAP”.  But if I was asked to explain how OLAP worked its magic it was very difficult to provide a simple explanation for such a complex technical subject (even to IT personnel).

When I first added OLAP lessons to my Expert Skills book I had to rise to this same challenge once again.  Was it really possible to explain the huge number of skills needed to effectively implement OLAP in such a simple way that even non-technical users could learn to create efficient data models?  I did this by creating 68 short, bite-sized lessons that absolutely anybody can understand.  You’ll find links to many of the lessons in the article below.

The challenge in this article was to distil 68 lessons and 165 pages down to a short 2-minute article.  If you read the article carefully, you will understand exactly what OLAP is, how it works, and what it can deliver for you. 

Leave me a comment if you find the article useful.

Excel power pivot - 2 minute overview

Excel users are often confused about exactly what Power Pivot does (especially if they have been browsing the Internet which is a mine of misinformation).  

I’m often asked by business analysts for a “Quick Overview” of what Excel’s new “power” tools actually do.  

Multidimensional analysis using Power Pivot isn’t one of those subjects that is easy to explain in a few words but in this article you’ll discover in just two minutes what Power Pivot is, what it does and why it is useful.  If you want to delve a little deeper, you can explore the hyperlinks within the text but I’d recommend that you first just read straight through the article to understand the big picture. 

Excel Power Pivot is one of several "power" tools that are now included in Excel

The term “Power Pivot” is often rather confusingly used to refer to several different tools that are used to implement a technology called OLAP (OnLine Analytical Processing).  OLAP isn’t new.  Microsoft’s first OLAP Server (Microsoft Analytical Services) was released over 20 years ago.

The OLAP method of data analysis requires two steps:

  1. Create an OLAP database (Excel calls this a Data Model).
  2. Use reporting tools to analyse and visualize the data residing in the Data Model.

All current versions of Excel for Windows (Excel 2019 and Excel 365) now include Power Pivot and are capable of performing OLAP data analysis.  The OLAP enabled tools are:

Tools to create a data model:

Tools to analyze and visualize the data residing in the data model:

Business Intelligence (BI)

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 instant analysis and presentation of data sourced from an OLAP database (data model) to support timely business decisions. 

Most users do not need a lot of training to benefit from Business Intelligence

Get & Transform and Power Pivot are sophisticated tools that enable IT professionals to create data models.  A good understanding of relational database theory is needed to use them.  

The tools used to analyze and present data residing in a data model (3-D Maps, OLAP Pivot Tables and Excel) are designed as easy-to-learn tools that most users can quickly master.

Excel Power Pivot - The vision behind Business Intelligence
The vision behind Business Intelligence

OLAP skills are extremely rare amongst IT staff (and highly valued)

It has been estimated that only 10% of Excel users are able to use regular Excel pivot tables.  It likely that far less than 1% of Excel users will develop the skills needed to create correctly designed data models.  Because OLAP skills are so rare they are highly valued by employers.

Excel Power Pivot has a similar "look and feel" to Excel

In the screen shot below you can see that the Power Pivot user interface has some similarities to Excel.  Tables that have been imported look very much like Excel tables and a familiar tab-strip enables you to select a table as if it were located on a worksheet.  But, of course, Power Pivot is an entirely different application that has an entirely different purpose.  Power Pivot tables have little in common with Excel tables.   You can find an explanation of the differences between Power Pivot tables and Excel tables here.

Excel Power Pivot window
The Power Pivot application

An OLAP database (or data model) is very different to a regular database

Most corporate data is held in a relational database (like SQL Server, Oracle and Access).  Relational databases consist of a series of tables linked by relationships.  You can find an explanation of how table relationships are created here.

Relational databases are really good at Finding, Adding, Deleting and Editing rows of data stored in tables.  This type of database is called an OLTP database (OnLine Transaction Processing).  You can find an explanation of how OLTP databases are designed here.

A simple OLTP database showing relationships
A simple OLTP database showing relationships

For reporting and analyzing data you need a different type of database called an OLAP database (OnLine Analytical Processing).  OLAP databases are wonderful for quickly creating summary reports (even when there are millions of rows of source data).  Power Pivot calls this type of database a Data Model.  

Excel data model design Star Schema Example
An OLAP database (Data Model)

The OLAP database (data model) shown above is a lot simpler than the OLTP database from which it was created.  It is really easy to analyze data once it has been converted into a properly-constructed data model.  You can find an explanation of how OLAP databases are designed here.

Once a Data Model has been created, even unsophisticated users can use an OLAP Pivot Table (a new type of pivot table, hosted within Excel, that uses a data model as its source data) to quickly and intuitively analyze data.  Microsoft sometimes call this new way of working: Modern Data Analysis.  You can find an explanation of how Modern Data Analysis differs from Classic Data Analysis here.

Excel Power Pivot OLAP Pivot Tables

What is DAX?

DAX is an acronym for Data Analysis Expressions.

DAX is a collection of over 200 functions that can be used when creating a data model.  Data models often need to have aggregate fields that are calculated from the information in the source data.  This type of field (called a calculated measure) can be easily added to the data model using one or more DAX functions.

While calculated measures are really no more complex than regular Excel functions there are many DAX-related concepts that need to be mastered to use them effectively (such as row and filter context and implicit and explicit measures).  

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

This is the only up-to-date Excel book currently published and will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

Excel Power Pivot Lessons in Session 11

Session Eleven: Create Get & Transform queries
Lesson 11‑1: Understand Get & Transform and ETL
Lesson 11‑2: Create a simple extract and load web query
Lesson 11‑3: Understand queries and connections
Lesson 11‑4: Move, remove, rename, filter and sort columns
Lesson 11‑5: Split delimited data
Lesson 11‑6: Understand data types
Lesson 11‑7: Create and use a linked data type
Lesson 11‑8: Add a linked data type field using a data card
Lesson 11‑9: Resolve geocoding errors in the geography linked data type
Lesson 11‑10: Understand Get & Transform data types
Lesson 11‑11: Specify data types
Lesson 11‑12: Understand steps and PQFL
Lesson 11‑13: Remove empty, error and top and bottom rows
Lesson 11‑14: Understand and work with null values
Lesson 11‑15: Transform date and time columns
Lesson 11‑16: Transform number columns
Lesson 11‑17: Add a custom calculated column
Lesson 11‑18: Create an aggregated
data query
Lesson 11‑19: Unpivot aggregated data
Lesson 11‑20: Work with multiple
queries
Lesson 11‑21: Create an append query
Lesson 11‑22: Understand Column From Examples
Lesson 11‑23: Use Column From Examples with selected source columns
Lesson 11‑24: Use Column From Examples to extract characters from strings
Lesson 11‑25: Appreciate the potential of Column From Examples
Lesson 11‑26: Understand primary and foreign keys
Lesson 11‑27: Link primary and foreign keys using XLOOKUP
Lesson 11‑28: Efficiently import data using a view
Lesson 11‑29: Understand linked table and pivot table refresh
Lesson 11‑30: Load a query directly into the PivotTable cache
Lesson 11‑31: Understand normal and de-normalized data
Lesson 11‑32: Create a simple two-table merged query
Lesson 11‑33: Create a five-table merged query
Lesson 11‑34: Create a merged query using fuzzy logic
Lesson 11‑35: Improve fuzzy logic results using a custom transformation table
Session 11: Exercise
Session 11: Exercise Answers

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

 

Excel Power Pivot Lessons in Session 13

Session Thirteen: An introduction to
DAX
Lesson 13‑1: Understand the Power Pivot window
Lesson 13‑2: Add a DAX calculated column
Lesson 13‑3: Create a DAX calculated measure
Lesson 13‑4: Understand Implicit and Explicit measures
Lesson 13‑5: Understand row and filter context
Lesson 13‑6: Create a calculated measure using the measure dialog
Lesson 13‑7: Use a DAX DISTINCTCOUNT function
Lesson 13‑8: Create a Key Performance Indicator
Lesson 13‑9: Use a DAX CALCULATE function
Lesson 13‑10: Use a DAX measure to calculate year-on-year growth
Lesson 13‑11: Use a DAX ALL function
Session 13: Exercise
Session 13: Exercise Answers
Appendix A: Power Pivot Rules

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 OLAP Pivot Tables

Excel OLAP pivot tables

This article explains the difference between a regular Excel pivot table and the new OLAP pivot table used by Power Pivot.

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.

Understand Get & Transform and ETL

Excel Get & Transform overview

Get & Transform (or Power Query) is an advanced ETL tool. ETL is an acronym for Extract, Transform and Load. This article simply explains this amazing tool.

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.

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.

fourteen − 7 =

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