How to Export to a Database from TM1
I needed to create a rollup in a data warehouse the other day from elements that are stored in TM1. To do this I needed to export data from TM1 to an external database. Here are two methods for exporting to a database from TM1. Both methods work just as well for SQL Server, Oracle, DB2 or any other database engine.
Simple Method to Export to a Database from TM1
Simply, the easiest way to get data out of TM1 and into a database is to publish it out of TM1 using an ASCIIOUTPUT command in a Turbo Integrator process. This will create a text file of whatever dimensions and data you want exported from TM1. Then using the database engine of your choice, load that data into the datawarehouse. Easy peasy!
Direct Method for Loading Straight from TM1 into a Database
There are also options to directly connect TM1 that work also work from TI processes. These are ODBCOPEN (to open a database connection) and ODBCOUTPUT (to export the data). These are direct update processes into a database, but will be a somewhat more complicated that just using an ASCIIOUTPUT.
To be honest, the simple method (option 1 above) to export to a database from TM1 is probably the one we choose the most. It is simple and easy to trace and reconcile.