Node Express Analytics Dashboard with Cube.js

Node Express Analytics Dashboard with Cube.js

In the following tutorial, I’ll show you how to create a basic analytics dashboard with Node, Express, and&nbsp;<a href="https://github.com/statsbotco/cube.js" target="_blank">Cube.js</a>. 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&nbsp;<a href="https://statsbot.co/blog/building-mongodb-dashboard-using-node.js" target="_blank">Building a MongoDB Dashboard tutorial</a>. It covers the basics of setting up Mongo and its BI Connector.

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="/javascripts/cubejs-client-core.umd.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="/javascripts/cubejs-client-core.umd.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.

database node-js express data-analysis javascript

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

How to Use Express.js, Node.js and MongoDB.js

In this post, I will show you how to use Express.js, Node.js and MongoDB.js. We will be creating a very simple Node application, that will allow users to input data that they want to store in a MongoDB database. It will also show all items that have been entered into the database.

How to creating and managing sessions in Node.js and Express.js for Beginners

In this article, I am going to Creating and managing sessions in in Node.js. We will use express as a framework and various other modules such as body-parser to handle form data.

Node.js Express FrameWork Tutorial - Learn Express.js in 15 minutes

Express JS is an awesome opinionated framework for Node.js that helps you create REST end points. Let's learn Express.js in 15 minutes! - Node.js Express FrameWork Tutorial. This framework is built in such a way that it acts as a minimal and flexible Node.js web application framework, providing a robust set of features for building single and multipage, and hybrid web application.

Node.js Express MongoDB Tutorial

Node.js Express MongoDB Tutorial is CRUD example, I have created to showcase Express web framework, MongoDB NoSQL database, and Node.js server platform.

Hands on with Node.Js Streams | Examples & Approach

The practical implications of having Streams in Node.js are vast. Nodejs Streams are a great way to handle data chunks and uncomplicate development.