Connecting PowerPivot to Different Data Sources in Excel 2010

  • Article

Office Visual How To

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.

Overview

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

Code It

This article discusses three of the data sources that you can use with PowerPivot:

  • Microsoft Access

  • Microsoft Azure Marketplace DataMarket

  • Text files

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

  1. Open a new workbook in Excel and then click the PowerPivot tab.

  2. Click the PowerPivot Window button on the ribbon.

  3. In the Get External Data group, click From Database, and then select From Access.

  4. Click the Browse button, select the appropriate Access database, and then click Next.

    At this point, you have two options.

    • You can import data from a table that is in the database or from a query that is in the database. If you do not want all of the records from a table, you can apply a filter to limit the records that are returned.

    • You can import the data by using an SQL query. Unlike the first option, where you can return data based on a query that is defined in the database, you use the second option to specify an SQL query in PowerPivot that is executed against the database to retrieve the records.

    The next steps show how to retrieve data from tables and queries in the database.

  5. Select the option, Select from a list of tables…, and then click Next.

  6. Select the tables that you want to import.

  7. By default, all of the columns and records in the selected tables are returned. To filter the records or specify a subset of the columns, click Preview & Filter.

  8. To automatically include tables that are related to the selected tables click Select Related Tables.

  9. Click Finish to load the data into 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

  1. In PowerPivot, in the Get External Data group, select From Azure Datamarket.

    Important

    If you do not see the option, you have an earlier version of PowerPivot and should download the latest version of PowerPivot.

    Figure 1. PowerPivot, Connect to an Azure DataMarket Dataset


    Azure Marketplace DataMarket, Service Root URL

  2. Click View available Azure DataMarket datasets to browse the available datasets.

  3. After you locate a dataset, click the Subscribe button in the top-right part of the page. Clicking the button subscribes you to the dataset and enables you to access the data in it. After you subscribe, go back to the dataset home page.

  4. Click the Details tab. Partway down the page, locate the URL under Service root URL and copy it into the PowerPivot import wizard. This URL is the dataset URL that PowerPivot uses to retrieve the data.

    Figure 2. Azure Marketplace DataMarket, Service Root URL


    Azure Marketplace DataMarket, Service Root URL

  5. To locate the account key that is associated with your account, click Find in the Table Import Wizard to open a page that displays the key. (You might have to log in first to view the key.)

  6. After you locate the key, copy and paste it into the import wizard and then click Next to display a list of available tables. From here, you can modify the tables and their corresponding columns to select which data to import.

  7. Click Finish to import the data.

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

  1. In the Get External Data group in the PowerPivot window, Select From Text.

  2. Click Browse, and then navigate to the text file that you want to import.

  3. If the file includes column headings in the first row, select Use first row as column headers.

  4. Clear any columns that you do not want to import.

  5. To filter the data, click the drop-down arrows in the field(s) that you want to use as a filter and select the field values to include.

  6. Click Finish to import the data.

Read It

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:

  • Microsoft SQL Server

  • Microsoft SQL Azure

  • Microsoft SQL Server Parallel Data Warehouse

  • Microsoft Access

  • Oracle

  • Teradata

  • Sybase

  • Informix

  • IBM DB2

  • Microsoft Analysis Services

  • Microsoft Reporting Services

  • Data Feeds (WCF Data Services formerly ADO.Net Data Services)

  • Excel workbook

  • Text file

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.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/298dfb85-e080-4564-80b4-c2b16e1adf5f]

Length: 00:08:36

Explore It

 

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.