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

Posted on January 1, 2017 by Pablo Seibelt in R bloggers | 0 Comments

[This article was first published on R - Data Science Heroes Blog, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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)