Artyom Keydunov for Cube

Posted on Mar 14, 2019 • Updated on Jul 2, 2019 • Originally published at statsbot.co

Building an Analytics Dashboard with Node, Express, and Cube.js

#javascript #node #opensource #tutorial

In the following tutorial, I’ll show you how to create a basic analytics dashboard with Node, Express, and 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. 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. The full source code is available on Github.

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. The BI Connector can be downloaded here.

There is a good repository on Github with a curated list of JSON / BSON datasets from the web in order to practice in MongoDB. We’ll pick a tweets dataset for our dashboard.

Download test data and import it using the following command in your MongoDB directory.

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

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

# 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 to create an application skeleton.

# 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).

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

We’re going to use an open source framework, Cube.js 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.

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

$ 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.

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.

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 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.

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

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.

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:

$ 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.

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.

<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.

<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.

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.

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.

// 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.

$ npm start

And visit http://localhost:3000 to see your analytics dashboard in action. Also, we have a live demo of the app hosted on Heroku here.
The full source code is available on Github.

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.

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.