We've got a bunch of TM1 databases and for some of them, whether it's about TM1 calculated data or users data entry,we need to push TM1 Data to Oracle.
What would be the best way to do so.
Usually, we use the ODBC stuff in the following manner :
- open the odbc connection in the Prolog
on the Data tab generate insert SQL commands and run them through a ODBCOutput TI command
still on the Data tab, for each 1000 records, run a SQL Commit command otherwise the Oracle tablespaces are growing too much
run a Commit and an ODBC close on the Epilog
Yesterday we've made a test and the process took 7 minutes to export the data in a text file and more than 6 hours to do the same in an Oracle table.
For sure we'll need to change our method.
I've got 2 solutions in mind at this time : use the exported text file through an external ETL which will feed Oracle in a more efficient way or generate a SQL+ script that could be fired in the Epilog.
Is there anybody in the forum who faced the same kind of need ? What would be the more efficient way ?
Thanks for the help.
Regards,
Bob