Building an Analytics Dashboard with Node, Express, and Cube.js
Tags: javascript, node, opensource, tutorial
Published: 2019-03-14 14:15:51

---
title: Building an Analytics Dashboard with Node, Express, and Cube.js
published: true
description: Building an Analytics Dashboard with Node, Express, and Cube.js
tags: javascript,node,opensource,tutorial
cover: https://thepracticaldev.s3.amazonaws.com/i/8s4o69pbdb5tc3gnhp8z.png
canonical_url: https://statsbot.co/blog/node-express-analytics-dashboard-with-cubejs
cover_image: https://thepracticaldev.s3.amazonaws.com/i/iz6qkuret6vya69b6tog.png
---

In the following tutorial, I’ll show you how to create a basic analytics dashboard with Node, Express, and [Cube.js](https://github.com/cube-js/cube.js). As a database, we’re going to use MongoDB with MongoDB BI Connector. If you’re not familiar with it, I highly recommend you go through the [Building a MongoDB Dashboard tutorial](https://statsbot.co/blog/building-mongodb-dashboard-using-node.js). It covers the basics of setting up Mongo and its BI Connector.

Here how the final dashboard would look. You can check [the live demo on Heroku here.](https://express-analytics-dashboard.herokuapp.com) The full source code is [available on Github.](https://github.com/cube-js/cube.js/tree/master/examples/express-analytics-dashboard)

![](https://media.graphcms.com/hSWmTK9hQgyFh2eISUdL)

## Getting a Sample Dataset

_You can skip this step if you already have some data for your dashboard_

If you don’t have a local MongoDB instance, please download it [here](https://www.mongodb.com/download-center/community). The BI Connector can be downloaded [here](https://www.mongodb.com/download-center/bi-connector).

There is a good [repository on Github](https://github.com/ozlerhakan/mongodb-json-files) with a curated list of JSON / BSON datasets from the web in order to practice in MongoDB. We’ll pick a [tweets dataset](https://github.com/ozlerhakan/mongodb-json-files/blob/master/datasets/tweets.zip) for our dashboard.

[Download test data](https://github.com/ozlerhakan/mongodb-json-files/blob/master/datasets/tweets.zip) and import it using the following command in your MongoDB directory.

```bash
$ bin/mongorestore Your-Downloads-Folder/dump/twitter/tweets.bson
```

Now make sure both MongoDB and MongoDB BI Connector processes are running.

```bash
# Run from MongoDB directory
$ bin/mongod

# Run from MongoDB BI Connector directory
$ bin/mongosqld
```

## Setting up a Backend

We’ll use an [express application generator](https://expressjs.com/en/starter/generator.html) to create an application skeleton. 

```bash
# Install it if you don’t have it already
$ npm install express-generator -g
```

Next, create a new express app with the view engine set to Handlebars (hbs).

```bash
$ express --view=hbs express-analytics-dashboard
```

We’re going to use [an open source framework, Cube.js](https://cube.dev) as our analytical backend. It generates and executes SQL queries, as well as provides caching, data pre-aggregation, security, and API to query results and build visualizations. [You can learn more about it here.](https://cube.dev)

Cube.js can be easily embedded into an Express application. Let’s add it to our project dependencies.

```bash
$ npm install --save @cubejs-backend/server-core @cubejs-backend/mongobi-driver dotenv
```

We’ve added a core server package for Cube.js and the Cube.js  MongoBI driver. We’ve also added a `dotenv` package to manage our credentials. Let’s create a `.env` file with the following credentials; we need them to tell Cube.js how to connect to Mongo.

```bash
CUBEJS_DB_HOST=localhost
CUBEJS_DB_NAME=twitter
CUBEJS_DB_PORT=3307
CUBEJS_DB_TYPE=mongobi
CUBEJS_API_SECRET=SECRET
```

Now, let’s mount the Cube.js Server into our express application. Add the following code right after the routes declaration in your `app.js`.

```javascript
var CubejsServerCore = require('@cubejs-backend/server-core');
// ...
app.use('/', indexRouter);

require('dotenv').config();
CubejsServerCore.create().initApp(app);
// ...
```

With the above two lines of code, we have loaded all required configs from the `.env` file and mounted Cube.js into our Express app. By default, it’s mounted into the `/cubejs-api/v1/` path namespace. But you can change it and a lot of other things by passing the [configuration object](https://cube.dev/docs/@cubejs-backend-server-core#api-reference-cubejs-server-core-create-options) to the `CubejsServerCore.create()` method. We’ll keep the default settings for our tutorial.

Now, let’s create a Cube.js Schema for our tweets table. Cube.js uses Data Schema to generate and execute SQL; [you can read more about it here.](https://cube.dev/docs/getting-started-cubejs-schema)

Create a folder, `schema`, with a `Tweets.js` file inside with the following content.

```javascript
cube(`Tweets`, {
  sql: `select * from tweets`,

  measures: {
    count: {
      type: `count`
    },

    favoriteCount: {
      type: `sum`,
      sql: `favorite_count`
    },

    retweetCount: {
      type: `sum`,
      sql: `retweet_count`
    }
  },

  dimensions: {
    location: {
      type: `string`,
      sql: `\`user.location\``
    },

    lang: {
      type: `string`,
      sql: `lang`
    }
  }
});
```

In Cube.js, you can describe your queries in Javascript and then they will be compiled into SQL and executed inside your database. It uses `measures` and `dimensions` as basic units to describe various analytics queries. [This tutorial is a good place to get started with Cube.js Schema.](https://cube.dev/docs/getting-started-cubejs-schema)


Now let’s move on to building a dashboard on the frontend.

## Alternative Setup: Run Cube.js in Serverless Mode

If you want to run it as a microservice or as a serverless function - use Cube.js CLI. The code below shows how you can generate a new Cube.js app with Cube.js CLI:

```bash
$ npm install -g cubejs-cli
$ cubejs create -d mongobi -t serverless
```

It will create a new project pre-configured to be deployed to AWS Lambda with Serverless framework. You can learn more about [Cube.js serverless deployment here.](https://cube.dev/docs/deployment)

## Building Analytics Dashboard

We’ll use Bootstrap for styling, the Cube.js client to load data, and Chart.js to display it.
Replace the content of `views/index.hbs` with the following.

```html
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
      integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.2/Chart.bundle.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>
<script src="https://unpkg.com/@cubejs-client/core@0.6.0/dist/cubejs-client-core.js"></script>

<div class="container" id="app">
  <div class="row">
    <div class="col-md-4">
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Total Tweets</h5>
                <div class="card-text">
                    <h3 id="total-tweets"></h3>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Total Retweets</h5>
                <div class="card-text">
                  <h3 id="total-retweets"></h3>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card">
            <div class="card-body">
                <h5 class="card-title">Total Favorites</h5>
                <div class="card-text">
                  <h3 id="total-favorites"></h3>
                </div>
            </div>
        </div>
    </div>
  </div>
  <br />
  <br />
  <div class="row">
      <div class="col-md-6">
          <div class="card">
              <div class="card-body">
                  <h5 class="card-title">Top Tweets Locations</h5>
                  <div class="card-text">
                    <canvas id="pie-chart"></canvas>
                  </div>
              </div>
          </div>
      </div>
      <div class="col-md-6">
          <div class="card">
              <div class="card-body">
                  <h5 class="card-title">Most Popular Languages</h5>
                  <div class="card-text">
                    <canvas id="bar-chart"></canvas>
                  </div>
              </div>
          </div>
      </div>
  </div>
</div>

<script>
  var cubejsApi = cubejs(
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NTIzOTk5MjcsImV4cCI6MTU1MjQ4NjMyN30.SOO-A6GfGH7ar3EoeBb0cjj10BVxO3ffjvmqQziXIZA',
    { apiUrl: 'http://localhost:3000/cubejs-api/v1' }
  );

  var kpis = [
    { measure: "Tweets.count", element: "total-tweets" },
    { measure: "Tweets.retweetCount", element: "total-retweets" },
    { measure: "Tweets.favoriteCount", element: "total-favorites" }
  ];

  kpis.forEach(kpi => {
    cubejsApi.load({
      measures: [kpi.measure]
    }).then(resultSet => {
      document.getElementById(kpi.element).textContent =
        numeral(resultSet.totalRow()[kpi.measure]).format('0,0');
    })
  });

  // A helper method to format data for Chart.js
  // and add some nice colors
  var chartJsData = function(resultSet) {
    return {
      datasets: [{
        data: resultSet.series()[0].series.map(function(r) { return r.value }),
        backgroundColor: [
          'rgb(255, 99, 132)',
          'rgb(255, 159, 64)',
          'rgb(255, 205, 86)',
          'rgb(75, 192, 192)',
          'rgb(54, 162, 235)'
        ]
      }],
      labels: resultSet.categories().map(function(c) { return c.category })
    }
  }

  cubejsApi.load({
    measures: ["Tweets.count"],
    dimensions: ["Tweets.location"],
    filters: [
      {
        dimension: "Tweets.location",
        operator: "notEquals",
        values: [""]
      }
    ],
    limit: 5
  }).then(resultSet => {
    new Chart(document.getElementById("pie-chart"), {
      type: 'pie',
      data: chartJsData(resultSet)
    })
  });

  cubejsApi.load({
    measures: ["Tweets.count"],
    dimensions: ["Tweets.lang"],
    limit: 5
  }).then(resultSet => {
    new Chart(document.getElementById("bar-chart"), {
      type: 'bar',
      data: chartJsData(resultSet),
      options: { legend: { display: false } }
    })
  });
</script>
```

Let’s break this down into pieces. First, we’re loading our required libraries. The Cube.js client could be installed in different ways, here we’re just loading a UMD build from CDN. We’re also loading Bootstrap, Chart.js, and numeral.js to format numbers from CDN.

```html
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
      integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.2/Chart.bundle.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>
<script src="https://unpkg.com/@cubejs-client/core@0.6.0/dist/cubejs-client-core.js"></script>
```

The next part is just a plain HTML markup with the Bootstrap grid. 


The last part is where we load and display data in our dashboard widgets. For the sake of this tutorial, we don’t use any frontend libraries. But, if you want, Cube.js has bindings for all popular frontend frameworks, such as React. 

First, we’re initializing the Cube.js client and passing an API Token and API URL. Your API Token should be printed to the terminal upon the server start. The URL should be the same.

```javascript
var cubejsApi = cubejs(
  'YOUR-API-TOKEN',
  { apiUrl: 'http://localhost:3000/cubejs-api/v1' }
);
```

Next, we’re loading and displaying data for the upper row of the dashboard, the KPIs section. Here we display just the plain numbers with some formatting done by numeral.js.

```javascript
var kpis = [
  { measure: "Tweets.count", element: "total-tweets" },
  { measure: "Tweets.retweetCount", element: "total-retweets" },
  { measure: "Tweets.favoriteCount", element: "total-favorites" }
];

kpis.forEach(kpi => {
  cubejsApi
    .load({
      measures: [kpi.measure]
    })
    .then(resultSet => {
      document.getElementById(kpi.element).textContent = numeral(
        resultSet.totalRow()[kpi.measure]
      ).format("0,0");
    });
});
```


The row has one pie and one bar chart, plotted with Chart.js. To display the bar chart, we’re requesting the `Tweets.count` measure and grouping it by the `Tweets.location` dimension. We’re also applying a filter to exclude tweets with an empty location. Finally, we’re setting the limit to 5 to get only the top 5 locations.

You can learn more about the Cube.js Query format here.

For the bar chart, we’re doing a similar grouping, but instead of location, we’re grouping by the `Tweets.lang` dimension.


```javascript
// A helper method to format data for Chart.js
// and add some nice colors
var chartJsData = function(resultSet) {
  return {
    datasets: [
      {
        data: resultSet.series()[0].series.map(function(r) {
          return r.value;
        }),
        backgroundColor: [
          "rgb(255, 99, 132)",
          "rgb(255, 159, 64)",
          "rgb(255, 205, 86)",
          "rgb(75, 192, 192)",
          "rgb(54, 162, 235)"
        ]
      }
    ],
    labels: resultSet.categories().map(function(c) {
      return c.category;
    })
  };
};

cubejsApi
  .load({
    measures: ["Tweets.count"],
    dimensions: ["Tweets.location"],
    filters: [
      {
        dimension: "Tweets.location",
        operator: "notEquals",
        values: [""]
      }
    ],
    limit: 5
  })
  .then(resultSet => {
    new Chart(document.getElementById("pie-chart"), {
      type: "pie",
      data: chartJsData(resultSet)
    });
  });

cubejsApi
  .load({
    measures: ["Tweets.count"],
    dimensions: ["Tweets.lang"],
    limit: 5
  })
  .then(resultSet => {
    new Chart(document.getElementById("bar-chart"), {
      type: "bar",
      data: chartJsData(resultSet),
      options: { legend: { display: false } }
    });
  });
```

Now, to see the dashboard in action, start your server.

```bash
$ npm start
```

And visit [http://localhost:3000](http://localhost:3000) to see your analytics dashboard in action. Also, we have a live demo of the app [hosted on Heroku here](https://express-analytics-dashboard.herokuapp.com/).
The [full source code is available on Github.](https://github.com/cube-js/cube.js/tree/master/examples/express-analytics-dashboard) 

## Why Cube.js

Why is using Cube.js better than hitting MongoDB directly with SQL queries? Cube.js solves a plethora of different problems every production-ready analytic application needs to solve: analytic SQL generation, query results caching and execution orchestration, data pre-aggregation, security, API for query results fetch, and visualization.

![](https://raw.githubusercontent.com/statsbotco/cube.js/master/docs/old-was-vs-cubejs-way.png)

These features allow you to build production-grade analytics applications that are able to handle thousands of concurrent users and billions of data points. They also allow you to do analytics on a production MongoDB read replica or even a MongoDB main node due to their ability to reduce the amount of actual queries issued to a MongoDB instance.