Error publishing the report in Power BI containing script R. ODBC connection failed.

[MPS]

Good day to all.

I can not publish a report containing the R script in Power Bi.

Could you tell me what to do next?

The script executes the task of calling the stored procedure on MS SQL 2016 and passes parameters to it.

Nothing is returned from the procedure.

From Power BI Desktop everything goes perfectly.

At the publication I receive an error ODBC connection failed. That is, before I run the script, I do not even get there.

I'm using RODBC ([64-bit] R-3.5.1).

I also have a gateway configured with the SQL Server data source type.

Thank you.

[v-shex-msft]

Hi @MPS,

It seems like you can try to use r script to invoke other datasource, these operation only works on desktop.

For security reasons, power bi service not allow r script to access networks or connector to other data source which out of current dataset.

Please take a look at below documents to know more about limitations about use r script on 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

Requirements and Limitations of R packages

There are a handful of requirements and limitations for R packages:

• The Power BI service, for the most part, supports R packages with free and open-source software licenses such as GPL-2, GPL-3, MIT+, and so on.
• The Power BI service supports packages published in CRAN. The service does not support private or custom R packages. We encourage users to make their private packages available on CRAN prior to requesting the package be available in the Power BI service.

• For Power BI Desktop has two variations for R packages:

  • For R visuals, you can install any package, including custom R packages
  • For Custom R visuals, only public CRAN packages are supported for auto-installation of the packages
• For security and privacy reasons, we currently don't support R packages that provide client-server queries over the World-Wide Web (such as RgoogleMaps) in the service. Networking is blocked for such attempts. See R packages in the Power BI service for a list of supported and unsupported R packages.
• The approval process for including a new R package has a tree of dependencies; some dependencies required to be installed in the service cannot be supported.

Regards,

Xiaoxin Sheng

[MPS · Accepted solution]

Good afternoon, Xiaoxin Sheng!
Thanks for the quick and full answer.
You confirmed my fears.
But, strange as it may seem, this ban led me last night to a decision that was not related to my question. We say - If God closed one door in front of you, do not be upset - He will open another -)

Thank you again.