Microsoft Access as an ODBC Connection

[LanceTylor]

Hello!

My client has been using a CSV as a data source for their TM1 model but they have reached the limit for number of rows. To save costs, they would like to use Microsoft Access as their data source.

I switched a dimension build process to connect with Microsoft Access through the ODBC connection. When the process is run, the physical memory on the server will go from 10 GB to 15 GB (max 20 GB) when the process has been completed. The dimension is only 100 or so elements. It will continuously keep going up as it cycles through the prolog, metadata, data, epilog. IBM support suggested I enter "UseSQLFetchScroll = T" into the Tm1s.cfg file, but that did nothing.

I understand that the issue is likely that TM1 can only use 64 bit ODBC drivers and Microsoft Access only comes with a 32 bit driver.

Are there any ideas on how I can allow my client to stay with Microsoft Access as their database source for TM1?

Thank you for your help!

Relevant info:
-Running on Windows 2008R2
-Cognos TM1 10.2.2 Fix Pack 4

[tomok]

Are there any ideas on how I can allow my client to stay with Microsoft Access as their database source for TM1?

Yes, Don't waste your time trying to make an ODBC connection to the Access database and just write a macro in the database that will export the relevant contents to a flat file and make that file the source for the TI process.

[Alan Kirk]

I'm not sure what you mean by "reached the limit for number of rows". What limit, exactly? There's a (practical rather than theoretical) limit in terms of how big a text file can be but that limit is so huge that one would have to wonder how much garbage is in the columns if a file ever got within a bull's roar of that limit.

More to the point, I cannot conceive of a situation where Access can serve up directly a greater number of rows than can be contained in a text file. And certainly of no situation where it can do it faster, especially if more than one... hmm, who are we kidding, one or more join is involved, and there are more records in the tables than you have fingers. (That may be an exaggeration, but not by much...)

As an aside, you'd be doing your client a favour if you got them off Access anyway. It's so unspeakably gutless as a database for any but the most trivial of datasets, and I always found the 2GB file limit to be a pain in the backside given that the loading process would cause "database bloat" necessitating constant compacting and recompacting. I hear what you say about saving costs but SQL Server Express is vastly more powerful than Access and comes at a cost of 0 dollars and 0 cents. And it lives in a 64 bit world along with the rest of us these days.

But I'm inclined to agree with Tomok that the trouble of trying to do a direct connection to Access isn't worth it.

[David Usherwood]

While not disagreeing with either about the weaknesses of Access, IBM did (to our surprise) release a 32 to 64 gateway in 10.2.2 - (http://www-01.ibm.com/support/knowledge ... proxy.html). Don't know anyone who's using it though.

[LanceTylor]

Thank you Tomok, David and Alan for your replies.

I will likely advise to make the transfer over to SQL Server Express. I was unaware that this was a free option for databases smaller than 10 GB.

Allan, when we open our CSV file it says that the 1.1 million row limit has been reached. Any rows below are truncated. In the meantime, before we make the switch over to the SQL Server Express, my client will still be using a CSV as the data source.

When TM1 uses the CSV as the data source, does it technically "open" the file? As in, will TM1 only read the first 1.1 million rows or will it still read the entire CSV file for data loading purposes? Obviously want to make sure no data is lost during the data load/dimension build processes

Thanks again!


PS IBM support was able to replicate my issue but converted the .mdb file to a .accdb file to make Access work. However, on our clients server, when we replicated the same steps as IBM Support, we were unable to make it work. Still had physical memory climb over 3 GB from one dimension build process.

[Steve Rowe]

Allan, when we open our CSV file it says that the 1.1 million row limit has been reached. Any rows below are truncated. In the meantime, before we make the switch over to the SQL Server Express, my client will still be using a CSV as the data source.

Won't this be a limitation of the tool you are using to look at the csv rather than a limitation of the csv itself? i.e. When you process it through the TI you will get all lines?

[tomok]

A CSV file is nothing more than a text file that has commas as the delimiter between "fields". The 1 million record limit is only because you are trying to open it in Excel. As long as you are not trying to edit the file in Excel then there is nothing "wrong" with leaving it in CSV format.

[jim wood]

A couple of points for you:

1) In a TI process it will only open the first few rows in the preview pane. It will however (as Tomok mentioned) process the whole file.
2) If you have code in both meta and data tabs in will process the whole file for each tab. If you add a calculation variable in the variables tab it will default to both, adding the code to both tabs. Unless you need to make meta updates from the data, try to keep your code to the data tab, halving the processing time.

I hope this helps,

Jim.

[LanceTylor]

For anyone reading this in the future....

Solution for using Microsoft Access is to make sure to have 64 bit of Microsoft Office and Access installed. Then install the add in for Access "Microsoft Access Database Engine 2010" 64 bit. Then create a 64 bit driver off Access.

[BariAbdul]

Thanks a lot for the update Lance.But ,Personally I would like to go with Tomok's suggestion.It is rather straight forward and easy-peasy.Thanks