Forum Discussion

Pedro-Pereira's avatar
Pedro-Pereira
Advocate I
7 years ago

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 adres...
Need Help
zoloturu's avatar
zoloturu
Memorable Member
7 years ago

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's avatar
Pedro-Pereira
Advocate I
7 years ago

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's avatar
    zoloturu
    Memorable Member
    7 years ago

    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!

  • cmsouth's avatar
    cmsouth
    Frequent Visitor
    4 years ago

    @Pedro-Pereira, following back on this older thread -- were you able to connect to MySQL with the online service?  I'm using dbConnect and it works fine in the desktop but i get the same message you received when refreshing in the online service -> "Unknown MySQL Server Host".

    I will say i've had a lot of trial and error to get this far -- for only a few lines of code there are a lot of things that have to go right!

     

    For the next person googling, here's what i am trying to do:

    - at a daily PBi refresh, save a few summary datapoints from a table visual (basically a pivot table on manufacturing stages to summarize dollars, counts, average time, median time, maximum time in each stage).  
    - read the table data and graph each day's summary data in a stacked graph to see trends as product moves thru stages

    - figured since i've got a visual setup for the MySQL, go ahead and build a simple graph.  So my code is:

     
    # Paste or type your script code here:
    library (RMySQL)
    library(data.table)
    library(dplyr)
    library(ggplot2)
    names(dataset) <- c('date', 'stage', 'value', 'count', 'avgtime', 'maxtime', 'mediantime')
    g <- ggplot(dataset, aes(x=stage, y=value, fill=count)) + geom_bar(position="dodge", stat="identity")
    g
    con <- dbConnect(MySQL(), user="XXXXXXX", password="YYYYYYY",
    dbname="DDDDDDD", host="HOST URL in AWS",client.flag=CLIENT_MULTI_STATEMENTS)
    # change PBI names to column names and write it back
    names(dataset) <- c('date', 'stage', 'value', 'count', 'avgtime', 'maxtime', 'mediantime')
    dbWriteTable(con,"MYSQL TABLE NAME",dataset,append=TRUE,row.names=FALSE)
    dbDisconnect(con)