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

[busmansholiday]

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. 

[v-nuoc-msft]

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]

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.