ODBC History

ODBC or Open Database Connectivity is a platform neutral, universal approach for providing an Application Programming Interface (API) to access relational database management systems. ODBC was an early modular approach to enable database access. The ODBC library itself is an independent Operating System (OS) component which acts like a bridge passing requests from the consuming application to the corresponding ODBC drivers. The various data store vendors develop their product’s ODBC drivers and then either ship those with the product and or make them available over the Internet. Microsoft designed Object Linking and Embedding Database (OLEDB) as a replacement for ODBC mainly due to the desire to support a much larger variety of data stores beyond those that are pure relational databases. A nice alternative point to this post is that we take so much for granted in today’s technological landscape. At one point in time, performing database access was not a trivial task.

In today’s world, particularly the SQL Server BI world we have a plethora of drivers and providers for accessing databases, email systems, spreadsheets you name it. Like SQL Server native extended store procedures (XPs), Microsoft is slowly (but surely) phasing out the old ODBC API. Currently, Microsoft OSs still support the ODBC platform/technology, however the higher-level components are withdrawing support. SQL Server Analysis Services (SSAS) is one such component, since SQL Server 2005, SSAS has not supported the ODBC specification. The following is a quote describing the non-support of ODBC by SSAS 2005, the entire technical article can be located here http://technet.microsoft.com/en-us/library/aa964120.aspx :

‘Microsoft SQL Server Analysis Services 2000 supported a range of OLE DB providers. In particular, it supported OLE DB for ODBC. You could configure an ODBC data source to access your database.Analysis Services 2005 provides greater functionality by providing data-source views. Because it supports different syntaxes, Analysis Services 2005 imposes stricter requirements on client libraries than does Analysis Services 2000. Analysis Services 2005 does not support Microsoft OLE DB Provider for ODBC. To access your database, you must find an appropriate version of the OLE DB provider for Analysis Services.

Besides OLE DB in Analysis Services 2005, you can use managed providers to access your relational database.’

Detecting SSAS 2000 ODBC Data Sources with the Katmai Upgrade Advisor

When you analyze a SQL Server 2000sp4 SSAS instance that contains ODBC data sources with the SQL Server 2008 Upgrade Advisor (UA) utility you will receive an advisory stating that ‘ODBC Data Sources are not supported’. As described in my previous post, this is one of the more common issues that the UA will find when analyzing Analysis Services instances. A lot of folks out there are still running on the SQL Server 2000 platform including Analysis Services.

Post Katmai Installation & Resolutions

Once you perform the SQL Server 2008 upgrade you will need to update or recreate your Analysis Services data source to use a corresponding OLEDB provider. This issue applies only to SQL Server 2000sp4 upgrades to SQL Server 2008. That being said, because of the sheer number of Analysis Services 2000 installations running in production that do in fact use the ODBC drivers this is a fairly common Katmai upgrade issue.

Derek Comingore