TI errors to VBA

[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

[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.

[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

[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

[Mike Cowie]

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