DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • An Introduction to Type Safety in JavaScript With Prisma
  • The Complete Tutorial on the Top 5 Ways to Query Your Relational Database in JavaScript - Part 2
  • Schema Change Management Tools: A Practical Overview
  • What Are SpeedUp and ScaleUp in DBMS?

Trending

  • The Modern Data Stack Is Overrated — Here’s What Works
  • Unlocking AI Coding Assistants Part 4: Generate Spring Boot Application
  • A Developer's Guide to Mastering Agentic AI: From Theory to Practice
  • A Guide to Container Runtimes
  1. DZone
  2. Data Engineering
  3. Databases
  4. Create a D3 Dashboard With Cube.js

Create a D3 Dashboard With Cube.js

By 
Artyom Keydunov user avatar
Artyom Keydunov
·
Jan. 21, 20 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
11.0K Views

Join the DZone community and get the full member experience.

Join For Free

In this tutorial, I’ll cover building a basic dashboard application with Cube.js and the most popular library for visualizing data — D3.js. Although Cube.js doesn’t provide a visualization layer itself, it is very easy to integrate with any existing charting library. Additionally, you can use Cube.js Templates to scaffold a frontend application with your favorite charting library, frontend framework, and UI kit. The scaffolding engine will wire it all together and configure it to work with the Cube.js backend.

You can check the online demo of this dashboard here and the complete source code of the example app is available on Github.

We are going to use Postgres to store our data. Cube.js will connect to it and act as a middleware between the database and the client, providing our API, abstraction, caching, and a lot more. On the frontend, we’ll have React with Material UI and D3 for chart rendering. Below, you can find a schema of the whole architecture of the example app.

If you have any questions while going through this guide, please feel free to join this Slack community and post your question there.

Happy Hacking!

You may also like: Cube.js: Ultimate Guide to the Open-Source Dashboard Framework 

Setting Up a Database and Cube.js

The first thing we need to have in place is a database. We’ll use Postgres for this tutorial. However, you can use your favorite SQL (or Mongo) database. Please refer to the Cube.js documentation on how to connect to different databases.

If you don’t have any data for the dashboard, you can load our sample e-commerce Postgres dataset.

Shell
 




x


 
1
$ curl http://cube.dev/downloads/ecom-dump-d3-example.sql > ecom-dump.sql
2
$ createdb ecom
3
$ 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:

Shell
 




xxxxxxxxxx
1


 
1
$ npm install -g cubejs-cli
2
$ cubejs create d3-dashboard -d postgres



The commands above install Cube.js CLI and create a new service configured to work with a Postgres database.

Cube.js uses environment variables for configuration. It uses environment variables starting with CUBEJS_. 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 the .env file with the following:

Shell
 




xxxxxxxxxx
1


 
1
CUBEJS_API_SECRET=SECRET
2
CUBEJS_DB_TYPE=postgres
3
CUBEJS_DB_NAME=ecom
4
CUBEJS_WEB_SOCKETS=true




Now let’s start the server and open the developer playground at http://localhost:4000.

Shell
 




xxxxxxxxxx
1


 
1
$ npm run dev



The next step is to create a Cube.js data schema. Cube.js uses the data schema to generate SQL, which will be executed in your database. Cube.js Playground can generate simple schemas based on the database’s tables. Let’s navigate to the Schema page and generate the schemas we need for our dashboard. Select the line_items, orders, products, product_categories, and users tables and click Generate Schema.

Generating schema

Generating schema

Let’s test our newly generated schema. Go to the Build page and select a measure in the dropdown. You should be able to see a simple line chart. You can choose D3 from the charting library dropdown to see an example of a D3 visualization. Note that it is just an example and you can always customize and expand it.
Beginning D3 visualization


Now, let’s make some updates to our schema. The schema generation makes it easy to get started and test the dataset, but for real-world use cases, we almost always need to make manual changes.

In the schema, we define measures and dimensions and how they map into SQL queries. You can find extensive documentation about data schema here. We’re going to add a priceRange dimension to the Orders cube. It will indicate whether the total price of the order falls into one of the buckets: “$0 - $100”, “$100 - $200”, “$200+”.

To do this, we first need to define a price dimension for the order. In our database, orders don’t have a price column, but we can calculate it based on the total price of the line_items inside the order. Our schema has already automatically indicated and defined a relationship between the Orders and LineTimes cubes. You can read more about joins here.

JavaScript
 




xxxxxxxxxx
1


 
1
// You can check the belongsTo join
2
// to the Orders cube inside the LineItems cube
3
joins: {
4
  Orders: {
5
    sql: `${CUBE}.order_id = ${Orders}.id`,
6
    relationship: `belongsTo`
7
  }
8
}



The LineItems cube has price measure with a sum type. We can reference this measure from the Orders cube as a dimension, and it will give us the sum of all the line items that belong to that order. It’s called a subQuery dimension; you can learn more about it here.

JavaScript
 




xxxxxxxxxx
1


 
1
// Add the following dimension to the Orders cube
2
price: {
3
  sql: `${LineItems.price}`,
4
  subQuery: true,
5
  type: `number`,
6
  format: `currency`
7
}



Now, based on this dimension, we can create a priceRange dimension. We’ll use a case statement to define a conditional logic for our price buckets.

JavaScript
 




x
13


 
1
// Add the following dimension to the Orders cube
2
priceRange: {
3
  type: `string`,
4
  case: {
5
    when: [
6
      { sql: `${price} < 101`, label: `$0 - $100` },
7
      { sql: `${price} < 201`, label: `$100 - $200` }
8
    ],
9
    else: {
10
      label: `$200+`
11
    }
12
  }
13
}



Let’s try our newly created dimension! Go to the Build page in the playground, select the Orders count measure with the Orders price range dimension. You can always check the generated SQL by clicking the SQL button on the control bar.

That’s it for the backend! In the next part, we’ll look closer at how to render the results of our queries with D3.

Rendering Chart With D3.js

Now, as we can build our first chart, let’s inspect the example code playground uses to render it with the D3. Before that, we need to understand how Cube.js accepts and processes a query and returns the result back.

A Cube.js query is a simple JSON object containing several properties. The main properties of the query are measures, dimensions, timeDimensions, and filters. You can learn more about the Cube.js JSON query format and its properties here. You can always inspect the JSON query in the playground by clicking the JSON Query button next to the chart selector.

Inspecting JSON query

Inspecting JSON query



The Cube.js backend accepts this query and then uses it and the schema we created earlier to generate an SQL query. This SQL query will be executed in our database and the result will be sent back to the client.

Although Cube.js can be queried via a plain HTTP REST API, we’re going to use the Cube.js JavaScript client library. Among other things, it provides useful tools to process the data after it has been returned from the backend.

Once the data is loaded, the Cube.js client creates a ResultSet object, which provides a set of methods to access and manipulate the data. We’re going to use two of them now: ResultSet.series and ResultSet.chartPivot. You can learn about all the features of the Cube.js client library in the docs.

The ResultSet.series method returns an array of data series with key, title, and series data. The method accepts one argument, pivotConfig. It is an object, containing rules about how the data should be pivoted; we’ll talk about it a bit. In a line chart, each series is usually represented by a separate line. This method is useful for preparing data in the format expected by D3.

JavaScript
 




xxxxxxxxxx
1
23


 
1
// For query
2
{
3
  measures: ['Stories.count'],
4
  timeDimensions: [{
5
    dimension: 'Stories.time',
6
    dateRange: ['2015-01-01', '2015-12-31'],
7
    granularity: 'month'
8
  }]
9
}
10
 
          
11
// ResultSet.series() will return
12
[
13
  {
14
    "key":"Stories.count",
15
    "title": "Stories Count",
16
    "series": [
17
      { "x":"2015-01-01T00:00:00", "value": 27120 },
18
      { "x":"2015-02-01T00:00:00", "value": 25861 },
19
      { "x": "2015-03-01T00:00:00", "value": 29661 },
20
      //...
21
    ]
22
  }
23
]



The next method we need is ResultSet.chartPivot. It accepts the same pivotConfig argument and returns an array of data with values for the X-axis and for every series we have.

JavaScript
 




xxxxxxxxxx
1
17


 
1
// For query
2
{
3
  measures: ['Stories.count'],
4
  timeDimensions: [{
5
    dimension: 'Stories.time',
6
    dateRange: ['2015-01-01', '2015-12-31'],
7
    granularity: 'month'
8
  }]
9
}
10
 
          
11
// ResultSet.chartPivot() will return
12
[
13
  { "x":"2015-01-01T00:00:00", "Stories.count": 27120 },
14
  { "x":"2015-02-01T00:00:00", "Stories.count": 25861 },
15
  { "x": "2015-03-01T00:00:00", "Stories.count": 29661 },
16
  //...
17
]



As mentioned above, the pivotConfig argument is an object used to control how to transform, or pivot, data. The object has two properties: x and y, both are arrays. By adding measures or dimensions to one of them, you can control what goes to the X-axis and what goes to the Y-axis. For a query with one measure and one timeDimension, pivotConfig has the following default value:

JavaScript
 




xxxxxxxxxx
1


 
1
{
2
   x: `CubeName.myTimeDimension.granularity`,
3
   y: `measures`
4
}



Here, "measures" is a special value, meaning that all the measures should go to the Y-axis. In most cases, the default value of the pivotConfig should work fine. In the next part, I’ll show you when and how we need to change it.

Now, let’s look at the frontend code playground generates when we select a D3 chart. Select a measure in the playground and change the visualization type to the D3. Next, click the Code button to inspect the frontend code to render the chart.

Code to render the chart

Code to render the chart


Here is the full source code from that page:

JavaScript
 




xxxxxxxxxx
1
101


 
1
import React from 'react';
2
import cubejs from '@cubejs-client/core';
3
import { QueryRenderer } from '@cubejs-client/react';
4
import { Spin } from 'antd';
5
 
          
6
import * as d3 from 'd3';
7
const COLORS_SERIES = ['#FF6492', '#141446', '#7A77FF'];
8
 
          
9
const draw = (node, resultSet, chartType) => {
10
  // Set the dimensions and margins of the graph
11
  const margin = {top: 10, right: 30, bottom: 30, left: 60},
12
    width = node.clientWidth - margin.left - margin.right,
13
    height = 400 - margin.top - margin.bottom;
14
 
          
15
  d3.select(node).html("");
16
  const svg = d3.select(node)
17
  .append("svg")
18
    .attr("width", width + margin.left + margin.right)
19
    .attr("height", height + margin.top + margin.bottom)
20
  .append("g")
21
    .attr("transform",
22
          "translate(" + margin.left + "," + margin.top + ")");
23
 
          
24
  // Prepare data in D3 format
25
  const data = resultSet.series().map((series) => ({
26
    key: series.title, values: series.series
27
  }));
28
 
          
29
  // color palette
30
  const color = d3.scaleOrdinal()
31
    .domain(data.map(d => d.key ))
32
    .range(COLORS_SERIES)
33
 
          
34
  // Add X axis
35
  const x = d3.scaleTime()
36
    .domain(d3.extent(resultSet.chartPivot(), c => d3.isoParse(c.x)))
37
    .range([ 0, width ]);
38
  svg.append("g")
39
    .attr("transform", "translate(0," + height + ")")
40
    .call(d3.axisBottom(x));
41
 
          
42
  // Add Y axis
43
  const y = d3.scaleLinear()
44
    .domain([0, d3.max(data.map((s) => d3.max(s.values, (i) => i.value)))])
45
    .range([ height, 0 ]);
46
  svg.append("g")
47
    .call(d3.axisLeft(y));
48
 
          
49
  // Draw the lines
50
  svg.selectAll(".line")
51
    .data(data)
52
    .enter()
53
    .append("path")
54
      .attr("fill", "none")
55
      .attr("stroke", d => color(d.key))
56
      .attr("stroke-width", 1.5)
57
      .attr("d", (d) => {
58
        return d3.line()
59
          .x(d => x(d3.isoParse(d.x)))
60
          .y(d => y(+d.value))
61
          (d.values)
62
      })
63
 
          
64
}
65
 
          
66
const lineRender = ({ resultSet }) => (
67
  <div ref={el => el && draw(el, resultSet, 'line')} />
68
)
69
 
          
70
 
          
71
const API_URL = "http://localhost:4000"; // change to your actual endpoint
72
 
          
73
const cubejsApi = cubejs(
74
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NzkwMjU0ODcsImV4cCI6MTU3OTExMTg4N30.nUyJ4AEsNk9ks9C8OwGPCHrcTXyJtqJxm02df7RGnQU",
75
  { apiUrl: API_URL + "/cubejs-api/v1" }
76
);
77
 
          
78
const renderChart = (Component) => ({ resultSet, error }) => (
79
  (resultSet && <Component resultSet={resultSet} />) ||
80
  (error && error.toString()) ||
81
  (<Spin />)
82
)
83
 
          
84
const ChartRenderer = () => <QueryRenderer
85
  query={{
86
    "measures": [
87
      "Orders.count"
88
    ],
89
    "timeDimensions": [
90
      {
91
        "dimension": "Orders.createdAt",
92
        "granularity": "month"
93
      }
94
    ],
95
    "filters": []
96
  }}
97
  cubejsApi={cubejsApi}
98
  render={renderChart(lineRender)}
99
/>;
100
 
          
101
export default ChartRenderer;



The React component that renders the chart is just a single line wrapping a draw function, which does the entire job.

JavaScript
 




xxxxxxxxxx
1


 
1
const lineRender = ({ resultSet }) => (
2
  <div ref={el => el && draw(el, resultSet, 'line')} />
3
)



There is a lot going on in this draw function. Although it renders a chart already, think about it as an example and a good starting point for customization. As we’ll work on our own dashboard in the next part, I’ll show you how to do it.

Feel free to click the Edit button and play around with the code in Code Sandbox.

Editing in Code Sandbox

Editing in Code Sandbox


Building a Frontend Dashboard

Now, we are ready to build our frontend application. We’re going to use Cube.js Templates, which is a scaffolding engine for quickly creating frontend applications configured to work with the Cube.js backend. It provides a selection of different frontend frameworks, UI kits, and charting libraries to mix together. We’ll pick React, Material UI, and D3.js. Let’s navigate to the Dashboard App tab and create a new dashboard application.

Creating a new dashboard

Creating a new dashboard


It could take several minutes to generate an app and install all the dependencies. Once it is done, you will have a dashboard-app folder inside your Cube.js project folder. To start a frontend application, either go to the “Dashboard App” tab in the playground and hit the “Start” button, or run the following command inside the dashboard-app folder:

Shell
 




xxxxxxxxxx
1


 
1
$ npm start



Make sure the Cube.js backend process is up and running since our frontend application uses its API. The frontend application is running on http://localhost:3000. If you open it in your browser, you should be able to see an empty dashboard.

Empty dashboard

Empty dashboard


To add a chart to the dashboard, we can either build it in the playground and click the add to dashboard button or edit the src/pages/DashboardPage.js file in the dashboard-app folder. Let’s go with the latter option. Among other things, this file declares the DashboardItems variable, which is an array of queries for charts.

Edit dashboard-app/src/pages/DashboardPage.js to add charts to the dashboard.

diff
 




xxxxxxxxxx
1
77


 
1
-const DashboardItems = [];
2
+const DashboardItems = [
3
+  {
4
+    id: 0,
5
+    name: "Orders last 14 days",
6
+    vizState: {
7
+      query: {
8
+        measures: ["Orders.count"],
9
+        timeDimensions: [
10
+          {
11
+            dimension: "Orders.createdAt",
12
+            granularity: "day",
13
+            dateRange: "last 14 days"
14
+          }
15
+        ],
16
+        filters: []
17
+      },
18
+      chartType: "line"
19
+    }
20
+  },
21
+  {
22
+    id: 1,
23
+    name: "Orders Status by Customers City",
24
+    vizState: {
25
+      query: {
26
+        measures: ["Orders.count"],
27
+        dimensions: ["Users.city", "Orders.status"],
28
+        timeDimensions: [
29
+          {
30
+            dimension: "Orders.createdAt",
31
+            dateRange: "last year"
32
+          }
33
+        ]
34
+      },
35
+      chartType: "bar",
36
+      pivotConfig: {
37
+        x: ["Users.city"],
38
+        y: ["Orders.status", "measures"]
39
+      }
40
+    }
41
+  },
42
+  {
43
+    id: 3,
44
+    name: "Orders by Product Categories Over Time",
45
+    vizState: {
46
+      query: {
47
+        measures: ["Orders.count"],
48
+        timeDimensions: [
49
+          {
50
+            dimension: "Orders.createdAt",
51
+            granularity: "month",
52
+            dateRange: "last year"
53
+          }
54
+        ],
55
+        dimensions: ["ProductCategories.name"]
56
+      },
57
+      chartType: "area"
58
+    }
59
+  },
60
+  {
61
+    id: 3,
62
+    name: "Orders by Price Range",
63
+    vizState: {
64
+      query: {
65
+        measures: ["Orders.count"],
66
+        filters: [
67
+          {
68
+            "dimension": "Orders.price",
69
+            "operator": "set"
70
+          }
71
+        ],
72
+        dimensions: ["Orders.priceRange"]
73
+      },
74
+      chartType: "pie"
75
+    }
76
+  }
77
+];



As you can see above, we’ve just added an array of Cube.js query objects.

If you refresh the dashboard, you should be able to see your charts!

Dashboards

Dashboards


You can notice that one of our queries has the pivotConfig defined as the following:

JavaScript
 




xxxxxxxxxx
1


 
1
  pivotConfig: {
2
    x: ["Users.city"],
3
    y: ["Orders.status", "measures"]
4
  }



As I mentioned in the previous section, the default value for the pivotConfig usually works fine, but in some cases, like this one, we need to adjust it to get the desired result. We want to plot a bar chart here with the cities on the X-Axis and the number of orders on the Y-Axis grouped by the orders' statuses. That is exactly what we are passing here in the pivotConfig: Users.city to the X-Axis and measures with Orders.status to the Y-axis to get the grouped result.

To customize the rendering of the charts, you can edit the dashboard-app/src/pages/ChartRenderer.js file. It should look familiar to what we saw in the previous part.

You can check the online demo of this dashboard here and the complete source code of the example app is available on Github.

Congratulations on completing this guide! 

I’d love to hear from you about your experience following this tutorial. Please send any comments or feedback you might have here in the comments or in this Slack Community. Thank you and I hope you found this guide helpful!


Further Reading

  • High-Performance Data Analytics With Cube.js Pre-Aggregations
Database Dashboard (Mac OS) Data (computing) Chart Schema application code style sql JavaScript Dimension (data warehouse)

Published at DZone with permission of Artyom Keydunov. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • An Introduction to Type Safety in JavaScript With Prisma
  • The Complete Tutorial on the Top 5 Ways to Query Your Relational Database in JavaScript - Part 2
  • Schema Change Management Tools: A Practical Overview
  • What Are SpeedUp and ScaleUp in DBMS?

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!