Refresh semantic models after ETL

[_bs_]

Hello, I'd like to move away from scheduled refreshes of the Power BI semantic model and make it dynamic - so after the ETL has successfully refreshed.

The Setup we have is:

Workspaces use Power BI Pro license

On-Prem data warehouse / SQL Server 2016 / SSIS to refresh database tables. Email notifications are sent to a Mailbox when successful or failed.

What options do I have with this setup?

I believe Power Automate is suitable as it can trigger a refresh from when a Email arrives.

However keen to understand if something can be achieved using SQL server / SSIS such as writing C# as a script task.

Thank you.

[GilbertQ]

Hi @_bs_ 

What you could do is set up a para automate flow which will trigger a refresh of the semantic model using. Request step (which is an HTTPS endpoint) as the source for your power automate flow, which you can then call when running is SSIS. You could follow my blog post link below and change the initial step to be a HTTP request: Using Power Automate to schedule a refresh of a Power BI dataset at the time you want! - FourMoo | M...

[_bs_]

Thank you @GilbertQ , could you expand on the call the P.A from within SSIS?

Which SSIS task would I need and how to reference the P.A.

Thanks

[v-saisrao-msft · Accepted solution]

Hi @_bs_,

Thank you @tayloramy @GilbertQ, for your insights,

With Power BI Pro, it's recommended to use Power Automate to trigger a dataset refresh instead of directly accessing the Power BI REST API. You can set up a Power Automate flow with an HTTP trigger, which SSIS can call after ETL finishes using an Execute Process Task (like PowerShell or curl) or a Script Task (C#/VB). Power Automate will then refresh the semantic model, keeping within Pro refresh limits.

Data refresh in Power BI - Power BI | Microsoft Learn
Enhanced refresh with the Power BI REST API - Power BI | Microsoft Learn
Execute Process task - SQL Server Integration Services (SSIS) | Microsoft Learn
Script Task - SQL Server Integration Services (SSIS) | Microsoft Learn

Thank you.

[tayloramy]

Hi @_bs_, 

If you're only using pro licenses without a dedicated capacity, then as far as I know you need to use Power Automate to do this. Also note that you are limited to 8 refreshes a day. 

[tayloramy]

Hi @_bs_, 

There are REST APIs available to refresh a model: 
Enhanced refresh with the Power BI REST API - Power BI | Microsoft Learn

Datasets - Refresh Dataset - REST API (Power BI Power BI REST APIs) | Microsoft Learn

So you can trigger this from C# code. 

[_bs_]

Thank you @tayloramy , however the requirements list says the semantic model has to be in Power BI Premium, Premium per user, or Power BI Embedded, but I have Pro, so looks like it won't work.