TM1 and SQL Server

[abetschen]

Hi,

I know that is relatively simple (if there is such a thing) to pull data from an SQL Server DB with a piece of SQL, the proper database driver, and the proper access, using a TI process (I have seen some threads in this forum talking about this). I know this is possible for multiple types of databases. However the question that I have is if it is possible the other way around.

We have an application sitting on top of an SQL Server DB that manages projects life cycles (approval process, tracking progress and status for each project); approved budget originates in this apps. From a financial perspective we use TM1 to track actuals by project (coming out of the GL) and forecast by project; Currently Budget is loaded manually from the apps mentioned above. Also any new approved projects (project number, project description, project manager,...) needs to be added to our TM1 database. So for TM1 to be updated if a new project is being approved, I know we can have a TI process with a piece of SQL querying the SQL Server db. We should even be able to get the budget out of the project life cycle apps.

However, the project management team would like to load actuals and forecast from TM1 into the project life cycle app. Obviously we could do that manually by having TI create and extract the actuals and the forecast from TM1 into a flat file. This info could then be loaded through a process into the SQL Server DB.

My main concerns with this method are: since it is a manual process, maintaining both DBs in sync may become a challenge and require too much time.

In an ideal world, one would setup a connection from the SQL Server DB to TM1. This connection would pulled the data on the fly and display it in the SQL Server DB. Is that somehow possible?

With a lesser degree of integration, is it possible with an ODBC connection or any other type of connection to have a process kicked off from the SQL Server DB side, query TM1 and load the data to the SQL server DB?

Thank you for your input.

Best Regards,
Andre

[ScottW]

I'm not sure about pulling from TM1 to SQL Server (but someone on this forum will certainly know, with the correct ODBO driver and MDX query it's probably or quite certainly possible.) But you can definitely push from TM1 to SQL Server via a table update / table insert query. The login account that TM1 uses to access the SQL source will of course need to have write permissions to the table being updated.

You just need the following in your TI process
ODBCOpen
ODBCOutput
ODBCClose

( ... of course it's not quite that simple as you need to ensure you have all the correct SQL syntax in the ODBCOutput statement, but you get the picture.)