Data Sources Supported in PowerPivot Workbooks
PowerPivot for Excel can import data from a wide variety of sources. After you have imported the data, you can refresh the data at any time to reflect any changes to the data that have occurred in the source. 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.
You can also link to tables in Excel and copy and paste data from applications, like Excel and Word, that use an HTML format for the Clipboard. For more information, see Add Data by Using Excel Linked Tables and Copy and Paste Data to PowerPivot.
Microsoft Access 2003, 2007, 2010.
.accdb or .mdb
ACE 14 OLE DB provider
SQL Server relational databases
Microsoft SQL Server2005, 2008, 2008 R2; Microsoft SQL Azure Database 2
OLE DB Provider for SQL Server
SQL Server Native Client OLE DB Provider
SQL Server Native 10.0 Client OLE DB Provider
.NET Framework Data Provider for SQL Client
SQL Server Parallel Data Warehouse (PDW) 3
OLE DB provider for SQL Server PDW
Oracle relational databases
Oracle 9i, 10g, 11g.
Oracle OLE DB Provider
.NET Framework Data Provider for Oracle Client
.NET Framework Data Provider for SQL Server
MSDAORA OLE DB provider 4
Teradata relational databases
Teradata V2R6, V12
TDOLEDB OLE DB provider
.Net Data Provider for Teradata
Informix relational databases
Informix OLE DB provider
IBM DB2 relational databases
Sybase relational databases
Sybase OLE DB provider
Other relational databases
OLE DB provider or ODBC driver
.txt, .tab, .csv
ACE 14 OLE DB provider for Microsoft Access
Microsoft Excel files
Excel 97-2003, 2007, 2010
.xlsx, xlsm, .xlsb, .xltx, .xltm
ACE 14 OLE DB provider
Microsoft SQL Server 2008 R2 Analysis Services
xlsx, xlsm, .xlsb, .xltx, .xltm
(used only with PowerPivot workbooks that are published to SharePoint farms that have PowerPivot for SharePoint installed)
Analysis Services cube
Microsoft SQL Server 2005, 2008, 2008 R2 Analysis Services
(used to import data from Reporting Services reports, Atom service documents, and single data feed)
Atom 1.0 format
Any database that is exposed as an ADO.Net Data Services Framework service, such as Microsoft SQL Server 2005, 2008, 2008, 2008 R2 Reporting Services report
.atomsvc for a service document that defines one or more feeds
.atom for an Atom web feed document
Microsoft Data Feed Provider for PowerPivot
.NET Framework data feed data provider for PowerPivot
Office Database Connection files
1 You can also use the OLE DB Provider for ODBC.
2 For more information about SQL Azure, see the web site SQL Azure.
3 For more information about SQL Server PDW, see the web site SQL Server 2008 R2 Parallel Data Warehouse.
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.