Microsoft Excel

The Excel 12 Blog Rides Again, or “CUBE Functions Part 1”

OK, I am freshly back from a fascinating couple weeks of visiting a number of customers (thanks to all you that spent time with us over the last few weeks), so it is time to get back to talking about Excel 12 features.

We’ve already talked about using Pivot Tables to bring OLAP data from SQL Server Analysis Services into Excel.  In cases where you want to explore the data and look for interesting facts within the data by “slicing and dicing” it, PivotTables are the perfect tool.  You can change the fields that are being displayed, drill into the data, change the filtering and sorting that has been applied, and in the case of OLAP data from SQL Server Analysis Services, you can use all the other features I have covered to look at the data your way.

There are other scenarios that involve bringing OLAP data from SQL Server Analysis Services into Excel, though, that are not as well suited to using a PivotTable.  While PivotTables are an ideal tool for quickly exploring your data and finding a wide variety of answers in an ad hoc manner, they are not as well suited for building fixed reports with irregular or custom layouts.  Here are a few quick examples:

• "Asymmetric reports,” wherein the rows or columns don’t follow a fixed pattern in terms of the data they contain (more on this below)
• Reports that mix data from multiple data sources
• Reports that require a lot of custom spacing and/or precise grouping and ordering, often in a different way than the data is stored in the data source
  

To address those needs (and others),  we wanted to provide a way to place OLAP data from SQL Server Analysis Services directly into specific spreadsheet cells without being constrained in any way by what might be in the adjacent cells.  This would provide users with the power of a multi-dimensional data store in conjunction with the power of the Excel formula language and calculation engine.  Let’s take a look.

Here is an example of an asymmetric report that was built using the Excel 12 beta build and the Adventure Works sample database that ships with SQL Server 2005 Analysis Services. 

(Click to enlarge)

Note that the fields across the top axis (on columns) do not come from the same dimension.  The sales reasons (‘On Promotion’ and ‘Price’) and the countries (‘Canada’ and ‘United States’) are members from two completely distinct dimensions.  Also note that while all the values are based on the items in Column A, this report contains blank “spacer” columns in columns B and E.  Finally, you can see the formula for cell C2 which fetches the ‘On Promotion’ member from the ‘Sales Reason’ cube dimension.  This formula uses the CUBEMEMBER function (which is one of seven new CUBE functions) to place OLAP data from SQL Server Analysis Services into Excel spreadsheet cells.

What are the new CUBE functions?
We have implemented seven new CUBE functions that can be used in Excel formulas just like any other function in Excel.  These functions permit Excel to fetch data from SQL Server Analysis Services (2000 & 2005), including any member, set, aggregated value, property, or KPI (Key Performance Indicator) from the OLAP cube.  This data can then be placed anywhere in the spreadsheet, intermingled with other local calculations and/or within other formulas.  Here are the seven new CUBE functions:

CUBEMEMBER (connection, member_expression,[caption])
This function will fetch the member or tuple defined by the member_expression.  For example, (from the illustration above,) the formula:  =CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]")  returns the member named “On Promotion” from the “Sales Reason” dimension of the Adventure Works cube.
 
CUBEVALUE (connection, [member_expression_1], [member_expression_2], …)
This function will fetch the aggregated value from the cube filtered by the various member_expression arguments.  For example, the formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]")  returns the value $5,035,271.22  which is the aggregated amount in the Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.

CUBESET (connection, set_expression, [caption], [sort_order], [sort_by])
This function will fetch the set that is defined by the set_expression parameter.  Optional parameters allow you to specify the ordering of the set as well as the caption to be displayed in the Excel cell that contains this formula.  (Note that the set itself won’t have a display value.)  For example, the formula: =CUBESET ("Adventure Works","[Customer].[Customer Geography].[All Customers].children","Countries") returns the set of countries in the Customer Geography hierarchy and shows “Countries” as the cell’s display value.

CUBESETCOUNT (set)
This function returns the number of items in a set.  Typically the argument to this
function will be a CUBESET function or a reference to a CUBESET function.

CUBERANKEDMEMBER (connection, set_expression, rank, [caption])
This function returns the Nth item from a set.  This can be very useful when building a Top N (or Bottom N) report in Excel.

CUBEMEMBERPROPERTY (connection, member_expression, property)
This function returns a property of a member in the OLAP cube.

CUBEKPIMEMBER (connection, kpi_name, kpi_property, [caption])
This function returns a KPI (Key Performance Indicator) from the OLAP cube.

CUBE functions provide MDX to other CUBE functions
There is one way in which the CUBE functions are significantly different from any other Excel functions.  This is in the way that they behave when they are passed in as arguments to other CUBE functions.

Functions that are not CUBE functions return a value which is displayed in the Excel spreadsheet cell.  For example, the result of a SUM function will be a number that is displayed in the cell.

CUBE functions (except for CUBESETCOUNT) return a result which is more complex than this.  CUBE functions return two distinct values.  One is the value that is displayed in the cell.  But there is also a second hidden value which can be thought of as an MDX expression (MDX is the Multi-Dimensional eXpression language used by SQL Server Analysis Services) defining the result.  When one CUBE function uses another CUBE function as its argument, the argument takes on the MDX value rather than the display value.

Here’s an illustration that I hope will help to make this clearer.  Here is the asymmetric report that we looked at earlier.

(Click to enlarge)

Cell C2 actually contains two separate values.  The first is the display value that you see in the cell, namely “On Promotion”.  But when the formula in cell C3 uses cell C2 as an argument, the value that it obtains from cell C2 is actually “[Sales Reason].[Sales Reasons].[Sales Reason].&[2]” which is the MDX unique name for the “On Promotion” member.  You can find this hidden value behind the CUBE function using Excel’s object model by selecting cell C2 and then typing “?Activecell.MDX” into the immediate window in the Visual Basic Editor.

And that’s the overview.  In my next post, I will provide some examples that illustrate how to use these functions in your reports.