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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Publishing R visual into Power BI Service-Run time error RODBC

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;

 

Script Runtime Error
character(0) Attaching package: 'dplyr' The following objects are masked from 'package:stats': filter, lag The following objects are masked from 'package:base': intersect, setdiff, setequal, union Error in sqlQuery(myconn, paste0(" \n ;WITH cte_name AS\n (\n SELECT TOP 15 s.DW_ItemID AS DW_ItemID ,i.Item AS Item,SUM(s.SalesAmount) AS TotalSalesAmount\n\t\t\t\t\t ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Rankid\n FROM rpt.Sales s \n LEFT JOIN dwh.Item i ON s.DW_ItemID=i.DW_ItemID\n\t\t\t\t\t LEFT JOIN DWH.Date d ON s.Date=d.Date\n WHERE s.Type=2 AND s.Date>=convert(date, '", : 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  
Please try again later or contact support. If you contact support, please provide these details.

 

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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 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.

 

Known Limitations

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

17 REPLIES 17
mcclurej
Helper I
Helper I

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:

  • Check your brackets: If you have a bit of code that you would feed back the output into the terminal or a markdown box, this will not compile in PowerBI. Example (dataOutput <- 1111) will not work but dataOutput <- 1111 will work
  • Not all supported features of current packages are supported by PowerBI, yes it has a feature support list such as supporting tidyverse, but be aware that PowerBI's version of any library may be out of date by quite a bit. Just keep at it until you get your visual to work (you may have to come up with some work arounds)
  • If you publish a dataset, and it throws an exception, delete that dataset before republishing. I've found that an overwrite may not fix your bad R code, and an older version gets stored in the "cloud", no idea why/how.
v-shex-msft
Community Support
Community Support

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 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.

 

Known Limitations

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi, 

"library(tvm)" is available in Power BI or not?

Thanks 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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!

 

 

Anonymous
Not applicable

Hello all, 

 

I am facing similar issues but working on a sql server on-premise. 

Script Runtime Error
Error in odbcClearError(ch) : first argument is not an open RODBC channel In addition: Warning messages: 1: In odbcDriverConnect(con) : [RODBC] ERROR: state , code 0, message ╨î 2: In odbcDriverConnect(con) : ODBC connection failed  
 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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

Anonymous
Not applicable

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,

https://docs.microsoft.com/en-us/power-bi/service-r-packages-support#r-packages-that-are-supported-i...

 

Hope this helps!!

Anonymous
Not applicable

Hi Sevilay,

 

Thanks for the reply.

 

Did you find any alternative solution. How did you manage?

 

Regards,

Naveen

Anonymous
Not applicable

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 ...

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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 😞

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors