Using TI to kick off a SQL Store Procedure

[LanceTylor]

Hi All,

I am currently trying to build a TI process that is can be used to trigger a SQL Store procedure followed by a standard TI cube data load process? An alternative process is that the store procedure that is triggered can kick off a TI process? I guess its a pull of the data from TI (most likely an action button) or push from SQL DB

The requirement I am trying to fulfil is giving the user the ability to update their transactional database (last minute monthly journal entry) and use a process to refresh the cube and subsequentially the income statement BI report. The dataflow is as follows: Transactional DB -> DB Staging Table(Store Procedure)-> TI Cube Load Process. This process is set to run nightly too.

Has anyone done this? Any ideas or guidance would be really helpful. Maybe using a CMD function?

As Always, thanks for any input

Regards
Lance

[Steve Rowe]

I think kicking off a stored proc is pretty straight forward from TI. Don't you just open up an ODBC connection in your TI and then issue whatever the SQL is that starts the stored proc?

A while since I've done it so I could be misremembering it...

[asutcliffe]

I've not tried execute a stored procedure via ODBCOutput but I guess it should work. We've done something similar recently by using ExecuteCommand to trigger the process via the RDBMS's command line tool and then proceeding with the TI load. Setting ExecuteCommand's wait argument to 1 ensures the procedure finishes before progressing though behaviour might differ depending on the RDBMS you're using. We're using SQL Server's sqlcmd command.

[mvaspal]

We also go the exact same way as asutcliffe but from an oracle database.

[LanceTylor]

Thanks for your quick responses. I am going to be looking at this next week so if all goes well you shouldn't hear from me.

Regards
Lance

[iansdigby]

Quite simply EXEC [stored procedure name]
within the Data Source tab of TI will run a SQL Server stored procedure as long as you have the ODBC set up correctly. It will return records too if needed.

And how much better than trying to write a complicated SQL string within the punishingly tiny window you get in TI. All the SQL code can be written in SQL server, with its lovely colour coded and debug-friendly interface.