TM1 Forum

Hi All,

I've looked throught the forum and was looking for some details on ODBC connections from external applications which use TM1 cubes as a source, rather than TM1 using the ODBCOutput etc commands within TI to push the data out.

Does anyone have any information on this ? In particular, I am interested in hearing from experiences whereby SQL Server can connect and extract data whenever it likes from TM1.

Many thanks
Ajay

Since TM1's not a relational database, it doesn't support SQL, and thus doesn't support ODBC queries.

Thanks David...these were similar thoughts to mine.

Perhaps I need to setup a job in SQL which executes a *.bat file, that contains an executable TI process that ODBC "pushes" the data into SQL.

Think it's a case of which end of the connection kicks it off. You could try updating a little table in SQL and having a TM1 Chore on a 10 minute loop checking the table and sending the data when the flag is on, then clearing it.

Hi Ajay,

You can pull data from TM1 into another database but as David mentioned TM1 is not an RDBMS therefore you can't do this via ODBC. Rather what you have to use is ODBO and the TM1 OLEDB Provider.

However due to TM1's non-standard implementation of MDX the TM1 OLEDB provider can be a little "quirky" and certainly doesn't have anywhere near the performance of say SQLServer or Oracle ODBC in terms of data throughput. In most cases I'd recommend outputting to test file or inserting into an RDBMS, but that doesn't mean the alternative of pulling via OLEDB isn't possible.

Interesting thought (about ODBO) - and I do agree with your caveats.
Have you (or have others) a client using this setup in production?
And how does the performance compare, for meaningful volumes:

• Flat file export and bulk load
  OdbcOutput('Insert blah')
  ODBO

?

I do know of at least one (very large complex) site using ODBO for inter-server type communications but due to poor experiences in the past with performance and stability I have tended to shy away from it. Where I am presently we have one "inter-server" type application that uses SQL Server as a middle-man: the central management server uses ODBCInput to write to tables what needs to be updated on the "satellite" servers and the satellites have regularly scheduled chores which monitor the SQL tables and import if there is anything to update. For smaller, simpler sites text file export / import would work equally well in most situations, but RDBMS and the ability to modify queries to set what to import gives great flexibility and works really well.

I imagine (or rather would hope) that the OLEDB provider is an area of focus for IBM engineering to improve and optimize, but I haven't done any performance comparisons to see how it stacks up now in 9.5.2 vs. ODBC.

I wouldn't even entertain the idea of using the ODBO option. I have compared the flat file option versus INSERT INTO and the flat file and bulk copy into SQL is about 100 times faster. That's because of all the overhead associated with sending individual INSERT INTO commands for each row, which have to be accepted, syntax checked, and run, versus just a plain builk copy with none of that overhead. It all boils down to how much data you are moving. If it's relatively small then the INSERT INTO is going to be much easier, IMO, to do because it requires less involvement with others because the TM1 developer can usually do it themselves. For bulk loads most IT departments are going to require a DBA to do it because of the risk involved. The more people involved, the more complicated it becomes in my experience. Sometimes it's worth it, many times not.