Dataset Refresh via API Failing

[MOVC]

We have a dataset connected to Azure SQL published to a Premium workspace in our P1 Capacity with an incremental refresh enabled that works correctly when refreshed manually or on a schedule. However if we trigger the refresh via the API (in this case, the refresh dataset action in Power Automate) the refresh fails with the following error message:

Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: A severe error occurred on the current command. The results, if any, should be discarded.\r\nA severe error occurred on the current command. The results, if any, should be discarded."}}

I can't find any useful information online about this, the error message is apparently from the SQL side but that doesn't seem to make sense with it only failing for API refreshes.

[lbendlin]

Which API are you using? Enhanced Refresh Request API?  Are you attempting to refresh the entire semantic model, or tables, or partitions?

[MOVC]

Apologies if there was confusion, I was trying to use the Power BI connector in Power Automate (image) to carry out the refresh. There doesn't appear to be any additional settings so I assume it's trying to refresh the entire model. I can confirm that this method works for other semantic models that don't have incremental refresh enabled though, I was wondering if it was something specific about incremental refresh that was incompatible with the API method.

[MOVC]

It looks like I have to use the enhanced API for this:

Enhanced refresh with the Power BI REST API - Power BI | Microsoft Learn

I guess you can't use the Enhanced Refresh via Power Automate?

[lbendlin]

Yes, you can, but you have to create your own custom connector and action. I can show you the swagger file if you want.

[MOVC]

Unfortunately I don't have Tenant Level Administrator, I assume this will prevent me from being able to run the required API request? It was why I was trying to use Power Automate in the first place.

[lbendlin]

No, this has nothing to do with tenant administration. As long as you have permission to modify the semantic model you can run that API call.

[MOVC]

Oh interesting, if you have an example then that would be great. Even if it's just so I can see how the API request is formed.

[lbendlin]

Here's how we implemented it

and here's a sample flow

[MOVC]

Thanks, unfortunately our business has locked down the ability to register Azure AD apps so I'll have to get approval to test it (unless there's another way?).

[lbendlin]

Sorry to hear that.  One alternative is to use PowerShell instead. It has all the convenience modules, or you can hand craft your REST calls.