OData Tool Spotlight – PowerPivot for Excel 2010

in my endeavor to explore open data protocol – i have been writing a blog series titled tool spotlight. in these series i try to take a tool which supports odata and play around with the tool. today we will be checking out one such tool. the tool is known as “ powerpivot for excel 2010 ”. so rest of this post will be to know more about powerpivot. lets start

here is what msdn documentation says about powerpivot:

powerpivot for excel is an add-in to excel 2010 that is used for adding and integrating large amounts of data in excel workbooks. the data that you add to the workbook is stored internally, as an embedded powerpivot database inside the .xlsx file.

as the documentation says this is an add-in to excel. although this add-in is intended to be used with large amounts of data, it does have a feature to play around with odata. we will see in further sections as to how to do this.

powerpivot for excel 2010 is available as a free download at the following location:

www.powerpivot.com

once you install the add in excel will now get a new ribbon tab by name “powerpivot”. here is a snap shot of excel after the add in has been installed:

if you click the “ powerpivot window ” button which is the first button on the left hand side, a new window will open up. this is the powerpivot add in. here is the snapshot of the same:

powerpivot allows us to work with odata feeds in two ways. they are:

• odata data feeds – publicly available odata feeds like netflix
• azure datamarket datasets – azure datamarket exposes the datasets as odata

we will see how to work with above data sets one by one.

first open excel, select powerpivot tab and click on the powerpivot window button. in the powerpivot window, in “get external data” tab group click “from data feeds”.

we will be presented with “table import wizard”. provide a name in the “friendly connection name” textbox. for my example i will be using publicly available odata feed from netflix. provide the url in the “data feed url” text box. the netflix odata feed is available at http://odata.netflix.com/catalog . note that “/catalog” is terms of odata protocol is known as service root document or in terms of atom is a atom service document. powerpivot is intelligent enough to detect that and will provide the options accordingly. here is a screen shot of table import wizard:

note the options in this window. browse button will allow us to select a locally stored atom service document (*.atomsvc) or locally stored atom feed (*.atom).

test connection button will try to connect to the data source and will let us know whether it was a success or not. the advanced button will let us play around with advanced properties which we can set on the request.

now if the data feed url is set to http://odata.netflix.com or http://odata.netflix.com/catalog here is what clicking the next button will look like:

note that powerpivot was able to detect that the url provided was a service root document and it has figured out the datasets available. genres, languages, people, titleaudioformats, titleawards, titles, titlescreenformats are all datasets available as aprt of netflix. here we can select all or one of the dataset and get the data down to powerpivot. there is a preview & filter option available. with this we will be given a sample data from the dataset selected and we can filter the columns we need.

as you can see there is a checkbox on each column. this allows you to either bring in the column or not for visualization. when we click finish on the table import wizard the selected data set will be pulled down from the source to inside the powerpivot.

for the example i will be working with titles dataset and since netflix has huge collection, i will be bringing down only 100 titles. here is the url i am using: http://odata.netflix.com/v2/catalog/titles?$top=100&$orderby=releaseyear desc

here is what the data looks like after pulling it down to powerpivot:

connecting to azure datamarket data feeds:

about azure data market:

if your first question at this moment is “ what is azure data market ”, here is the official definition:

the windows azure™ marketplace is an online market buying, and selling finished software as a service (saas) applications and premium datasets. the windows azure marketplace helps connect companies seeking innovative cloud based solutions with partners who have developed solutions that are ready to use.

azure data market originally known as azure marketplace allows you to browse premium datasets which are all exposed as odata. you can browse for a dataset which is offered free of cost. log in to data market with your live id and subscribe for a data set. once you sign up for data market you will be getting your own account key. this is required to consume the data sets that you subscribe. you can browse for data sets here: https://datamarket.azure.com/browse/data . i will be using the following data sets for this post: https://api.datamarket.azure.com/data.ashx/stockviz/capitalmarketanalyticsindia/liveprices?$top=100

use power pivot to connect to data feed:

in power pivot window, click on the button “from azure datamarket”. check the below screenshot:

you will be presented with “table import wizard”. you will need to have your data market data set feed url handy now. also you need to have your account key. here is the table import wizard:

this is the same table import wizard we saw in the previous section. so you will find the same features here like – advanced properties, browse, test connection. only change is the security settings section where you need to provide your account key. once you finish the import the data set will be pulled into the power pivot. here is the screenshot:

as you can see, importing data into power pivot whether from a public feed or azure data market is a seamless experience. now lets see what we can do with the data.

pivot charting:

after you have pulled down the data from the feed to power pivot, you can use pivot table option and create a pivot chart from the data you just downloaded. in this example i am using the netflix data that we pulled in the first section. here is how you add a pivot chart:

the pivot char will be added to your main workbook. here is a chart i was able to design from the data we downloaded. i have plotted a graph for average rating vs. number of movies under that rating. i have also added slicer like rating i.e. parental guidance, universal etc. and release year. here is the screenshot of the pivot chart:

summary:

in this blog post we took an overview of how easy it is to use power pivot add in for excel 2010 to consume odata. we saw how power pivot provides us with an option to preview and filter the data before consuming it. we also saw how power pivot makes the experience of connecting to a public feed vs connecting to a data market feed so seamless. also if you a person who is entitled with creating meaningful charts out of the data you download, power pivot gives you the ability to create pivot charts and within no time you have a meaning to the data you downloaded. i highly recommend playing around with power pivot. i certainly believe odata and power pivot make it a deadly combination for business intelligence on the fly.

hope this blog post provides you with some learning on odata and power pivot. do let me know of your feedback if any.

as usual, till next time – happy coding. code with passion, decode with patience.