Home >
BI for Excel >
HandsOn 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
"Excelfriendly 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 INDEXlike 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
INDEXlike 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.
