Freigeben über

Impact of the SQL Server OLE DB Deprecation on Analysis Services

  • Artikel

A few weeks back, the SQL Server connectivity team announced on their blog that the SQL Server OLE DB provider is being deprecated and recommended that developers adopt ODBC instead. You can read the blog post in detail here. There has been some concern in the Microsoft BI community about this announcement and how it affects Analysis Services and BI solutions built on it. This blog post attempts to address these concerns.

Firstly, I would like to clarify that although the SQL Server OLE DB provider (also called SQLNCLI or SNAC OLE DB) is being deprecated, the OLE DB API and the OLE DB core components that ship in Windows are not being deprecated. There are a number of other OLE DB providers, some developed by Microsoft and some by third parties and ISVs, that are unaffected by this announcement. This includes the Analysis Services OLE DB provider (MSOLAP).

Analysis Services is an OLE DB consumer as well as an OLE DB provider.

  • OLE DB consumer: Analysis Services supports OLE DB for consuming data from relational databases such as SQL Server, Oracle, etc. It does not support ODBC directly although you can use the MSDASQL (OLE DB to ODBC bridge) provider to access ODBC sources. It does support ADO.NET however there is a performance penalty for the Analysis Services native code to ADO.NET managed code interop. For these reasons, OLE DB is the most recommended and high performance interface for Analysis Services to consume data. And this continues to be true in SQL Server “Denali”.
  • OLE DB provider: Analysis Services supports OLE DB for exposing data and analytics to client applications such Excel. The Analysis Services OLE DB provider (MSOLAP) supports the OLAP and Data Mining extensions to OLE DB that BI client applications rely on. The MSOLAP provider ships with every release of SQL Server (including “Denali”) and is also distributed with every copy of Excel.

SQL Server is the most commonly used data source for Analysis Services and SNAC OLE DB is most recommended and high performance way for Analysis Services to consume data from SQL Server. This recommendation continues to hold true in SQL Server “Denali” as well. So if you are building a new BI application using SQL Server “Denali”, our recommendation is to use SNAC OLE DB. Even though SNAC OLE DB is being deprecated, it is still supported in SQL Server “Denali”.

As a result of the SNAC OLE DB deprecation, Analysis Services will be enhanced in a future release to natively support ODBC for consuming data. At that time, customers can switch their BI applications from SNAC OLE DB to SNAC ODBC. This switch should be relatively straightforward and require just updating the connection string in the Analysis Services DataSource object.

Hopefully this blog post addresses most of the questions and concerns on the SQL Server OLE DB deprecation. If not, please add your comments to this post and we will do our best to clarify.

Comments

  • Anonymous
    September 13, 2011
    Carry on doing what we're doing until Denali+1 at which time we change the conn str. Sounds simple enough.
  • Anonymous
    September 13, 2011
    Where did you see Denali + 1? All I saw was "a future release".
  • Anonymous
    September 13, 2011
    ok....carry on doing what we're doing until a future release at which time we change the conn str. Sounds simple enough.