Better Business Intelligence in Elixir with Livebook
Tags: elixir, livebook, startup
Published: 2021-07-30 16:57:02

As a developer, has your manager ever come and asked a question like, "How much money are we making?" If you were a line-of-business developer at a global insurance company, you'd reach for your handy, nosebleed-expensive Business Intelligence (BI) suite to answer this question. But you're not, so how did you answer it for them?

Obviously, you'd do what we all do. You'd SSH into your server, start an Elixir `iex` session or a Rails console, then run some scripts to query data, sum numbers, and come up with answers.

Well, give yourself a raise! Because you just built a  BI suite.

It may not seem super sophisticated, but it solves the business need. And for problems like this, Livebook can be a better BI tool for Elixir developers.

## BI What?

What is a [BI tool](https://en.wikipedia.org/wiki/Business_intelligence)?

> Business intelligence (BI) comprises the strategies and technologies used ... for the data analysis of business information.

Translated from Gartner-speak, that means any tools you run to get a picture of how the business is doing are BI tools.

In the last bootstrapped startup I worked at, management routinely asked backend developers for business numbers. It was simple stuff, like:

- How many new clients did we add this week?
- What was our customer's spend?
- Who are our top 10 customers this week and what were their numbers?

As simple as this stuff seems, it's really important for those business focused leaders to understand and make better decisions. That's why global insurance companies with applications that are too complicated to bring up a Rails console on spend six figures on BI suites.

How did we get those answers? Using our Elixir `iex`, or interactive shell. We ran some scripts and gave them CSV friendly rows they could add to their spreadsheets. In that early stage startup, we were using `iex` as our BI tool. At a startup before that, we did the same thing but using the Rails console.

If you're using the Rails console, Elixir's `iex`, or another REPL to examine your data, then that's your BI tool for now. But with Elixir, we can do better. Livebook gives you data, charts and graphs too, but because it's executing your Elixir code, it can also call out to your other integrations and pull in even more.

To understand why Livebook can be a better tool, let's go further and talk about BI tools in general, not just your REPL.

## Old School Business Intelligence

Our premise in this post is that we can give "serious" BI tools a run for their money with Elixir and Livebook. Let's see what we're up against.

Companies spend lots of money every year on their BI tools. You hear some of the numbers and it seems bananas. But it's because they _add_ a lot of value. Spotting trends in your data and customer behavior can make the difference of success and failure for a company.

Most BIS tools are commercial. But there's a handful of credible open source projects. [Metabase](https://www.metabase.com/), for example, is an open source BI tool that works quite well. It connects directly to an application's database and helps you do some spelunking, aggregation, and  shiny graphing. You can even create and share custom dashboards. Think of it as Grafana, but for MBAs – it's a great tool.


<div class="callout">
Deploying Metabase alongside your app might look like this:

![Metabase app setup](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eqn1qzsfh0ff7d8x93nx.png)

Metabase is an application that probably shouldn't be exposed publicly and it needs direct access to your database. It's also a bit of a mammoth – one doesn't just walk into Metabase and expect to get anything done, there's a real learning curve even when you know how to write SQL. It can be a heavy tool when you just want to do some quick poking around.

It's also an app you need to keep running. We sell hosting, so we're generally OK with that. In fact, [Metabase ships a Docker image](https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-docker.html) and Fly lets you quickly [deploy apps using Docker](http://127.0.0.1:43579/docs/app-guides/run-a-global-image-service/#deploying-docker-images-to-fly). Money.

</div>

This is fine when you want a dedicated data dashboard or you want to let non-developers see reports and graphs and be business-intelligent. However, when a project is young and you're a developer, digging with code is powerful. This is where Livebook can help!

## Why is Livebook Better?

Let's start with what Livebook is.

[Livebook](https://github.com/elixir-nx/livebook) started out as Elixir's version of [Jupyter Notebooks](https://jupyter.org/). Jupyter is pretty great. But it turns out that code notebooks on Elixir are something special; they do something you usually can't pull off in Python. That's because Elixir has powerful built-in clustering, built on Erlang's BEAM/OTP runtime. Livebook notebooks can talk directly to running Elixir apps.  And so we can do analysis and visualization directly off the models in our applications.

Livebook really sings on Fly.io. We make it easy to deploy clusters of Elixir applications that can talk privately between themselves. More importantly: it's easy to bring up a secure [WireGuard](https://www.wireguard.com/) tunnel to those applications. So I can run Livebook locally on my machine, and attach it to any of my apps running on Fly.io!

![Livebook remote connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/df8dx3q7mbi8gujmfcut.png)

For lean-and-mean startups, this is a win. You only need your app and your database running. Then, on an "as-needed" basis, you connect to your app with Livebook for analysis. Inside Livebook, analysis is done using your app's Elixir code. Livebook therefore doesn't need to connect directly to the database to run queries, and, even better, we get to re-use the business logic, associations, and schemas our apps already have.

As a BI tool, Livebook notebooks  have these benefits:

- They use your application's code, like a REPL lets you.
- They have the ability to generate charts, graphics, and tables.
- Notebooks are markdown files and can be checked-in with your project. They can be shared with the team! No more, "I can't run the numbers today because Bill is out and he has the scripts."
- They are self-documenting because it's just markdown.
- They are designed to be highly reproducible. They are easy to re-run when you want updated numbers.

Some of this you can pull off using just your REPL and some raw SQL queries. But why would you?   It's easier to use your project's code, database models (Ecto schemas for Elixir) and associations.

Further,  your apps probably rely on external services like Stripe. Because Livebook talks directly to your Elixir code, you can query those external services with  that code, and then combine the results with your data. This is a combination that even dedicated tools like Metabase have a hard time beating.

## Connecting to Your App on Fly

Great! You have a notebook that loads and visualizes some data! To get the benefits, you need to connect it to your app running on Fly.io.

Follow the Fly.io [Elixir Guide for Connecting Livebook to your App in Production](/docs/app-guides/elixir-livebook-connection-to-your-app/) to connect Livebook to your app.

With Livebook connected to your app, you can run your notebook and start gaining insight to your data!

## Gaining Intelligence

How you use Livebook depends on your application and your industry.

Here are some ideas to get the brain juices flowing. Each of these example notebooks would be a "module" in a serious commercial BI suite, and you'd pay $45k for a license for it.

### User Account Setup

How many of your users have fully set up their accounts?

Build and share a notebook that tracks where users are in your onboarding process.

### Users Bouncing From The App

Where are accounts stalling out in onboarding?

Graph the stages accounts are at and let analysts drill into the onboarding funnel.

### Sales Analysis

How were sales for your products or services last month?

Graph a multi-series chart comparing sales across products.

What about total sales per week?

Build a notebook with a date input making it easy to switch the week being charted.

### Integrations

What external financial systems are you integrated with?

Notebooks can execute your code to query those services and visualize refund rates, processing fees, and more.

## Wrapping Up

What's great about the Livebook approach is you are writing working Elixir code. When you are ready to build an Admin Dashboard page in your app, you've already done the hard work of figuring out what data is valuable and even the code for how to get it!