Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Improve performance of R script in Power BI

Hello,

 

I am trying to use the following R script in Power BI Desktop (DirectQuery mode) to eventually be able to embed it in a front-end application through Power BI Embedded:

 

library(ggplot2)
#library(lubridate)

dataset <- dataset[order(dataset$BuildingId, dataset$Gebouwfunctie, dataset$day_of_week, dataset$hour, dataset$minute),]

numbers <- seq(1,672,1)
count <- nrow(dataset)

dataset$xaxis <- rep(numbers, length.out=nrow(dataset))
 
lineplot <- ggplot(dataset, aes(dataset$xaxis)) +
  geom_line(aes(y=dataset$NormalizedTotal), color = "#D21F44") +
  geom_ribbon(aes(ymin = dataset$q_25_bench, ymax = dataset$q_75_bench, fill = dataset$Gebouwfunctie), alpha = 0.3) +
  scale_x_continuous(breaks = seq(1,672,96),
                   labels=c("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag")) +
  scale_fill_manual(name="", values = c("#00AFDA", "#85D3E6", "#BDEEFA", "#0181A1", "#E7F9FD")) +
  labs(x = "", y = "") +
  ggtitle("Genormaliseerd Elektriciteitsverbruik") +
  theme_light() +
  theme(legend.position = "top", plot.title = element_text(size=20, hjust=-0.02, family="Segoe UI", color="#858585"), axis.title = element_text(size=20, hjust = 0.5, family="Segoe UI", color="#858585")) +
  theme(axis.text.x = element_text(size=14, angle=0, family="Segoe UI", color="#858585")) +
  theme(axis.text.y = element_text(size=14, angle=0, family="Segoe UI", color="#858585"))
 
lineplot

This should result in the following graph (on the right in the fields pane you can see the table which it is based on):

 

Benchmark GraphBenchmark Graph

So in essence it works. But the problem is that, depending on which building one selects (third filter on the right of the graph), the performance is so slow (for some buildings) that it eventually runs out of memory, because of which it is not being shown in the front-end and an error message is returned (in Power BI Desktop it always works, although slowly).

 

So my question is basically this: how can I improve the performance of this report page (including the graph itself and all the filters), without losing functionality, such that it doesn't run out of memory anymore in the front-end. My knowledge of R (and indirectly how this translates, when filtering, to DAX or M I guess), and of all the engines running in the background to make the whole thing work, is just too thin to grasp it all.

 

Thank you very much in advance!

 

 

P.s. Let me know if you need (part of) the dataset, maybe I can deliver it to you in an anonymized fashion somehow.

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

>>In test device, CPU use before opening the pbix is under 5%, memory is at 38% (~950 MB). When opening the pbix, CPU peaks but then stablizes at around 8% and for memory this is about 55%.

It seems like your test device has less memory amount, please increase RAM amount and try again.(When power bi calculate on some complex formulas or loading huge amount of data, it will cost lots of resouces)

 

>>So my first suspicion is that the slow performance is indeed due to the Embed R Script editor of Power BI.

AFAK, embed R editor is a specific lite version of r script editor, if dev team not plan to update it, I also not sure how to improve performance.


Maybe you can submit a idea about this.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Anonymous,


If you run these formula in R script editor and power bi, what editor has poor performance?

 

If R editor has similar performace as power bi, I think this issue may related to your formula, please try to optimization your formula.(you can try to post to R script related forum to get better support of optimization)

 

If R editor has better performance than power bi, I think it may related to embed r script editor of power bi.

 

BTW, what bit and version of power bi desktop you used? How many idle RAM in your test device?
If you switch to import mode, does this operation increase performance?

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft,

 

Thank you very much for your quick reply.

 

I have run the script in R studio, and it does seem to be quicker there. So my first suspicion is that the slow performance is indeed due to the Embed R Script editor of Power BI. If that's the case, is there anything I can do to increase performance at all?

 

The problem is that I cannot test the interactions of the Power BI filters with the R script visual, and these might be some cause for slow performance as well. Do you have any suggestions for this?

 

Here is the information about my Power BI Desktop version:

Power BI Desktop infoPower BI Desktop info

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Furthermore, importing the data isn't really an option as the max model/pbix size is being reached.

 

In test device, CPU use before opening the pbix is under 5%, memory is at 38% (~950 MB). When opening the pbix, CPU peaks but then stablizes at around 8% and for memory this is about 55%.

 

Any other ideas? Thanks in advance!

Hi @Anonymous,

 

>>In test device, CPU use before opening the pbix is under 5%, memory is at 38% (~950 MB). When opening the pbix, CPU peaks but then stablizes at around 8% and for memory this is about 55%.

It seems like your test device has less memory amount, please increase RAM amount and try again.(When power bi calculate on some complex formulas or loading huge amount of data, it will cost lots of resouces)

 

>>So my first suspicion is that the slow performance is indeed due to the Embed R Script editor of Power BI.

AFAK, embed R editor is a specific lite version of r script editor, if dev team not plan to update it, I also not sure how to improve performance.


Maybe you can submit a idea about this.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft,

 

Yeah, well the problem isn't in the test device. The problem is that it is slow when deployed to an Azure Power BI Workspace Collection resource (I know, it is deprecated and we still have to switch to Power BI Premium). And as that is in the cloud, I cannot know what are the specs of the machine that the pbix is then running on.

 

So I think we will have to switch to Power BI Premium as soon as possible and see if that improves performance.

 

Thanks anyway!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.