Cubevalue function for Excel 2002/2003?

Excel 2007 offers a lot of new functions for retrieving data from a SQL Server Analysis cube, like CUBEVALUE. While similar functions are also provided by the Excel 2002/2003 Add-In, you could also add a vba module in order to implement such functions in Excel 2002/2003.

First, simply create a new module and put this function inside the module:

Function MDGet(Server As String, InitialCatalog As String, Cube As String, ParamArray DimensionMembers() As Variant)
    On Error GoTo errorhandler:
    Dim cset As New ADOMD.Cellset
    Dim conn As New ADODB.connection
    conn.Open "Data Source=" & Server & ";Provider=MSOLAP;Initial Catalog=" & CatalogName & "" & InitialCatalog & ""
    mdxstring = "Select from [" & Cube & "] where ("
    For i = 0 To UBound(DimensionMembers)
        mdxstring = mdxstring & DimensionMembers(i) & ","
    Next i
    mdxstring = Left(mdxstring, Len(mdxstring) – 1) & ")"
    cset.Open mdxstring, conn
    MDGet = cset(0).Value
   Exit Function
    MDGet = Err.Description
End Function

In order to get this to work you need references to "Microsoft ActiveX Data Objects (Multi Dimensional)" and "Microsoft ActiveX Data Objects 2.8 Library" as shown in the screenshot below:

The new function takes three or more parameters:

Parameter Meaning Example
Server Hostname of the SSAS server localhost
InitialCatalog Name of the SSAS database on this server Adventure Works DW
Cube Name of the cube in this database Adventure Works
ParamArray Zero, one or more axes members [Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2004]

The function can be used like any other Excel built-in function. You can also use the dialog for entering the formula (by clicking on the   icon on the left side of the entry field).

The above parameters simply result in this formula:

=MDGet("localhost","Adventure Works DW","Adventure Works","[Measures].[Internet Sales Amount]","[Date].[Calendar Year].&[2004]")

Of course you will want take the server, database and cube name from Excel cells in order to change parameters more easily. And you can take the axes members from other fields as well.

The final result may look like this:

Note: Server, Database and Cube are aliases for B1, B2 and B3 respectively.

For larger Excel sheets using this function for many cells, you should consider some kind of connection handling in your code because opening and closing the connection for each cell has some performance drawbacks.

Another way to enhance performance is to return a matrix from the function, so one mdx query results in many field values on your Excel sheet. I’ll post the code for this in my next blog entry.

Of course, the Excel 2007 functions are much more powerful. For example, in Excel 2007 you can define sets and address them in other cells. And you don’t need the string concatenation we did above in order to turn the name of an element (e.g. 2004) into its unique dimension name (e.g. [Date].[Calendar Year].&[2004]) as this can be done more easily with the new CubeMember-function in Excel 2007. But I still think the above function may be useful for some cases where you don’t have Excel 2007 installed.

This entry was posted in Allgemein. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s