Hidden Gem: Cube Functions
I recently posted on LinkedIn about cube functions and how I find them very helpful, link to post, Cube Functions. In that post 74% of you responded saying you were not familiar with cube formulas and would like to learn more so here goes.
Today we will learn more about what cube functions are and some of the best resources available for learning more about cube functions. Surprising enough cube functions have been around in Microsoft Excel since at least 2007 and were original designed to work with OLAP data coming from SQL Server Analysis Services.
Today people use the cube functions to access the Power Pivot data model. The below image shows how cube functions can be used in building reports/dashboards. In the picture below we have pulled from the data model the budget, forecast, and actual totals by various channels and time using the cube value and cube member function.
The purpose of the cube functions are to pull data from your PowerPivot data model without having the constraints of a pivot table. I find these functions helps make dashboard design easier because it allows you to design the report any way you want directly in the Excel grid.
The cube functions available for use in Excel include:
CUBEPKIMEMEBER() - Returns the requested property for a Key Performance Indicator(KPI) of a cube
CUBEMEMBER() - Returns a member of the cube
CUBEMEMBERPROPERTY() - Returns the Requested property (attribute) for a cube member
CUBERANKEDMEMBER() - Returns the n-th ranked member of a set
CUBESET() - Defines a set of members to create a sub cube
CUBSETCOUNT() - Returns the number of items in a set
CUBEVALUE() - Returns the aggregated value from a data cube.
Above List comes from an article titled: Cube Functions in Microsoft Excel 2010 | Microsoft Press Store which explains how cube functions work.
The rest of the article will focus on some great resources for learning how to use Cube Functions in Excel.
Best Cube Function Videos
The first video I want to share is by Chris Dutton who is the founder of Maven Analytics. The video is a little over 15 minutes long and does a great job of showing some practical examples of how you would use cube functions in building a report/dashboard linked to your PowerPivot data model. Chris also summed it up well when he said CUBE FUNCTIONS will be ignored by 99% of users but absolutely cherished by the other 1%. I guess that means I am finally part of the 1% :)
The second video explains a little about OLAP cubes and what they are and how cube functions are pulling data from an OLAP cube. The video explains to load data to the PowerPivot Date model and how four cube functions work, cubemember, cubevalue, cubeset, and cubekpimember.
The third and final video is very brief at around 3 minutes long but it walks through the benefit of using cube functions over pivot tables.
Best Cube Function Blogs
The first blog comes from the website Excel Campus which is run by Jon Acampora a self-professed Excel nerd. What I really like about this blog is how it explains the different ways you can enter a cube function and uses an analogy of the difference between ice cubes and crushed ice to explain the different ways of writing the formulas.
Tips & Tricks for Writing CUBEVALUE Formulas - Excel Campus
The second blog comes from Rick de Groot about BI GORILLA. He specializes in Excel, Power Query, and Power BI and has a log of great content on his blog.
An Intro to Cube Functions for Power Pivot in Excel - BI Gorilla
He does a good job of explaining why cube functions are beneficial and collaborate with DAX not replace it. The focus when it comes to data modeling is learning to write DAX not cube functions. As Rick states “You could say that Cube formulas are the portal between the Data model and the Excel Worksheet.”
Conclusion
In addition to the above videos and blogs that mentioned a book that I recommend that is great for learning DAX titled “Learn to Write Dax: A Practical Guide to Learning Power Pivot for Excel and Power BI from Matt Allington dedicates a chapter to explaining how the cube functions work and how they are a valuable tool to have as you learn data modeling with Power Pivot and Power BI.
I hope you enjoy the above blogs and vidoe’s as you take the time to learn how cube functions can help enhance and improve your Excel dashboards and reports. As always please leave a comment below on what you thought about today’s subject.