Cube.js: The Ultimate Guide to Open Source Dashboard Framework

Cube.js is an open source framework for building analytical web applications. It is mainly used to build internal business intelligence tools or add customer-oriented analysis to existing applications. In most cases, the first step in building such an application is to analyze the dashboard. It usually starts with "adding an analysis dashboard to the management panel", and then, as often happens in software development, things get more complicated.

Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.

When you start using Cube.js, you will want to build a tool that is simple at first, but easily expandable in terms of functionality, complexity, and data volume. Cube.js lays a solid foundation for future analysis systems, whether it is a stand-alone application or embedded in an existing analysis system.

This tutorial can be regarded as "Cube.js 101", which introduces the basic design steps of the first dashboard from database to visualization.

Architecture

Most modern web applications are built as single-page applications, with front-end and back-end separation. Following the microservice architecture, the backend is usually divided into multiple services.

Usually, the backend of Cube.js runs as a service, managing the connection to the database, including query queues, caching, and pre-aggregation. At the same time, an API is exposed for front-end applications to build dashboards and other analysis functions.

rear end

The analysis starts with the data generated and resides in the database. If the user already has a database suitable for the application, it can usually be used directly for analysis. Modern popular databases, such as Postgres or MySQL, can do simple analysis work. The simple term here refers to data with less than 1 billion rows.

In addition, MongoDB is also possible, but you need to add MongoDB Connector for BI. It allows executing SQL code on top of MongoDB data. This is free and can be downloaded directly from the MongoDB website. It should be noted that for performance reasons, it is not good practice to run analytical queries directly on the production database. Therefore, even though Cube.js can significantly reduce the workload of the database, it is still recommended to connect to the copy.

All in all, if you are using Postgres or MySQL, you only need to create a copy. If you use MongoDB, please download MongoDB Connector for BI and create a copy.

If you don't have any data to build the dashboard, you can load the e-commerce Postgres dataset in the example.

$ curl http://cube.dev/downloads/ecom-dump.sql> ecom-dump.sql
$createdbecom
$ psql--dbname ecom -f ecom-dump.sql

When there is data in the database, you can start to create the back-end service of Cube.js. Run the following command in the terminal:

$npm install -g cubejs-cli
$cubejs create dashboard-backend -d postgres

The above command installs the Cube.js CLI and creates a new service, configured to work with the Postgres database.

Cube.js uses environment variables for configuration. The environment variables start with CUBEJS_. To configure the connection to the database, you need to specify the type and name of the database. In the Cube.js project folder, replace the following contents of .env:

CUBEJS_API_SECRET =SECRET
CUBEJS_DB_TYPE = postgres
CUBEJS_DB_NAME = ecom

Cube.js data schema

The next step is to create a Cube.js data Schema. Cube.js uses the data schema to generate SQL code, which will be executed in the database. Data Schema is not a substitute for SQL. It aims to make SQL reusable and give it structure while retaining all its features. The basic elements of data Schema are measures and dimensions.

Measure is called quantitative data, such as unit sales, unique visits, profit, etc.

Dimension is called classification data, such as status, gender, product name, or time unit (for example, day, week, month).

Usually, the schema file is located in the schema folder. The following are examples of architectures that can be used to describe user data.

cube（`Users`，{
sql：`SELECT*FROMusers`，

measures：{
count：{
sql：`id`，
type：`count`
}
}，

dimensions：{
city：{
sql：`city`，
type：`string`
}，

signedUp：{
sql：`created_at`，
type：`time`
}，

companyName：{
sql：`company_name`，
type：`string`
}
}
}）;
{1}

Now, through the above Schema, you can send queries about user data to the Cube.js backend. Cube.js queries are pure JavaScript objects. Usually, it has one or more measures, dimensions and timeDimensions.

If you want to answer the question "Where is the user?", you can send the following query to Cube.js:

{
measures: ['Users.count'],
dimensions: ['Users.city']
}

Cube.js will generate the required SQL based on the Schema, execute it and send the result back.

We can create a slightly more complicated query and add a timeDimensions to see how the proportions of different cities have changed each month last year. To do this, you need to add the signedUp time dimension, group by month, and include only last year’s registrations.

{
measures: ['Users.count'],
dimensions: ['Users.city'],
timeDimensions: [{
dimension:'Users.signedUp',
granularity:'month',
dateRange: ['2018-01-31','2018-12-31']
}]
}

Cube.js can also generate a simple Schema based on the database table, and need to generate the required Schema for the dashboard, and start a server for development.

$ cubejsgenerate-t users,orders
$ npm run dev

You can open the development background at http://localhost:4000, check the generated Schema and send test queries.

front end

Through the React client of Cube.js, you can use React to build front-ends and dashboards. You can also use any framework or just vanilla JavaScript to build the front end of Cube.js. This tutorial will show you how to build a dashboard in pure JavaScript. We will use Create React App officially supported by the React team to set up everything. It packs all the dependencies of the React application and can easily start using new projects. Run the following command in the terminal:

$npxcreate-react-appdashboard-frontend
$cdcubejs-dashboard
$npmstart

The last line starts the server on port 3000 and opens the web browser via http://localhost:3000.

We will use Reactstrap to build our UI, which is a React wrapper for Bootstrap 4. Use NPM to install Reactstrap and Bootstrap. Reactstrap does not include Bootstrap CSS, so it needs to be installed separately:

$ npminstallreactstrapbootstrap--save

Before importing ./index.css, import the Bootstrap CSS in the src/index.js file:

import'bootstrap/dist/css/bootstrap.min.css';

Now, we are ready to use Reactstrap components.

The next step is to install the Cube.js client so that data can be obtained from the server and visualization library and displayed. In this tutorial, we will use Recharts. Cube.js is visualization agnostic, which means that any required library can be used. We will also use moment and numeral to format dates and numbers nicely.

$ npm install --save@ cubejs-client / core @cubejs-client / react rechartsmomentnumbers

In this way, we get the dependencies. Next, continue to create our first chart, replacing the content of src/App.js with the following:

importReact, {Component} from"react";
import{
BarChart,
Bar,
XAxis,
YAxis,
Tooltip,
ResponsiveContainer
} from"recharts";
importcubejs from"@cubejs-client/core";
importmoment from"moment";
import{QueryRenderer} from"@cubejs-client/react";

const cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, {
apiUrl: process.env.REACT_APP_API_URL
});

const dateFormatter = item => moment(item).format("MMM YY");

classAppextendsComponent{
render() {
return(
<QueryRenderer
query={{
measures: ["Orders.count"],
timeDimensions: [
{
dimension:"Orders.createdAt",
dateRange: ["2017-01-01","2018-12-31"],
granularity:"month"
}
]
}}
cubejsApi={cubejsApi}
render={({ resultSet }) => {
if(!resultSet) {
return"Loading...";
}

return(
<ResponsiveContainerwidth="100%"height={300}>
<BarChartdata={resultSet.chartPivot()}>
<XAxisdataKey="x"tickFormatter={dateFormatter} />
<YAxis/>
<TooltiplabelFormatter={dateFormatter} />
<BardataKey="Orders.count"fill="rgba(106, 110, 229)"/>
</BarChart>
</ResponsiveContainer>
);
}}
/>
);
}
}

exportdefaultApp;