Home >  BI for Excel >

Hands-On Excel:

Excel Spreadsheet Functions
That Read Values From Cubes

BI systems for Excel can give your spreadsheet formulas easy
access to massive amounts of data. Here, we demonstrate
functions offered by three different vendors.

by Charley Kyd
December, 2006

BI systems for Excel use spreadsheet functions to return values from cubes in a multidimensional database. This is a new concept for users of ordinary Excel. So let's take a closer look at how it's done.

We know of three vendors that provide spreadsheet functions that read data from their multidimensional databases. Generally, we call their database products "Excel-friendly OLAPs". ("Multidimensional" and "OLAP" databases are pretty much the same thing.)

All three vendors use a function that somewhat resembles Excel's INDEX function:

=INDEX(reference, row_num, column_num)

Here, reference is a range in a spreadsheet, and the other two arguments are numbers.

The equivalent OLAP functions have these differences:

• INDEX looks at a range of data in a spreadsheet, but the equivalent OLAP functions look at cubes of data on your hard drive or on a server.
   
• Spreadsheets can have only two dimensions, but cubes can have any number of dimensions.
   
• The INDEX function uses row and column numbers, but the equivalent OLAP functions use labels.
   
• The rows and columns that the INDEX references are usually simple lists, but cube dimensions usually have a hierarchy. In a cube, therefore, you can return the grand total for a parent member (like "Quarter 1") as easily as you could return the value for one of its children (like "February").

Let's take a look at how the three vendors use an INDEX-like function to return data from cubes.

Excel 2007 and Microsoft Analysis Services

In Excel 2007, Microsoft provides seven spreadsheet functions that return information from cubes. As this is written, CUBE functions work only with Analysis Services cubes. However, by the Spring of 2007 other OLAP vendors probably will add connections to allow Excel to work with their products.

Here's the general form of Excel's function:

CUBEVALUE(Connection, Member_Expression1, Member_Expression2…)

And here's an example:

=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")

Unlike the other vendors, Microsoft uses "member expressions" to define the members for each dimension. The advantage to this approach is that it gives Excel users a lot of power.

However, there are at least three disadvantages to this approach.

First, the syntax for multidimensional expressions (MDX) can be quite complex.

Second, it's much easier to make a mistake than it is with the approach that the other vendors take, and the mistakes are more difficult to discover.

Third, the spreadsheet formulas are longer than those of the other two vendors. And that requires extra planning in your spreadsheet.

PARIS Technologies PowerOLAP

PowerOLAP offers more than 60 spreadsheet functions that can return information from its cubes. The OLAPRead function is equivalent to the CUBEVALUE function in Excel 2007:

OLAPRead(Database, Cube, Member1, Member2...)

And here's an example:

=OLAPRead(MyDatabase, "Sales", "Profit", "2004", "Beverages",
"All Regions")

Here, MyDatabase is a range name that refers to a cell that has a path to the database, or it could have other connection information. And "All Regions" illustrates that you must specify a member for each dimension in a PowerOLAP cube. In contrast, Analysis Services uses the default member for unspecified dimensions.

PowerOLAP doesn't need to use the entire path to specify each member because, unlike Analysis Services, PowerOLAP requires all members to be unique within a dimension.

Cognos TM1

TM1 offers more than 20 spreadsheet functions. TM1's version of the INDEX-like function is:

And here's an example:

=DBR("Sales", "Profit", "2004", "Beverages", "All Regions")

Like PowerOLAP, TM1 requires that you specify a member for each dimension in a cube.

When you use any of these functions you normally use cell addresses or range names to point to cells that contain the arguments. That way, you can change one value in a cell, and then recalculate, to point one or more formulas to a new region, month, department, product line, or whatever.

These products offer other spreadsheet functions that can be quite useful. For example, in Create an Accordion Report In Excel Using OLAP Data we show how to create a report that expands and contracts like an accordion.