search

scraperwiki / odata-tool

Access your ScraperWiki data in tools like Tableau, QlikView and Excel Power Query
3 stars 0 forks source link

OData connector does not work with Excel Power Query #10

Open IanHopkinson opened 10 years ago

IanHopkinson commented 10 years ago

Excel Power Query will read this OData source description: http://odata.linium.com/Gameday/Gameday.svc/ But then fails on the associated tables with a 500 error

zarino commented 10 years ago

Could you demonstrate to me this week?

IanHopkinson commented 10 years ago

Yes! Ideally I'd like you to transmit your Odataf-fu to someone (morty?) So that we can continue development seamlessly. I think the problem is that Power Query expects to get the blah/thing.svc/ which lists available tables as it's entry point whereas Tableau goes straight to the table.

On 16 March 2014 11:53, Zarino Zappia notifications@github.com wrote:

Could you demonstrate to me this week?

Reply to this email directly or view it on GitHubhttps://github.com/scraperwiki/odata-tool/issues/10#issuecomment-37755132 .

Dr Ian Hopkinson Lead Data Scientist ScraperWiki Ltd Liverpool

View my linkedin profilehttp://uk.linkedin.com/pub/ian-hopkinson/17/2a4/394/

zarino commented 10 years ago

If that's the case, then have you tried just removing the last bit of the OData URL you're given by ScraperWiki?

https://ds-ec2.scraperwiki.com/dzr4aky/c56048adc9c74a8/cgi-bin/odata

IanHopkinson commented 10 years ago

I get: DataSource.Error: OData: The feed's metadata document appears to be invalid. if I do that

On 16 March 2014 12:02, Zarino Zappia notifications@github.com wrote:

If that's the case, then have you tried just removing the last bit of the OData URL you're given by ScraperWiki?

https://ds-ec2.scraperwiki.com/dzr4aky/c56048adc9c74a8/cgi-bin/odata

Reply to this email directly or view it on GitHubhttps://github.com/scraperwiki/odata-tool/issues/10#issuecomment-37755328 .

Dr Ian Hopkinson Lead Data Scientist ScraperWiki Ltd Liverpool

View my linkedin profilehttp://uk.linkedin.com/pub/ian-hopkinson/17/2a4/394/

IanHopkinson commented 10 years ago

I can see two differences between that and a feed I know gets the right "frontpage" (http://odata.linium.com/Gameday/Gameday.svc/)

  1. Naming and trailing slash for xml:base in "working" system
  2. href's in "working" example are not full URLs

I'm guessing (1) more likely to be a problem.

I'm slightly dubious of the "working" system since the endpoints don't work

On 16 March 2014 12:08, Ian Hopkinson ian@scraperwiki.com wrote:

I get: DataSource.Error: OData: The feed's metadata document appears to be invalid. if I do that

On 16 March 2014 12:02, Zarino Zappia notifications@github.com wrote:

If that's the case, then have you tried just removing the last bit of the OData URL you're given by ScraperWiki?

https://ds-ec2.scraperwiki.com/dzr4aky/c56048adc9c74a8/cgi-bin/odata

Reply to this email directly or view it on GitHubhttps://github.com/scraperwiki/odata-tool/issues/10#issuecomment-37755328 .

Dr Ian Hopkinson Lead Data Scientist ScraperWiki Ltd Liverpool

View my linkedin profilehttp://uk.linkedin.com/pub/ian-hopkinson/17/2a4/394/

Dr Ian Hopkinson Lead Data Scientist ScraperWiki Ltd Liverpool

View my linkedin profilehttp://uk.linkedin.com/pub/ian-hopkinson/17/2a4/394/

zarino commented 10 years ago

It could be even simpler: ScraperWiki doesn't generate a $metadata document, since it isn't required by the OData spec. But it might be required by Power Query. We'll take a look this week.

zarino commented 10 years ago

Ideally I'd like you to transmit your OData-fu to someone (morty?)

@IanHopkinson I don't know how busy you are, but it might actually be useful for us two to do it. The OData spec is pretty simple, and doesn't require any special programming knowledge to understand. Same goes for the tool Sophie and I made.

drj11 commented 10 years ago

So far it looks like we actually need to implement the $metadata endpoint: http://social.technet.microsoft.com/Forums/en-US/dbef427b-552f-4211-95ac-2f5cc3b6222e/datasourceerror-odata-the-feeds-metadata-document-appears-to-be-invalid-details-null?forum=powerquery

But exactly what we need to implement is unclear.

drj11 commented 10 years ago

I think the best bet is to look at the example metadata document http://services.odata.org/OData/OData.svc/$metadata and implement as much as that as we can.

First I think we should estimate how long that might take, because it's clearly not a quick task.

drj11 commented 10 years ago

The Big Risk here is that it's only possible to validate by trying to plug it into Excel Power Query. So testing / validation / debugging are going to take ages.

Another risk is that sqlite doesn't require you to type columns in a table, whereas it looks like Excel Power Query might. We might be able to workaround that by either saying everything is a string, or checking all of the column values, or ignoring columns that don't have a single type. In practice most tools will generate tables that have well-typed columns. Note for implementors: SELECT DISTINCT TYPEOF(col) FROM table; will return a list of the types used in column col.

I suggest a strategy of implementing each tag type in the example $metadata document. It looks a bit scary, but each OData Thing basically corresponds to a Thing in RDBMS terminology:

EntityType is basically the schema for a table (there are several of these). Property is basically a named column in a table, these are typed using attributes of the element. Key seems to be the unique key for a table.

EntityType elements seem to describe the type of a table; actual tables (instances) are described with a single EntityContainer element and several EntitySet elements. Predictably, there is one EntitySet per EntityType (in other words, whilst the system is flexible enough to describe more than one table having the same type, this doesn't happen, at least not in the example that we have).

[ ] ½ pair day. implement a 404 for the $metadata endpoint (this is simple and gives a better error message) [ ] ½ pair day. describe a column from a table with a Property element (make them all Edm.String if necessary?) [ ] ½ pair day. describe a table with a EntityType element, and a EntitySet element. [ ] ½ pair day. glue all this together inside a couple of Schema elements and the edmx:Edmx and edmx:DataServices elements. [ ] ½ pair day. Plug Into Excel and test. [ ] ½ pair day. polish (translate column names nicely, set the right type for columns, not just Edm.String)