Access ODBC connections

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Access ODBC connections

Post by Steve Rowe »

Hi,
There is always one thing you don't test during an upgrade or an assumption you make that turns out to be wrong....Mine was that a 64-bit ODBC driver would be easy to get hold of...


So I can't get my 9.0 64 bit TM1 server to talk to access.
Some questions then
1. I can't find anywhere that links to a 64 bit ODBC driver for Access, so does anyone have an idea where one might be. I can find several places that explicitly say there is no such thing but they are 2 or 3 year old posts now.
2. Is there a way to persuade a 64 bit TM1 server to use 332 bit ODBC drivers, again I assume not.
3. I found this link which is to a security patch for a 64-bit MDAC pack which I'm hoping might contain an Access ODBC connection link, but....I can find no link or reference to the 64 bit MDAC on the M$ sight.

Any help or pointers I'd be very grateful...

Cheers, :oops:
Technical Director
www.infocat.co.uk
User avatar
rollo19
Posts: 80
Joined: Wed May 28, 2008 2:42 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Perth, Australia

Re: Access ODBC connections

Post by rollo19 »

You might be using: C:\Windows\System32\odbcad32.exe to configure your ODBC connection from Administrative tools
You could try running: C:\WINDOWS\SysWOW64\odbcad32.exe
http://www.archivum.info/microsoft.publ ... 01995.html
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Access ODBC connections

Post by Steve Rowe »

Thanks Rollo, that's the different ODBC consoles for 32 and 64 on a 64 bit box. Once you have 64 bit TM1 running though you can only use 64 bit ODBC drivers though (on the system32 path). I think so anyway.....
Technical Director
www.infocat.co.uk
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: Access ODBC connections

Post by mattgoff »

You're right Steve, 64-bit TM1 has to use the 64-bit Data Sources control panel. I did a quick search, and it appears you're SOL. Looks like it's *shudder* AXNet time....

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Access ODBC connections

Post by Michel Zijlema »

Hi Steve,

I don't think there will be 64-bit ODBC drivers for Access available, so you need to use the 32-bit driver over the ODBC Gateway.
To make this work you first need to setup the (system!) DSN through the odbcad32.exe console (the one in the WoW64 directory).
Second you need the ODBC Gateway installed on your server and you need to add the ELFODBC parameters in your tm1s.cfg file.
When all has been set OK you should be able to connect to the Access database from your 64-bit TM1 Server instance.

Michel
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

AXNET: R.I.P.

Post by Mike Cowie »

mattgoff wrote:You're right Steve, 64-bit TM1 has to use the 64-bit Data Sources control panel. I did a quick search, and it appears you're SOL. Looks like it's *shudder* AXNet time....
Unless Steve decides to run TM1 9.4, in which case it can never be AXNET time again. From the 9.4 release notes:
Axnet is no longer supported in TM1. If you are running the x64 version of the TM1 server and
previously use Axnet to access 32-bit applications, you must install and use the appropriate 64-bit
drivers for your applications.
We got stuck with this situation once already (an ODBC driver for GL data whose vendor hadn't yet developed a 64-bit version) and certainly Access fits into the same category. Apparently this was killed because of the need for Unicode support and the Axnet solution's apparent inability to support that fully. I can't say I'll miss it terribly, but it did help in those few cases where we didn't have 64-bit drivers or couldn't when nobody in IT could get the 64-bit Oracle drivers working.

Some replies from support to the "What the heck do we do now?" question:
If you are using a 64-bit TM1 9.4 Server for Windows x64, and if your are to sue [sic] an ODBC datasource, for your TI processes, you must use a 64-bit ODBC Driver.

If the database vendor does not offer a 64-bit ODBC Driver for Windows x64, maybe some Third Party vendor like DataDirect or EasySoft is offering such a 64-bit ODBC Driver.

If there is no 64-bit ODBC Driver, if there is only a 32-bit ODBC Driver, you cannot connect to that database if you are using 64-bit TM1 9.4 Server for Windows x64.

By IBM Cognos, there are only 2 solutions:
- Do not upgrade to 64-bit TM1 9.4 Server for Windows x64. but instead stay with 64-bit TM1 9.1 Server for Windows x64 supporting the axnet ODBC gateway.
- If possible use the 32-bit TM1 9.4 Server for Windows x86 instead of the 64-bit TM1 9.4 Server for Windows x64.

There may be other solutions, like a 32-bit ODBC to 64-bit ODBC Bridge connecting a 32-bit ODBC Driver with the 64-bit OODBC Driver Manager of Windows x64, but only the database vendor or Third Party vendors specialised on Database Connectivity software like DataDirect or EasySoft can answer such questions.
Someone else from IBM Cognos suggested in a separate conversation trying an intermediate 32-bit TM1 Server to get the 32-bit ODBC data effectively into a TM1 "staging" area. Then, you could get it onto a 64-bit TM1 Server through replication or some other means, like exporting it to a file. That could work, too, though it isn't ideal (especially if you're using replication to accomplish the data movement).

In our case we were able to live with a 32-bit version in production since the TM1 application was quite small, but at some point we may run into this again.

Sorry to be the bearer of bad news...

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Access ODBC connections

Post by Steve Rowe »

Thanks all, just waiting to restart the server as I have added the Elf... settings to my config file and I should to be go.
Assuming everything works like it is supposed to of course!

I actually found some help on the IBM site very easily too
Technical Director
www.infocat.co.uk
Bob Stuecheli
Posts: 12
Joined: Fri Dec 12, 2008 8:53 pm
OLAP Product: TM1
Version: 10.2.2, 8.4.5, 2.5 (in 1987)
Excel Version: 2013
Location: Troy, MI

Re: Access ODBC connections

Post by Bob Stuecheli »

This thread was about 10 days too late for me. I just went through about 12 days working with Iboglix support trying to get the 64 bit ODBC drivers for the Oracle 10g database to work with the 64 bit version of 9.0 SP3. Of course that still isn't working. However by accident I did stumble across the right sequence of installing the drivers, the 32 bit ODBC Gateway (axnet), the 64 bit server, and then the EFLODBC parameters.

But I don't like having the client password sitting in the cfg file unencrypted. I am not overly concerned because we have only 4 people that can access the LAN directory where the cfg file is located, but I still don't like it. Does anyone know a way around this?

Bob
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: Access ODBC connections

Post by mattgoff »

Bob Stuecheli wrote:This thread was about 10 days too late for me. I just went through about 12 days working with Iboglix support trying to get the 64 bit ODBC drivers for the Oracle 10g database to work with the 64 bit version of 9.0 SP3. Of course that still isn't working. However by accident I did stumble across the right sequence of installing the drivers, the 32 bit ODBC Gateway (axnet), the 64 bit server, and then the EFLODBC parameters.

But I don't like having the client password sitting in the cfg file unencrypted. I am not overly concerned because we have only 4 people that can access the LAN directory where the cfg file is located, but I still don't like it. Does anyone know a way around this?

Bob
What wasn't working with the 64-bit drivers? We're running Oracle 10.2.0.2 64-bit drivers OK....

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
Bob Stuecheli
Posts: 12
Joined: Fri Dec 12, 2008 8:53 pm
OLAP Product: TM1
Version: 10.2.2, 8.4.5, 2.5 (in 1987)
Excel Version: 2013
Location: Troy, MI

Re: Access ODBC connections

Post by Bob Stuecheli »

Our database is 10.2.0.3 and the DBS's couldn't find 64 bit drivers for that release. We have 10.2.0.1 64 bit drivers and a patch to 10.2.0.4 but neither would work for us. We could set up a DSN and test the connection to the database just fine through the ODBC Database Administrator, but when setting up a TI process using ODBC, we could click on the Preview button and it would bring back the 10 rows of data, but when clicking on it a second time the server would crash.

In our experience, TM1 seems to be very picky about the database and driver versions. Running a local server on my PC with the 32 bit 10.2.0.1 drivers would not connect to the database in TM1 while it was fine in SQL Plus, but the 10.2.0.3 drivers did work on my PC. It could be something unique to our environment - who knows.
KevinB
Posts: 13
Joined: Tue Jun 02, 2009 8:03 pm
Version: 9.1.4
Excel Version: 2007

Re: Access ODBC connections

Post by KevinB »

I installed TM1 9.1.4 64bit Server on Windows 2003 x64 Server with Oracle 9i.

I did install the Oracle Instantclient 10.02.00.02 for the ODBC driver.

I finally got the TI process to connect to Oracle but running a process takes a ridiculous amount of time! The Oracle query would fetch about 800 rows and the TI process took 5 minutes to complete.

Does this have to do with the Axnet service? How do I know if the Axnet service is really working. Is there a way to tell?

Please help!

~Kevin Beckley
Wilsonville, OR
Post Reply