dbplotR

dbplotR is a shiny app allowing users to produce interactive graphs directly from a database connection.

Inspired by the Big Query Visualiser app, the user interface is generated by shiny, while DT and plotly enable you to explore your data with state of the art interactive graphs.

RODBC takes care of the database connection. For more information on how to perform database queries in R via an RODBC connection, please check here or here (or the regular posts on Stack Overflow).

Tutorial

Required Packages

dbplotR requires numerous packages, which must be installed before the app can be used. If unsure, install the packages by running the following code:

install.packages("shiny")
install.packages("shinydashboard")
install.packages("shinybs")
install.packages("DT")
install.packages("plotly")
install.packages("dplyr")
install.packages("RODBC")

Running the App

To run dbplotR, you have two options:

The other option is run the program directly from your R console via Github.

runGitHub( "dbplotR", "dashee87")

Or you can copy the R files to your computer and run the program locally (or deploy it to your server).

Note: To export static images from dbplotR, you'll need a plotly account to access its API. You must then provide your username and API key to dbplotR, by running the following code before you run dbplotR.

Sys.setenv("plotly_username" = "YOUR USER NAME")
Sys.setenv("plotly_api_key" = "YOUR API KEY")

More information on static image export with plotly can be found here.

Security

This app relies on the input and output of sensitive information (passwords, database data, etc). Such information could be intercepted and the database compromised if some precautions are not in place. Running the app on your local host (either by downloading the files or running runGitHub("dbplotR", "dashee87")) is probably the safest option (ensure shiny to configured to run on 127.0.0.1 (the local host)). If deployed to a server, Shiny Server Pro features authentication, while authentication can be added to the open source version by following the instructions here.

SQL injections are another security concern. This is when a security vulnerability is exploited to send unwanted SQL queries to the database. To minimise its susceptibility to such attacks, the range of SQL queries permitted in dbplotR is severely restricted (e.g. only select queries, semicolons are prohibited).

Status

This package is under active development and is currently subject to regular updates.

Issues

If you spot any bugs (and I don't doubt you will) or have a suggestion for future versions, then I'd love to hear from you.

Submit issues/requests here.

Future

Short term:

• Improve some UI features (button alignment, table rendering, etc)
• Allow alternative database packages (see here) and uploading of CSVs
• Add maps and other cool plotly graphs

Longer term:

I know it's called dbplotR, but I'm very keen to expand the app into statistical analysis and machine learning.

• Hypothesis Testing (e.g. t-tests)
• Regression
• Forecasting (e.g. ARIMA)
• Clustering (e.g. k-means, PCA, SVMs)

Disclaimer

As stated in the security section, reckless use of dbplotR could leave your entire database exposed. The user is fully responsible for such problems.