TM1 9.4 SQL Procedure within a Process

[rfielden]

Newbie here again. Is it possible to run a procedure within a TM1 process to derive variables used in a TM1 spreadsheet? I need to run thru 2-3 processes against an Oracle table(s) to derive a set of variables used in our TM1 spreadsheet. Any advice would be appreciated. Thanks, Rick.

[David Usherwood]

Yes
In TI, you can specify an ODBC data source. In the query tab you can execute a stored proc in place of a query. (Watch out for parameters - from memory, they need to look like '?p1?' . The server passes the text of the 'query' directly to the relational engine. Sadly if there is a syntax error you are not told what the problem is. The output from the stored proc comes into TI in columnar format, just as if it were a flat file, table or view.

[rfielden]

Thank you sir and good news. I will give it a try and update this post with results.

[rfielden]

I tried running the procedure several ways within a process with no luck. I get an OK box when click preview but that is it. Looking at results, nothing was updated per procedure. Any clues where this lowly, pion newbie may be mucking up?

[rfielden]

Resolved. Needed to use BEGIN....END format for procedure in the process to work.