If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

TM1 data to Oracle or SQL Sever

Started by prafulla20j, 11 Sep 2009 01:04:37 AM

Previous topic - Next topic

prafulla20j

Hi,
My company just decided to move to TM1 for Planning. We were using Khalix before and used to load Plan data to Oracle. We have a reporting package out of Oracle.
Is it possible to load snapshot of plan data after the analyst inputs done; from TM1 to Oracle or SQL server database.
As much I know about TM1 so far, I feel its gonna be very difficult.
Any thoughts on loading TM1 data into Relational tables.
I know TM1 cube can be directly integrated to cognos 8 as an alternative.

Regards,

MichelZ

Hi,

In TM1 you can set up a Turbo Integrator process that uses a cubeview (defining the cube area you want to export) as a datasource and use the ODBCOpen, ODBCOutput (on the TI Advanced Data tab) and ODBCClose statements to write the selected data to a relational table.
We have customers creating their budget/forecasts in TM1 and writing the results to JD Edwards, SAP, etc.

Michel

OLAPBPMguy

I agree with Michel.  This is not overly difficult; with ODBCOutput TM1 can write new records or update records to relational tables.  Although depending on database administration policy, and/or ODBC driver performance, and/or database performance during large updates you may be better off dumping CSVs out of TM1 and importing into the relational db as distinct steps.

Also if you move to planning in TM1 you may find that the push to a separate reporting system becomes redundant as you can report directly out of TM1 quite effectively with the added advantage of the numbers being real time.

prafulla20j

Thanks guys. This really helped, knowing the options available.

yng

Hi Michael,

can you send me sample TI process output data from TM1 cube to relational database by ODBC?

yng

MichelZ

Hi,

I don't have an full example at hand that I can publish here, but below are a few example lines.

On the Prolog tab I open a connection to my ODBC data destination and I delete all rows from the TM_TEMP table that I want to rebuild in the process.

ODBCOpen('MyDB', '', '');
ODBCOutput('MyDB', 'DELETE FROM TM_TEMP');


On the Data tab I write new records to the TM_TEMP table, inserting some variable values from the datasource that I'm processing.

ODBCOutput('MyDB',
'INSERT INTO TM_TEMP
  (ROWNR, TM1_DEPT, TM1_RANGE_A, TM1_RANGE_B)
   VALUES (' | Trim(Str(VrowNR,7,0)) | ',''' | v_prov | ''',''' | v_clm_class | ''',''' | v_cln_class | ''')');


On the Epilog tab I close the connection to the ODBC data destination.
   
ODBCClose('MyDB');



Michel

yng

Hi, Michel!

Thanks for your help and samples!
Now, I can put any dates into SQL server.

Regards,
yng

KJohnson

Any help would be appreciated.  I have been succesfun opening the ODBC connection, I just can't get the SQL piece to work.

When I run this, it gives me a "Error execuing Sql..." message. referencing the last line.
I'm assuming I have a syntax issue somewhere, I just can't figure it out.

ODBCOutPut ( 'derp2prd',
                       'INSERT INTO JPRPT_U_TM12SPOTFIRE
                                           (  Value
                                            , Currency
                                            ,  year
                                            ,  scenario
                                            ,  entity_dept
                                            ,  account
                                            ,  prodline
                                            ,  ms
                                            , ic
                                            , line
                                            , period
                                            , amount )  |
                                VALUES ('
                                               | vvalue | ','''
                                               | vcurrency | ','''
                                               | vyear | ','''
                                               | vscenario | ','''
                                               | ventity_dept | ','''
                                               | vaccount | ','''
                                               | vprodline | ','''
                                               | vms | ','''
                                               | vic | ','''
                                               | vline | ','''
                                               | vperiod | ','''
                                               | vamount | ''')');


MichelZ

I don't know whether you've solved your problem by now, but anyway...

In the code I am missing a closing quote after ,amount) and an opening quote before VALUES ('.

Michel