07.10.2008

Returning MDX query results in an Excel matrix formula

Technical Value

SQL Server 2005 | SQL Server 2008

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:

  1. SELECT      
  2.   {      
  3.     [Measures].[Internet Sales Amount],      
  4.     [Measures].[Internet Order Quantity],      
  5.     [Measures].[Internet Gross Profit]      
  6.   } ON 0,      
  7.   [Product].[Category].[Category] ON 1      
  8. FROM [Adventure Works]

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

image

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

image

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

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:

  1. Function MDGetMDX(Server As String, InitialCatalog As String, Cube As String, mdx As String, WithCaption As Boolean) As Variant      
  2.     On Error GoTo errorhandler:      
  3.     Dim cset As New ADOMD.Cellset      
  4.     Dim conn As New ADODB.connection      
  5.     Dim x As Variant      
  6.     Dim i As Integer, j As Integer      
  7.     Dim i0 As Integer, j0 As Integer ' begin of the data area      
  8.     Dim i1 As Integer, j1 As Integer ' size of the data area      
  9.     conn.Open "Data Source=" & Server & ";Provider=MSOLAP;Initial Catalog=" & CatalogName & "" & InitialCatalog & ""      
  10.     cset.Open mdx, conn      
  11.     If cset.Axes.Count > 2 Then      
  12.         MDGetMDX = "More than 2 axes are not allowed!"      
  13.         Exit Function      
  14.     End If      
  15.     If cset.Axes.Count > 0 Then i1 = cset.Axes(0).Positions.Count Else j1 = 0      
  16.     If cset.Axes.Count > 1 Then j1 = cset.Axes(1).Positions.Count Else j1 = 0      
  17.     If WithCaption Then      
  18.         ' column headings are displayed as rows      
  19.         If cset.Axes.Count > 1 Then i0 = cset.Axes(1).DimensionCount Else i0 = 0      
  20.         ' row headings are displayed as columns      
  21.         If cset.Axes.Count > 0 Then j0 = cset.Axes(0).DimensionCount Else j0 = 0      
  22.     Else      
  23.         i0 = 0
  24. Function nz(x As Variant, other As Variant) As Variant      
  25.     If Not IsNull(x) Then      
  26.         nz = x      
  27.     Else      
  28.         nz = other      
  29.     End If      
  30. End Function
  31.         j0 = 0      
  32.     End If      
  33.     If cset.Axes.Count = 2 Then      
  34.         ReDim x(j0 + j1 - 1, i0 + i1 - 1)      
  35.     ElseIf cset.Axes.Count = 1 Then      
  36.         ReDim x(j0, i0 + i1 - 1)      
  37.     Else      
  38.         ReDim x(1, 1)      
  39.     End If      
  40.     For i = 0 To UBound(x, 2)      
  41.         For j = 0 To UBound(x, 1)      
  42.             x(j, i) = ""      
  43.         Next      
  44.     Next      
  45.     ' Show caption:      
  46.     If WithCaption Then      
  47.         For i = 0 To i1 - 1      
  48.             For j = 0 To cset.Axes(0).Positions(i).Members.Count - 1      
  49.                 x(j, i + i0) = cset.Axes(0).Positions(i).Members(j).Caption      
  50.             Next      
  51.         Next      
  52.         For j = 0 To j1 - 1      
  53.             For i = 0 To cset.Axes(1).Positions(j).Members.Count - 1      
  54.                x(j + j0, i) = cset.Axes(1).Positions(j).Members(i).Caption      
  55.             Next      
  56.         Next      
  57.     End If      
  58.     If cset.Axes.Count = 2 Then      
  59.         For i = 0 To i1 - 1      
  60.             For j = 0 To j1 - 1      
  61.                 x(j + j0, i + i0) = nz(cset(i, j).Value, 0)      
  62.             Next      
  63.         Next      
  64.     ElseIf cset.Axes.Count = 1 Then      
  65.         For i = 0 To i1 - 1      
  66.             x(j0, i + i0) = nz(cset(i).Value, "")      
  67.         Next      
  68.     Else      
  69.         x(0, 0) = cset(0).Value      
  70.     End If      
  71.     MDGetMDX = x      
  72.     cset.Close      
  73.     conn.Close      
  74.     Exit Function      
  75. errorhandler:      
  76.     MDGetMDX = Err.Description      
  77. End Function

 

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