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:
-
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
-
cset.Close
-
conn.Close
-
Exit Function
-
errorhandler:
-
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.
Kommentare (0)