Error 504 and 500 when using Reporting API with Power BI

Hello

we are using the Reporting API with Power BI. In Power BI Service we have set up a refresh schedule. We have two issues with that and found a workaround only for one of the issues.

Error 504

When the Query exceeds a certain timeframe (roughly 3 years), we always get an error 504 (Gateway Timeout).

I found a workaround, by splitting the query. We have a query for each year and append the tables to a single table.

Error 500

Even with this workaround, we still have problems. Only a fraction of the refreshes are successful. Most refreshes still fail.

The error message is always the same: "OData: Request failed: The remote server returned an error: (500) Internal Server Error. Table: TimeTracker." ("TimeTracker" is the name of the query in our model.)

Not only is the data not up to date on most days. If the refresh fails too often in a row, Power BI Service automatically deactivates the refresh schedule and I have to activate it again manually.

I know it's load for your server. Unfortunately, Power BI Service does not allow to exclude a query from refresh (only works on Desktop).

On the other hand, we are not a huge company. We have around 35 employees creating ca. 40.000 rows/entries per year. I don't understand why you have such a strict limit and/or can't handle that load.

Is there anything we can do to mitigate the problem? For example, is there a way to exclude certain columns to reduce the load?

dev

January 16, 2020 17:19

Hi Beka,

Please let me know what API version are you using, is it 2.1 or new 3.0?

We have improved performance for the API 2.1 in the latest version, so that data retrieval should be improved, but still 3 years per 40k rows will be ~120k rows, that will be more than 100mb of data, not sure that this is the best approach.

And 2.1 does not support paging.

What we can do is maybe you try to switch to 3.0 version, it works faster, allows you to select only data you want and supports paging, grouping and filtering out the box: https://support.7pace.com/hc/en-us/articles/360035502332-Reporting-API-Overview

Thanks,
Maxim Lutsan

Beka Kobaidze

January 16, 2020 20:55

Hi Maxim,

thanks for the quick reply.

Indeed, I'm still using 2.1. It will be a lot of work to migrate to 3.0 and I was hoping I could avoid that.

I found another temporary workaround, maybe it's interesting for other users. I just copy the data of past years to excel, import the excel to Power BI and append it to the current (short-term) Timetracker query.

Cheers
Beka

Error 504 and 500 when using Reporting API with Power BI

Comments