Data Sources Supported in PowerPivot Workbooks

PowerPivot for Excel can import data from a wide variety of sources. This topic explains the kinds of data sources you can use and provides guidance for identifying which ones work best with a PowerPivot workbook.

You can import data from the data sources in the following table. PowerPivot for Excel does not install the providers that are listed for each data source. Some providers might already be installed with other applications on your computer; in other cases you will need to download and install the provider.

4 In some cases, using the MSDAORA OLE DB provider can result in connection errors, particularly with newer versions of Oracle. If you encounter any errors, we recommend that you use one of the other providers listed for Oracle.

Importing tables from relational databases saves you steps because foreign key relationships are used during import to create relationships between worksheets in the PowerPivot window.

Importing multiple tables, and then deleting the ones you don't need, can also save you steps. If you import tables one at a time, you might still need to create relationships between the tables manually.

Columns that contain similar data in different data sources are the basis of creating relationships within the PowerPivot window. When using heterogeneous data sources, choose tables that have columns that can be mapped to tables in other data sources that contain identical or similar data.

To support data refresh for a workbook that you publish to SharePoint, choose data sources that are equally accessible to both workstations and servers. After publishing the workbook, you can set up a data refresh schedule to update information in the workbook automatically. Using data sources that are available on network servers makes data refresh possible. For more information, see Create a PowerPivot Workbook for SharePoint.

OLE DB providers can sometimes offer faster performance for large scale data. When choosing between different providers for the same data source, you should try the OLE DB provider first.