Forum Discussion

trevorgermain's avatar
trevorgermain
Advocate I
8 years ago
Solved

Too many requests in Web.Contents. Can I share a request?

Hi,

 

I am querying a web api using Web.Contents in a loop to get paged data. The Api has a throttled request limit of 300 requests per 10 minutes.

 

 

{
    "results": {
        "timesheets": {
            "123456": {
                "id": "123456",
                "job_code": "0001",
                "user": "9999"
            }
        }
    },
    "more": "true",
    "supplemental_data": {
        "job_codes": {
            "0001": {
                "name": "Job 1"
            }
        },
        "users": {
            "9999": {
                "name": "Trevor"
            }
        }
    }
}

I use List.Generate, combined with Web.Contents to get all of the data for a specific date range, using the "more" value to determine if more pages are available.

 

To model, The "timesheets" records become my facts, and the supplemental data becomes my dimensions. The main query creates my fact table, then I reference that query and transform to extract my users and jobcodes dimensions.

 

By referencing the main query, I wind up making the paged Api calls 3x (one for each table). Ideally, I'd like to have the Api called only once, and populate my 3 tables without having to go back to the source. Is there any way to "cache" the Api requests, and reuse them as the source for multiple queries?

 

  • trevorgermain's avatar
    trevorgermain
    8 years ago

    I ended up writing a web api as an intermediary that caches responses in a Redis cache. It seems to work really well for this scenario.

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee

    Hi trevorgermain,

    By referencing the main query, I wind up making the paged Api calls 3x (one for each table). Ideally, I'd like to have the Api called only once, and populate my 3 tables without having to go back to the source. Is there any way to "cache" the Api requests, and reuse them as the source for multiple queries?

    As far as I know, there isn't a way to do it currently.

     

    Here is a good article about Referenced Queries And Caching In Power BI And Power Query, which could help you better understand this issue. :smileyhappy:

     

    Regards

    • trevorgermain's avatar
      trevorgermain
      Advocate I

      I ended up writing a web api as an intermediary that caches responses in a Redis cache. It seems to work really well for this scenario.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee

        Hi trevorgermain,

         

        Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread(which could also help others who have similar issue easily find the answer)? :smileyhappy:

         

        Regards