Consuming OData Feed using Microsoft PowerPivot
PowerPivot is a data analysis
add-in 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 http://www.odata.org/ 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: http://odata.netflix.com/Catalog/.
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):
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:
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:
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.