1 Reply Latest reply on Oct 13, 2018 6:54 PM by patrick.byrne.0

    Demo of Using R to Write-back to SQL DB

    Ryan Moore

      This post is not a question, but rather the sharing of an idea that I have not seen previously executed. This idea is the combination of using R within Tableau to write user input back to a SQL database. To make it work, I first create parameters for the user input I want to collect. Then, to overcome the issue that all R code is forced to be a table calculation, I create a sheet with two rows where, I run the Rscript only when there is one row. I use an accompanying sheet that has a dashboard action to filter the R sheet to one row to run the Rscript.  R then loads the package (RODBC) to be able to connect to the SQL database. After the connection has been made, my if statements go through a few checks. These checks include if the values already exist in the database, if the user wanted to delete or add values, or if the user has access to delete or add. Then…presto! We have a unique workaround to write back to SQL.

       

      Hurdles and Considerations:

      1. Click no to executing external services when first opening the workbook.
      2. The R script will be an opening and closing connections to the SQL database with every change you make when the R sheet has only one row.
      3. Intermediate calculated fields were made for each of the user input parameters by wrapping the input with quotes (‘s).  This is required because the SQL query must be one string and I cannot use a quote in the script code without it stopping the R script. I overcame this by using the paste0 function and the previously mentioned intermediate calculated fields.
      4. I had to do a few more addition checks to see if the R sheet has 1 or 2 rows. Because of reasons I could not determine when switching from add to delete, the R script would run and delete the input regardless of if the script was supposed to not run. You’ll see that I send in total(sum(row count)) into the Rscript for this reason.
      5. You’ll need to replace the SQL connection details I removed for this demo.
      6. You’ll need to replace the table name with your own created table with the same columns for the sample workbook to work.
        1. Columns - > Date, Names, Value
      7. You’ll of course need to be running R with Rserve up to be able to connect using Tableau’s external services.
      8. R will also need RODBC at the minimum. I use data.table for manipulation and checking if previous values already exist.

       

      I’ll do my best to respond to any questions here but connect with me on LinkedIn for a faster response as I am looking to expand my network. Depending on interest levels, I’ll make a video of how to operate the dashboard and a blog post.

      https://www.linkedin.com/in/ryan-moore-03347b57/