This lesson is excerpted from the Expert Skills book
This lesson is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002.
This lesson is contained in the session: Power Pivot, Data Modeling, OLAP and Business Intelligence.
An OLAP pivot table is simply a visual and user-friendly way to display, format and present the values stored in a data model.
The CUBEVALUE function enables you to obtain values directly from the data model without a pivot table. This opens up many new possibilities when presenting and formatting data.
1 Download the sample file Stock List-4 by clicking the button below.
If you had worked through the Excel Skills book, you would have created the pivot table in this sample file from a data model in: Lesson 12‑6: Use an OLAP pivot table to analyze data residing in a data model.
2 Attempt to insert a column or row within the pivot table.
Notice that (as with all pivot tables) this isn’t possible.
When you attempt to insert a row or column, a warning dialog is shown:
3 Click inside the pivot table to activate it.
4 Convert the pivot table into a set of CUBEVALUE functions.
Click: PivotTable Analyze->Calculations->OLAP Tools->Convert to Formulas
(This option is grayed out when working with regular pivot tables).
Notice that the OLAP pivot table no longer exists but the data previously displayed remains:
Each value is now directly accessed from the data model using CUBEVALUE functions.
5 Examine the CUBEVALUE function for the Sum of UnitsInStock column in the Beverages category.
- Click once in cell C4.
Look at the function displayed in the formula bar:
The first argument identifies the name of the connection to the data model. For students who have worked through the Expert Skills book, this is the data model that you defined in: Lesson 12 3: Add tables to a data model and Lesson 12 5: Add a relationship to a data model using Power Pivot.
The last two arguments are MDX expressions. The Expert Skills book gives an overview of the MDX query language in: Lesson 12 19: Understand MDX queries and OLAP pivot table limitations.
- Click on cell B4 and C3 to see how the MDX expressions have been created.
- Click once in cell C4.
- Look at the function displayed in the formula bar:
If you have worked through the Expert Skills book your understanding of data models should enable you to broadly understand how the references work.
You have used an OLAP pivot table to automatically create the MDX code for you. This technique allows you to create CUBEVALUE functions without any understanding of the MDX language.
6 Insert a column before column C and a row before row 5.
There is now no barrier to inserting rows and columns, as each cell contains a normal function.
You can also format the values in any way you wish.
7 Save your work as Stock List-5
Additional information (shown in the sidebar of the Expert Skills book)
Why does the CUBEVALUE function have the prefix CUBE?
It is common to teach and visualize OLAP concepts in the context of an imaginary three-dimensional OLAP Cube.
The imaginary cube is a little like a Rubik’s cube but contains a set of aggregated values in each cell.
OLAP concepts are taught in the context of Slicing, Dicing and Rotating (Pivoting) the cube of data.
MDX queries (created by the CUBE functions) retrieve aggregated totals from specified cells (intersections of three values) from a cube of data.
The GETPIVOTDATA function is also supported by OLAP pivot tables
In the Expert Skills book Lesson 9 23: Use the GETPIVOTDATA function, students learn how to use the GETPIVOTDATA function to access values in a regular pivot table from outside the pivot table.
The GETPIVOTDATA function can only be used to reference values that are visible in a pivot table.
This can be a huge problem, as a user may change the fields displayed in a pivot table, causing functions that use the GETPIVOTDATA function to fail.
If there were a function that directly accessed data from the pivot table data cache (that regular pivot tables use instead of the data model) this problem would be overcome.
Unfortunately, there is no way to do this in a regular pivot table.
The CUBEVALUE function has none of these problems as data is accessed directly from the data model without any interaction with a pivot table.
OLAP pivot tables still support the GETPIVOTDATA function but you’ll probably prefer to use CUBEVALUE functions in their place when working with data that originates in a data model.