13.09.2008

Cubevalue function for Excel 2002/2003?

Technical Value

SQL Server 2005 | SQL Server 2008

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:

  1. Function MDGet(Server As String, InitialCatalog As String, Cube As String, ParamArray DimensionMembers() As Variant)      
  2.     On Error GoTo errorhandler:      
  3.     Dim cset As New ADOMD.Cellset      
  4.     Dim conn As New ADODB.connection      
  5.     conn.Open "Data Source=" & Server & ";Provider=MSOLAP;Initial Catalog=" & CatalogName & "" & InitialCatalog & ""      
  6.     mdxstring = "Select from [" & Cube & "] where ("      
  7.     For i = 0 To UBound(DimensionMembers)      
  8.         mdxstring = mdxstring & DimensionMembers(i) & ","      
  9.     Next i      
  10.     mdxstring = Left(mdxstring, Len(mdxstring) - 1) & ")"      
  11.     cset.Open mdxstring, conn      
  12.     MDGet = cset(0).Value      
  13.     cset.Close      
  14.     conn.Close      
  15.    Exit Function      
  16. errorhandler:      
  17.     MDGet = Err.Description      
  18. 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:

image

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 image  icon on the left side of the entry field).

image

The above parameters simply result in this formula:

  1. =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:

image

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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden