Auto refresh R source

[rikBI]

Hi All,

We are building a powerBI dashboard with R code that gets its data from an online service. This needs to be refreshed on an hourly basis. Powerbi can't refesh this source without the powerBI gateway according to the message on powerbi. Is there a workaround, since we don't need a gateway to on premise for online data?

[simontaylor]

Is there any change 6 years later?

[ankitpatira]

@rikBI It is asking for gateway because R script gets executed against your local R server which will go to your online data source. If you absolutely can't install gateway then use Web connector to get data from online source within power bi desktop and it will refresh without needing gateway.

[rikBI]

Hi Ankit,

Thank you for your suggestion. We tried that as well, but we need the R script to dynamically generate the URL.

[v-yuezhe-msft · Accepted solution]

Hi @rikBI,

I am afraid that you are not able to properly refresh R source without installing  personal gateway. According to the article, gateway is an essential requirement in order to set schedule refresh for R dataset.

Thanks,
Lydia Zhang

[Greg_Deckler]

Ah yes, according to the documentation, you need to use a gateway to refresh R. Might want to post this to Ideas if it is not already out there.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-in-query-editor/

[MAAbdullah47]

Again On Power Bi I paste the same code:

DataSource.Error: ADO.NET: R script error.
Loading required package: methods
Warning message:
package 'mice' was built under R version 3.3.3
Error in mice(Orders_Basket_Table_1, m = 1, maxit = 50, meth = "pmm", :
object 'Orders_Basket_Table_1' not found
Execution halted

Details:
DataSourceKind=R

Note: Orders_Basket_Table_1 is the name of the dataset and it is surely available.

++++++++++++++++++

I tried as mentioned in the link:

DataSource.Error: ADO.NET: R script error.
Loading required package: methods
Warning message:
package 'mice' was built under R version 3.3.3
Error in `[.data.frame`(data, , jj) : undefined columns selected
Calls: mice -> sampler -> is.factor -> [ -> [.data.frame
Execution halted

Details:
DataSourceKind=R

Both of them not work ?!  

[BhaveshPatel]

@MAAbdullah47 

You can use Power Update to auto refresh your Power BI File Containing R script and schedule it to auto publish to PowerBI.com. It does not require gateway set up and works with minimal set up. It is also free for certain no of tasks.

Download from 

www.PowerOnBI.com

[MAAbdullah47]

Thank You, I tested it in R studio it gives the following error:

> tempData <- mice(dataset,m=1,maxit=50,meth='pmm',seed=100)
Error in mice(dataset, m = 1, maxit = 50, meth = "pmm", seed = 100) :
No missing values found

Not sure if the same thing might happen in Power Bi or not?

[Greg_Deckler]

So, you write an R query or you put an R step in a query?