Accessing External Data Sources with Microsoft SQL Server Analysis Services 2005

Edward Melomed

Microsoft Corporation

November 2006

Applies to:
   Microsoft SQL Server 2005 SP1
   Microsoft .NET Framework

Summary: This white paper covers a variety of client object models supported by Microsoft SQL Server Analysis Services when connecting to relational data sources. The example problems and solutions were gathered by members of the Analysis Services team while working with users of Analysis Services. (5 printed pages)

Click here to download the Word version of this article.

Contents

Introduction
Connecting to External Data Sources
OLE DB Providers
Microsoft .NET Framework Data Providers
Conclusion

Introduction

This white paper answers some commonly asked questions about accessing relational databases with Analysis Services 2005.

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.

This white paper lists supported providers, and describes how to install and configure them. It also describes problems encountered by customers and how to resolve these problems.

Connecting to External Data Sources

Analysis Services establishes a connection to a relational database, based on information in the data-source object.

The connection string in the data-source object is passed to the OLE DB or Microsoft .NET Framework library. This, in turn, loads the correct version of the provider.

Which OLE DB providers Analysis Services supports depends on whether it can construct a valid SQL query that can be recognized by a particular relational database.

At the time of this writing, Analysis Services supports the providers in the following sections.

OLE DB Providers

Microsoft OLE DB Provider for Microsoft Jet 4.0

Provider name: Microsoft.Jet.OLE DB. 4.0

Target database: Single .mdb file

Supported platform: x86

Installation note: Installed as part of operating system

Microsoft SQL OLE DB Provider for SQL Server

Provider name: SQLOLEDB

Target databases:

SQL Server 7.0 running on x86

SQL Server 2000 running on x86, ia64

SQL Server 2005 running on x86, x64, ia64

Supported platforms: x86, x64, ia64

Installation note: Installed as part of operating system

Microsoft SQL Native Client OLE DB Provider

Provider name: SNAC

Target databases:

SQL Server 7.0 running on x86

SQL Server 2000 running on x86, ia64

SQL Server 2005 running on x86, x64, ia64

Supported platforms: x86, x64, ia64

Installation note: Distributed as stand-alone installation package available for download from Microsoft Download Center

Microsoft OLE DB Provider for Oracle

Provider name: MSDAORA

Target database: Oracle 9.x running on x86

Supported platform: x86

Installation note: Installed with operating system

Microsoft OLE DB Provider for DB2

Provider name: DB2OLEDB

Target databases:

IBM DB2 for OS/400 V5R1 and later

IBM DB2 UDB for Microsoft Windows 7.1 and later

IBM DB2 UDB for AIX V7.1 and later

IBM DB2 UDB for Linux (KB919646) 8.1 and later

IBM DB2 UDB for Sun Solaris (KB911174) 8.1 and later

Supported platforms:

Windows 2000 Service Pack 4 or later on x86

Windows Server 2003 or later on x86, x64, and IA64 (KB916098)

Windows XP Professional with SP1 or later on x86, x64, and IA64 (KB916098)

Installation notes:

Available only for Enterprise, Developer, and Evaluation editions of SQL Server 2005

Available for installation from Microsoft Download Center

Known issues:

Not able to connect

Error message: "An internal network library error has occurred…SQLCODE is "-379""

Solution:

Not all properties in the list of OLE DB provider properties are assigned values. Open the connection dialog box, and review the list of all properties. A sample connection string could be the following.

Provider=DB2OLEDB;Data Source=PICOLAB032;Persist Security Info=True;Password=;User ID=;Initial Catalog=FM2000;Network Transport Library=TCPIP;Host CCSID=28591;PC Code Page=1208;Network Address=PICOLAB032;Network Port=50000;Package Collection=DB2ADMIN;DBMS Platform=DB2/NT

Column names generated exceed predefined column length

Solution:

Change the db2v0801.xsl cartridge file installed into the Tools location: (C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge)

Server folder: (%Installation Location%\OLAP\bin\Cartridges)

Open the cartridge file, and decrease the value in two of the following lines:

<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>
<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>

OLE DB Provider for Teradata

Provider name: TDOLEDB

Target database: Teradata V2R6 running on x86

Supported platform: x86

Installation note: Available for installation from http://www.teradata.com

Known issue:

The Teradata OLE DB provider does not always properly report the size of a column.

Solution:

For each string attribute, run a SELECT Max(Characters(attribute)) FROM table.

Note those lengths.

Change every key/name-binding information for an attribute, so that it has the correct length.

Microsoft .NET Framework Data Providers

.NET Framework Data Provider for SQL Client

Target databases:

SQL Server 7.0 running on x86

SQL Server 2000 running on x86, x64. ia64

SQL Server 2005 running on x86, x64, ia64

Supported platforms: x86, x64, ia64

.NET Framework Data Provider for Oracle Client

Target databases: Oracle 8.x, 9.x, 10.x

Supported platforms: x86, x64, ia64

Installation note: Requires Oracle client software installed

Known issues:

"Connection failed because of an error in initializing provider. ORA-06413: Connection not open"

ORA-12154: TNS: could not resolve the connect identifier

Solution:

On the x64 platform, Business Intelligence Development Studio (BIDS) and SQL Management Studio (SSMS) run as 32-bit processes. They are installed under: c:\Program Files (x86)\...

There is a known problem when using the Oracle provider from an executable with a local path that includes parentheses.

To fix the problem, install Analysis Server into a location that does not include '(' ')' characters in the path.

To fix the problem connecting to Oracle from BIDS and SSMS, start BIDS with a batch file to put the 32-bit Oracle client first in the path. Launch BIDS without parentheses in the path.

Set path= c:\oracle\product\10.2.0\client_1\bin;%path%
"C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

Conclusion

This white paper covers a variety of client object models supported by Analysis Services when connecting to relational data sources. The example problems and solutions were gathered by members of the Analysis Services team while working with users of Analysis Services.

For more information

You will find an important source of information for troubleshooting relational-database connectivity problems on the SQL Server Analysis Services forum on the Microsoft Developer Network (MSDN).

For more information, see either the SQL Server Developer Center or the Analysis Services section of MSDN.