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 it 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.
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:
- Create an OLAP database (Excel calls this a Data Model).
- 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:
- Get & Transform (previously named: Power Query). A tool for cleaning (transforming) source data and loading the cleaned data into Power Pivot tables. You can find an explanation of what this tool does here.
- Power Pivot. A tool used to take the data tables that Get & Transform has pre-prepared and define relationships between them to create data models (also called OLAP databases). You can find an explanation of what this tool does here.
Tools to analyze and visualize the data residing in the data model:
- 3-D Maps (previously named: Power Maps). An easy-to-learn application that provides a way to visualize geographical data contained in a data model. You can learn more about how 3-D Maps uses a data model here.
- OLAP Pivot Table. An easy-to-learn analytical tool that is able to present summarized data contained in a data model. You can find an in-depth explanation of the differences between regular pivot tables and OLAP pivot tables here.
- Excel itself. Excel is able to directly access summarized data residing in a data model. Excel’s extensive analysis, charting and other visualization features can then be used to further refine a report or visualization. You can find an explanation of how this can be done here.
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 master.
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.
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.
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.
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.
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.
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).