CUBE Functions 2: A Few Examples

Last time, I explained the seven new CUBE functions that are available in Excel 12 to fetch data from Analysis Services cubes.  With the theory that a picture is worth a thousand words, I would like to walk through a few examples that illustrate how these functions can be used to produce reports based on OLAP data (data that comes from SQL Server Analysis Services).

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).

(Click to enlarge)

• 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 + `).

(Click to enlarge)

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.

(Click to enlarge)

• 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.

(Click to enlarge)

Hopefully these examples help illustrate how these formulas can be used.  Next time, I’ll talk about how Formula AutoComplete simplifies the task of authoring CUBE functions.