Refresh BigQuery data(error)

[rw-brusnika]

Im try refresh my Big Query data

And im get this error message

MessageERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Exceeded rate limits: Your user_method exceeded quota for api requests per user per method. 

Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net

Activity ID67a3881e-e6af-ac24-ef72-993f1e887bf7

Request ID0a518bce-f399-a97f-06b7-305d62b00daf

Time2018-05-05 18:53:40Z

Im not reach any big query limits.

[joshpt]

Hi Steffen 

thanks for sharing your story with us.

To be honest i think the issue it's not on the google BQ side, as i don't see any spike or quota limit when i go to our google console after the error occurs on PowerBi.

There's also que question that this issue on our end only happens on PowerBi service, on PowerBi desktop it works fine. 

As i've said before i think Powerbi team messed up this time, releasing a conector that doesn't do what it should and this is a big issue for us. The worst is that there's no ETA to solve this and it's hard to make a proper data and reporting strategy with this level of uncertainty.

[lemarcfj]

@Anonymous Thanks for your reply. I actually downloaded Simba but haven't poked around much. My issue is that I don't know the SQL syntax required to replicate the qeueries. Does Simba require knowledge of syntax or is it more of a drag and drop interface?

[lemarcfj]

@joshpt 

Agreed. It's honestly ludacris that this would be an issue and that it hasn't been acknowledged. 

[joshpt]

@lemarcfj i still don't get why do you need SQL to use direct query. The direct query funcionality that Powerbi has, what does is to retrieve the information from BQ everytime you refresh the report. The way you create the bridge between PowerBI and BQ is the same.

There are some issues with this approach in terms of BQ costs and for that you may need to review the data structure you have on BQ. 

What i did here was to create some views that go directly to the raw data (for this you need to know SQL). After i've scheduled this view to dump the results on a table and this table it's then consumed by powerbi.

[giusvizz]

I have the same error

MessageERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Exceeded rate limits: Your user_method exceeded quota for api requests per user per method. 

Are there any updates on the BigQuery connector?

[Anonymous]

@joshpt - I don't disagree with you. Adding a pre-build connector, that works as bad as this one, is ridiculous. However, I believe the issue is not limited to Power BI. The error description is clearly generated in BigQuery - and we should be able to see the rejected call in the cloud console. Clearly, both Microsoft and Google have to dig into this issue, if they want their systems to interact.

@lemarcfj - The ODBC driver generally works like the pre-build connector. The only technical difference is, that you have to set it up and install a gateway on the server. It does not eliminate the quota errors, but it greatly reduced them.

[joshpt]

Hi @Anonymous

thanks for your comment but i still don't see anything on my google cloud console after the refresh fails. 

The only issue is the PowerBi Service (Cloud) but sometime it works on weekends. On PowerBi desktop i've never had an issue refreshing the data. This is why i assume the issue resides in the Powerbi connector and has nothing to do with Google BigQuery. The only thing that comes in my mind is google detecting that a lot of requests are being made from the same ip/machine that makes several calls at the same time, but uses diferent credentials.

It shoudn't be an easy task otherwise powerbi team already have sorted that out. This thread it's been around for some time already. 

@giusvizz the last time i've got an answer from microsoft, was told me that they are thinging on a solution for this but there's no ETA for this. Besides that i don't have another info.  Which i would...

 Best

[Anonymous]

Wow I am amazed at how bad this bigquery connector is. First I had problems because powerBI said it couldn't see the tables (even though connecting through desktop was problem-free), and now I get this error about quota's that I'm pretty sure I'm not exceeding (or at least google doesnt show it in their reporting) and of course it works flawless in powerBI desktop. 

But I guess it's now a year later and still no fix. I just think that the PowerBI team need to admit that their BigQuery connector is not reliable and remove it from the options. I spent several weeks creating reports based on the bigquery/powerbi combination and now I'm kind of finding out that it doesnt work reliably at all.

Very frustrating 😞

[Anonymous]

OK here's  sort of a mea culpa from my side:

So I was convinced that I could not be exceeding any quota, simply because my projects were on the "blaze" plan in bigquery, which doesnt have any limits. However, what I didnt know, was that under water, powerbi was not using the billing project that belonged to the actual project, but some other project (I still dont know which one). So in order for me to get refreshing working again, I had to add a billing project to my powerquery, like so:

Original:
let
Source = GoogleBigQuery.Database(null),

#"k-swaps-cg679" = Source{[Name="k-swaps-cg679"]}[Data],
.....

New

let
Source = GoogleBigQuery.Database([BillingProject="k-swaps-cg679"]),

#"k-swaps-cg679" = Source{[Name="k-swaps-cg679"]}[Data],
.....

In my case, the billing project was the exact same as the firebase project name.

I just hope this helps others. I think both PowerBI (in choosing a default billing project instead of the one belonging to the project it is connecting to) and bigquery (for uninformative error messages) can improve in this regard.

[dguedes]

Hello guys,

you will need to specify the the Billing Project in your M Query on Query Editor: 

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

Have a look into this link: https://www.linkedin.com/pulse/power-bi-google-bigquery-billing-project-default-rohit-vangala

Hope it helps.