Consuming OData Feed using Microsoft PowerPivot

June 20, 2010

Consuming OData Feed using Microsoft PowerPivot

PowerPivot is a data analysis
add-inConsuming OData Feed Using Microsoft PowerPivot for excel that brings to
it computational power.
It also helps “to create compelling
self-service BI solutions, facilitates
sharing and collaboration
on user-generated BI solutions”
(taken from the PowerPivot site).
This post will help you to understand how to consume OData feed
from PowerPivot in order to use the data the OData exposes.

The OData Feed

OData exposed feeds can be found in the site.
Under the Producers tab you will find OData producers which expose
their data using the OData protocol. In my example I’ll use the
Netflix’s feed that can be found here:

Consuming Netflix OData Feed using PowerPivot

In order to use PowerPivot for excel you first need to download it.
There are some prerequisites for doing that including having Office
2010 installed on your machine. After installing PowerPivot open your
excel and go to the PowerPivot tab and open PowerPivot window.
One of the options in the PowerPivot window is to get data from
data feeds (you will see the OData protocol logo beside it):
PowerPivot Window

Use the From Data Feeds menu item in order to open the Table
Import Wizard
Table Import Wizard 

In the Data Feed Url textbox insert the relevant OData feed (in my
case the Netflix’s feed Url). Press Next to import the data definition
and then you will see the entity sets the feed exposes as tables:
Data Feed Tables

Choose the tables you want to import and press Finish to end the
process and import the tables. This can take some time depending
on the size of the exposed data set.
I chose the Genres set and this is the result:
Imported Results
Now I can use the PowerPivot’s features to build my relevant report
or to manipulate the data I’ve received.


Let sum up, I showed how to consume OData feed using Microsoft’s
PowerPivot. The OData ecosystem is growing very fast and as you
could see from this example it consumers can also be BI tools like
PowerPivot and many more. This is one of the reasons to start
learning this protocol now.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>