Using PowerPivot to incorporate publicly available data sets in your PerformancePoint dashboards

  • Article

Windows Azure Marketplace is a great place to find publicly available data for use in your BI solutions. In this post, I'm going to show how convenient it is to use Windows Azure Marketplace to get public data into your PPS dashboards.

You will need:

Step 1: Set up and account and create a key on Windows Azure Marketplace

You will need a Windows Live ID to sign in. If you don't have one already, the Windows Azure Marketplace login page has instructions on how to sign up.

Once logged in, click on "Account Keys" in the left panel. This will navigate to the page which has all your Account Keys. Click on "Add Account Key" to get a new key. You will need a key when you access data from the Azure Marketplace.

On the next page, give your new key a description and click  "Create." The new key will be added to you Account Keys list.

Step 2: Explore the Data

Click on the "Data" tab to see all the data available.

Explore the data sources, or search for a specific one using the search box. Click on the link of the data and then click on "Sign Up" on the description page. 

Build your query in the Service Explorer on the right hand side panel and click "Run Query":

You will be able see the first page of the results on the page.

Step 3: Export to PowerPivot 

Go to the "Export" tab in the Service Explorer. Choose "Excel PowerPivot" option in the Export to Program and click Download.

This will download a .atomsvc file and open Excel PowerPivot Window. The Table Import Wizard in PowerPivot will ask you for your account key. Copy and paste the key you created in Step 1 here, click "Next", select the tables in the next window, and click "Finish"

That's it. You have the data in PowerPivot!

You can now create PivotTables, PivotCharts and save the workbook in a shared location. This workbook can be used as a data source for your PPS dashboards. My previous blog post on Fast Prototyping with PowerPivot  has step-by-step instructions on how to use PowerPivot models as PPS data sources.

Poornima Hanumara | Program Manager | Office BI