Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)

CHAPTER 9. Using cube formulas with the Data Model or OLAP data

In this chapter, you will:

• Be introduced to OLAP

• Connect to an OLAP cube

• Understand the structure of an OLAP cube

• Understand the limitations of OLAP pivot tables

• Create an offline cube

• Break out of the pivot table mold with cube functions

• Add calculations to OLAP pivot tables

There is a great technique with pivot tables where you convert the pivot table to a series of cube formulas. Originally, this technique was only relevant for someone who had SQL Service Analysis Services installed. But today, one click of Add This Data To The Data Model moves your data set to the Power Pivot engine, and Power Pivot stores data in an OLAP engine. Thus, you can use cube formulas simply by moving ...