Several datasets in one workspace - API refresh failures

[anvais]

Hi,

We use PBI Service API to control our dataset imports and their refreshes. We are experiencing random refresh failures when we are refreshing our datasets with Azure SQL connection via PBI Service API. This works for 95% of the datasets but fails for the rest 5% on a nighlty basis (key didn't match any values in the table). In the morning the refresh works just fine without any changes to the dataset that failed a few hours earlier (random times during the night). We use the same custom-built template PBIX-report that we push to service and alter the dataset only by inserting different db credentials when refreshing it - thus showing different data on the actual report.

Within one workspace, is there a limit how many simultaneous refreshes we can trigger in Service or how many refreshes can be running at the same time? Is there differences between refreshes made via the UI and API? Currently we have roughly 100 datasets we refresh on a nightly basis, all of which have the same source with different credentials and the issue is not in backend. Previously we used to have a separate workspace for each user group and managing it became a nightmare (no refresh issues though), thus having datasets in the same workspace is the optimal structure for us in terms of the Service UI.

Has anyone experienced similar issues? Another way to tackle this is that we start to push the datasets instead of letting the Service do the work.

Best,

anvais

[v-alq-msft]

Hi, @anvais 

I assume this error could be an issue with your M script within your Power BI Desktop file. It can also be due to an out-of-date Power BI Desktop version. You may check the data format between Azure SQL and Query Editor in Power BI Desktop and set up data format for every column.

For further information, you may refer the following links.

Expression.Error: The key didn't match any rows in the table 

Troubleshooting refresh scenarios 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anvais]

Hi, and thanks for your reply. I'll look into it, but everything suggested shouldn't be the issue in our case. Feels like we've taken care of it already.

For me the randomness is confusing - the table the error emerges from differs basically every night and the datasets that fail are always a set of five out of ten but the fails are random within that group. It's exactly the same power bi file that we upload to service and for each user group we only change the SQL source credentials so it loads different schema into the model. The schema is always in the same exact format for each power bi file, only the content varies. The data types are always forced to follow the same structure as everything is stored in our DW. Also, the refresh completes without any errors in the morning through the API or the UI - unless I start 3-5 refreshes simultaneously. 

That's why I'm thinking it has to be with some kind of limited amount of parallel refreshes one can run in Service. But I can't find any documentation related to this topic. Also, when we initiate the refresh we always insert the credentials and one parameter into the model - can this be something the service isn't able to handle when multiple (2-10) refreshes are running at the same time?

Best,

anvais