Export from TM1 to Oracle

[mcguija]

Hi,

Currently we are exporting tm1 data to oracle via ODBC. It works but it seems a little slow.

1. Can we use OLEDB? If yes,
2. What would need to be installed from a TM1 perspective (if anything)
3. What would need to be installed from a Oracle Client perspective (if anything).
4. What would the TI code look like?

Here is a snippet of the code we're currently using from the Data tab.

#=============================== Send the value to the ODBC Output ==========================

sSQL = 'INSERT INTO ' | sTableName | ' (VERSION , VERSION_DESC_EN , VERSION_DESC_FR, CURRENT_BUDGET_FLAG, CURRENT_FORECAST_FLAG, LAST_ACTUALIZED_PERIO
D, LAST_ACTUALIZED_YEAR) VALUES ( '''| elmName |''', '''| sDescEN |''', '''| sDescFR |''' , '''| sCurrentBudgetFlag |''' , '''| sCurrentForecastFlag
|''' , '| sLastActualizedFP |' , '| sLastActualizedFY |' );' ;

ODBCOutput(sSource, sSQL );

Thanks
J.

[tomok]

Can we use OLEDB?

No. The only way you'll be able to speed things up is to optimize on the Oracle side. For example, using INSERT INTO, while technically correct as a way to insert data, it would be much faster to create the equivalent of a stored procedure in Oracle and just pass the parameters that what you want to insert. You can have a stored proc that takes off indexing and stuff like that, BEFORE you run this TI, then turn it back on after this one is finished. Basically, you want to minimize the overhead that has to be done with each and every INSERT INTO. Updating indexes and stuff like that would be much faster done in batch, outside of TM1.

[lotsaram]

I disagree with Tom. You can use the TM1 OLEDB driver on the Oracle side to PULL from TM1 as opposed to using TI and ODBCInsert on the TM1 side to PUSH into Oracle. On your Oracle server you would just need to have the TM1 OLEDB driver installed (usually via client install of TM1) and set up a procedure there to query TM1 via the MDX interface. You would then be doing everything on the Oracle side, the TI code wouldn't look like anything because there wouldn't be any.

There is a clear distinction between "can" and "should" though. The TM1 OLEDB interface is not always known for performance and stability. My advice would be to stick with ODBCInsert and see what you can do to optimize that as a first step. I would exhaust that avenue before spending time investigating TM1 as an OLEDB source.

[mcguija]

Thanks guys. I am now looking at other avenues to speed things up.

Jason