The Ultimate Guide to Cube.js - the Open Source Dashboard Framework

is an open source framework for building analytical web applications. It is primarily used to build internal business intelligence tools or to add customer-facing analytics to an existing application. In a majority of cases, the first step of building such an application is an analytics dashboard. It usually starts with—“let’s add an analytics dashboard to our admin panel.” Then, as it always happens in software development, things get more complicated, much more complicated. Cube.js When we started working on Cube.js, we wanted to build a tool, which is simple to start but scales easily in features, complexity, and data volume. Cube.js puts down a solid foundation for your future analytical system, whether it is a standalone application or embedded into the existing one. You can think about this tutorial as I’ll walk you through the basic steps of designing the first dashboard from the database to visualizations. “Cube.js 101.” The live demo of the final dashboard is available here. . The full source code is on Github Architecture The majority of modern web applications are built as a , where the frontend is separated from the backend. The backend also usually is split into multiple services, following a . single-page application microservice architecture Cube.js embraces this approach. Conventionally you run Cube.js Backend as a service. It manages the connection to your database, including queries queue, caching, pre-aggregation, and more. It also exposes an API for your frontend app to build dashboards and other analytics features. Backend Analytics starts with the data and data resides in a database. That is the first thing we need to have in place. You most likely already have a database for your application and usually, it is just fine to use for analytics. Modern popular databases such as Postgres or MySQL are well suited for a simple analytical workload. By simple, I mean a data volume with less than 1 billion rows. MongoDB is fine as well, the only thing you’ll need to add is MongoDB Connector for BI. It allows executing SQL code on top of your MongoDB data. It is free and can be easily downloaded from the MongoDB website. One more thing to keep in mind is replication. It is considered a bad practice to run analytics queries against your production database mostly because of the performance issues. Cube.js can dramatically reduce the amount of a database’s workload, but still, I’d recommend connecting to the replica. To summarize— If you use Postgres or MySQL, just create a replica and we’re good to go. If you use MongoDB—download MongoDB Connector for BI and create a replica. If you don’t have any data for the dashboard, you can load our sample e-commerce Postgres dataset. $ curl http://cube.dev/downloads/ecom-dump.sql > ecom-dump.sql $ createdb ecom $ psql --dbname ecom -f ecom-dump.sql Now, as we have data in the database, we’re ready to create the Cube.js Backend service. Run the following commands in your terminal: $ npm install -g cubejs-cli $ cubejs create dashboard-backend -d postgres The commands above install Cube.js CLI and create a new service, configured to work with Postgres database. Cube.js uses environment variables for configuration. It uses environment variables starting with . To configure the connection to our database, we need to specify the DB type and name. In the Cube.js project folder replace the contents of with the following: CUBEJS_ .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 uses the data schema to generate an SQL code, which will be executed in your database. The data schema is not a replacement for SQL. It is designed to make SQL reusable and give it a structure while preserving all of its power. Basic elements of the data schema are `measures` and `dimensions`. Cube.js data schema is referred to as quantitative data, such as the number of units sold, number of unique visits, profit, and so on. Measure is referred to as categorical data, such as state, gender, product name, or units of time (e.g., day, week, month). Dimension Conventionally, schema files are located in the `schema` folder. Here is an example of the schema, which can be used to describe users’ data. cube( , { : , : { : { : , : } }, : { : { : , : }, : { : , : }, : { : , : } } }); `Users` sql `SELECT * FROM users` measures count sql `id` type `count` dimensions city sql `city` type `string` signedUp sql `created_at` type `time` companyName sql `company_name` type `string` Now, with the above schema in place, we can send queries to the Cube.js backend about users’ data. Cube.js queries are plain javascript objects. Usually it has one or more , , and . measures dimensions timeDimensions If we want to answer the question “Where are our users based?” we can send the following query to the 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. Let’s create a slightly more complicated query. We can add a to see how the ratio of different cities has been changing every month during the last year. To do this, we will add a time dimension, group it by monthly, and filter only last year’s signups. timeDimensions signedUp { : [ ], : [ ], : [{ : , : , : [ , ] }] } measures 'Users.count' dimensions 'Users.city' timeDimensions dimension 'Users.signedUp' granularity 'month' dateRange '2018-01-31' '2018-12-31' Cube.js can generate simple schemas based on your database’s tables. Let’s generate schemas we need for our dashboard and then start a dev server. $ cubejs generate -t users,orders $ npm run dev You can inspect generated schemas and send test queries by opening a development playground at . http://localhost:4000 Frontend We’ll build our frontend and dashboard with React, using the Cube.js React client. But you can use any framework or just vanilla javascript to build a frontend with Cube.js. This tutorial shows you how to build a dashboard in pure javascript. We’ll set everything up using Create React App, which is officially supported by the React team. It packages all the dependencies for React app and makes it easy to get started with a new project. Run the following commands in your terminal: $ npx create-react-app dashboard-frontend $ cubejs-dashboard $ npm start cd The last line starts a server on port 3000 and opens your web browser at . http://localhost:3000 We’ll build our UI with Reactstrap, which is a React wrapper for Bootstrap 4. Install Reactstrap and Bootstrap from NPM. Reactstrap does not include Bootstrap CSS, so this needs to be installed separately: $ npm install reactstrap bootstrap --save Import Bootstrap CSS in the `src/index.js` file before importing : ./index.css ; import 'bootstrap/dist/css/bootstrap.min.css' Now we are ready to use the Reactstrap components. The next step is to install Cube.js client to fetch the data from the server and our visualization library to display it. For this tutorial, we’re going to use Recharts. Cube.js is visualization agnostic, meaning you can use any library you want. We’ll also use moment and numeral to nicely format dates and numbers. $ npm install --save @cubejs-client/core @cubejs-client/react recharts moment numeral Finally, we’re done with dependencies, so let’s go ahead and create our first chart. Replace the contents of with the following: src/App.js React, { Component } ; { BarChart, Bar, XAxis, YAxis, Tooltip, ResponsiveContainer } ; cubejs ; moment ; { QueryRenderer } ; cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, { : process.env.REACT_APP_API_URL }); dateFormatter = moment(item).format( ); { render() { ( ); }} /> ); } } export default App; import from "react" import from "recharts" import from "@cubejs-client/core" import from "moment" import from "@cubejs-client/react" const apiUrl const => item "MMM YY" class App extends Component return { if (!resultSet) { return "Loading..."; } return ( QueryRenderer query {{ measures: Orders.count timeDimensions: dimension: Orders.createdAt dateRange: 2017-01-01 2018-12-31 granularity: month cubejsApi {cubejsApi} render {({ resultSet You can check out this example in the CodeSandbox. Let’s look deeper at how we load data and draw the chart. First, we’re initializing the Cube.js API client: cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, { : process.env.REACT_APP_API_URL }); const apiUrl Here we are using the and environment variables. Create React App automatically loads your env variables from the . file if they start with . The Cube.js backend will print the development API token during the startup. REACT_APP_CUBEJS_TOKEN REACT_APP_API_URL env REACT_APP_ Create a file with the correct credentials. .env REACT_APP_CUBEJS_TOKEN=COPY-API-TOKEN-FROM-TERMINAL-OUTPUT REACT_APP_API_URL=http://localhost:4000/cubejs-api/v1 Next, we are using the QueryRenderer Cube.js React Component to load Orders data. { }} /> measures "Orders.count" timeDimensions dimension "Orders.createdAt" dateRange "2017-01-01" "2018-12-31" granularity "month" // Render result QueryRenderer performs an API request to the Cube.js backend and uses the technique to let you render the result however you want to. We’ve already covered the query format above, but in case you want to refresh—here is render props the query format full reference. The parameter of QueryRenderer is a function of the type . The output of this function will be rendered by the QueryRenderer. A is an object containing data obtained from the query. If this object is not defined, it means that the data is still being fetched. render ({error, resultSet, isLoading}) => React.Node resultSet provides multiple methods for data manipulation, but in our case, we need just the method, which returns data in a format expected by Recharts. resultSet chartPivot We’ll plot the Orders data as a bar chart inside a responsive container. (!resultSet) { ; } ( ); if return "Loading..." return ResponsiveContainer width "100%" height {300} Building a Dashboard We learned how to build a single chart with Cube.js and Recharts, and we are now ready to start building the whole dashboard. There are some best practices regarding designing the layout of the dashboard. The common practice is to put the most important and high-level metrics on the top as single value charts, sometimes called , and then list the relevant breakdowns of those metrics. KPIs Here is the screenshot of our final dashboard with KPIs on top followed by bar and line charts. First, let’s refactor our chart and extract the common code into a reusable component. Create an file the following content: src/Chart.js React ; { Card, CardTitle, CardBody, CardText } ; { QueryRenderer } ; Chart = ( {title} { if (!resultSet) { return ; } return render(resultSet); }} /> ); export default Chart; import from "react" import from "reactstrap" import from "@cubejs-client/react" const ( ) => { cubejsApi, title, query, render } Card Next, let’s use this component to create the dashboard. Replace the content of with the following: src/App.js React, { Component } ; { Container, Row, Col } ; { AreaChart, Area, XAxis, YAxis, Tooltip, ResponsiveContainer, Legend, BarChart, Bar } ; moment ; numeral ; cubejs ; Chart ; cubejsApi = cubejs(process.env.REACT_APP_CUBEJS_TOKEN, { : process.env.REACT_APP_API_URL }); numberFormatter = numeral(item).format( ); dateFormatter = moment(item).format( ); renderSingleValue = ( ); { render() { ( renderSingleValue(resultSet, "Users.count")} /> renderSingleValue(resultSet, "Orders.count")} /> renderSingleValue(resultSet, "Orders.count")} /> <Chart cubejsApi={cubejsApi} title="New Users Over Time" query={{ measures: ["Users.count"], timeDimensions: [ { dimension: "Users.createdAt", dateRange: ["2017-01-01", "2018-12-31"], granularity: "month" } ] }} render={resultSet => ( )} /> <Chart cubejsApi={cubejsApi} title="Orders by Status Over time" query={{ measures: ["Orders.count"], dimensions: ["Orders.status"], timeDimensions: [ { dimension: "Orders.createdAt", dateRange: ["2017-01-01", "2018-12-31"], granularity: "month" } ] }} render={resultSet => { return ( ); }} /> ); } } export default App; import from "react" import from "reactstrap" import from "recharts" import from "moment" import from "numeral" import from "@cubejs-client/core" import from "./Chart.js" const apiUrl const => item "0,0" const => item "MMM YY" const ( ) => resultSet, key {numberFormatter(resultSet.chartPivot()[0][key])} h1 height {300} h1 class App extends Component return Container fluid That is enough to build our first dashboard. . Give it a try in the CodeSanbox Next Steps We’ve built a simple proof of concept dashboard with Cube.js. You can check the . The is available on Github. live demo here full source code To learn more about Cube.js backend deployment, you can refer to the Also, here you can find on a variety of topics. deployment documentation. more tutorials And join our ! It is a great to place to get help and stay up to date with new releases. Slack Community

The Ultimate Guide to Cube.js - the Open Source Dashboard Framework

Too Long; Didn't Read

Companies Mentioned

@keydunov

RELATED STORIES