TI errors to VBA

Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

TI errors to VBA

Post by Martin Ryan »

Confounded by own slackness in not allowing multiple search terms in the old Applix forum...

I have some API code successfully running TI processes from VBA, but only the catastrophic error messages are making it back to Excel. I want to be able to inform the user if there have been any minor errors, so they can ask an administrator to check the log files.

Anyway got any ideas or, even better, samples?

Cheers,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Daniel Lennon
Posts: 6
Joined: Fri May 30, 2008 6:33 am
OLAP Product: TM1
Version: 9.1 SP2 Build 9.1.2.49
Excel Version: 2003 2007
Location: Sydney, Australia

Re: TI errors to VBA

Post by Daniel Lennon »

Martin,

I have logged process execution results to a cube in the past... code example is below.

You need the following at the start of Prolog

Code: Select all

vAuditProcessName = '<ProcessName>' ;
vAuditProcessYearVariable = <Year being loaded>;
vAuditProcessMonthVariable = <Month being loaded>;

# Start ProcessAudit - This belongs at the start of Prolog
NumericGlobalVariable ( 'PrologMinorErrorCount' ) ;
NumericGlobalVariable ( 'MetaDataMinorErrorCount' ) ;
NumericGlobalVariable ( 'DataMinorErrorCount' ) ;
StringGlobalVariable ( 'ProcessReturnCode' ) ;
vAuditProcessCube = 'TM1ProcessAudit' ;
vAuditProcessStart = Now;
vDate = TIMST ( Now, '\D \M \Y' ) ;
CellPutN ( vAuditProcessStart, vAuditProcessCube, vAuditProcessName, 'StartTime' ) ;
CellPutS ( vDate, vAuditProcessCube, vAuditProcessName, 'Date' ) ;
CellPutS ( vAuditProcessYearVariable, vAuditProcessCube, vAuditProcessName, 'Year Variable' ) ;
CellPutS ( vAuditProcessMonthVariable, vAuditProcessCube, vAuditProcessName, 'Month Variable' ) ;
# End ProcessAudit
and the following at the end of Epilog

Code: Select all

# Start ProcessAudit - This belongs at the end of Epilog
vAuditProcessEnd = Now;
CellPutN ( vAuditProcessEnd, vAuditProcessCube, vAuditProcessName, 'EndTime' ) ;
CellPutN ( vAuditProcessEnd - vAuditProcessStart, vAuditProcessCube, vAuditProcessName, 'TimeTaken' ) ;
CellPutS ( NumberToString ( PrologMinorErrorCount ), vAuditProcessCube, vAuditProcessName, 'Prolog Error' ) ;
CellPutS ( NumberToString ( MetaDataMinorErrorCount ), vAuditProcessCube, vAuditProcessName, 'MetaData Error' ) ;
CellPutS ( NumberToString ( DataMinorErrorCount ), vAuditProcessCube, vAuditProcessName, 'Data Error' ) ;
CellPutS ( ProcessReturnCode, vAuditProcessCube, vAuditProcessName, 'Return Code' ) ;
CellPutS ( GetProcessErrorFilename, vAuditProcessCube, vAuditProcessName, 'Errorlog' ) ;
# End ProcessAudit
This writes all the results to a Cube made up of the }Processes dimension and a Measure dimension that has all the following

StartTime
Date
Year Variable
Month Variable
End Time
Time Taken
Prolog Error
Metadata Error
Data Error
Return Code
Error Log

Now, back to the question...

If you did all of that then you could use the API to return the cube values back, maybe the Return Code and a link to the Error Log file that was created.

Hope this helps.

DL.
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: TI errors to VBA

Post by Mike Cowie »

Hi Martin,

Which TI process execute API function are you using: TM1ProcessExecute or TM1ProcessExecuteEx?

If the former, you may want to look at the latter, which apparently returns some additional information that could get you what you need. The declaration according to the 9.1 manual:

Code: Select all

Declare Function TM1ProcessExecute Lib "tm1api.dll" (ByVal hPool As Long, ByVal hProcess As Long, ByVal hParametersArray As Long) As Long
As far as return values are concerned, the doc says:
If the return is not an error object is should contain a TM1 array. The array contains
two elements. The first element is an error code. The error codes are listed below.
The second element is the path to the error log file. The error log file is generated
only if an error occurs.

The returned array contains one of the following error codes.
• 0 (process successful)
• TM1ProcessAborted
• TM1ProcessHasMinorErrors
• TM1ProcessQuitCalled
• TM1ProcessCompletedWithMessages
If you get stumped trying to make it work let me know and I'll take a crack at it.

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: TI errors to VBA

Post by Martin Ryan »

Thanks Mike, I've used that TM1ProcessExecuteEx and it seems to be producing more results that TM1ProcessExecute.

I couldn't get the results of the array to come back, but with this code:

Code: Select all

  lReturn = TM1ProcessExecuteEx(hProcessPool, hProcess, hParametersArray)
  itype = TM1ValType(hUser, lReturn)
itype returned different values - 3 for success, 7 for minor errors. Not sure what 1, 2 and 4 through 6 might be - probably the results you mention below. I've just captured them using an else statement "Unknown minor error, check the logs". All I really care about is telling the user either the process went off without a hitch, or that they need to get in touch with admin to investigate an error.

Thanks for your help Mike and Daniel.

Cheers,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: TI errors to VBA

Post by Mike Cowie »

Martin Ryan wrote:itype returned different values - 3 for success, 7 for minor errors. Not sure what 1, 2 and 4 through 6 might be - probably the results you mention below. I've just captured them using an else statement "Unknown minor error, check the logs". All I really care about is telling the user either the process went off without a hitch, or that they need to get in touch with admin to investigate an error.
Hi Martin,

Glad to hear you were able to get further along. I think you'll find that the 3 means lReturn contains a handle to an integer result (which will be a 0, if you believe the documentation) and the 7 means lReturn is a handle to an array. You can test that more exactly with some of the TM1 constant functions:

Code: Select all

If itype = TM1ValTypeIndex Then
  'lReturn is an integer result
ElseIf itype = TM1ValTypeArray Then
  'lReturn is an array containing information about the error type (minor, ProcessQuit, etc) and a reference to the error log location
ElseIf itype = TM1ValTypeError Then
  'lReturn contains some kind of error trying to run the process (e.g., wrong number of parameters)
Else
  'Who knows
End If
Hope that helps.

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
Post Reply