cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clued__init__ Frequent Visitor
Frequent Visitor

Query Containing R Script Algorithm is Evaluated Twice on Refresh

I have an R-based algorithm that I am running in a query. The R script outputs a single dataframe and writes a timestamped .csv file. When I refresh this query in the Query Editor, I received one .csv output, as expected. But when I refresh my report in the report editor, I receive two .csv outputs with a ~6 second difference in timestamp. Running the algorithm twice makes the procedure much more resource-intensive for my computer, and it might cause my R script to time out at the 30 minute limit.

 

Are there any known fixes I can try? Is this a known issue?

 

Here is a similar issue which was not resolved: 

R script runs query more than once

 

Here is a post by Ken Puls suggesting that Power BI might run queries once for preview and once for the actual load:

Power Query Refresh Speeds Suck

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Query Containing R Script Algorithm is Evaluated Twice on Refresh

@clued__init__,

 

Check if Option to disable data previews to download in the background helps.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
clued__init__ Frequent Visitor
Frequent Visitor

Re: Query Containing R Script Algorithm is Evaluated Twice on Refresh

Unfortunately I already had that setting disabled. Thanks for the input.

Highlighted
clued__init__ Frequent Visitor
Frequent Visitor

Re: Query Containing R Script Algorithm is Evaluated Twice on Refresh

I was able to reproduce my problem in the simplest possible case. The issue seems to be the way I am using the parameter query, and that I reference this parameter when I define the dataset that feeds into my R script. This issue does not occur when there are no parameter references (i.e. if I run the R scipt and hard-code the output filepath, I get one set of outputs).

 

To test, you will need R Open installed. Then just create the following 2 queries via Advanced Editor, and name them as shown. Replace the path in "Folder Path to Write to:" with a location you prefer. Apply changes and return to the report editor. Each time you "Refresh" you will have 2 sets of csv outputs. The digits appended to the csv output names represent millisecond accuracy.

 

Folder Path to Write to:

 

"C:/Users/User_Name/Desktop" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

 

R_Script

 

let
    RScript = R.Execute("setwd(paste0(dataset$project_path))#(lf)
employee <- c('John Doe','Peter Gynn','Jolie Hope');#(lf)
salary <- c(21000, 23400, 26800)#(lf)
startdate <- as.Date(c('2010-11-1','2008-3-25','2007-3-14'))#(lf)
employ.data <- data.frame(employee, salary, startdate)#(lf)
write.table(employ.data, file = paste(""PBI_R_Output_Test_1_"", (as.numeric(format(Sys.time(), ""%OS3"")) * 1000), "".csv"", sep = """"), sep="","", row.names=FALSE, na = """")#(lf)
write.table(employ.data, file = paste(""PBI_R_Output_Test_2_"", (as.numeric(format(Sys.time(), ""%OS3"")) * 1000), "".csv"", sep = """"), sep="","", row.names=FALSE, na = """")",
[dataset=Table.FromList({[project_path=#"Folder Path to Write to:"]},Record.FieldValues,{"project_path"})]), #"employ data" = RScript{[Name="employ.data"]}[Value] in #"employ data"

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 285 members 2,734 guests
Please welcome our newest community members: