Opening connection to database with ODBCOpen

[tosca1978]

Hi all,

using 9.5.2 on 64 bit server

I am trying to publish data from a TM1 cube to a SQL table in the datawarehouse and falling at the first hurdle...ODBCOpen.

I have the following code in the prologue tab of a TI:

Code: Select all

ODBCOpen('COLUMBUS_TM1_PUBLISH','','');

When I run the TI to check that this works I get a message saying unable to open database.

I have successfully created many TI's on this server that bring data into TM1 from another SQL database that is part of the same datawarehouse (COLUMBUS_TM1_SOURCE_DATA) using the Data Source/ODBC script of TI. I always leave Username and Password blank as it uses windows single sign on. Therefore I assumed the following when creating this new process to publish data to the datawarehouse:

1. I could use the same credentials
2. I must have the correct 64 bit drivers installed for the original TI's to work that bring the data into TM1.

I have tried hardcoding username and password but still get the same error.

I have tried looking through the documentation but using ODBC as a data source is much better documented than using ODBC to publish data from TM1!

Any help would be much appreciated.

Cheers

[Michel Zijlema]

Hi,

I see that you use the DSN 'COLUMBUS_TM1_PUBLISH' in your problem process, while in the processes that succesfully pull data in you use the DSN 'COLUMBUS_TM1_SOURCE_DATA'.
Is the setup of both DSN's the same - if not, what's the difference? What happens if you use the problem ODBCOpen on the 'COLUMBUS_TM1_SOURCE_DATA' - does this connect?

Michel

[tosca1978]

Hi Michel,

Thanks for your reply. As you suggested I have changed the code in the prologue of the problem TI to:

Code: Select all

ODBCOpen('COLUMBUS_TM1_SOURCE_DATA','','');

I still get the same error message in the log file:

Error: Prolog procedure line (7): Unable to open data source: "COLUMBUS_TM1_SOURCE_DATA"

I should have really tested this earlier - sorry. I'm confused though. I can successfully connect to that database when pulling data into TM1 through the DATASOURCE tab of the TI with no username and password. Yet I cannot connect via the ODBCOpen function.

Could the problem be that the user account used to manage the TM1 service doesn't have the correct rights to the ODBC source? I didn't think so as at this stage I am only trying to open the source as opposed to writing to it. I will ask the admin person who handles the services account tomorrow.

Otherwise if you have any ideas I would be most grateful.

Cheers

Mike

[tomok]

If you are opening an ODBC connection through the Data tab and don't have provide an ID and a password it will attempt to connect SQL Server using the service account that TM1 is running under. However, it has been my experience that this does not work if you are issuing an invoking ODBCOpen function anywhere else in a TI process, you have to be using an SQL-based ID and password. Try switching to an SQL-based ID (and provide that ID and password in the function) and it will probably work.

[tosca1978]

Hi Tomok,

thanks for your reply.

I will ask the admin of the SQL database to set me up with a username and password and code these into the ODBCOpen function parameters. I'll post the outcome!

Cheers

[tosca1978]

Hi,

a username and password was set up for the SQL database with write access. I put these into the ODBDOpen function:

Code: Select all

ODBCOpen('COLUMBUS_TM1_SOURCE_DATA','TM1_ADMIN','admin');

However, I still get the same error message.

Are there any settings to set up trusted ODBC sources that I need to address? Any help would be much appreciated.

Cheers

[tomok]

Was the ODBC connection on the TM1 server set up under a specific ID or is it a globally accessible one?

[tosca1978]

Problem fixed!

The system services account used to run the TM1 service did not have a DSN set up for the new database. Admin has now set this up and the ODBCOpen process runs successfully with the SQL based ID and Password coded into it.

Michel/Tomok - many thanks for your help.

Cheers