Automate R script from PowerBI

[ranjan]

Hi,

I have recently started using Power BI for visualization. Am working on a project where I have to do some processing on data in SQL server and then load that data to PowerBI for the dashboarding. Now, it will work like a tool which will be refreshed at certain frequency.
I am trying to replace the processing part of the process from SQL server to R, since its much faster and versatile in processing.

Data(in SQL server) => Stored Procs in SQL Server => PowerBI ----- current scenario
Data(in SQL server) => R script => PowerBI ----- expected scenario

For now I am doing that using Getdata option in Power BI and run the R script manually everytime I need to refresh it. R connects to SQL server, loads it in memory, does the processing and a table is generated which I can use for dashboarding in PBI.

Is there a way where I can write some kind of function or query which can pick the R script when I open the dashboard, run it and refresh the dashoard. Basically, automate the process. I am stuck for quite some time now.

Appreciate any help.

Regards,
Ranjan

[TomMartens · Accepted solution]

Hey,

unfortunately it's currently not possible to automate some data processsing using R scripts. This is due to the deprecation of the standalone Personal Gateway and the fact the new gateway currently does not support the execution of R scripts for data processing.

This thread may provide some additional information

https://community.powerbi.com/t5/Integrations-with-Files-and/R-Integration-with-Power-BI-Scheduled-R...

If you are in the lucky position to be able to use SQL Server 2016 or SQL Server 2017 you can use the integration of R or even Python (SQL Server 2017) from within a stored procedure, maybe this link gets you started:

https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sql-server-r-tutorials

Hopefully this of some help

Regards

Tom

[ranjan]

Thanks Tom,

I hope it works out soon enough. And yeah, as suggested, I have started working on getting SQL Server 2016/17 so that I can run R script from there itself.