Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel   
 BI for Excel
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     
 

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:

DBR(Cube, Member1, Member2...)

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.


 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.