In this tutorial, I’ll cover building a basic dashboard application with Cube and the most popular library for visualizing data—D3.js. Although Cube doesn’t provide a visualization layer itself, it is very easy to integrate with any existing charting library.
We are going to use Postgres to store our data. Cube will connect to it and act as a middleware between the database and the client, providing 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! 💻
Setting up a Database and Cube
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 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.
Now, as we have data in the database, we’re ready to create the Cube API service. Run the following command in your terminal to create a new service, configured to work with a Postgres database:
Cube uses environment variables for configuration. To configure the connection to our database, we need to specify the DB type and name. In the Cube project folder, replace the contents of the
.env file with the following:
Note: Have a look at the full list of DB environment variables if the database is running in a container or different system.
Now let’s start the server and open the Developer Playground at http://localhost:4000.
The next step is to create a Cube data schema. Cube uses the data schema to generate an SQL code, which will be executed in your database. Cube 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
users tables and click Generate 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 D3 visualization. Note that it is just an example and you can always customize and expand it.
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. This is an optional step; feel free to skip to the next chapter, where we’ll focus on rendering results with D3.
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
LineTimes cubes. You can read more about joins here.
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.
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.
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 API part. In the next chapter, 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 accepts and processes a query and returns the result back.
A Cube query is a simple JSON object containing several properties. The main properties of the query are
filters. You can learn more about the Cube 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.
Cube API 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.
Once the data is loaded, the Cube 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.chartPivot. You can learn about all the features of the Cube client library in the docs.
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.
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.
As mentioned above, the
pivotConfig argument is an object for controlling how to transform, or pivot, data. The object has two properties:
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
pivotConfig has the following default value:
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 chapter, 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 to inspect the frontend code to render the chart.
Here is the full source code from that page.
The React component that renders the chart is just a single line wrapping a
draw function, which does the entire job.
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 chapter, I’ll show you how to do it.
Feel free to click the Edit button and play around with the code in Code Sandbox.
Building a Frontend Dashboard
Now we are ready to build our frontend application. We’re going to use Cube Templates, which is a scaffolding engine for quickly creating frontend applications configured to work with the Cube API. 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.
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 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:
Make sure the Cube API is up and running since our frontend application uses it. 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.
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.
dashboard-app/src/pages/DashboardPage.js to add charts to the dashboard.
As you can see above, we’ve just added an array of Cube query objects.
If you refresh the dashboard, you should be able to see your charts!
You can notice that one of our queries has the
pivotConfig defined as the following.
As I mentioned in the previous chapter 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
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 chapter.
In the next, final, chapter I'll show you how to add a filter to the dashboard and make it more interactive and dynamic.
In this chapter, we'll add a filter to our dashboard to make it more interactive. The filter will allow users to look at specific sets of orders based on their status: processing, completed, or shipped.
Cube makes it easy to add such dynamic features because we don't need to add anything to our data schema. We
already have a dimension,
Orders.status, and we can just filter by this
dimension by adding filters properties to our JSON query.
Say we have the following query, which is used to plot an area chart with the number of orders over time grouped by the product category.
To load only completed orders with this query, we need to add a filters property to it.
You can learn about all the filters operators in the query format docs.
So all we need to do to make the filter work is to conditionally add this
filters property to all our dashboard queries. To do this, let's introduce the
dashboardItemsWithFilter method in
In this method, we check if the filter value s any other rather than "all" we inject the filters
property with the corresponding filter value to all the queries.
Now, we need to render the user input for the filter. We can use the
<ButtonGroup /> component from the Material UI kit for this and render a button per the possible state of the order plus the "All" button. We'll use the React
useState hook to store and update the filter value.
First make sure to import
useState and the required components from Material UI.
Next, we render the buttons group and change the value of the
on the button's click. Note that we use the newly created
dashboardItemsWithFilter method to
iterate over dashboard items for rendering.
That is all we need to create a simple filter and make our D3 dashboard dynamic and interactive.
Congratulations on completing this guide! 🎉
I’d love to hear from you about your experience following this guide. Please send any comments or feedback you might have in this Slack Community. Thank you and I hope you found this guide helpful!