Load Data to Power Pivot from Uncommon Data Sources

By Michael Alexander

Besides the data sources that are most important to a majority of Excel analysts, there are a few more data sources that Power Pivot is able to connect to and load data from.

Although these data sources are not likely to be used by your average analyst, it’s worth dedicating a few lines to each one, if only to know that they exist and are available if ever you should need them:

  • Microsoft SQL Azure: SQL Azure is a cloud-based relational database service that some companies use as an inexpensive way to gain the benefits of SQL Server without taking on the full cost of hardware, software, and IT staff. Power Pivot can load data from SQL Azure in much the same way as the other relational databases.
  • Microsoft SQL Parallel Data Warehouse: SQL Parallel Data Warehouse (SQL PDW) is an appliance that partitions very large data tables into separate servers and manages query processing between them. SQL PDW is used to provide scalability and performance for big data analytics. From a Power Pivot perspective, it’s no different than connecting to any other relational database.
  • Microsoft Analysis Services: Analysis Services is Microsoft’s OLAP (Online Analytical Processing) product. The data in Analysis Services is traditionally stored in a multidimensional cube.
  • Report: The curiously named Report data source refers to SQL Server Reporting Services reports. In a very basic sense, Reporting Services is a business intelligence tool used to create stylized PDF-style reports from SQL Server data. In the context of Power Pivot, a Reporting Services Report can be used as a data-feed service, providing a refreshable connection to the underlying SQL Server data.
  • From Windows Azure Marketplace: Windows Azure Marketplace is an OData (Open Data Protocol) service that provides both free and paid data sources. Register for a free Azure Marketplace account and you get instant access to governmental data, industrial market data, consumer data, and much more. You can enhance your Power Pivot analyses by loading the data from the Azure marketplace using this connection type.
  • Suggested Related Data: This data source reviews the content of the Power Pivot data model and, based on its findings, suggests Azure Marketplace data that you may be interested in.
  • Other Feeds: The Other Feeds data source allows you to import data from OData web services into Power Pivot. OData connections are facilitated by XML Atom files. Point the OData connection to the URL of the .atomsvcs file and you essentially have a connection to the published web service.