Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i have successfuly built an report on Power BI Desktop which includes R script to create a ggplot using "ODBC Driver 13 for SQL Server" for data connection. however when i have published the report into Power BI web it gives run time errors as stated below;
Solved! Go to Solution.
Hi @Anonymous,
I'd like to suggest you take a look at following links to know limitations when you use R script on power bi service.
Reference links:
R packages in the Power BI service
Creating R visuals in the Power BI service
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.
R visuals in the Power BI service have a few limitations:
R visuals support is limited to the packages identified on the following page . There currently is no support for custom packages.
Data size limitations – data used by the R visual for plotting is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used and a message is displayed on the image.
Calculation time limitation – if an R visual calculation exceeds 60 seconds the script times out, resulting in an error.
R visuals are refreshed upon data updates, filtering, and highlighting. However, the image itself is not interactive and does not support tool tips.
R visuals respond to highlighting other visuals, but you cannot click on elements in the R visual in order to cross filter other elements.
R visuals are currently not supported for the Time data type. Please use Date/Time instead.
R Visuals do not display when using Publish to web.
R visuals currently do not print with dashboard and reports printing
R visuals are currently not supported in the DirectQuery mode of Analysis Services
As document 'r security' mentioned, current it block all requests such as 'accessing the Internet', or 'accessing to other resources' that are 'not required' to create the R visual.
Regards,
Xiaoxin Sheng
Sorry for Necroing this thread, however, it is the first search result to come up 9/10 on google when you are trying to figure out what is wrong with publishing R Script Visuals, and is not correct anymore.
YOU ABSOLUTELY CAN publish visuals. The issue with Power BI is likely your script.
That is, PowerBI is extremely picky on syntax, whereas base R and R Studio will let you get away with a LOT of slack.
Tips:
Hi @Anonymous,
I'd like to suggest you take a look at following links to know limitations when you use R script on power bi service.
Reference links:
R packages in the Power BI service
Creating R visuals in the Power BI service
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.
R visuals in the Power BI service have a few limitations:
R visuals support is limited to the packages identified on the following page . There currently is no support for custom packages.
Data size limitations – data used by the R visual for plotting is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used and a message is displayed on the image.
Calculation time limitation – if an R visual calculation exceeds 60 seconds the script times out, resulting in an error.
R visuals are refreshed upon data updates, filtering, and highlighting. However, the image itself is not interactive and does not support tool tips.
R visuals respond to highlighting other visuals, but you cannot click on elements in the R visual in order to cross filter other elements.
R visuals are currently not supported for the Time data type. Please use Date/Time instead.
R Visuals do not display when using Publish to web.
R visuals currently do not print with dashboard and reports printing
R visuals are currently not supported in the DirectQuery mode of Analysis Services
As document 'r security' mentioned, current it block all requests such as 'accessing the Internet', or 'accessing to other resources' that are 'not required' to create the R visual.
Regards,
Xiaoxin Sheng
Hi,
"library(tvm)" is available in Power BI or not?
Thanks
hi @v-shex-msft and @Anonymous :
I am encountering the same issue where I tried to pass the values from slicers as variables in SQL query to create a table in SQL server using RODBC. The Power BI service is not supporting this functionality so the report I built on PowerBI desktop won't work after I publish it. The reason for not using the built-in parameters in PowerBI is that it does not allow filtered values from slicers to be passed as parameters.
Any workaround or comments are very appreciated.
Best regards,
Kevin Zheng
Hi,
as I got the answer some time ago that for security reasons the R odbc connection is blocked in general, there is no solution possible within an R visual / R interface etc.
BUT: With the help of PowerApp, a premium license and the correct connector, you can interact with a underlying database - including a writeback.
best regards
Hi @Anonymous,
Thanks for the reply. I am not familiar with Power App at all. Are you suggesting with power App it can allow the dashboard user to select the values from the slicers(in power BI) and then these values can become the variables of the query to retrieve the data from the SQL server? If the answer is yes. Could you please direct me to the resource that teaches me how to do it?
Thanks in advance!
Hello all,
I am facing similar issues but working on a sql server on-premise.
I am wondering if I can use the data gateway which is connecting the PBI Service and my local database.
Another point is the question what exactly is supported in PBI Service regarding the RODBC package if not a connection between the R and a database?
Do I need an mirroring endpoint and a network adress to get this open channel or how it shall be done?
Thanks in advance and best regards
Hi,
thank you so much for your reply.
my R visual is using ggplot2 package which is supported by Power BI Service. there is also ODBC connection string with the credentials set up within the same R script which executes SQL query on my data source (Azure SQL datawarehouse) to retrieve the dataset needed to plot the graph, and it uses RODBC package which is also suported by Power BI Service. i have also installed Personal gateway on my local machine for testing purposes however "use a data gateway" option is greyed out on power BI site displaying a message that " you dont need a data gateway for this dataset, because all of its data sources are in the cloud"
the error message i am getting on the report;
"first argument is not an open RODBC channel In addition: Warning messages: 1: In `[<-.factor`(`*tmp*`, 1, value = c(NA, 2L, 3L, 4L, 5L, 6L, 7L, : invalid factor level, NA generated 2: In odbcDriverConnect(connectionString) : [RODBC] ERROR: state , code 0, message 3: In odbcDriverConnect(connectionString) : ODBC connection failed "
looking at the error message, it seems to be trying to make a connection to the azure cloud but failing it.
your help is much appreciated, thanks in advance.
Hi Sevilay,
Did you find any solution for ODBC error?
Regards
RG
Hi,
i have been in touch with Microsoft Power BI team and i was told that what i was trying to achive is a supported functionality on Power BI desktop however not supported by Power BI Service.
Hi Sevilay,
I am also encountering similar issue. I have some R visuals which do not run on PBI service and throw error like "ODBC connection failed".
did you find any solution for that? Please let me know if you got succeded.
Thanks,
Naveen
Hi Naveen,
i have exchanged emails with Microsoft Power BI support team and they have confirmed what RODBC is supported within Power bi desktop however not supported on PBI service level. they have also noted that there is a document bug on following Link;
the bug is that below Microsoft link states that the RODBC library is supported within a R script visual on PBI service,
Hope this helps!!
Hi Sevilay,
Thanks for the reply.
Did you find any alternative solution. How did you manage?
Regards,
Naveen
Hi Guys,
Currently , I am facing the same issue. Just wondering if you guys found the solution ? Please share if you have some way out ...
Hi,
The question is do you really need to use RODBC? in my case i was trying to create a dynamic visual using R script based on data selection from date slicer.every time slicer selection is changed it sends the new value into the strored procedure, and retrieve the new data back to R visual. since this functionality is not supported in Power BI, i have ended up passing the date selections as a variable into my stored procedure via RODBC. this has enabled my visual to update dynamicly every time date slicer is changed.that is the reason i needed RODBC connection to my sql server. since RODBC functionality is not supported on PBI service level, i have ended up creating start and end date as user parameters within the report avoiding stored procedure execution via RODBC connection. https://docs.microsoft.com/en-us/power-bi/service-parameters
Hope this helps.
Hi,
Thanks for the reply.
Actually i am diong the same, my parent control brings the datewise parent jobs . User selects parent jobs to see the previous cycles of the jobs with date. This is what i have done via R visual that uisng odbc tha executes a proc with date and job code parameter passed dynamically to return previous cycles Since at service level it is not supported. I was wondering if there is some way out to dynamically query data w.r.t user selected criteria from sql server at Azure with Ad pwd auth type. The link you sent doesnt applies to my issue.
i am afraid i dont know if there is any other way. if you are a pro user you can get free help by logging a call with Microsoft. they are quiet quick in responding, and very hepful. https://powerbi.microsoft.com/en-us/support/
sorry, i couldnt be much help to you 😞