Forum Discussion

KJanssens's avatar
KJanssens
Icon for Helper II rankHelper II
6 years ago

Odata fetches more records than needed

I have a Odata feed (from Dynamics 365 Finance and Operations) through which I want to fetch the last X orders.

 

When I fetch the last 9999 orders, it gets fetched quite fast. However, when I want to fetch more than 10k orders, I see (by using Fiddler) that it tries to get ALL orders (in multiple batches of 10k) before it filters out (locally) only the top X orders... This means it wants to fetch millions of records...

 

Is this normal behaviour?

 

Thanks,

Koen

Need Help

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi KJanssens ,

     

    What is the kind of way you use to get last N orders , add url parameter in odata feed or use Table.SelectRows() ? Have you used filter step at the next step of Source step?

     

    Please don't have any Confidential Information or Real data in your reply.


    Best regards,

     

    • KJanssens's avatar
      KJanssens
      Icon for Helper II rankHelper II
      6 years ago

      v-lid-msft  I made some tests:

      Unfiltered

       

      let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderHeaders_table = Source{[Name="SalesOrderHeaders",Signature="table"]}[Data]
in
    SalesOrderHeaders_table

       

         Result:  

      • In Power Query Editor, preview is generated with request "GET /data/SalesOrderHeaders?$top=1000"
      • In Power BI Desktop, the data gets loaded with multiple consequent requests (as expected):
        • GET /data/SalesOrderHeaders
        • GET /data/SalesOrderHeaders?$skip=10000&$top=10000
        • GET /data/SalesOrderHeaders?$skip=20000&$top=10000
        • ... and so on until all records are fetched (which takes ages)

      Filtered in feed request (top 9999)

       

      let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data/SalesOrderHeaders?top=9999", null, [Implementation="2.0"])
in
    Source

       

      Result:

      • Preview generated with request: "GET /data/SalesOrderHeaders?top=9999 "
      • Full data load generates following (too many) requests:
        • GET /data/SalesOrderHeaders?top=9999
        • GET /data/SalesOrderHeaders?top=9999&$skip=10000&$top=10000
        • GET /data/SalesOrderHeaders?top=9999&$skip=20000&$top=10000
        • and so on...

      Filtered in feed request (top 9999) combined with FirstN(9998)

       

      let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data/SalesOrderHeaders?top=9999", null, [Implementation="2.0"]),
    #"Kept First Rows" = Table.FirstN(Source,9998)
in
    #"Kept First Rows"

       

      Result:

      • Preview generated with request: "GET /data/SalesOrderHeaders?top=9999 "
      • Full data load generates only one request (as expected):
        • GET /data/SalesOrderHeaders?top=9999

      Filtered in feed request (top 12345) combined with FirstN(10001)

       

      let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data/SalesOrderHeaders?top=12345", null, [Implementation="2.0"]),
    #"Kept First Rows" = Table.FirstN(Source,10001)
in
    #"Kept First Rows"

       

      Result:

      • Preview generated with request: "GET /data/SalesOrderHeaders?top=12345"
      • Full data load generates following requests (as expected):
        • GET /data/SalesOrderHeaders?top=12345
        • GET /data/SalesOrderHeaders?top=12345&$skip=10000&$top=10000
        • This last request is quite weird though, however it returns the correct amount of records

      Filtered with FirstN(9999)

       

      let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderHeaders_table = Source{[Name="SalesOrderHeaders",Signature="table"]}[Data],
    KeptFirstRows = Table.FirstN(SalesOrderHeaders_table,9999)
in
    KeptFirstRows

       

      Result:

      • Preview generated with request: "GET /data/SalesOrderHeaders?top=1000"
      • Full data load generates following request (as expected):
        • GET /data/SalesOrderHeaders?top=9999

      Filtered with FirstN(10001)

       

      let
    Source = OData.Feed("https://xxx.sandbox.operations.dynamics.com/data", null, [Implementation="2.0"]),
    SalesOrderHeaders_table = Source{[Name="SalesOrderHeaders",Signature="table"]}[Data],
    KeptFirstRows = Table.FirstN(SalesOrderHeaders_table,10001)
in
    KeptFirstRows

       

      Result:

      • Preview generated with request: "GET /data/SalesOrderHeaders?top=1000"
      • Full data load generates following request (too many):
        • GET /data/SalesOrderHeaders?top=10001
        • GET /data/SalesOrderHeaders?$skip=10000&$top=10000
        • GET /data/SalesOrderHeaders?$skip=20000&$top=10000
        • ... and so on ...

      Note that for all of the above scenarios, the first request of a series of requests is always done twice, and it also (almost always) generates requests "GET /data/$metadata"   and "GET /data" which is unnecessary IMHO...