Using Excel CUBE Functions with PowerPivot

Arriving Here from a Search Engine or via Excel Help?

This article below by Dick Moffat, as well as the one by Dany Hoter, is an excellent, detailed example of how to use cube functions with *any* OLAP data source, and NOT just PowerPivot.  Cube functions work the same with PowerPivot as they do with other OLAP sources like Analysis Services.

I highly recommend reading both for examples and ideas.

But if you want to use cube functions with just plain tables of regular data, you can do that with Excel 2010!  Just download PowerPivot (free addin from MS), copy/paste or link your tables of Excel data into PowerPivot sheet tabs, and you are off and running.  The New Visitor page has information on how to get started, including download links.

And now, on to Dick’s excellent article…

Using Excel Cube Functions with PowerPivot
By Dick Moffat
Personal Logic Associates Inc.

Today I am going to give you a quick and dirty example of what I think is one of the key features of PowerPivot that will give it a much broader initial and on-going impact for experienced power spreadsheet developers.

This is where existing spreadsheets can get the value-add of having PowerPivot data available to them in a way that is not only understandable for the traditional spreadsheet junkie (as opposed to the typical BI one) but that also that will add major value while integrating into existing models.

Excel CUBE Functions

A few weeks ago my friend Dany Hoter wrote a piece here about the use of the CUBE functions in Excel with PowerPivot data.  Dany’s article was a great intro to the capability of this unsung capability of Excel available since Excel XP.  I am going to go deeper into this issue here.

The Cube functions require access to an Analysis Services On-Line Analytical Process cube (OLAP) which has to be provided to the user from SQL Server’s Analysis Services application.

While there are companies around the world that are capable of taking advantage of this capability, this is by no means a large percentage of overall Excel users and from my experience it is generally an unknown and untried feature.

In Excel 2007 (and now 2010) the CUBE functions became native to Excel (as opposed to available through an Add-In) and were integrated with the new “Connection” object within the program.  Once again though, if one wanted to use an OLAP Cube in a Connection it required availability of an Analysis Services Cube of data.

But in Excel 2010, thanks to PowerPivot, users can now create their own “Cubes” inside PowerPivot and they automatically present themselves as an available Connection inside the Excel parent file.  This is a big thing and I hope to show you another reason why.

A Cube Automatically?

I’m sure I don’t have to explain here how one creates a PowerPivot data source consisting of multiple Relational data sources.  But one fact that may not be obvious is that the data set that is created by these PowerPivot objects, and by their relationships, is in fact a “Virtual” Cube in itself.  In the purest sense it is a ROLAP Cube – created at run-time from Relational data.

This default “Virtual” Cube is named “PowerPivot Data” and is exposed when you click the Connections Button on the Data Tab:

This is inherently an OLAP Cube conceptually and so is an acceptable source for Excel’s native CUBE Functions.

So what does this “Cube” Do For me?

OLAP “Cubes” were designed for two major reasons:

  1. To allow analysis of data in a hierarchical fashion.  In the business world that usually means Financial or Sales or other data organized by the hierarchy of business units (Region/Country/Zone/State/County/City) or by dates (Year/Quarter/Month/Week/Day) or by Product lines (Product Category/Product/SKU), where a number or numbers (i.e. Account balances or sales quantities or values) are stored at the lowest  level of detail (i.e. by Day or by SKU) and the total value or quantity can then be “Rolled Up” along any Hierarchy or Dimension and across dimensions.  So for example you want to see total sales for March 2010 of a particular Product and its SKUS in a particular Country….  Then you want to be able to “Drill up” or Drill Down” on any value in any dimension down to the lowest level or up to the highest easily and automatically.  This is where a Pivot Table attached to one of these Cubes is a natural presentation and analysis mechanism.
  2. To take disparate data sources and aggregate them automatically using the hierarchies in 1 above.  In the original implementations of OLAP Cubes the hardware available had limited RAM and slow processors and so many OLAP Cubes had to be created over-night and written to disk in the classic De-Normalized format that allowed for relatively quick queries to be made against data that otherwise would simply not be possible in a Normalized format.  But this is the 21st Century with incredibly cheap and sizable RAM memory on every PC and with processors so fast that it’s hard to believe.  This is the work that PowerPivot is designed for – so it can reconstitute your OLAP “Virtual” Cube at run-time inside your Excel file itself (and with compression to boot).

So if you bring in your sales data (like in the example Bike data available with PowerPivot samples) and set it up in a PowerPivot “Connection” you can refer to that info from your spreadsheet layer using not just Pivot Tables, but also using the Cube functions.  But the Cube functions give the traditional spreadsheet maker (and his/her boss) the flexibility to present this data in the classic free-form spreadsheet style as Income statements with analysis or to group them on the sheet in any way they want.

The Example

The data being used in this example is available in the “AW_CompanySales.accdb” database supplied as an example for use with PowerPivot. Keep in mind that there are many “Memo” field-types in this example database and it would be worth your while to change most of those fields to a Text format and add a few Indexes to improve the performance of the database when importing into PowerPivot.

This is an example of what the key tables look like when imported into PowerPivot :

When you create Relationships between these tables within PowerPivot, the result is a Virtual Cube with the ability to “Roll-Up” the core values (“Facts”) along any Dimension of the data, which actually are fields in the data sources of the table being used for Hierarchical relationships.  So Product Categories in the “ProductCategory” table have children in the “ProductSubCategory” table, which have children in the “Product” table.  This is the fundamental design of a “Snow-Flake” OLAP design (read about it!) and it should be fairly intuitive to anyone who knows the data sources and how they are related conceptually.

In my example, however, I have pulled together this same data into a single large De-Normalized PowerPivot table by creating an Access Query.   The result is exactly as PowerPivot does virtually within its “PowerPivot Data” Connection and hopefully will help me explain this functionality better.

This is how that PowerPivot window looks (in an unfortunately stiched together image I’m afraid):

Do you notice how there are multiple instances of Country and State across records (?)  This is in effect a result set from an Access query or SQL View or SQL Statement and is truly de-normalized.  There will be certain scenarios where this might work to your advantage over bringing in individual tables and joining them inside PowerPivot, but that’s an issue for another day. There is little or no impact on file size for the two ways of importing the source data however.  It is meant to help us see our data easier in this example.

Keep in mind that there are a million daily records in this data set and is a recipient of the exceptional compression algorithms of PowerPivot.  It is also worth noting that the single table de-normalized version is 1/10th the size of the file with multiple tables (3 megabytes compared to 30 megabytes).

The “Measures”

Once you have the data in place within PowerPivot you have to take the next step and create the Measures you will be working with.  Measures are the core of OLAP Cubes and are also the core of PowerPivot.  Measures are the aggregations that you will be analyzing based on the data in columns you wish to SUM or COUNT or AVERAGE (or whatever of the standard Excel Mathematical and Statistical functions you want to use).

The most basic example of a Measure is ours called “[Sum of SalesAmount]’, where it simply sums the [SalesAmount] column at every conceivable level of the Virtual OLAP Cube inside Excel,  created by PowerPivot.  In this case [SalesAmount] is simply a basic data column provided directly from the data source, but there is no reason why you can’t use DAX to create new Calculated Fields of calculated data and then create “Measures” based on those fields.

To create the aggregations you want in your spreadsheet you need to have created the “Measure” inside a Pivot Table in the Excel environment either within the “drag and drop interface or using the menus.  I created our Measure using the Pivot Table interface like this:

I started to create a Pivot Table from inside my PowerPivot environment (or in the Excel environment from the PowerPivot Tab), and then I dropped the [SalesAmount] field from my Field List into the Values section below.  By default the Pivot Table assigned this as a “SUM” function and it created the Measure [Sum of SalesAmount] automatically.  It is now available to the Workbook, not only to any Pivot Tables you might create, but also to any CUBE functions that you might want to use.

This does NOT mean you have to have a Pivot Table in your Workbook, BUT you do have to have started the process in order to create the Measure or Measures you want to refer to in your CUBE functions.   You can create the Measures from within the PowerPivot Pivot Table menu as well,  but using the Excel Pivot Table GUI this way strikes me as more intuitive and equally capable.  We can then rely on this Measure to aggregate the total “Sales Amount” at every level across any Dimension in our Virtual OLAP Cube.

The Spreadsheet:

So here’s the spreadsheet I want to feed from PowerPivot:

The CUBEVALUE() Function

In order to drive right to the heart of this functionality I am going to use just one of Excel’s Cube functions, CUBEVALUE in this model (but will show one more function later as well in an enhanced version).

So we are looking for the Sum of the Sales Amount ([SumofSalesAmount]) of Bikes ([Category]) for Fiscal Years 2006 ([FiscalYear]) for Australia ([Country]).  So this is the pseudo-code for this formula:

Sum the Sales Amount where Category = “Bikes” and Fiscal Year = 2006 and Country = “Australia”

Notice that the values are in the PowerPivot tables at the lowest level by Product, by City, by Date of the “Fact” Sales Amount.  Each value is shown as a member of a Week and Fiscal and a Calendar Month and a Fiscal and a Calendar Year.

So when we ask for values from the Fiscal Year, the Category and the Country fields we are inherently asking for aggregated totals ate the intersection of those three fields.  That is what you do when you look for values in an OLAP Cube.

This is the CUBEVALUE() function to get this value for “Bikes” in Australia for Fiscal 2006 :

=CUBEVALUE(“PowerPivot Data”,”[Sum of SalesAmount]“,”[Country].[Australia]“,”[Category].[Bikes]“,”[FiscalYear].[2006]“)

Which returns a total of $91,490,280.05.

Therefore to get the equivalent value for “Accessories” in Australia for 2005 the formula would be:

=CUBEVALUE(“PowerPivot Data”,”[Sum of SalesAmount]“,”[Country].[Australia]“,”[Category].[Accessories]“,”[FiscalYear].[2005]“)

And the result is $902,316.21.

If you have ever used a SUMIFS Function in Excel (or SUMIF or even IF functions) then this kind of formula is pretty straightforward IMHO.

The “Inherent” Connection

Notice that this function is a DIRECT reference to the “PowerPivot Data” Connection rather than referencing another cell that has a reference to the Connection?  If you were connecting to an external data source you would likely want to make a single cell the one that links to the data source and then you would “borrow” that link for all subsequent references to the Cube.  Otherwise you would have a HUGE performance hit.

But if the Connection is a “Virtual” Connection in PowerPivot inside the spreadsheet itself, then there is no such penalty.  So you can refer to the “PowerPivot Data” inherent connection in every formula. That is much easier to understand and to audit as well.

Adding the Power of Excel to the Mix

As a loooong-time spreadsheet user I have developed a fondness for the “Naming” capability of Excel.  By applying a “Name” to a cell or cells I can then refer to them everywhere in the Workbook without regards to the actual Sheer and Cell reference of the range.  This makes things more auditable and just generally more readable.

In addition, I believe in the use of cell references for Variables in formulae rather than hard-codes words and values.  To an experienced Spreadsheet developer this is simply “Best Practices”, but I still see many instances of “Hard-Coding” in Workbooks that make me cringe.  If the value will (or may) change it must be a cell reference or a named reference.  Of course ALL references could be named either as individual cells or arrays but I tend to use only single cell Variable names as it is likely easier for others to comprehend than using an multi-cell reference as an array in a formula (but there’s no reason nt to do so if you feel so inclined).

So by changing the View to “Formulas” in Excel these are the contents of the key cells and the first column of our little model:

 

Using Cell References and Names

The reference to the “PowerPivot Data” is located in cell B11 on the spreadsheet and is named in a way that would make it easy to reuse and to understand (“strConnection”).   This is the first expression in the CUBEVALUE() function.

The reference to the Field we want is in cell B12 and refers to the Measure we created [Sum of SalesAmount].  This is the second expression in the CUBEVALUE() function.

Referencing the Dimensions

Now that we have defined the Connection to the PowerPivot data and the definition of the Measure that we will be summing, we have to tell the CUBEVALUE() function the values we want to select for across the various Dimensions we want to analyze.

PowerPivot is capable of intuitively determining the Field (or Dimension) that the value you are looking for is from based on the contents and the data types of the fields in the OLAP cube.  This is way cool … but I am not quite ready to use this capability in my spreadsheets.  Instead I simply use a [Fieldname].[Value] syntax to tell the function what I am looking for.

So the criteria in cell B4 are going to be:

“[Country].[Australia]“,”[Category].[Bikes]“,”[FiscalYear].[2006]“

But I am using the contents of cells to drive my selections … so the correct syntax is:

“[Country].["&$B$5&"]“,”[Category].["&$B7&"]“,”["&$C$5&"].["&C$6&"]“

The result is exactly the same, but is driven by the contents of cells rather than being “hard-coded”.

There is no doubt that there are advanced CUBE functions that will draw the values in each Dimension automatically into the cells based on the contents of the data and also we can drive all of this using Slicers (as I will demonstrate later) but in many, many business scenarios the business is organized in a stable and consistent way. Month over month managers want to view certain values according to an organization that may or may not match their organization or groupings of the data coming in from outside sources.  So in our little example I am most concerned about Bikes, Accessories, Clothing and Components and I want all others to fall into a catch-all category called “Other”, and this total is calculated in cell B7 by taking the total for Australia’s sales for 2005 and subtracting that from the SUM of the three Categories looked at distinctly above:

=B8-SUM(B4:B6)

The ISERROR function is necessary in case there is no data for the combination of dimension values you chose and returns a zero rather than an error.

So what we have here is a simple spreadsheet model that uses the cache of PowerPivot data by drawing on the CUBEVALUE() function to query the OLAP cube inside the PowerPivot cache and to return the totals in the database at the intersect of all the values in the Dimensions my spreadsheet asked for.

This is serious spreadsheeting …..

Adding Slicers to the Mix

So let’s take our same example and add some of the new Slicer functionality of Excel and PowerPivot.

Rather than setting up a separate spreadsheet for every Country the company does business with I have created a Slicer that is pulling a unique list of all the values from the Country Dimension.  This Slicer is then tied to cell B5 and not only shows the name of the Country but also drives the formulas that use the [Country] Dimension of the PowerPivot OLAP cube.

So cell B5 has this formula in it:

=CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_Country,1)

Which is referencing the value of the Slicer named “Slicer_Country”.

Cell C6 has this formula:

=CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_CalendarYear,1)

It is of course referencing the slicer that is driving the Calendar year.  Cell D6 is just the contents of C6 minus 1 (which could also be derived using a CUBE Function as well – but this works for me).

In order to get the totals for all countries one need only click on the Funnel in the upper right of the Slicer.

Conclusion

There is no doubt that even in this little example of a spreadsheet there are a lot of moving parts, but if you understand how this model works you will be well on your way to integrating PowerPivot into many, many of even your existing Excel spreadsheets.  Or at least this will definitely change how you design future ones.   I have spent years emulating this functionality using VBA and DAO and ADO and customized functions for which I have sometimes been handsomely paid.  But this capability of PowerPivot moves the bar way forward and will allow you and me to finally start seriously integrating Excel into the world of serious BI analysis.

While there is a lot of talk about DAX and the Filtering capabilities of PowerPivot I think that I will be applying PowerPivot to many of my spreadsheets in the way detailed here.   With an understanding of the Relational nature of your data and with a strong knowledge of the capabilities (and flexibility) of Excel I believe that many of you will be able to bring value to your use of PowerPivot in Excel 2010 in short order using the CUBE functions against the inherent OLAP cube that is a PowerPivot data cache.

Dick Moffat

London, Ontario

June 19, 2010

dick@plogic.ca

41 Responses to Using Excel CUBE Functions with PowerPivot

  1. m-b says:

    Thanks for this post, I will definitely use this in future PowerPivot workbooks. This also enables you to convert a PivotTable based on PowerPivot data to formulas (PivotTable Tools, Options, OLAP Tools, Convert to Formulas). That way Excel creates the formulas for you so all you need to do then is adjust them to fit your needs. That is really BIG for me as this means I no longer have to build an intermediate PivotTable and then use GETPIVOTDATA to achieve the same results. So thanks again!

    • Biggus Dickus says:

      I’m glad one person got it !!! It is REALLY BIG for everyone IMHO.

      Thanx and let me now how it goes (dick@plogic.ca).

      Dick

  2. James says:

    Hello,

    Could you please share me this sample ? I want to try this function.

    Thanks.

  3. Rajiv says:

    Thanks for the post, adding slicer to cube function on excel is a great feature, especially for people how are used to with cube excel functions. BTW, are there any sites if anyone could recommend for advance cube fucntion for excel??

    Thanks for such a useful post.

    Rajiv

  4. [...] type of cube would most likely be implemented as a “hub” cube or accessed via Excel’s CUBE  formula. In other words the presentation of the data would be through other cubes or simply via [...]

  5. winston says:

    Hi Dick,

    Trying to follow along the tutorial.
    I’m receiving an N/A# error from this formula, not sure why, it appears to be ok

    =CUBEVALUE(“PowerPivot Data”,”[Sum of SalesAmount]“,”[Country].[Australia]“,”[Category].[Bikes]“,”[FiscalYear].[2006]“)

    Any thoughts what I may be doing wrong?

    Kind regards,
    Winston

  6. Biggus Dickus says:

    Not exactly sure. Could you forward me your Exvel file at dick@plogic.ca so I can have a look?

    Dick

  7. Rajiv says:

    Hi Winston,

    May be you could try the following:

    =CUBEVALUE(“PowerPivot Data”,”[Sum of SalesAmount],[Country].[Australia],[Category].[Bikes],[FiscalYear].[2006]“)

    Regards,
    Rajiv

  8. DickM says:

    Actuallu your probem might be with the quotation marks if you cpied the formula from the Blog site and pasted it into Excel directly. On this blog (and mine) the default font insists on changing generic double-quotes to Open and Close quotes and that will cause you problems when you paste into Excel. If that is the issue you just have to go in and edit the qutation marks once you are in Excel and replace them with Excel’s native font’s single double quote.

    Let me know if this works.

    Dick

  9. winston says:

    Thanks all,

    I manually typed the formula based on the formula on screen and intellisense (sic?) appeared pretty happy with what I had typed.

    Rajiv,
    I tried altering the formula per your instructions. I received the same #N/A error. Additionally, it appears the quote marks are needed around each member_expression.

    At the moment, HFM and Essbase get my vote for working with OLAP cubes

    Dick,

    I emailed the file to you at the address provided

    Kind regards,
    Winston

  10. winston says:

    Thanks Dick,

    All looks good in my send log
    I’m uploading to my public folder on my skydrive
    http://tinyurl.com/422ceok

    The file name is Using Excel CUBE Functions with PowerPivot – ws.zip

    Thanks
    Winston

  11. Lee Hawthorn says:

    I’m using the “Convert to formulas” option on the OLAP tools menu to generate this formula from a Power Pivot Table. This problem is that the formulas only work on the 1st Pivot table. For the 2nd Pivot table they generate N/A.

    Any ideas?

  12. Jesper Bork says:

    Thanks for a great post on using Excel CUBE functions with PowerPivot. I’d like to contribute with a discovery I made today and hope that someone here may help solve the last piece of the puzzle of filtering CUBEVALUE calculations by a random date range – here are the pieces I have so far:

    It seems that CUBEVALUE accepts a call to CUBESET as a member expression and that CUBESET accepts an MDX syntax set, and so I got an expression similar to =CUBEVALUE(“PowerPivot Data”; CUBESET(“PowerPivot Data”; “{[Calendar].[Date].[01-01-2010]:[Calendar].[Date].[15-11-2011]}”); “[Measures].[Sum of Sales]“) to work, effectively filtering my measure by the specified date range.

    Now I just need to replace the hard-coded begin and end dates with cell references to have the calculation driven by the users input, however, I can’t get the syntax from your post working within the CUBESET call, i.e. CUBESET(“PowerPivot Data”;”{[Calendar].[Date].["&$A$2&"]:[Calendar].[Date].["&$C$2&"]}”). Any ideas ?

  13. Rob says:

    Hi Jesper,

    I’ve come across this one and although I didn’t nest the CUBESET function within the CUBEVALUE function (I created that in a separate cell) I think the issue is with the format of the date in your cell. I’ve found the only way to get dates working correctly within my Cube formulas is to convert the date to a string and add a “T00:00:00″ string to each date i.e. 2011-11-24T00:00:00

    So, “CONCATENATE(TEXT(A1,”yyyy-mm-dd”),tString)” does this for me if the date is in A1 and tString = “T00:00:00″

    Hope it works for you,

    Rob.

    • Rob says:

      Sorry Jesper,

      Just looked at your formula more closely. As the dates are in string form, I don’t think you can use the colon – there is no way that Excel can sum the intervening cells.

      You will need to add every date you want the CUBESET formula to use separately i.e. CUBESET(“PowerPivot Data”,”{[DateTable].[RealDate].&["&A2&"],[DateTable].[RealDate].&["&B2&"],[DateTable].[RealDate].&["&C2&"]]“)

      All the best,

      Rob.

      • Rob says:

        Hi Jesper,

        I’ve found that you need to convert dates to a string and append “T00:00:00″ to the date in order for these cube formulas to work.

        All my dates are in the form “yyyy-mm-ddT00:00:00″ in my cells and the CUBE formulas like this and work swimmingly!

        Thanks Dick!

        Rob.

  14. Kevin says:

    Hey,

    Thanks for this article, its been a big help for me as I begin to navigate my way through PowerPivot and OLAP related functions.

    I was wondering if you could perhaps explain, if its possible, a way to display dynamic members related to a slicer.

    For example, above, you include a slicer for Country. Suppose that categories differed by country, and in Germany you had several additional columns that you do not have in Australia.

    Suppose Germany also sold “tricycles” as a product category. In my spreadsheet, I want to display all the categories currently listed if I were to click Germany, plus an additional category for “tricycles”. Thus, the names of the categories are dependent upon the slicer value. Is this possible?

    Thanks. I’ve been using PowerPivot for a new job and your site has really helped!

  15. Charley Kyd says:

    Dick,

    Take a closer look at the Slicer_CalendarYear and Slicer_Country arguments. If you use them anywhere else in Excel, they return null strings. But in CUBE functions, they communicate the correct information.

    What’s their type name? How can other Excel formulas use the information they provide to CUBE functions?

    And where can we find some documentation that explains this and other CUBE function weirdness in some detail?

    Thanks.

    Charley

  16. John Bradley says:

    Hey Rob,

    Love the blog. Follow you religiously and have found many practical solutions to powerpivot problems here.

    Have a particular problem that I am trying to solve regarding cube functions. Using cube functions to put together a dynamic set of data that has way to many contexts to be practical in a pivot table alone. However, we are trying to return notes that are in our cube as a value. Can’t seem to figure out how I would set up a measure to return a text. Thought about using cubemember but can’t get the context to work.

    Any suggestions? Not sure if I am being as clear as I need to receive assistance but any brain power you could use on this would be fantastic!

    Kind Regards,

    John Bradley

    • powerpivotpro says:

      Hi John, thanks for the kind words. Maybe try this technique:

      http://www.mrexcel.com/forum/showthread.php?t=620834

      • John Bradley says:

        Hi again, your method to create the measure worked beautifully and returns text like it is supposed to. However, when I try to apply the measure in my cube formula as follows:

        =CUBEVALUE(“PowerPivot Data”,”[Measures].[Text]“,”[BankTransactions].[RealDate].[All].["&$C85&"]“,”[NameKey].[MerchantAccountName].["&DY$11&"]“,”[BankTransactions].[IsNote].[All].[True]“) it returns an error.

        I understand you are very busy but hope you can help me figure this out. Is the cubevalue not the correct function? I have attempted to use cubemember as well but don’t know how to get the functions to use the correct context to get my other parameters. Any additional insight you might be able to provide would be greatly appreciated. Again I very much enjoy your reading and especially love the more “spicy” posts you often take the time to create.

        Kind Regards,

        • John Bradley says:

          After we refreshed the data set our text appeared so looks like our formula is working. Very kind regards for your earlier help!

  17. Jaco says:

    Thanks for the article, great stuff!

    Question: If created a Cubeset that references powerpivot data, but, when I refresh the powerpivot data (with changes in the data) then the cubeset is not refreshed/updated to reflect these changes? Any pointers to what is happening here?

    • powerpivotpro says:

      Have you also refreshed on the Excel side? Refreshing the PowerPivot window doesn’t trigger Excel to also refresh the cube formulas, at least not in Excel 2010. You then need to go to the Data tab in Excel and refresh – either refresh all connections or the PowerPivot connection specifically.

  18. Jason says:

    In the article you refer to having a “huge performance hit” if you reference an external connection in each cube value formula. Can you give an example of how you would set one cell to use the connection and have all the other cube functions “borrow” from that cell?

  19. Jeff Weir says:

    Freakin’ awesome article. Especially the bit up front that explains what a Rubix err ROLAP Cube is, and why they are so useful.

    Thanks very much for this…I’m plagiarizing you as we speak ;-)

  20. Tommy Petersen says:

    I must admit its an awsome article. I just learned powerpivot a bit over a year ago. In the start i was amazed by powerpivots capabilitys. But i soon found myself tied to the world of DAX and pivot outputs. Ad-hoc analysis and dashboard design became an issue when i had to intergrate summarized external data to transactional data in the ledger account. The cube formulas are an amazing way to organize, design and present data. All the reports i make are based on powerpivot/cubeformulas now.

    I admit it takes a bit of time getting used to, but once you “crack” the code and actually understand the formulas the possibilties are indeed limitless.

    Cheers and many thanks for the article.

  21. Jon says:

    Great article! I appreciate the detailed explanation and agree that this is a huge benefit of using PowerPivot models.

    I have two questions:

    1. I have been using the GETPIVOTDATA function to quickly build the formula for CUBEVALUE formulas that contain a lot of expressions. It is much faster than trying to type the entire formula since the GETPIVOTDATA function is automatically written when you click a cell inside the pivot. Is there a better way to do this?

    2. I noticed that the GETPIVOTDATA function puts an ampersand (&) in front of the last field of the member expression when that field is the filter criteria. For example, the following expression filters for Nov FY14. “[MonthCalendar].[FY-Month].&[FY14-Nov]” The ampersand is automatically included in the GETPIVOTDATA function and it is required (removing it will cause an error). However, it is not required in the CUBEVALUE function. CUBEVALUE accepts the expression with or without the ampersand. Does anyone know why this is?

    I like to keep the ampersand in the CUBEVALUE formulas because it makes it easy to see if an expression contains filter criteria. If the expression does not contain an ampersand then it is returning the whole field. I guess you could also tell if there is filter criteria in the expression because the expression will contain three arguments instead of two.

    Thanks again!

    • powerpivotpro says:

      Behind the scenes I am pretty sure the cube functions are still sending “queries” to the Power Pivot engine with the & included. It’s just that when we built the cube functions at MS, we didn’t want to force users to learn yet another special character that is only required sometimes.

      So the cube functions add the & if it’s not already there.

      In MDX, which is ultimately the language that Excel uses to send queries to Power Pivot, “member unique names” have ampersand prefixes.

      Would you like to do a guest post on your trick with GETPIVOTDATA? I think that’s quite clever :)

      • Jon says:

        Hi Rob,

        Sorry, I did not see your response come through and was actually just re-reading this article and saw your comment. :)

        Yes, I would be happy to do a post on the GETPIVOTDATA trick.

        I have another question about the CUBEVALUE function. Does the order of the member expressions in the function matter in terms of performance/calculation speed? I would assume that you may want to order the member expressions by filter size. For example, if you had the following formula:

        =CUBEVALUE(“PowerPivot Data”,”[Measures].[Total Sales]“,”[fSales].[Date].[All].[1/2/2014]“,”[fSales].[Region].[All].[West]“)

        If the CUBEVALUE was evaluated in the order of the expressions then it would first filter for the Date = 1/2/2014, then filter for the Region = West. Let’s say there were only 10 rows with the date of 1/2/2014, and 20,000 rows with the region of West in the fSales table.

        Will the function calculate faster based on the order of the expressions?

        I kept the example limited to one table, but if the order DOES matter, then the next question is how the related tables would factor into this logic.

        Thanks again!

  22. Anand says:

    Great Job Man,
    But I would like U to add someting regarding CUBE FUNCTIONS vs CALCULATE resultset scenarios.

  23. […] Function which can “hook into” a pivot table which has been converted to static  CUBE functions. The UDF can then extract the “cross-tab” filters for each cell and generate a DAX […]

  24. jeffrey Weir says:

    I am finally starting to play around with OLAP stuff, now that I have a job in an organisation that has dragged itself out of the dark ages and into the light.

    One thing that I can’t get my head around is this quote from the article above:
    Notice that this function is a DIRECT reference to the “PowerPivot Data” Connection rather than referencing another cell that has a reference to the Connection? If you were connecting to an external data source you would likely want to make a single cell the one that links to the data source and then you would “borrow” that link for all subsequent references to the Cube. Otherwise you would have a HUGE performance hit.

    I would have thought that there would be no difference whatsoever between referencing that hard-coded string in every CUBEVALUE function vs pointing every CUBEVALUE to a cell where that string is stored. Am I missing something? It’s only a pointer to a connection, isn’t it?

    Note that this is in relation to an external SSAS OLAP cube

  25. DSD70 says:

    Is there a way to specify a Null value in the member expression? For instance, in the example above, if the Category was null instead of “Other”, how could I get just the values for which there is no value in the Category field? I have verified that if I specify “All” the fields with missing values are included, but I have yet to find a way to specify it specifically. Thanks for any help.

Leave a Comment or Question