New Excel's CUBE Function

The development team has implemented 7 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.

Example 1 – Basic Report

Let’s start by taking a look at this report which shows some data from the Adventure Works sample cube (which is included with SQL Server 2005 Analysis Services).

• Cells C1 thru C3 each contain a single member, obtained using one CUBEMEMBER function in each cell.
• Cell C5 contains a tuple, consisting of the combination of cells C1 thru C3. This was built using a single CUBEMEMBER function that refers to the three cells above it.
• Cell B5 contains a set of all customers, sorted in descending order by the tuple in cell C5. Thus this is the set of people ordered by the amount they spent on bikes in 2004.
• Cells B6 thru B15 contain the top 10 members of the set in cell B5, obtained using ten instances of the CUBERANKEDMEMBER function.
• Cells C6 thru C15 contain the aggregated values from the cube sliced by the person to the left of the number and by the tuple in cell C5.

Here’s the same spreadsheet, configured to display the formulas in each cell (using Formula Audting Mode or CTRL + `).

Among other things, this report does a good job of demonstrating how easy it is to combine data out of a database with Excel’s formula language.

Example 2 - Report Filters
Many different OLAP reports call for dynamic filtering. When you use a PivotTable, you do this using Report Filters (the drop downs at the top of the PivotTable). We did the work so that these Report Filters can be used by CUBE functions as well so that you can filter the values in your CUBE functions with one or two clicks. Report Filters behave just as though they had been authored using the CUBESET function. This means that you can use CUBERANKEDMEMBER functions to enumerate the items that have been selected in the filter and that you can pass the report filter as an argument to the CUBEVALUE and CUBESETCOUNT functions.

Here’s a report that is filtered by a PivotTable Report Filter.

• Cells A1 thru B2 contain a PivotTable that contains two Report Filter fields. Instead of using the Pivot Table for rows, columns and the data area, I have placed formulas in cells A4 thru C7.
• Cell A4 contains a CUBESET function that returns the set of Product Categories.
• Cells A5 thru A7 contain CUBERANKEDMEMBER functions that get the first three members of the set in cell A4.
• Cells B4 and C4 each contain a CUBEMEMBER function that returns a single measure from the Adventure Works cube.
• Cells B5 thru C7 each contain a CUBEVALUE function that returns the aggregated value from the cube for the given filters in cells B1 and B2 and for the product category to the left and for the measure at the top of the column.

Here’s the same spreadsheet, configured to display the formulas in each cell.