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.