Work around for RODBC connection on Power BI Service

[Anonymous]

Hi all,

After exploring Power BI for a bit, I have been trying to build a dashboard/ tool on power bi desktop. This tool allows users to select values from slicers and these values becomes parameters in R visual and then R visual creates dataset on SQL server using these variables via RODBC connection. The new summarised dataset then feeds back to power bi desktop for visualisations i.e. bar charts and line charts. These all work perfectly fine on power bi desktop.

However, this RODBC connection is not supported by power bi service due to the limitation of R visual in service.

R scripts security

R visuals are created from R scripts, which could potentially contain code with security or privacy risks.

These risks mainly exist in the authoring phase when the script author run the script on their own computer.

The Power BI service applies a sandbox technology to protect users and the service from security risks.

This sandbox approach imposes some restrictions on the R scripts running in the Power BI service, such as accessing the Internet, or accessing to other resources that are not required to create the R visual.

The reason I am using R visual as the intermediate step is that the values from slicers cannot be passed into built-in parameter tool (this has to be manually specified in power bi) and then be passed into SQL Query or Store Procedure.

I am wondering if there is any work around to achieve my goal? I noticed there has been a lot of people asking for this functionality however it hasn’t been resolved for years. Similar post on power bi community is here.

Another problem but not the major issue is sometimes it would give error “invalid object name” after about 25 seconds when I hit fresh button in power bi desktop. This only happens when the query takes longer than 20-30 seconds to return the result. So I guess it might have something to do with the timeout setting but I can’t find it in power bi desktop settings. Can you also please advise if there is any fixes?

Please let me know if you need any more information regarding my questions.

Thanks in advance.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, the overtime of R visuals is 60 seconds and it is fixed If an R visual calculation exceeds 60 seconds, the script times out and results  in an error.

There are also some limitations on R scripts.As you know, the sandbox approach imposes some restrictions on the R scripts running in the Power BI service, such as accessing the Internet, or accessing to other resources that are not required to create the R visual and they can't be changed. I wonder the requirements you want to achieve. Maybe there is a workaround by using other visuals.

Best Regards

Allan

[Anonymous]

@v-alq-msft 

Hi Allen,

Thanks for the reply.

I will need slicers or drop down lists which has functionality to pass into parameters and then query on a fact table which is huge(14 million of rows and hundreds of columns). The dataset obviously can't be pulled into power bi desktop in one go therefore I used R visual as the work around to only pull the summary the user need into powerbi desktop (that's right the slicers / drop down lists provide users with the options to customise their summary e.g. the total sales(one of options) by years(one of options) by states(one of options) with certain filters applied(one of options)).

Is there any other smart tricks / visuals that will allow this user interaction to happen on power bi service. 

Best regards,

Kevin