none
Problems with Azure Datamarket sources RRS feed

  • Question

  • Hi,

    I've been having some trouble with using Azure Datamarket as a data source in the November update.

    First of all, using the dedicated Azure Datamarket option, I ran into some big performance issues with a large data set. While everything works ok on small tables, as soon as I try to work with a very large table (eg start applying filters etc) I get the feeling that Power Query wants to download entire table locally before it does anything. I guess this is because the functionality to push things like filters back to the OData query hasn't been implemented yet?

    Anyway, I thought I would construct my own OData URIs instead. However, I found that when I did this and used OData.Feed(), I got some very strange behaviour. First of all I found that the URI I gave to Power Query was ignored and I got the 'default' results instead (I know the URI was correct because I used it successfully in the regular Excel OData import option on the Data tab). Then when it did decide to start working, I found that it refreshed fine in the query window but when I clicked out of that window and went back to Excel and tried to refresh, the refresh never completed and Excel hung.

    Are there some known issues here? Is using hand-written OData against the Azure Datamarket allowed?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Friday, November 29, 2013 1:37 PM

Answers

  • Query folding definitely works with Datamarket sources. Are you able to collect network traces using a tool like Fiddler? We try to optimize larger sources by issuing requests for ranges of data in parallel, and that may be causing a problem. If you're able to use OData.Feed directly against Datamarket, you can also try to disable paging manually by passing an additional parameter to the call e.g. OData.Feed("http://some/odata/url", null, false).

    I believe the bug with the hand-written OData URL ignoring any query parameters has been fixed and that the fix should be in the next preview.

    Sunday, December 1, 2013 1:27 PM

All replies

  • Query folding definitely works with Datamarket sources. Are you able to collect network traces using a tool like Fiddler? We try to optimize larger sources by issuing requests for ranges of data in parallel, and that may be causing a problem. If you're able to use OData.Feed directly against Datamarket, you can also try to disable paging manually by passing an additional parameter to the call e.g. OData.Feed("http://some/odata/url", null, false).

    I believe the bug with the hand-written OData URL ignoring any query parameters has been fixed and that the fix should be in the next preview.

    Sunday, December 1, 2013 1:27 PM
  • Thanks Curt, I'll take a look with Fiddler and let you know what I can see.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Monday, December 2, 2013 9:34 AM