ODBC Connections

[Ajay]

Hi All,

I am about to work with ODBC for the first time on TM1 and wondered if any of you have sound experience of it.

I really don't know where to start so some direction would be needed at this stage, like do i need to install anything ? are there changes i need to make to the cfg file ? will TI automatically find my data source ? do i need to define source data tables etc.

Any help would be useful

Thanks
Ajay

[Alan Kirk]

I am about to work with ODBC for the first time on TM1 and wondered if any of you have sound experience of it.

I really don't know where to start so some direction would be needed at this stage, like do i need to install anything ? are there changes i need to make to the cfg file ? will TI automatically find my data source ? do i need to define source data tables etc.

Any help would be useful

Do you mean using ODBC as a data source for a TI? It'd be useful to know what type of database, since that will have an effect on the answer. As will the type of server platform that you're running on (Windows 32, Windows 64 or *nix.)

Over the years I've used MS Access, IBM DB2 and MS SQL Server, all on 32 bit Windows. The MS applications are dead easy.

With Access, assuming that you use a secured database, you need only do the following:
- Create a login for the TI to use in your .mdw file (and assign it a password);
- On the Windows server, go to Start -> Programs -> Administrative Tools -> Data Sources (ODBC) (the path may vary depending on your version, but it will be something like that; administrative tools are also usually accessible through the Control Panel).
- Go to the System DSN tab, select the MS Access driver, and fill in the details. The Data Source Name (DSN) that you assign in this dialog will be the one that you specify in the TI process. Avoid punctuation or spaces in the name. Also, bear in mind that unless you're working directly on the server you'll probably need to define the DSN on your own client computer so that TI can pull down sample data for it when you're editing it. Don't forget to set the System Database to your .mdw file.

With SQL Server:
- You'll also need to have a login created. This would need to be done by the SQL Server's DBA, who would also need to assign you permissions to the tables or views that you'd be pulling the data from.
- The SQL Server driver should also be built in to Windows Server, but be careful that you have a version that will work properly with the SQL server. I had a few problems with that when we migrated out PeopleSoft GL system from DB2 to SQL Server. You may need to go to the MS Downloads site and confirm that you have the latest MDACs installed. This link is useful for background:
http://msdn.microsoft.com/en-us/library/ms810805.aspx
- Create a System DSN in a similar way to the way that I described under Access, though the dialog will have more entries to make. This will be the name that you use in your TI.

With DB2:
- I recall that we had to get a custom driver. However it's been a few years since we migrated and I don't recall off the top of my head what the issues were that we had. This is of course largely immaterial if you aren't using DB2. Of course you SHOULD be using DB2 since it's such a fine product. Oh, wait... I just remembered that IBM doesn't own this forum, so we don't have to be fanbois over here. Meh, use MySQL instead, it's much better and far cheaper. (Though I've never tried to connecdt to it from TM1. Yet.)

With Oracle:
We do have one feeder system that is being upgraded to use an Oracle 10g database engine. Originally we were going to be doing an ODBC feed from that, BUT... the IT guys who were creating the new feeder system pointed me in the direction of the client software that I needed to download to be able to do that. The install package, JUST the install package, was 475 Meg. For the client software. "I am nooooot putting THAT on my server to pull down around 300 rows of data once per week", sez I. We ended up having them write an export procedure which spat the rows into a text file instead. Similarly at the place that I used to work (which used Oracle Financials with an Oracle 7.3 database, I think it was) we were never able to get the ODBC connection working properly and used exported text files. However that was many years ago, and under TM1 7.1. I'm afraid that I can't give you any more details on connecting to Oracle.

With 64 bit Servers:
I believe that you still need to install 32 bit ODBC drivers, though there will be others who are better qualified to describe the issues there. Aside from which it's academic if you aren't on a 64 bit server.

Hope you find something of some use to you here.

[Ajay]

Thanks Alan for a very detailed response..apologies in the lateness of getting back to you.

Basically...We are running TM1 9.0 SP3 U7 (64bit Windows). I wish to pull data from a Sage Financials from within a TI job to simply populate some financials into a TM1 cube.

I've never used a product like Sage, so i am reliant on the DBA, who told me that I needed to have access to the Oracle database, which i'm guessing, it sits on.

I've looked at some of the documentation out there but it doesn't really explain what you need to do.

[Alan Kirk]

Thanks Alan for a very detailed response..apologies in the lateness of getting back to you.

Basically...We are running TM1 9.0 SP3 U7 (64bit Windows). I wish to pull data from a Sage Financials from within a TI job to simply populate some financials into a TM1 cube.

I've never used a product like Sage, so i am reliant on the DBA, who told me that I needed to have access to the Oracle database, which i'm guessing, it sits on.

I've looked at some of the documentation out there but it doesn't really explain what you need to do.

Yes, the SAGE application will indeed be built over the top of the Oracle database. Actually a knowledge of SAGE as such won't be all that important; the important part will be the knowledge of the tables and relationships that the SAGE applications create. You won't be drawing data from SAGE as such, but rather from the DBMS (in this case Oracle) that it sits on.

Frankly I wouldn't even try getting into the tables and relationships unless you're going to be responsible for maintaining the SAGE application as well at some point, or you just want to know them. However chances are that you won't have time to get up to speed on them before you need to deploy your TM1 app anyway, so you'll need to reply on the DBA. After all, that's their specialty.

It's more likely that rather than querying the actual transaction tables directly, you'll need to have Views of the tables defined for you. That's most likely something that the DBA will have to do for you, and s/he should also provide the SQL query that you'll need for pulling the data from the view. (Unless it's something very straightforward like a Select All statement, but it seldom is with a GL system.) Certainly they'll need to provide you with a login and password for the Oracle database that allows you to access the tables or views. These will be entered into your TI process, which will use them to log onto the database and draw data from it.

You WILL need to install an Oracle driver on your TM1 server; again I'd suggest talking to the DBA about which drivers are available and/or recommended for the version of the Oracle DBMS that SAGE is running on. Some other Forum members who have implemented an Oracle connection on a 64 bit machine (I'm betting that there are some around here somewhere) might also have some good insights on that.

Once the driver is installed, you can just create a DSN in the way that I described previously, and that data source name is used in your TI process. Enter the login and password, and if all goes well you should be able to see the data just as if it was coming from a good old fashioned text file.

(As I mentioned previously, you may need to get the Oracle driver installed on your desktop and create a DSN there as well if you want to write the process on your own machine rather than while proxied on to the server.)