Shiny Chart Builder – Explore your database with a point-and-click interface

January 2, 2017
By

(This article was first published on R - Data Science Heroes Blog, and kindly contributed to R-bloggers)

I have a new year’s surprise for you! This shiny app means to be a system for basic reporting in the style of most Business Intelligence tools, you can create a report without knowing any SQL or R.

This package heavily relies on dplyr for database abstraction, it theoretically works with any dplyr-compatible database, but may require some tuning for some of the databases.

The flow

The way you should use this app is to build your chart with the Sample mode, and when you have the visualization you want, you untick the sample mode, which goes to the database to fetch the complete dataset you need. The app does some tricks with dplyr to avoid over-querying data.

Scatterplot example:
Scatterplot

Line chart example:
Linechart

Configuration

The app has to be configured by placing 2 files on the root of the project: config.R and update_samples.R. Example files using sqlite have been provided in the examples folder.

Before using the shiny app, you have to execute the script “update_samples.R”, which will download samples of all tables (Might take a while on big databases). If you only want to query a subset of your tables, modify this script so it only finds those tables.

This script should be reran ocasionally, depending on how much your database changes, maybe daily, weekly or monthly, use your job scheduler or cron to do execute this script.

Also, the stack overflows easily because of the level of recursion used, on the server or machine where you deploy this, you should allow for big stack sizes, i’ve tried and it worked fine with the unlimited setting in my experience. (This command works on Linux, you should find your equivalent in other operating systems if you find stack overflow errors)

ulimit -s unlimited

Missing features

This is a very preliminar release, a lot of things may be missing, pull requests are welcome!

Some examples of missing features:

  • Some advanced settings to control appearance have to be added
  • Bar charts
  • Histograms
  • Faceting maybe?
  • More database examples
  • Bookmarking charts (It fails with the default bookmarker)

Download

The repository you can download this application from is:
https://github.com/sicarul/shiny-chart-builder

To leave a comment for the author, please follow the link and comment on their blog: R - Data Science Heroes Blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)