Search This Blog

Tuesday, October 7, 2008

Returning MDX query results in an Excel matrix formula

In my last post I had an example for a function to return a single value from an MDX query which has certain performance drawbacks when being used in many Excel fields.

Since Excel supports matrix formulas to do a computation on multiple values, this can also be used for MDX query results. Another benefit for this approach is, that dimension members that are added later on, are also displayed in the query result.

Before I post the function code, here's the result. For my test, I used this MDX query on the Adventure Works cube:

SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity],
[Measures].[Internet Gross Profit]
} ON 0,
[Product].[Category].[Category] ON 1
FROM [Adventure Works]

The result, when being called from SQL Server Management Studio, looks like this:

image4

The following screenshot shows how this result looks like in Excel using the matrix MDX formula:

image9

Now, what are the #N/A entries about? The matrix formula measures the size of the resulting cell area and returns a two dimensional array of exactly that size. When entering the formula I chose a larger area on the worksheet in order to be prepared for additional entries on both axes. Of course you could think of returning a larger matrix from the function in order to properly fill out the unused cell. The best approach would be to supply width and height as parameters.

So, this is how the formula is to be entered:

image_thumb14

Be sure to press CTRL+ALT+ENTER when entering the formula in order to turn it into a matrix formula.

The MDXGetMDX function takes five parameters:

Parameter Description Example
Server Hostname of the SSAS server localhost
InitalCatalog Name of the SSAS database Adventure Works DW
Cube Name of the SSAS cube Adventure Works
mdx MDX code of the query select ... on 0,
... on 1
from cubename
WithCaption When true, captions are printed true

Finally, here's the code I used for the MDGetMDX(...) function:

Function MDGetMDX(Server As String, InitialCatalog As String, Cube As String, mdx As String, WithCaption As Boolean) As Variant
On Error GoTo errorhandler:
Dim cset As New ADOMD.Cellset
Dim conn As New ADODB.connection
Dim x As Variant
Dim i As Integer, j As Integer
Dim i0 As Integer, j0 As Integer ' begin of the data area
Dim i1 As Integer, j1 As Integer ' size of the data area
conn.Open "Data Source=" & Server & ";Provider=MSOLAP;Initial Catalog=" & CatalogName & "" & InitialCatalog & ""
cset.Open mdx, conn
If cset.Axes.Count > 2 Then
MDGetMDX = "More than 2 axes are not allowed!"
Exit Function
End If
If cset.Axes.Count > 0 Then i1 = cset.Axes(0).Positions.Count Else j1 = 0
If cset.Axes.Count > 1 Then j1 = cset.Axes(1).Positions.Count Else j1 = 0
If WithCaption Then
' column headings are displayed as rows
If cset.Axes.Count > 1 Then i0 = cset.Axes(1).DimensionCount Else i0 = 0
' row headings are displayed as columns
If cset.Axes.Count > 0 Then j0 = cset.Axes(0).DimensionCount Else j0 = 0
Else
i0 = 0
j0 = 0
End If
If cset.Axes.Count = 2 Then
ReDim x(j0 + j1 - 1, i0 + i1 - 1)
ElseIf cset.Axes.Count = 1 Then
ReDim x(j0, i0 + i1 - 1)
Else
ReDim x(1, 1)
End If
For i = 0 To UBound(x, 2)
For j = 0 To UBound(x, 1)
x(j, i) = ""
Next
Next
' Show caption:
If WithCaption Then
For i = 0 To i1 - 1
For j = 0 To cset.Axes(0).Positions(i).Members.Count - 1
x(j, i + i0) = cset.Axes(0).Positions(i).Members(j).Caption
Next
Next
For j = 0 To j1 - 1
For i = 0 To cset.Axes(1).Positions(j).Members.Count - 1
x(j + j0, i) = cset.Axes(1).Positions(j).Members(i).Caption
Next
Next
End If
If cset.Axes.Count = 2 Then
For i = 0 To i1 - 1
For j = 0 To j1 - 1
x(j + j0, i + i0) = nz(cset(i, j).Value, 0)
Next
Next
ElseIf cset.Axes.Count = 1 Then
For i = 0 To i1 - 1
x(j0, i + i0) = nz(cset(i).Value, "")
Next
Else
x(0, 0) = cset(0).Value
End If
MDGetMDX = x
cset.Close
conn.Close
Exit Function
errorhandler:
MDGetMDX = Err.Description
End Function

Function nz(x As Variant, other As Variant) As Variant
If Not IsNull(x) Then
nz = x
Else
nz = other
End If
End Function

7 comments:

  1. This looks excellent, friend. However, I tried your code but nothing appears as a result in the worksheet.

    How can I get the MDX query result in Excel worksheet using this function?

    ReplyDelete
  2. Are you sure you entered the formula as a matrix formula? You have to hold CTRL+SHIFT and then press ENTER while the cursor is in the input box for the formula. The formula then appears in curly brackets

    ReplyDelete
  3. Thank you for the response. I am able to see the curly brackets.
    When the WithCaption is True, I get blank report. When the WithCaption is set to False, I get only first cell value(which is 700760) in the cell where formula is.
    I'm using SQL Server 2008 SP1 and Excel 2007.

    ReplyDelete
  4. Sounds like you didn't mark enough cells. Before entering the matrix formula in the described way you have to mark the full area of cells. Try with 10 by 10 cells to see the effect. Everything outside of your selection will be cropped.

    ReplyDelete
  5. Many Thanks! Correct that was the mistake. Happy that I learned something new in Excel with your help. Much appreciated!

    ReplyDelete
  6. Bah! thanks a ton, but I was going batty, you state "CTRL+ALT+ENTER" but it's ctrl+SHIFT+enter!

    ReplyDelete
  7. It’s actually a great and helpful piece of information. I am glad that you shared this helpful information with us. Please keep us up to date like this. Thanks for sharing
    CBEST, CBEST test preparation

    ReplyDelete