PowerPivot Feed OData and JSON

Question

Hello

I have a simple question, can PowerPivot get the data from a OData feed in json format?

Answer 1

Hi Casual,

The answer is YES. Here are some articles for your reference about ODdata and consuming OData Feed by using PowerPivot:
http://social.msdn.microsoft.com/Forums/is/ssdt/thread/877fba5b-3e3f-423b-b62e-30261e49dfb1
http://blogs.microsoft.co.il/blogs/gilf/archive/2010/06/20/consuming-odata-feed-using-microsoft-powerpivot.aspx

Regards,

---

Elvis Long
TechNet Community Support

Answer 2

Thanks for the reply but the question was if PowerPivot can get the data in JSON format, which is compacter than XML, the size of the JSON format is less than the half of the Atom format, which make the data transfer twice faster than the Atom format.

OData define two formats: Atom (http://www.odata.org/documentation/atom-format) and JSON (http://www.odata.org/documentation/json-format).

In ASP.Net you can create a DataService to create OData webservice and returned format is controlled from the request header "Accept" parameter (http://msdn.microsoft.com/en-us/library/ff478141.aspx), if the "Accept" parameter is "application/json" the DataService returns the data in JSON format.

From my tests PowerPivot queries the OData webeservice without the "Accept" header-parameter and the returned format is Atom (XML). So my question is, can PowerPivot get the data from an OData webservice in JSON format? Because in the advanced options of the Data Feed Connection I didn't find any option to specify the format, like JSON or Atom, or to set a custom request header parameter, like the "Accept" parameter.

Answer 3

Bump for an answer (specific to a JSON formatted ODATA feed) as I'm interested as well.

Answer 4

Has anybody experimented with the JSON format?

Thanks!

---

Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Answer 5

I've made some tests and Excel PowerPivot could process the ODATA only when in XML format. With json, I got an error.

Answer 6

Power Query supports JSON natively and can be used to load data directly into Power Pivot

though, this will at the moment only work on the desktop and not in an SharePoint environment

Power Query download:
http://www.microsoft.com/en-us/download/details.aspx?id=39379

hth,
gerhard

---

- www.pmOne.com -

Answer 7

Hi

I can't find an data source for importing json.

Where can I find that?

regards

Christoph

Answer 8

Hello Christoph,

Gerhard is talking about the new "Power Query" (former Data Explorer) for Excel 2013 to import data from a oData-Json web service into Excel to process it further with PowerPivot.

I did also some tests with OData-Json in Excel 2010 / PowerPivot and I also don't get it working. Even with Excel - External Data => "From Web Query" you can't import JSON data, only Xml.  

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 9

Hi

I've done also tests with OData in Excel 2012 and Power Query but was not possible.

:-(

Best regards

Christoph

Answer 10

Hi using Power Query I can import the sample JSON feed http://date.jsontest.com/ without any problem

simply go to Power Query --> From Web --> Paste "http://date.jsontest.com/" as URL

can you check if this works for you?

-gerhard

---

- www.pmOne.com -

Answer 11

Hi

Ah yes, from Web, I can import this exmaple.

But my data is a list of Products.

[{"Id":1,"Name":"der Name","Category":null,"Price":0.0,"Matchcode":"test"},{"Id":2,"Name":"der Name2","Category":null,"Price":0.0,"Matchcode":"test2"}]
When I import these data, I get one column, 2 Rows with [Record] in the cells.
In my case, I have a lot of data tables and it's not realy handy, when the customers need to import every table separatly.
With odata/xml, the customer can see all "data tables" and select the currently needed. My problem with ASP.NET WebAPI OData is the fix data model.
II need dynamic classes/data objects.
Christoph

Answer 12

it seems that JSON feeds currently must only contain exactly one lists/tables
opposed to XML where your XML may contain various lists/tables

in order to work with your list of [Record]s, you first have to convert that list to a table by left-clicking on the column and using "To Table"
then you can expand that column to get a real table to work with

hth,
gerhard

---

- www.pmOne.com -

Answer 13

Hi Gerhard

Thanks for tests. So I have to found an solution with web.api ODATA to solve my problem with dynamic data.

Christoph