COMate: How can i use MDX (ADODB)

srod · Post by **srod** » Mon Jun 29, 2009 1:12 pm

Well, COMatePLUS should definitely display the error message then; the debugger is getting that message from COMate itself! I suspect you are misusing COMate a little bit.

Place a Debug COMate_GetLastErrorDescription() after each method / property call then.

If the problem is the objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") line then I suspect that the server wants an indirect pointer to the iDispatch object. If this is the case then we will need to modify the code - but first you need to confirm that this is the problem line (with some Debug COMate_GetLastErrorDescription() lines)!

My OLE viewer tool tells me that we definitely need to be using SetPropertyRef() here.

Code: Select all

IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"

Define.COMateObject objMDX, objADOMD

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog 
  If objADOMD 
    vCube.s = "zxCube" 
    vCompany.s = "[Company].[Company 200].[Level 1].&[200]" 
    vCurrency.s = "[Currency].[Currencies].[EUR]" 
    vCustomer.s = "[Customer].[Customers].[All]" 
    vDatasource.s = "[DataSource].[DataSources].[DMC]" 
    vGCN.s = "NET" 
    vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]" 
    vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]" 
    vPeriod.s = "P4" 
    vYear.s = "2009" 
    vForeCastType.s = "1" 
    
    mdx.s = "with " 
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) " 
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from " 
    mdx.s = mdx.s + "( select {" + vCompany + "} on columns from  " 
    mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from " 
    mdx.s = mdx.s + "( select " + vDatasource + " on columns from " 
    mdx.s = mdx.s + "( select " + vCurrency + " on columns from " 
    mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from " 
    mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from " 
    mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value " 
  
    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
      objADOMD\SetProperty("Source = '"+mdx+"'") 
      Debug COMate_GetLastErrorDescription()
      objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") 
      Debug COMate_GetLastErrorDescription()
      objADOMD\Invoke("Open") 
      Debug COMate_GetLastErrorDescription()
      
      Debug objADOMD\GetIntegerProperty("(0,0)") 
    Else
      Debug COMate_GetLastErrorDescription()
    EndIf
    objADOMD\Release() 
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf

srod · Post by **srod** » Mon Jun 29, 2009 1:27 pm

Aha.... change the following line

Code: Select all

objADOMD\Invoke("Open")

to

Code: Select all

objADOMD\Invoke("Open(#Optional, #Optional)")

Try that!

I'll get this damn thing working if I have to chew my way through my coffee table first!

CNESM · Post by **CNESM** » Mon Jun 29, 2009 1:46 pm

I slim the code a little bit with the COMate_GetLastErrorDescription:

Code: Select all

Define.COMateObject objMDX, objADOMD 

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog 
  If objADOMD 
  
    mdx.s = "with "
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 2009])"
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from"
    mdx.s = mdx.s + "(select [Company].[Company 200].[Level 1].&[200] on columns from"
    mdx.s = mdx.s + "(select [PRODLINE].[ProdLines].[Level 3].&[3] on columns from"
    mdx.s = mdx.s + "(select [Customer].[Customers].[All] on columns from"
    mdx.s = mdx.s + "(select [METALTYPE].[METALTYPES].[ALL] on columns from"
    mdx.s = mdx.s + "(select [DataSource].[DataSources].[DMC] on columns from"
    mdx.s = mdx.s + "(select [Currency].[Currencies].[EUR] on columns from"
    mdx.s = mdx.s + "(select [GCN].[GCN].[NET] on columns from"
    mdx.s = mdx.s + "(select [TCurrency].[Switch].[TCurrency] on columns from"
    mdx.s = mdx.s + "[FIBI WCH])))))))) cell properties value"

    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK 
      objADOMD\SetProperty("Source = '"+mdx+"'") 
      Debug COMate_GetLastErrorDescription() 
      objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") 
      Debug COMate_GetLastErrorDescription() 
      objADOMD\Invoke("Open(#Optional, #Optional)") 
      Debug COMate_GetLastErrorDescription() 
      Debug objADOMD\GetIntegerProperty("(0,0)") 
      Debug COMate_GetLastErrorDescription() 
    Else 
      Debug COMate_GetLastErrorDescription() 
    EndIf 
    objADOMD\Release()
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf

I get back following debugs:

Okay.
Okay.
Okay.
0
One or more arguments are invalid. Possibly a numerical overflow or too many nested objects, -if so, try splitting your method call into two or more subcalls.

Well, COMatePLUS should definitely display the error message then; the debugger is getting that message from COMate itself! I suspect you are misusing COMate a little bit.

If i disable first line error message on the MDX BI Server, COMate give back only "Okey." after the last GetLastErrorDescription function. if i activate this options, the debugger gave me the error "One or more arguments are invalid. Possibly a numerical overflow or too many nested objects, -if so, try splitting your method call into two or more subcalls." etc.

srod · Post by **srod** » Mon Jun 29, 2009 1:53 pm

The problem is :

Code: Select all

objADOMD\GetIntegerProperty("(0,0)")

There is no property name here!

I don't know enough about ADOMD, but there must be additional objects we need to retrieve in order to retrieve the data etc. I'll have a look around - very difficult though when I cannot test.

**EDIT : yes you need to trawl through the result-set using various objects and (possibly) safearrays. The VBA code you posted does a lot of it in the background.

http://support.microsoft.com/kb/828279

srod · Post by **srod** » Mon Jun 29, 2009 2:13 pm

Try the following code to display the various 'axis'. Does anything of sense get displayed? If so, then it is indeed the case that you have to take this kind of approach to get the data you are after and we are on the right lines!

Code: Select all

IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"

Define.COMateObject objMDX, objADOMD

Define.COMateEnumObject enAxes
Define.COmateObject obAxis

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog 
  If objADOMD 
    vCube.s = "zxCube" 
    vCompany.s = "[Company].[Company 200].[Level 1].&[200]" 
    vCurrency.s = "[Currency].[Currencies].[EUR]" 
    vCustomer.s = "[Customer].[Customers].[All]" 
    vDatasource.s = "[DataSource].[DataSources].[DMC]" 
    vGCN.s = "NET" 
    vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]" 
    vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]" 
    vPeriod.s = "P4" 
    vYear.s = "2009" 
    vForeCastType.s = "1" 
    
    mdx.s = "with " 
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) " 
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from " 
    mdx.s = mdx.s + "( select {" + vCompany + "} on columns from  " 
    mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from " 
    mdx.s = mdx.s + "( select " + vDatasource + " on columns from " 
    mdx.s = mdx.s + "( select " + vCurrency + " on columns from " 
    mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from " 
    mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from " 
    mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value " 
  
    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
      objADOMD\SetProperty("Source = '"+mdx+"'") 
      objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") 
      If objADOMD\Invoke("Open(#Optional, #Optional)")  = #S_OK
        enAxes = objADOMD\CreateEnumeration("Axes")
        If enAxes 
          obAxis = enAxes\GetNextObject()
          While obAxis
            Debug obAxis\GetStringProperty("Name")
            Debug "===="
            obAxis\Release()
            obAxis = enAxes\GetNextObject()
          Wend
          enAxes \Release()
        EndIf
    
      EndIf    
    Else
      Debug COMate_GetLastErrorDescription()
    EndIf
    objADOMD\Release() 
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf

If this works then I think we can get the 'cell data'.

CNESM · Post by **CNESM** » Mon Jun 29, 2009 2:26 pm

Hi srod

The code debuggs following data:

Code: Select all

Axis(0)
====
Axis(1)
====

srod · Post by **srod** » Mon Jun 29, 2009 2:31 pm

Excellent - we are on the right path then.

The problem all along has been this "(0, 0)" business!

I think with this, VBA generates an array and retrieves all the data accordingly. We of course have to do it by hand!

Let me see if I can find some code for retrieving the cell data; complicated because I cannot run the code. I may simply point you towards some code to convert yourself etc.

CNESM · Post by **CNESM** » Mon Jun 29, 2009 2:37 pm

Maybe this help:

Code: Select all

If oCompCn.State = ADODB.ObjectStateEnum.adStateClosed Then
        oCompCn.Open "Provider=" & sPROVIDER & ";Data Source=" & sSERVER & ";Initial Catalog=" & sINITIALCATALOG & ";"
    End If

    Set cst = New ADOMD.Cellset
     
    cst.Open mdx, oCompCn
    
    rows = cst.Axes(1).Positions.Count - 1
    cols = cst.Axes(0).Positions.Count - 1
    
    ReDim ar(1 To rows + 3, 1 To cols + 2)
    
    Set sh = Sheets("Data")
    sh.Cells.Clear
    
    
    For i = 0 To rows
        For k = 0 To cols
            ar(i + 3, k + 2) = cst(k, i)
        Next
    Next
       
    For i = 0 To rows
        ar(i + 3, 1) = cst.Axes(1).Positions(i).Members(0).Caption
    Next
       
    For k = 0 To cols
        ar(1, k + 2) = cst.Axes(0).Positions(k).Members(0).Caption
    Next
       
    cl = sh.Cells(rows + 3, cols + 2).Address
        
    sh.Cells.Range("A1:" & cl).Value = ar

This is a vba code, that i found online.

srod · Post by **srod** » Mon Jun 29, 2009 2:44 pm

Its the "cst(k, i)" which again refers to an array..... ah, I see how to do it. We need to package up the index (row, column) into a safe array and then pass that to the "get_Item" property. This then returns a cell object from which we can get the required value - yes it all makes sense now.

I'll need to roll my Safearray sleeves up.... God I hate safearrays! grrrr........

CNESM · Post by **CNESM** » Mon Jun 29, 2009 3:00 pm

I think you are not wrong with you answer!

We had to go on this way:

Code: Select all

IncludePath "..\" 
XIncludeFile "COMatePLUS.pbi" 

Define.COMateObject objMDX, objADOMD 

Define.COMateEnumObject enAxes 
Define.COmateObject obAxis 

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog 
  If objADOMD 
    vCube.s = "zxCube" 
    vCompany.s = "[Company].[Company 200].[Level 1].&[200]" 
    vCurrency.s = "[Currency].[Currencies].[EUR]" 
    vCustomer.s = "[Customer].[Customers].[All]" 
    vDatasource.s = "[DataSource].[DataSources].[DMC]" 
    vGCN.s = "NET" 
    vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]" 
    vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]" 
    vPeriod.s = "P4" 
    vYear.s = "2009" 
    vForeCastType.s = "1" 
    
    mdx.s = "with " 
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) " 
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from " 
    mdx.s = mdx.s + "( select {" + vCompany + "} on columns from  " 
    mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from " 
    mdx.s = mdx.s + "( select " + vDatasource + " on columns from " 
    mdx.s = mdx.s + "( select " + vCurrency + " on columns from " 
    mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from " 
    mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from " 
    mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value " 
  
    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK 
      objADOMD\SetProperty("Source = '"+mdx+"'") 
      objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") 
      If objADOMD\Invoke("Open(#Optional, #Optional)")  = #S_OK 
        enAxes = objADOMD\CreateEnumeration("Axes") 

         obAxis = enAxes\GetNextObject() 
         obAxis1 = enAxes\GetNextObject() 
         Debug obAxis\GetStringProperty("Name") 
         Debug obAxis1\GetStringProperty("Name") 
         
         rows = obAxis\GetIntegerProperty("Positions\Count")
         cols = obAxis1\GetIntegerProperty("Positions\Count")
         Debug rows
         Debug cols
         
         rows = obAxis\GetIntegerProperty("Positions(1)\Members(0)\Caption")
         cols = obAxis1\GetIntegerProperty("Positions(1)\Members(0)\Caption")
    
      EndIf    
    Else 
      Debug COMate_GetLastErrorDescription() 
    EndIf 
    objADOMD\Release() 
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf

The debugger give back:

Code: Select all

Axis(0)
Axis(1)
1
1

God I hate safearrays! grrrr........

Met too, srod, me too

srod · Post by **srod** » Mon Jun 29, 2009 3:05 pm

Nearly there...... few more minutes...

srod · Post by **srod** » Mon Jun 29, 2009 3:14 pm

Try the following which is hard-coded for a 2-dimensional recordset. I try and retrieve the data in cell (1, 1).

Code: Select all

IncludePath "..\"
XIncludeFile "COMatePLUS.pbi"

;-Safe array structures - taken from "VariantHelper_Include.pb"
;===============================================================================
  Structure SAFEARRAYBOUND 
    cElements.l 
    lLbound.l 
  EndStructure 

  Structure SAFEARRAY 
    cDims.w 
    fFeatures.w 
    cbElements.l 
    cLocks.l 
    *pvData.pData 
    rgsabound.SAFEARRAYBOUND[0] 
  EndStructure 
;===============================================================================
;-Set up a 1-d safearray of signed 16-bit integers with two elements (col, row).
;===============================================================================
  ;Begin with an array of SAFEARRAYBOUND structures; one for each dimension.
    Dim safeArrayBound.SAFEARRAYBOUND(1)
      With safeArrayBound(0)
        \lLbound = 0
        \cElements = 2
      EndWith
  ;Now create the array and check for success.
    *safeArray.SAFEARRAY = SafeArrayCreate_(#VT_I2, 1, @safeArrayBound())
    If *safeArray = 0
      MessageRequester("COMatePLUS!", "Couldn't create the Safearray!")
      End
    EndIf
;===============================================================================
;Populate the array to point to cell (1, 1) in the 'Cellset' recordset object.
;===============================================================================
    index = 0
    column = 1
    SafeArrayPutElement_(*safeArray, @index, @column)
    index = 1
    row = 1
    SafeArrayPutElement_(*safeArray, @index, @row)
  ;Bundle the Safearray up into a variant suitable for passing to a COM method.
    Define var.VARIANT
    With var
      \vt = #VT_ARRAY|#VT_I2
      \parray = *safeArray
    EndWith
;===============================================================================


Define.COMateObject objMDX, objADOMD, obAxis, obCell
Define.COMateEnumObject enAxes

objMDX=COMate_CreateObject("ADODB.Connection") 
If objMDX 
  objADOMD=COMate_CreateObject("ADOMD.Cellset");catalog 
  If objADOMD 
    vCube.s = "zxCube" 
    vCompany.s = "[Company].[Company 200].[Level 1].&[200]" 
    vCurrency.s = "[Currency].[Currencies].[EUR]" 
    vCustomer.s = "[Customer].[Customers].[All]" 
    vDatasource.s = "[DataSource].[DataSources].[DMC]" 
    vGCN.s = "NET" 
    vMEALTYPE.s = "[METALTYPE].[METALTYPES].[ALL]" 
    vProdLine.s = "[PRODLINE].[ProdLines].[Level 3].&[3]" 
    vPeriod.s = "P4" 
    vYear.s = "2009" 
    vForeCastType.s = "1" 
    
    mdx.s = "with " 
    mdx.s = mdx.s + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[P1 " + vYear + "]) " 
    mdx.s = mdx.s + "select [Act1] on columns,  [Account].[Accounts].[304] on rows from " 
    mdx.s = mdx.s + "( select {" + vCompany + "} on columns from  " 
    mdx.s = mdx.s + "( select {" + vProdLine + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vCustomer + "} on columns from " 
    mdx.s = mdx.s + "( select {" + vMEALTYPE + "} on columns from " 
    mdx.s = mdx.s + "( select " + vDatasource + " on columns from " 
    mdx.s = mdx.s + "( select " + vCurrency + " on columns from " 
    mdx.s = mdx.s + "( select [GCN].[GCN].[" + vGCN + "] on columns from " 
    mdx.s = mdx.s + "( select [TCurrency].[Switch].&[TCurrency] on columns from " 
    mdx.s = mdx.s + "[" + vCube + "] )))))))) cell properties value " 
  
    If objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
      objADOMD\SetProperty("Source = '"+mdx+"'") 
      objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject") 
      If objADOMD\Invoke("Open(#Optional, #Optional)")  = #S_OK
        numCells = 1
        enAxes = objADOMD\CreateEnumeration("Axes")
        If enAxes 
          obAxis = enAxes\GetNextObject()
          While obAxis
            numCells * obAxis\GetIntegerProperty("Positions.Count")
            Debug obAxis\GetStringProperty("Name")
            Debug "===="
            obAxis\Release()
            obAxis = enAxes\GetNextObject()
          Wend
          enAxes \Release()
          Debug "There were " + Str(numCells) + " cell(s) retrieved."
          Debug "===="
          ;Now get some cell data! We use cell (1, 1).
            obCell = objADOMD\GetObjectProperty("Item(" + Str(var) + " as VARIANT)")
            If obCell
              Debug "Cell (1, 1) as string : " + obCell\GetStringProperty("FormattedValue")
            Else
              Debug "Problem getting cell object!"
              Debug COMate_GetLastErrorDescription()
            EndIf
        EndIf
      EndIf    
    Else
      Debug COMate_GetLastErrorDescription()
    EndIf
    objADOMD\Release() 
  Else 
    Debug "Could not instantiate the ADOMD.Cellset object" 
  EndIf 
  objMDX\Release() 
Else 
  Debug "ADODB.Connection" 
EndIf 

;Now free the Safearray. Either use SafeArrayDestroy_() or VariantClear_() on any variant containing the array.
  VariantClear_(var)

CNESM · Post by **CNESM** » Mon Jun 29, 2009 3:20 pm

You wroght this code in this few minutes?! Wooowo

Debugger:

Code: Select all

Axis(0)
====
Axis(1)
====
There were 0 cell(s) retrieved.
====
Problem getting cell object!
The arguments are of the wrong type, lie outside of the range of validity or are incompatibly

srod · Post by **srod** » Mon Jun 29, 2009 3:22 pm

Well, that basically says that there is no data being returned from your SQL statement. You'd better check that carefully.

CNESM · Post by **CNESM** » Mon Jun 29, 2009 3:23 pm

You had an error in you code:

numCells * obAxis\GetIntegerProperty("Positions.Count")

to

numCells * obAxis\GetIntegerProperty("Positions\Count")

Debugger:

Axis(0)
====
Axis(1)
====
There were 1 cell(s) retrieved.
====
Problem getting cell object!
The arguments are of the wrong type, lie outside of the range of validity or are incompatibly