REST api to google big query crashes

[Johan]

This error occurs 7 out of 10 refresh runs:

ODBC: ERROR [HY000] [Microsoft][DriverOAuthSupport] (8701) Error interacting with REST API: Couldn't connect to server ERROR [HY000] [Microsoft][DriverOAuthSupport] (8701) Error interacting with REST API: Couldn't connect to server Table: 

If it stops, it occurs on different tables, so it's not the connection, permission or query.

Anyone an idea?

[Johan · Accepted solution]

we think it was related to having multiple refreshes on the same tables in GBQ at the same time. Since we have organised the refreshes to not run simultaneously it didn't happen anymore

[Anonymous]

Hey Johan,

I'm not sure I understand your explanation. You say you spread out the "refreshes" on your table in BQ. Do you mean the refreshes triggered by the powerBI service? I don't know of a way to control when particular data is being refreshed. For me the whole file just refreshes at once. Can you explain a little how you managed to do this?

My situation is that powerBI is refreshing 8 views on the same table, and I wonder if that is similar to your situation.

Thanks!

Jaap

[Johan]

Sure I can give more details.

We were loading the same report (prod and test) at the same time it appeared. So 2 refreshes loading the same tables/views. After stop doing that, the problem did not occur anymore.

Hope this helps.

Johan

[Anonymous]

OK I see, and when everything succeeded, were you still using multiple views on the same table? I'm a bit worried that that could be causing my issue. That because all of my 10 views use the same table, in the background it connects to that table 10 times at the same time.

I just checked Dataflows, and what I see there is that dataflows doesnt load any of my views in parallel, it always loads them serially, so 1 at a time. That might be why it works OK in Dataflows. I just don't understand why it always works in powerBI desktop, because that definitely tries to load everything in parallel, but always works 😞

[Johan]

yes, seems like the api gets unstable when you run multiple queries on the same source table at the same time. Try to avoid that.

[ju_jacobowiski]

Hi, @Johan . Did you manage to sort it out? Since Sunday my refresh stopped working in PowerBI Service. If I try to refresh, the error happens again but with another table.

[Anonymous]

Hey Ju_jacobowiski.

Can you try adding the billing project to the powerquery connection string?

They describe it in the documentation at the very bottom. For me the billing project was the same as the firebase project. (In the form of "monkey-c7c69") After I added that, my refreshes seem to have stopped failing. I don't understand why, but it seems to work. (This might only help if you actually have a project that supports billing)

Quote from the documentation: 

You can use Power BI with the Google BigQuery Billing Project. By default, Power BI uses the first project from the list returned for the user. To customize the behavior of the Billing Project when using it with Power BI, take the following steps:

• Specifying the following option in the underlying M in the Source step, which can be customized by using Power Query Editor in Power BI Desktop:

  Source = GoogleBigQuery.Database([BillingProject="Include-Billing-Project-Id-Here"])

[ju_jacobowiski]

@Anonymous 

The billing project-id is the Project ID - displayed in the project info card at BigQuery's home page? If yes, I tried it but the error remains. 😞

[ju_jacobowiski]

Hi, all. Thanks @Anonymous I managed to sort out this issue. In case someone faces the same problem, here what I've done:

1) Load BigQuery data into PowerBI dataflows, using it as a 'mini' data source.
2) Add the billing-id in the source step (at PowerBi dataflows)

Once it's done, I schedule a refresh between the Dataflows and Google BigQuery, which works just fine. Then I have to change in PowerBI desktop the source of the dataset (from BigQuery to PowerBI Dataflows).

Back to service, I set up the refresh of the dataset to sometime after the refresh of the dataflows is done (I let 2 hours of difference). So far is working fine.

Worth mentioning that Microsoft got in touch with me saying the problem is with BigQuery. But Google BigQuery says the problem is not on their end either - especially because it's possible to update the data through PowerBI Desktop. I hope the solution above help this out.

[v-diye-msft]

Hi @Johan 

Sorry, I can't reproduce your issue, please kindly raise it to support team for better advice. thanks!

https://powerbi.microsoft.com/en-us/support/