cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pedro-Pereira Frequent Visitor
Frequent Visitor

R visual connecting to MySQL database

Hi,

 

In summary, I need to be able to connect to a database trought an R visual in power BI service. My code works in the desktop but in the service it doesnt work anymore. When I use the IP adress I have the following error: [ DBI Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to MySQL server on '(ip adress)' (0)  ]

When I use the host name I have the following error: [ DBI Error in .local(drv, ...) : Failed to connect to database: Error: Unknown MySQL Server Host '(host name)' (11001)  ]

 

Further explanation:

 

My objective is to create a custom R visual in power BI and publish my report with it on power BI service. My visual, is similar to a heat map, but diferent enough that the ones availabe in the store (heatmap, mapbox, esri, etc) don't work for me.

For this map I need both my data points and data from a shapefile. I tried importing both data to power bi but R only accepts 1 dataframe. Joining both tables in power BI doesn´t make that much sense and PowerBI limits it to the top 150k rows (the SHP has 3M rows). As a workaround , that worked in the desktop version, I would like to grab the Shapefile data from a database directly from my R code with dbConnect and fetch (DBI package that is accepted).

 

I'm open to both alternatives to solve this problem or a way to fix this issue, if it is possible

 

Thank you in advance

5 REPLIES 5
zoloturu
Advisor

Re: R visual connecting to MySQL database

Hi @Pedro-Pereira,

 

R scripts are not supported in a web version of Power BI currently. You can vote for this idea here - https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17899522-support-r-visuals-to-pub...

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Pedro-Pereira Frequent Visitor
Frequent Visitor

Re: R visual connecting to MySQL database

Hi Ruslan ( @zoloturu ) , thanks for your reply
That vote seems to be a really interesting feature to add to power BI. However, I wasn't looking that further ahead. Right now I only need my R visual to work on app.powerbi.com. I tested some simple visuals and they work but my complex ones only work on desktop.
Do you understand my problem and if so do you know how to fix it?

Also can you please tell me where did you get the idea that I wanted to "publish to web" so that I can edit my post to not confuse other people.

 

Best regards

Pedro

zoloturu
Advisor

Re: R visual connecting to MySQL database

Hi @Pedro-Pereira,

 

Sorry, I missed that R is supported already in Service. I've checked that for you. Here is a link - https://docs.microsoft.com/en-us/power-bi/visuals/service-r-visuals, where you can find all the limitations.

 

And going further you can find that:

Note that in the service not all of the R packages are supported. See supported packages at the end of this article for the list of packages currently supported in the Power BI service.

And the link with a full list of supported packages in Power BI Service - https://docs.microsoft.com/en-us/power-bi/service-r-packages-support

 

Please go through both links and check whether you ran into the limits or not.

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

 

 

Pedro-Pereira Frequent Visitor
Frequent Visitor

Re: R visual connecting to MySQL database

Hello again @zoloturu

 

After carefully re-reading those articles and doing some tests I believe there are 2 things that migh the source of my problem:

1) from the link "https://docs.microsoft.com/en-us/power-bi/service-r-packages-support "  ->"For security and privacy reasons, R packages that provide client-server queries over the World-Wide Web (such as RgoogleMaps) in the service, are not supported. Networking is blocked for such attempts. See the following section for a list of supported and unsupported R packages. "

However, the package that I use to connect to the DB is supported by Service. And the package was basically made to connect to databases, so it would be weird to support it but to not allow most of the stuff to work.

 

2) from the link "https://docs.microsoft.com/en-us/power-bi/visuals/service-r-visuals" -> "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."

This might be another problem but they don't explain what the consider as not required. My connection is required but maybe they don't think it is.

After testing for a bit I managed to put all my code, including dbconnection and fetching of the query, inside a barplot function. So i don't believe that much that this is the problem as it would be super weird that he would consider something as not required for the visual when it is inside the funcion that creates the visual

 

Overall, I still don't know if it is a code problem, database problem or power BI Service problem (like the limitations above)

 

Best regards,

Pedro Pereira

 

 

zoloturu
Advisor

Re: R visual connecting to MySQL database

Hello @Pedro-Pereira,

 

Sometimes limitations are not listed in the description. It might be that they missed something. I had such experience when worked with Premium capacity and found weird not documented limits. I recommend you to go to the page https://powerbi.microsoft.com/en-us/support/ and create a support ticket using button downside. Then you will receive a help from MS support team. You can speed up this process mentioning this thread to a support engineer.

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 112 members 1,074 guests
Please welcome our newest community members: