Help says ODBC is Available. Where?

In BOL it clearly says that you can "use the Microsoft OLE DB Provider for ODBC Drivers provided." in relation to Analysis Services.

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/uas9/html/9fab8298-10dc-45a9-9a91-0c8e6d947468.htm

Unfortunately, this provider is not in my list. Here is the full text section from help.

"Other relational systems support the older Open Database Connectivity (ODBC) specification but do not yet support the OLE DB specification. For these systems, use the Microsoft OLE DB Provider for ODBC Drivers provider. Although ODBC providers can be used by referencing them with the Microsoft OLE DB Provider for ODBC Drivers provider, you should use a native OLE DB provider whenever possible for performance reasons."

HELP PLEASE!

Hi Hugh

From what I've seen there is no ODBC functionality in SQL 2005 analysis services. There is in the integration services not that this helps much as you have to dump all of the data from your source database into SQL. I have successfully created a linked server and then used native SQL source to attach to the data. I have run into problems with data limits and the system running out of memory and there is very little documentation, so am stil playing around with this. It seems that Microsoft has missed the mark here as SQL 2000 does allow this functionality so strange to take away functionality with new release.

|||

Thanks for the input kiwi. I have also used the .Net ODBC driver in Integration Services, but find it oddly missing from Analysis Services. Even stranger is that the documentation says it has an OLE ODBC driver which I can't find anywhere in any part of the Business Intelligence tools. I'll try using the linked server route to see if that works for me. Maybe they will resove this in a future patch.

Thanks again.

|||Kiwi, how did you connect to your linked server through the native SQL source? I can't figure out how to do that in the Data Source setup.|||

Never mind. I found the other post that talks about it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=78023&SiteId=1

I knew I could use saved views pointing to a linked server, but I was hoping to be able to directly address the linked server in the data source. If they really aren't planning to support ODBC out of the box in Analysis Services, that makes it a severly limited product. For all of us who have legacy systems that are going to be around for many years to come, we're stuck doing five times the work.

(I'm done whining now :). It's still a great product.)

|||

Hey Hugh

I didn't do all of that I simply used the managment studio to create the linked server and then in Business Intelligence development I used Named queries instead of tables and used Openquery. Worked fine apart from I ran into memory issues with returns over 100k records.

Kiwi

|||Thanks Kiwi. Don't know why I didn't think of that. Definitely much easier that way.