README.md

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), saveable views, 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

• Dashboards
• Better graph controls
• Graphs that don't require a time column
• View generated SQL
• 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 local database

Insights needs one database for its internal use.

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

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. Configure the connection your app

The connection to your app lives in config/initializers/insights.rb.

Copy it from config/initializers/insights.rb.example and edit accordingly.

2.4.1. Set up the path to insights.yml

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.4.2. Set up the database connection

INSIGHTS_DATABASE = {
  adapter: 'postgresql',
  encoding: 'unicode',
  host: 'localhost',
  database: 'insights_demo',
  pool: 5,
  variables: {
    # it's strongly recommended to have a timeout here!
    statement_timeout: 5000
  }
}

2.4.3. Set up the credentials

Uncomment any of the following to either have your app open to everyone or protected by a login screen.

INSIGHTS_LOGIN = false # no login screen
INSIGHTS_LOGIN = ['demo', 'demo']
INSIGHTS_LOGIN = [['demo', 'demo'], ['admin', 'pass']]
INSIGHTS_LOGIN = -> (user, pass) { user == 'demo' && pass == 'password' }
INSIGHTS_LOGIN = -> (user, pass) { connect_to_your_app_and_check_the_credentials(user, password) }

There's an example on authenticating against a devise backed users table in insights.rb.example

2.5. Run it!

bundle exec foreman start

and open http://localhost:3300

Misc

Updating

Run these commands to update

git pull
bundle
yarn
bundle exec rake db:migrate

Compile the assets for production

./script/build/react
RAILS_ENV=production bundle exec rake assets:precompile