March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have an application where I have a large dataset in POwerBI and I need to select subsets via slicers (easy) and then pass that subset to R (also easy). Then, for example, I want to run a regression and return a dataframe of coefficients and standard errors back to PowerBI for display not as a visual but in data for presentation in cards/tables, etc. It is easy to subset the data, easy to pass the subset to R, easy to Run (say lm) on that subset and easy to generate a rectangular table of beta estimates an SE (via tidyr::broom), but how do I get the broom-generated output object back into powerBI in real time? I do not want a visual from R, I want data.
To create a "real-time" link between Power BI and R so that you can dynamically update data based on slicer selections and then bring the results back to Power BI, you need to use a combination of Power BI's R scripting capabilities and DirectQuery or an API for real-time data refreshing. Here's how you can do it:
Step 1: Use Power BI's R Scripting
In Power BI, you would normally use the R Script Editor in the Power Query Editor to run R code. However, this is usually done during the data loading or transformation phase, not in real time.
Step 2: Dynamic R Scripting Based on Slicer Selection
Although Power BI does not support direct dynamic interaction with R scripts based on slicer selection in real-time analysis, you can simulate similar dynamic interaction in the following ways:
Parameterized Queries: Use Power BI parameters to control the data input to R scripts. These parameters can be indirectly bound to the slicer, but require a refresh to update the data based on the slicer selection.
Scheduled Refresh: For cloud-based scenarios, setting up a scheduled refresh will keep the data relatively fresh, but not truly real-time.
Step 3: Real-Time Data Return Strategy
Since you want to get your analytics in real time, consider the following approaches:
Direct Query: For databases that support this schema, using the DirectQuery schema allows Power BI reports to query the source data directly. You might run an R script externally to update a table in the database that Power BI queries in real time. This requires your R environment to write back to the database that Power BI is querying.
External Services: Develop an external service that runs R scripts and stores the results in the database, or provide an API endpoint. Power BI can fetch data from a number of web services, either directly or through customized connectors, enabling a quasi-real-time update mechanism. The choice of slicer can trigger a refresh or API call, although some manual intervention or scheduling is required.
Step 4: Real-time update workarounds
Manual Refresh: In the desktop scenario, the user is instructed to manually refresh the dataset after making a slicer selection. This is not seamless and cannot be used in a Power BI service without a scheduled refresh.
Automated Solution: Use the Power BI API to develop a more automated solution for programmatic dataset refreshing. However, keep in mind that real-time interactions are subject to Power BI architectural and service limitations.
Step 5: Data Visualization
Once the data is back in Power BI, you can use Power BI's native data visualization tools to present the results of the R-generated analysis.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Implicit in my earlier query was the idea that I'd like to be interactively getting back beta-values and coefficients from R, based on the slicer settings. I should have made that explicit. Because I want the data selecting/filtering, I can't just write out one set of beta-values to a csv file and then read them back in, it has to be a 'live' link.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
36 | |
27 | |
19 | |
11 | |
8 |
User | Count |
---|---|
55 | |
43 | |
25 | |
13 | |
12 |