Connecting PowerPivot to Different Data Sources in Excel 2010
Summary: Learn how to import data from different sources by using Microsoft PowerPivot for Excel 2010.
Applies to: Excel 2010 | Office 2010 | VBA
Published: February 2011
Provided by: Steve Hansen, Microsoft Visual Studio MVP and founder of Grid Logic.
The PowerPivot add-in for Microsoft Excel 2010 can import data from many data sources, including relational databases, multidimensional sources, data feeds, and text files. The real power of this broad data-source support is that you can import data from multiple data sources in PowerPivot, and then either combine the data sources or create ad hoc relationships between them. This enables you to perform an analysis on the data as if it were from a unified source. Download PowerPivot for Excel 2010 This article discusses three of the data sources that you can use with PowerPivot:
See the Read It section in this article for a comprehensive list of data sources that PowerPivot supports. Microsoft Access Microsoft Access 2010 is a common source of data from analytical applications. By using PowerPivot, you can easily connect to an Access database and retrieve the information that you want to analyze. To use Microsoft Access with PowerPivot
Note Importing data from a Microsoft SQL Server database requires a connection to a SQL Server instance, but is otherwise identical to importing data from a Microsoft Access database. Microsoft Azure Marketplace DataMarket The Microsoft Azure Marketplace DataMarket offers a large number of datasets on a wide range of subjects. Note To take advantage of the Azure Marketplace DataMarket, you must have a Windows Live account to subscribe to the datasets. To use Microsoft Azure Marketplace DataMarket data in PowerPivot
Note That data from the Azure DataMarket is exposed by using Windows Communication Foundation (WCF) Data Services (formerly ADO.NET Data Services). If you select the From Data Feeds option when you retrieve external data, you can connect to other WCF Data Services data sources that are not exposed through the Azure DataMarket. Text File PowerPivot lets users import text files with fields that are delimited by using a comma, tab, semicolon, space, colon, or vertical bar. To load data from a text file
One of the compelling features of PowerPivot is that it enables you to combine data from multiple sources and then use the resulting dataset as if the data were unified. For example, suppose that you manage a portfolio of office buildings. Suppose further that you have a SQL Server database that contains generic information about the buildings in your portfolio, an Analysis Services cube that contains financial information such as operating expenses for the buildings, and access to crime information via the Azure MarketPlace DataMarket. By using PowerPivot, you can combine all of this data in PowerPivot, create ad hoc relationships between the sources, and then analyze it all in Excel as if it were a single data source. You can use PowerPivot to leverage data from the following supported data sources in your next analysis project:
Tip This is not an exhaustive list; there is also an Others option that you can use to create a connection to data sources via an OLE DB or ODBC provider. This option alone significantly increases the number of data sources that you can connect to. |
> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/298dfb85-e080-4564-80b4-c2b16e1adf5f] Length: 00:08:36
About the Author Steve Hansen is the founder of Grid Logic, a Minnesota-based consulting firm specializing in business intelligence and information worker solutions. A developer, frequent author and speaker at technical conferences, Steve is a Microsoft MVP for his work with Visual Studio. One part code jockey, one part finance geek; Steve also has an MBA from the University of Minnesota with a concentration in finance. Steve would like to thank Grid Logic's Sean Van Dyk for his assistance with this article. |