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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
busmansholiday
Regular Visitor

Passing (sliced) data to R and then retrieving output object back to Powerbi

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. 

 

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

Hi @busmansholiday 

 

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.

busmansholiday
Regular Visitor

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. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors