Kick off data refresh with SQL Agent Job

[tinaklo]

Hi there,

Our setup...we have an enterprise gateway installed on our server to refresh the Power BI dashboard and report. The underlying data sources for the report are tables and views from a SQL server, not a cube.  Our datasets are setup with an ‘import’ connection.

My question...instead of scheduling refreshes via the Power BI Service, we would like to create a step within a SQL Agent job to execute the refresh.  Is this possible?  Maybe using Powershell?  Any suggestions on viable solutions?

Thank you.

[Eric_Zhang · Accepted solution]

@tinaklo

It is surely possible. Power BI Services provides the APIs to import data into a published report, you can follow this toturial Push data into a Power BI dashboard. Recently I am studying on this, it is quite easily understanding if you have some coding skill. Just form data in JSON format and call the APIs to load data to Power BI services.

Regarding developing, you can create a console application following the tutorial in Visual Studio or SQL Server Integration Service(my preference),and schedule a windows task or SQL Server Agent job to load data in a certain interval automatically.

Also check Power BI REST API limitations.

[Anonymous]

@tinaklo Not saying it "can't" be done, but I'm not aware of anyone having done it before. Obviously this isn't supported out of the box. The only mechanism to "refresh from on premises" would be to use Direct Query or a live connection to a SSAS instance. Then you don't need to schedule anything in the Service.

[spaxia]

Another solution can be to have an Azure SQL and replicate Data in the Cloud....