JavaScript
Self-hosted "SQL-not-required" data analytics and visualisation tool.

Insights

As our education marketplace grew, we faced a dilemma: we had little visibility into our data.

Questions such as "who is our best customer?", "which target countries are gaining in sales?" or "what are our most popular categories for paid leads?" required tinkering in the Rails console or writing custom stats pages. We lost a lot of developer time over the years.

So we started looking for the best Business Intelligence solution out there.

Unfortunately, they all had their problems. Some required knowledge of SQL. Some made multi-table data exploration a pain. Most cost an arm, a leg and an iPhone. Per month.

The best tool we found was Looker, but at a monthly price equal to our burn rate, it wouldn't work.

So I decided to re-implement the essential parts of Looker as an open source alternative.

Insights is a self-hosted "SQL-not-required" data analytics and business intelligence tool. Featuring linkable URLs, easy data exploration, automatic joins, graphs, exports, facets (pivots), pretty colors and a ridiculously permissive license (MIT).

It's a work in progress and you're brave for checking it out! Cheers!

Play with the demo here.

How does it work

Similar to Looker and their LookML, insights requires you to define your data model in a file called insights.yml.

You use the insights_export gem to generate this file from your Rails Models. (Adapters for other frameworks coming soon.)

You keep this file with your code and update it whenever something changes. You edit it to add custom fields (e.g. full_name: first_name || ' ' || last_name), hide existing fields (e.g. encrypted_password) or hide entire models.

When your database changes, run rake insights:export and the file is updated automatically.

One entry in this file looks like this:

Order:
  enabled: true  # set to false to hide
  model: Order
  table_name: orders
  primary_key: id
  columns:
    id:
      type: :number
      index: :primary_key
    total_price:
      type: :number
    hidden_field: false # this stays hidden
    currency:
      type: :string
    # ...
  custom:
    total_price_in_eur:
      sql: "$$.total_price * $$.currency_to_eur"
      type: :number
  links:
    incoming:
      order_lines:
        model: OrderLine
        model_key: order_id
        my_key: id
    outgoing:
      user:
        model: User
        model_key: id
        my_key: user_id

You give this insights.yml file and a database connection to insights and start exploring.

The point is this: your developers update the .yml file. Your CEO and CFO browse the interface. If they ask for a report, you send them a link to the right view and they can explore further.

How to use

Play with the demo here and try to answer the following questions:

1. Which product has been bought the most? (solution)
2. Sales by country by month (solution) - export it as a PDF as well!
3. Sales by delivery status (solution)
4. Where are your users from (solution)
5. ... by month? (solution)
6. ... only ones with confirmed orders? (solution)

Hint: to count rows, select the id field and then count from the table header.

Coming soon

• Saving views
• Dashboards
• Better graph controls
• Graphs that don't require a time column
• View generated SQL
• Massive Ruby backend code refactoring
• Moderate React/Kea frontend code refactoring
• Polishing

Installing

Installing insights is a two-part process:

1. Export your app's structure

First you need to create an insights.yml file from your main Rails application. This file will describe your database structure, including custom fields and aliases that you may define.

1.1. Generate an insights.yml file from your Rails app

Add gem 'insights_export' to your Gemfile and run:

rake insights:export

The generated config/insights.yml file needs to be accessible for insights in the next steps. I recommend keeping it in your app's repository and running rake insights:export each time your database structure changes to update it. You can then symlink it to insights.

2. Install Insights

2.1. Git clone (or fork and clone) the repository

git clone https://github.com/mariusandra/insights
cd insights

2.2. Install the ruby (2.3+) and nodejs (6+) packages:

Assuming bundler and yarn are installed:

bundle
yarn

Make sure the above commands succeed!

2.3. Setup the database

Copy config/database.yml.example to config/database.yml

Edit config/database.yml and change the target_database: block to point to your app's database.

Insights needs one database for its internal use. Feel free to keep the default SQLite configuration or change it for something beefier.

Then initialize it:

bundle exec rake db:create
bundle exec rake db:schema:load

2.4. Set up the path to insights.yml in config/initializers/insights.rb

(copy from config/initializers/insights.rb.example)

Anything like this, depending on your setup and the location of the file:

INSIGHTS_EXPORT_PATH = "#{Rails.root}/insights.yml"
INSIGHTS_EXPORT_PATH = '../my-app/config/insights.yml'
INSIGHTS_EXPORT_PATH = '/srv/my-app/current/config/insights.yml'

2.5. Set up the credentials in config/initializers/insights.rb

If you uncomment any of these lines, the app will be protected by a login screen

INSIGHTS_LOGIN = ['demo', 'demo']
INSIGHTS_LOGIN = [
  ['demo', 'demo'],
  ['admin', 'pass']
]
INSIGHTS_LOGIN = -> (user, pass) { user == 'demo' && pass == 'password' }

2.6. Run it!

bundle exec foreman start

and open http://localhost:3300