How we use Rails to extract and democratise Business Intelligence from data at Up

cmaitchison commented 5 years ago

A deep dive into how the Up Rails monolith asynchronously transforms data structured by the operational DB schema into data structured in an analytical schema, in order to power business intelligence queries - all within the monolith.

We use Multiverse to connect certain ActiveRecord objects to another Postgresql database. We then use background jobs to continually poll the operational tables in order to extract the data, strip it of Personally Identifiable Information (PII) and then load it into an analytics database, with different accessibility characteristics to the operational database. We then point the open source BI tool, Metabase, at our analytics database and democratise the access to business intelligence around our organisation without any detriment to the privacy of our customers.

In this talk I will discuss:

The details of how to connect your ActiveRecord objects to multiple databases, and why you would do that.
The differences in a transactional schema and an analytics schema (OLTP vs OLAP), aka Data Warehousing or Star Schema
How to transform data between the two structures
The privacy concerns around performing business intelligence on operational data
The benefits of the democratisation of access to business intelligence
Why we chose Metabase as our window into our analytics database