Guest Post by Andrew Todd


Cube Formulas: Ultimate flexibility for your reports but lots of drama to update…

A while back, I was tasked with finding a way to automate upkeep of a scorecard built primarily with thousands of cube formulas (Yes, thousands!). This particular scorecard was still under development, and maintaining and making changes to it had become a full-time job! All of the individual cube formulas needed to be updated several times a week, and this was expected to go on for months as executives made up their minds on the final product.

Fortunately, I found two tricks that allowed me to:

a.) Change slicer references in all cube formulas with a single click

b.) Modify entire tables across multiple sheets in seconds

These two tricks freed up time that I used to drive further improvements and start performing real analysis, rather than just maintaining a report.

Cube Formulas: Flexible and Powerful

Cube formulas allow you to add PowerPivot/SSAS Tabular calculations to any cell in virtually any orientation that you can think of. They’re a big part of what makes Excel simply the world’s best data tool, period. (Imagine if you could use them in Power BI Designer!)

The flexibility of cube formulas is powerful, but it does carry a price. Cube formulas are worksheet functions, so they bring their ‘worksheet function drama’ with them (lack of portability, unique formulas, individual updates, etc).

Here are two tricks to keep your formulas easy to maintain and update!

Cube Formula Trick 1: Consolidate Your Slicer References!

The first thing I noticed when opening my scorecard was the length of the cube formulas! I clicked on a cell containing a CUBEVALUE() formula and was greeted by multiple rows of slicer names in the function bar. The scorecard had a total of 10 slicers, and each needed to be referenced in the formulas. What’s more, not all of the slicer names in the scorecard were to be included in the calculations and yet more slicers were set to be added in the future!

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Sales]”,slicer_country1,slicer_country2,slicer_dim_calendar,slicer_salesrep,
slicer_contest,slicer_supplier,slicer_supplier1,slicer_supplier5,slicer_salesterritory
,slicer_salesterritor2)

Yikes! Imagine updating thousands of these! I’m going cross-eyed already!

The D-Man Innovates!

I got my buddy Donovan Kelsch from Kaman on the phone and we discussed the headaches of doing find and replace, VBA or manual changes to the thousands of cube formulas in a scorecard. Suddenly, a thought came to us: ‘What if you could obtain the slicer reference from another cell, just like you could for cube members or measures!?’

We both agreed that this was a cool idea, and while I got sidetracked on other challenges, Donovan began experimenting. Time passed until one day, I was greeted by an email from Donovan: “Cubesets in Named ranges! Brilliant!”

Replacing Multiple Slicer References with a Single Name

Before:

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Sales]”,slicer_country1,slicer_country2,slicer_calendarmonth,slicer_salesrep,
slicer_contest,slicer_supplier,slicer_supplier1,slicer_supplier5,slicer_salesterritory
,slicer_salesterritor2)

After:

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Sales]”,slicers) 

Replacing multiple slicer references with a single named range is easy to do. First, obtain the slicer names that you want to include in your formulas, and list them in a range within your scorecard.

getslicer

Now, you’ll need to create a CUBESET() formula for each slicer name, like this one:

=CUBESET(“ThisWorkbookDataModel”,slicer_CalendarMonth,”slicer_CalendarMonth”)

You’ll end up with your list looking like this (‘Show Formulas’ has been turned on to illustrate):

image

Next, highlight the list, click on the ‘Formulas’ tab and select ‘Define Name’. For this example, we’ll call our named range ‘slicers’.

image

Now, simply add the named range name within your CUBEVALUE() formula just like a slicer name.

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Total Sales]”,slicers)

The true power and utility of this technique is revealed when your boss tells you to add or remove a slicer from the scorecard. All you do is modify your named range contents, and the entire scorecard updates!

D-Man, that was brilliant! Hats off to you, sir!

Tip 2: Use Named Sets for Cube Members

Not only did my sprawling scorecard have tons of slicer references, it also had many sets of the same cube members grouped together on rows, like this.

image

While developing my scorecard, I would need to modify each of these sets of region cubemembers on multiple tabs and check to make sure that they all matched. Oh the horror… the horror.

Solving this problem required a closer look at the CUBEMEMBER() and CUBERANKEDMEMBER() functions.

CUBEMEMBER() 101

CUBEMEMBER() formulas allow you to reference a specific ‘slice’ of a PowerPivot lookup or data table. For example, in a Calendar table, the CUBEMEMBER for the month of January would be CUBEMEMBER(“ThisWorkbookDataModel”,”[Calendar].[Month].[All].[January]”).

Because CUBEMEMBERS() are a slice of your model, they can be referenced within a CUBEVALUE() formula to return a measure result just for that slice. A great best practice is to use a CUBEMEMBER() formula as a row value, and then reference the cell containing your CUBEMEMBER() function within your CUBEVALUE() formulas. Using this technique makes for great scorecard tables.

cellrefs 

Getting CUBEMEMBER()’s in Order with Named Sets and CUBERANKEDMEMBER()

A great way to manage a set of cube members is to build a named set and reference that set within a CUBERANKEDMEMBER() function. Named sets allow you to specify which cube members are to be included in a list, and also what order they should be in. This brings two benefits:

a.) Portability: The named set can be modified, and any range that references the named set will update not only the contents, but also the order of those cube members.

b.) Consistency/Accuracy: Only a single reference to the named set is needed. You won’t have to worry about inconsistencies in different CUBEMEMBER() formulas across multiple tabs.

Let’s Build a Named Set!

To create a named set, just insert a pivot table from Power Pivot and add the column with the cube members that you want to group together. For this example, I needed certain regions from the region table, so I pulled in the regions column.

Next, click on the ‘Analyze’ tab under Pivot Table Tools and select ‘Create Set Based on Row Items’. Add or remove the cube members that you want in your scorecard report, and arrange them in the order desired. Don’t forget to give your named set a snappy name! (I landed on ‘MySet’… wahoo… live dangerously!)

image

Next, to pull the named set into your scorecard, use CUBERANKEDMEMBER(), like this:

=CUBERANKEDMEMBER(“ThisWorkbookDataModel“,“[MySet]”,ROW(A1))

The last part with ROW(A1) just specifies that you want the set shown in the same order that you defined in your named set (ie rank 1, rank 2 etc).

Just like making updates to the slicer named range, you can now modify the contents or order of the members in your named set, and your scorecard cubevalue tables update automatically wherever the set is used! Want to move Region 1 down two rows in every table in the scorecard? No need to modify every single group of CUBEMEMBER() formulas, just click the down arrow in the named set manager and your entire scorecard updates!

image

Two Simple Tricks – One Awesome Data Tool

By using named ranges to consolidate slicer references and named sets to define a group of cube members, Excel developers can benefit from the flexibility of cube formulas, while still maintaining order and easy upkeep in their creations.

Just when you think you know it all, Excel proves it still has a whole lot of awesome up its sleeve! The fact Excel is so ubiquitous, yet it still offers gems like this to be discovered speaks to its depth as the world’s best data tool.

  Subscribe to PowerPivotPro!
X

Subscribe

Andrew Todd

I am a BI professional from Seattle, WA. I enjoy working with all of the Microsoft BI stack, especially Power BI. 

This Post Has 28 Comments

  1. These are some amazing and useful techniques. Thanks! And, lest anyone forget, everything you have done here (in Excel) will NEVER be done in Power BI (unless the two are somehow merged in the future) 🙂

    1. Thank you! Don’t get me wrong, I love Power BI Designer and I think it is going to crush a lot of BI tools that don’t deserve their prestige and glory. For a lot of companies, it is going to change the game and deliver untold value. But I’m definitely not expecting anything on the same level of Excel to come out for a very long time.

      I love your recent post on measuring distance in Power BI Designer btw. I need to brush up on my trig!

  2. This is AWESOME! Not only can we save a ton of time by using PowerPivot with its re-usable measures and incredible speed, but now we can save even more time in the Consumer-side maintenance. Thanks for a great article.

  3. Hey Andrew,

    Thank you for the time and effort to bring this technique to light. I have already added it to a “PowerPivotPro\Power Insights\Cube Formulas” folder on my local machine.

    Its the most time-saving couple of tips on cube formulas I have seen to-date.

    Looking forward to your next post!

  4. Wonderful post!!

    I am curious about the performance of the scorecard workbook containing thousands of CUBE formulas. There’s a dataset that I’m working with some kind of complicated, date-dimensioned measures. A pivot table sends one big MDX query to the data model. Awaiting results caused a time-out error. So, I switched to thousands of CUBE formulas that ping the dataset individually. No time-out error, but the calculation of which takes an hour and 15 minutes to complete.

    What is the performance like in the scorecard workbook you’re working with?

    1. Excellent question! It could be that you are experiencing faster times now because the slicers are no longer cross-filtering because you removed the pivots. Slicers do not cross filter for cube formulas alone… if you want that functionality, you need to add a pivot table in the report with the measures your are working with and connect the slicers to that table. So it might not have timed out because you weren’t sending the extra query for the slicer tiles?

      The scorecard I was working with (which was quite a while back on a less than optimal architecture) was just fine. I’ve rarely had performance issues with cube formulas. I’ll do some research though and get a more technical answer for this.

  5. I find that I am more and more exploring the endless possibilities of using Cube formula’s in a table setting for my dashboards. Articles like this are not only incredibly helpful, but they continuously demonstrate the simple fact that even amongst the guru’s out there – there is still endless things to be discovered and fleshed out. Keep it coming and thank you!

  6. Wow, this is totally awesome ! I have a number of solutions where I use cube formulas. This looks like a much better way of managing them. Thanks for sharing.

  7. Scott, the Wolf never tells and just remember you’re not the only rock star on the team, Great job Donovan and Andrew. Appreciate all the efforts and awesome result. PowerPivot plus a team of rock stars continue to exceed expectations beyond anything imaginable. Think about it a workbook built on over 8 million transactions with over 7,500 cube formulas and more than 800 calculated measures host on SharePoint and by the end of the month will be integrated with Datazen. Just another problem to be checked of the list. Thanks for making this possible Rob. The Wolf.

  8. Thanks for the elegant tips! This spurred me to use a variation of the Slicer-range trick: collecting various CUBESETs I’m using, for different dimensions, into a single named range, and referring to that range in all the CUBEVALUE functions. This significantly simplified my formulas, and, importantly, will make them much easier to maintain and enhance in the future.

  9. I wanted to post a follow-up comment for anyone reading this article. Another way that you can reduce clutter in your formulas is the use a CUBESET() within a CUBEVALUE() function, then reference a list of cells with the appropriate values. An example might be a set of project ID’s, as in the following: CUBEVALUE(“ConnectionName”,”[Measures].[Total]”,CUBESET(“ConnectionName”,A1:A35))

    In cell A1:A35 are text strings for the members that you want in your calculation, for example, cell A1 = [Projects].[ProjectId].[12345], cell A2 = [Projects].[ProjectId].[12333]

    This has an added benefit of getting around the 256 char limit in a single CUBESET function.

  10. Hi! this works fantastic! I’m trying to set up a page of slicers to control multiple tabs within a workbook. Is there a way to include one of my slicers in the “slicer_ref” but also reference it as a member_Expression on a certain tab. Essentially i want to show a view on one page that shows “Apples, Oranges, Bananas” as rows, but then have them as a slicer on a different page. does that make sense?

Leave a Comment or Question