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

by Pablo Seibelt

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:

Line chart example:

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

If you've never visit the Data Science Live Book... here's the home page

DSH Twitter

DSH Facebook

More DSH posts!