Page 1 of 3
COMate: How can i use MDX (ADODB)
Posted: Fri Jun 26, 2009 7:44 am
by CNESM
Hi people,
i (want) to use COMate to work with MDX. This Analysis Service based on a simple ADODB connection with a near sql syntacs. I create a runable sourcecode in VBA, now i want to translate the code to PB but i have some problem with the code. I dont know where the problem is. Maybe the objADOMD open call can be the source of trouble (I dont think that it is right to consign the MDX object as normal string, but i dont find another way).
Enclosed the runable VBA sourcecode:
Code: Select all
Public Const sSERVER = "DB.server.com"
Public Const sPROVIDER = "MSOLAP.3"
Public Const sINITIALCATALOG = "FIBI"
Public oCompCn As New ADODB.Connection 'Global Connection
Dim cst As ADOMD.Cellset
Dim mdx As String
Dim i As Long
Dim k As Long
Dim cl As String
Dim rows As Long
Dim cols As Long
vCube = "zxCube"
vCompany = "[Company].[Company 200].[Level 1].&[200]"
vCurrency = "[Currency].[Currencies].[EUR]"
vCustomer = "[Customer].[Customers].[All]"
vDatasource = "[DataSource].[DataSources].[DMC]"
vGCN = "NET"
vMEALTYPE = "[METALTYPE].[METALTYPES].[ALL]"
vProdLine = "[PRODLINE].[ProdLines].[Level 3].&[3]"
vPeriod = "P1"
vYear = "2009"
mdx = "with "
mdx = mdx + "member [Act1] as ([Measures].[Periodic],[Scenario].[Scenarios].[ACT],[Time].[YM].[Month].[" & vPeriod & " " & vYear & "]) "
mdx = mdx + "select [Act1] on columns, [Account].[Accounts].[304] on rows from "
mdx = mdx + "( select {" & vCompany & "} on columns from "
mdx = mdx + "( select {" & vProdLine & "} on columns from "
mdx = mdx + "( select {" & vCustomer & "} on columns from "
mdx = mdx + "( select {" & vMEALTYPE & "} on columns from "
mdx = mdx + "( select " & vDatasource & " on columns from "
mdx = mdx + "( select " & vCurrency & " on columns from "
mdx = mdx + "( select [GCN].[GCN].[" & vGCN & "] on columns from "
mdx = mdx + "( select [TCurrency].[Switch].&[TCurrency] on columns from "
mdx = mdx + "[" & vCube & "] )))))))) cell properties value "
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.Source = mdx
cst.ActiveConnection = oCompCn
cst.Open
'cst.Open mdx, oCompCn 'This works too
MsgBox cst(0, 0)
And now my stupid try to translate this code to PB:
Code: Select all
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 "
objMDX\SetProperty("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") ;invoke?
If objMDX
objADOMD\SetProperty("Source = "+mdx.s)
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX))
objADOMD\SetProperty("Open")
Debug objADOMD\GetIntegerProperty("(0,0)")
Else
Debug "error3"
EndIf
Else
Debug "error2"
EndIf
Else
Debug "error1"
EndIf
objADOMD\Release()
objMDX\Release()
Best regards
Florian
Re: COMate: How can i use MDX (ADODB)
Posted: Fri Jun 26, 2009 8:16 am
by Kiffi
CNESM wrote:Code: Select all
objMDX\SetProperty("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") ;invoke?
ja
Grüße ... Kiffi
Posted: Fri Jun 26, 2009 9:17 am
by srod
Also, move the
Code: Select all
objADOMD\Release()
objMDX\Release()
statements to sit inside their respective loops. Otherwise, in the case of an error, you may be trying to free objects which have not been created - crash!
Posted: Fri Jun 26, 2009 10:41 am
by CNESM
Hello again,
thanks for your fast help!
But the code doen't run correct. I think the problem is that:
Code: Select all
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX))
objMDX is an object but i dont know how i can consign the mdx object correct.
Posted: Fri Jun 26, 2009 10:43 am
by srod
Code: Select all
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX) + " as COMateObject")
Posted: Fri Jun 26, 2009 10:46 am
by srod
Also,
Code: Select all
objADOMD\SetProperty("Source = '"+mdx+"'")
Posted: Fri Jun 26, 2009 12:11 pm
by CNESM
Code: Select all
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX) + " as COMateObject")
Thats the thing i have searched! Thanks scrod! You are amazing! Unfort. the code doesn't run correct. There must be another wrong call inside the code ... mhh damn
Posted: Fri Jun 26, 2009 12:25 pm
by Kiffi
CNESM wrote:There must be another wrong call inside the code ... mhh damn
please post your current code again.
Greetings ... Kiffi
Posted: Fri Jun 26, 2009 12:28 pm
by srod
Try this :
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+"'")
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX)+" as COMateObject")
objADOMD\Invoke("Open")
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
If this fails, throw some Debug COMate_GetLastErrorDescriptions() around to shed some light on proceedings. The code works here but I do not have the relevant service running.
Posted: Fri Jun 26, 2009 12:55 pm
by CNESM
I am very confused ...
The GetLastErrorDescription get back following information:
Okay.
Thats all.
I think the problem is not comate. I will search the error by debugging the MDX server and talk to the executing company. When i have the solution i will post it hear! Thanks scrod for you fast and greatfull support for this excellent product!
Posted: Fri Jun 26, 2009 1:00 pm
by srod
CNESM wrote:I am very confused ...
The GetLastErrorDescription get back following information:
Okay.
Thats all.
I think the problem is not comate. I will search the error by debugging the MDX server and talk to the executing company. When i have the solution i will post it hear! Thanks scrod for you fast and greatfull support for this excellent product!
Well, it depends on where you place the Debug COMate_GetLastErrorDescription() etc? It will only report on the latest use of a COMate function / method call!
Does the VBA code work okay then but the COMate code produce something different?
By the way, I hope you are using the latest version of COMatePLUS (version 1.1). There was a bug in an earlier version which caused 'okay' to be reported even when an error had occurred! If you are in doubt then download COMatePLUS again and try the code again.
Posted: Mon Jun 29, 2009 11:47 am
by CNESM
Hi srod,
enclosed my actual code:
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+"'")
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX)+" as COMateObject")
objADOMD\Invoke("Open")
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
After talking to the executing company, they change some system setting. Now i should be possible to remote information via external programs, but the program still doesn't work.
i can connect to the server. The following code runs correct:
Code: Select all
objMDX\Invoke("Open('Provider=MSOLAP.3;Data Source=DB.server.com;Initial Catalog=FIBI;')") = #S_OK
When i change this settings i get indivual error, so this function call works. The error must be in the mdx call or in the following functions after the invoke open provider etc. call. Must i changes the dots in the mdx string to "\"?
Yes the vba code works correct and i get the information that i want.
i downloaded the newest version of COMatePLUS. Before the company change the settings i get the "okey." error message. Afterthe settings were change the function works correct but i only get an zerror value ...
Edit: I get the access to the full server adminstration. After using the server debugger, i get following error message:
Code: Select all
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.
Unfort. COMate doesn't show any error messages. COMate give back a "success" althrough the server send an error message....
I don't understand the message because the vba and the pb code are 100% the same. The vba runs correct but the pb code create this error message ?! Maybe COMate interpreted some signs of the mdx string wrong ...
Posted: Mon Jun 29, 2009 12:23 pm
by srod
I don't understand; that error message is a COMate error message!
Do you know which line is throwing that error?
Posted: Mon Jun 29, 2009 12:27 pm
by srod
Try switching :
Code: Select all
objADOMD\SetProperty("ActiveConnection = "+Str(objMDX)+" as COMateObject")
for
Code: Select all
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
Posted: Mon Jun 29, 2009 12:47 pm
by CNESM
I don't understand; that error message is a COMate error message!
This could be! The error message doesn't come from the server. The debugger give external application the chance to remote on highest error level by remoting the actual server situation. Without this activated option external application doesn't get all errors and/or information to create an error message.
Do you know which line is throwing that error?
I dont know .... maybe the error comes from the mdx call. There are different nested objects.
Code: Select all
objADOMD\SetPropertyRef("ActiveConnection = "+Str(objMDX)+" as COMateObject")
This doesn't help ...