The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

11.3. Integrating with Non-SQL Server 2005 Components

You can build your entire DW/BI system using Microsoft's technology. Microsoft has added some features to make a Microsoft-only stack work "better together," but these are surprisingly few. If you have good reasons for using a different technology for one or more components, you'll be in good company with a heterogeneous system.

In this section we outline a few of the issues you may encounter with developing and deploying a heterogeneous system.

11.3.1. Replacing the Relational Database

Many customers use Microsoft's business intelligence technology with a non-Microsoft relational database. Among our consulting clients, Oracle is the most common relational database used with SQL Server, although we've seen plenty of IBM's DB2 and a smattering of other technologies.

All of SQL Server's business intelligence components communicate with each other, and the relational database, through a variety of data providers, including OLE DB, ODBC, or .NET providers. The popular databases including those mentioned previously all have OLE DB providers written either by Microsoft, by the relational database vendor, or by a third party—sometimes all three. Any less common database technology is very likely to have an ODBC driver available. All of these options, combined with Microsoft's OLE DB for ODBC and Flat Files providers, enable you to write and read data from a huge variety of sources. (Although performance can vary significantly).