DEV Community: Yulia Kotova

Introducing Data Blending API in Cube.js

Yulia Kotova — Thu, 01 Oct 2020 17:09:01 +0000

Sometimes in our daily data visualization, we need to merge several similar data sources so that we can manipulate everything as one solid bunch of data.

For example, we may have an omnichannel shop where online and offline sales are stored in two tables. Or, we may have similar data sources that have only a single common dimension: time. How can we calculate summary metrics for a period? Joining by time is the wrong way because we can’t apply granularity to get the summary data correctly.

Furthermore, how can we find seasonal patterns from summarized metrics? And how can we get and process data synchronously to track correlations between channels?

Well, the new data blending functionality in version 0.20.0 of Cube.js takes care of all these cases.

If you’re not familiar with Cube.js yet, please have a look at this guide. It will show you how to set up the database, start a Cube.js server, and get information about data schemes and analytical cubes.

Please, keep in mind that we used here another dataset:

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

Now let’s dive into the metrics for an example shop and visualize sales by channel and as a summary.

Here is the full source and live demo of the example.

I used React to implement this example, but querying in Cube.js works the same way as in Angular, Vue, and vanilla JS.

Our schema has two cubes:

Orders.js

cube(`Orders`, {
 sql: `SELECT * FROM public.orders`,

 measures: {
   count: {
     type: `count`,
   },
 },

 dimensions: {
   id: {
     sql: `id`,
     type: `number`,
     primaryKey: true,
   },

   createdAt: {
     sql: `created_at`,
     type: `time`,
   },
 },
});

and OrdersOffline.js

cube(`OrdersOffline`, {
 sql: `SELECT * FROM public.orders_offline`,

 measures: {
   count: {
     type: `count`,
   },
 },

 dimensions: {
   id: {
     sql: `id`,
     type: `number`,
     primaryKey: true,
   },

   createdAt: {
     sql: `created_at`,
     type: `time`,
   },
 },
});

The existence of at least one-time dimension in each cube is a core requirement for merging the data properly. In other words, the data are suitable for blending only if you can present the data on a timeline. Sales statistics or two lists of users that both have an account created date are appropriate datasets for data blending. However, two lists of countries with only a population value can’t be united this way.

A Special Query Format for Data Blending

A simple and minimalistic approach is to apply data blending to a query object when we retrieve data from our frontend application. The schema and backend don’t need to be changed.

const { resultSet } = useCubeQuery([
   {
     measures: ['Orders.count'],
     timeDimensions: [
       {
         dimension: 'Orders.createdAt',
         dateRange: ['2022-01-01', '2022-12-31'],
         granularity: 'month',
       },
     ],
   },
   {
     measures: ['OrdersOffline.count'],
     timeDimensions: [
       {
         dimension: 'OrdersOffline.createdAt',
         dateRange: ['2022-01-01', '2022-12-31'],
         granularity: 'month',
       },
     ],
   },
 ]);

The blended data is an array of query objects, so we just combine regular Cube.js query objects into an array with a defined dateRange and granularity.

As a result, Cube.js returns an array of regular resultSet objects.

But what if we want to do calculations over blended data sources or create custom metrics? For example, how can we define ratios calculated using data from two sources? How can we apply formulas that depend on data from multiple sources?

In this case, we can use another data blending function. We start by setting up a new cube.

Data Blending Implementation within a Schema

Let’s create AllSales.js inside the schema folder:

cube(`AllSales`, {
 sql: `
 select id, created_at, 'OrdersOffline' row_type from ${OrdersOffline.sql()}
 UNION ALL
 select id, created_at, 'Orders' row_type from ${Orders.sql()}
 `,

 measures: {
   count: {
     sql: `id`,
     type: `count`,
   },

   onlineRevenue: {
     type: `count`,
     filters: [{ sql: `${CUBE}.row_type = 'Orders'` }],
   },

   offlineRevenue: {
     type: `count`,
     filters: [{ sql: `${CUBE}.row_type = 'OrdersOffline'` }],
   },

   onlineRevenuePercentage: {
     sql: `(${onlineRevenue} / NULLIF(${onlineRevenue} + ${offlineRevenue} + 0.0, 0))*100`,
     type: `number`,
   },

   offlineRevenuePercentage: {
     sql: `(${offlineRevenue} / NULLIF(${onlineRevenue} + ${offlineRevenue} + 0.0, 0))*100`,
     type: `number`,
   },

   commonPercentage: {
     sql: `${onlineRevenuePercentage} + ${offlineRevenuePercentage}`,
     type: `number`,
   },
 },

 dimensions: {
   createdAt: {
     sql: `created_at`,
     type: `time`,
   },

   revenueType: {
     sql: `row_type`,
     type: `string`,
   },
 },
});

Here we’ve applied a UNION statement to blend data from two tables, but it’s possible to combine even more.

Using this approach, we can easily define and combine values from several blended data sources. We can even use calculated values and SQL formulas.

We can retrieve data from frontend applications and process the results in the usual way:

const { resultSet: result } = useCubeQuery({
   measures: [
     'AllSales.onlineRevenuePercentage',
     'AllSales.offlineRevenuePercentage',
     'AllSales.commonPercentage',
   ],
   timeDimensions: [
     {
       dimension: 'AllSales.createdAt',
       dateRange: ['2022-01-01', '2022-12-31'],
       granularity: 'month',
     },
   ],
 });

Conclusion

If we need to visualize data from several sources and apply time granularity to the data, then with data blending, we need to write less code and we can simplify the application logic.

We looked at two ways to implement data blending:
We retrieved data as an array of query objects from a frontend application. This is simple to do and the schema doesn’t need to be modified. We can even merge data from several databases. Furthermore, we can retrieve and process independent data synchronously so that we can visualize it on a timeline.
We blended data by defining a special cube in a schema. This approach allows us to apply aggregate functions to all sources simultaneously and we can define calculated values.

We hope this tutorial will help you to write less code and help to build more creative visualizations. If you have any questions or feedback or you want to share your projects, please use our Slack channel or mention us at Twitter.

Also, don’t forget to sign up for our monthly newsletter to get more information about Cube.js updates and releases.

Comparing Data over Different Time Periods

Yulia Kotova — Thu, 10 Sep 2020 17:51:51 +0000

Comparing a single metric over different time periods is a quite common procedure in data analytics. You can evaluate your company’s performance by comparing this year’s revenue with the previous year’s. You can understand which are the low and high sales months in your market, and furthermore, you can predict customer behavior and figure out seasonal patterns.

Analytical systems like Google Analytics, Youtube Studio, and others provide tools for comparing a single metric over different periods of time.

Let’s see how you can implement such analytical visualization with Cube.js.

Here you can see the live demo and the full source code for this example.

We will explore a sample e-commerce dataset and compare the numbers of orders in the three most recent years. To do this, we need to run a Cube.js server and a simple dashboard application.

If you’re not yet familiar with Cube.js, please follow our guide to setting up the database, starting a Cube.js server, and getting information about data schemes and analytical cubes.

Now, let’s create the Orders cube inside the schema folder with the following code:

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  measures: {
    count: {
      type: `count`,
    },
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true,
    },
    createdAt: {
      sql: `created_at`,
      type: `time`,
    },
  },
});

Here the count measure is used to calculate the number of orders. The createdAt field is used to retrieve an order’s timestamp.

In the next sections, we will implement the example with React, but you can use the same approach with Vue, Angular, and vanilla JS.

Multiple Queries for a Single Chart

Here’s how we retrieve the data for each year and parse result sets into the format that is accepted by our charting library:

import React, { useState, useEffect } from 'react';
import { useCubeQuery } from '@cubejs-client/react';
import * as moment from 'moment';

import Line from './Line';

export default () => {
  const [data, setData] = useState([]);

  const { resultSet: result22 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2022-01-01', '2022-12-31'],
        granularity: 'month',
      },
    ],
  });

  const { resultSet: result21 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2021-01-01', '2021-12-31'],
        granularity: 'month',
      },
    ],
  });

  const { resultSet: result20 } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        dateRange: ['2020-01-01', '2020-12-31'],
        granularity: 'month',
      },
    ],
  });

  useEffect(() => {
    const parseResultSet = (resultSet) => {
      return {
        name: moment(
          resultSet.tablePivot()[0]['Orders.createdAt.month']
        ).format('YYYY'),
        data: resultSet
          .tablePivot()
          .map((item) => parseInt(item['Orders.count'])),
      };
    };

    const temp = [
      result22 ? parseResultSet(result22) : [],
      result21 ? parseResultSet(result21) : [],
      result20 ? parseResultSet(result20) : [],
    ];

    setData(temp);
  }, [result22, result21, result20]);

  return <Line data={data} title={'multiple queries'} />;
};

Once the data is prepared, we send it to the <Line /> rendering component and, behold, we create an informative chart:

The procedure is quite simple, isn’t it? However, as you can see, the code is a little bit messy and repetitive. More importantly, it’s not easy to adapt the code to a different amount of years or even to adjust it dynamically in response to users’ actions or application states.

How do we solve this problem? Let’s try another approach.

A Single Query for a Single Chart

With the 0.20.0 release of Cube.js, it’s possible to retrieve data for different time periods using a single query.

The compareDateRange property accepts an array of two or more different time periods and returns data for all of them in one request.

We can rewrite our previous code in a simpler way:

import React, { useState, useEffect } from 'react';
import { useCubeQuery } from '@cubejs-client/react';

import Line from './Line';

export default () => {
  const [data, setData] = useState([]);

  const { resultSet: result } = useCubeQuery({
    measures: ['Orders.count'],
    timeDimensions: [
      {
        dimension: 'Orders.createdAt',
        compareDateRange: [
          ['2022-01-01', '2022-12-31'],
          ['2021-01-01', '2021-12-31'],
          ['2020-01-01', '2020-12-31'],
        ],
        granularity: 'month',
      },
    ],
  });

  useEffect(() => {
    if (result) {
      const temp = result.series().map((data) => {
        return {
          name: data.key.substring(0, 4),
          data: data.series.map((item) => item.value),
        };
      });
      setData(temp);
    }
  }, [result]);

  return <Line data={data} title={'the single query'} />;
};

You can see that this code is much shorter. Furthermore, now we’re able to set the time periods via compareDateRange dynamically while keeping our code clean.

Please note that we didn’t make any changes in the <Line /> rendering component, so the chart will look essentially the same.

I hope that this will help you create useful data visualizations using substantially less code.

Also, don’t forget to sign up for our monthly newsletter to get more information about Cube.js updates and releases.

React Highcharts Example with Cube.js

Yulia Kotova — Thu, 23 Jul 2020 16:01:58 +0000

Started as a simple charting tool for monitoring a snow depth near the owner's country house in Norway, Highcharts quickly became one of the most popular visualization libraries. It provides a lot of great built-in interactive features and is easy to use.

In this tutorial we are going to build a simple e-commerce dashboard with Cube.js and Highcharts. We’ll use the main Highcharts library, as well as Maps, Stock, and Solid Gauge modules.

Please keep in mind that Highcharts libraries are available under different licenses, depending on whether it is intended for commercial/government use, or for personal or non-profit projects. Make sure to check its license page.

Below, you can see the demo of the dashboard that we’re going to build.

You can find a live demo here and the source code is available on Github.

To implement this example, we’ll need:

Database (we will use PostgreSQL) with sample data
Cube.js backend to handle communications between our database and the frontend
The frontend application (we will build one with React)

Analytics Backend

We’re going to use a PostgreSQL database and an example e-commerce dataset. Use the following commands to download and import the example dataset.

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

Next, let’s install the Cube.js CLI and create a new project.

$ npm -g install cubejs-cli
$ cubejs create highcharts -d postgres

Cube.js uses environment variables inside the .env file for configuration. Update the contents of the .env file with your own database credentials.

CUBEJS_DB_NAME=ecom
CUBEJS_DB_TYPE=postgres
CUBEJS_API_SECRET=SECRET

Now, let’s start with the Cube.js backend.

$ npm run dev

At this step, you can find the Cube.js playground at http://localhost:4000.

Here, you can see all the tables from our database and we can choose any of them to generate the schema.

The Cube.js Data Schema concept is based on multidimensional analysis and should look familiar to those with experience in OLAP cubes.

The two main entities are measures and dimensions:
dimensions are ”as is” properties that we get from our database, but measures are results of aggregation operations like count, sum, average, and others.

In this example, we need an orders and users table. Please, check it and click “Generate Schema.” Cube.js will then generate Orders.js and Users.js files inside the schema folder.

Cube.js data schema is javascript code and can be easily edited. You can also dynamically generate schema, if needed.

Let’s update the schema/Users.js file.

We’ll keep only state, id dimensions, and count measure because we’ll need to use them in our example.

cube(`Users`, {
  sql: `SELECT * FROM public.users`,
  dimensions: {
    state: {
      sql: `state`,
      type: `string`
    },
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    }
  }
});

That’s it for our backend. We’ve configured the database and created the Cube.js. backend. Now, we’re ready to start working on our frontend application.

Frontend Dashboard with Highcharts

Let’s generate our app with Cube.js templates. Navigate to the Dashboard App tab and select “Create custom application” with React and Ant Design.

It will take some time to create a dashboard app and install dependencies. Once it is finished, you should see the dashboard-app folder inside your project’s folder.

Next, let’s install the dependencies we’ll need. Run the following commands in the dashboard-app folder.

$ cd dashboard-app
$ npm install --save highcharts highcharts-react-official @highcharts/map-collection

The command above installs the following dependencies:

Feel free to remove all the files inside the src folder and the page folder, as well as update the dashboard/index.js file with the following content.

import React from 'react';
import ReactDOM from 'react-dom';
import './index.css';
import App from './App';
import * as serviceWorker from './serviceWorker';
ReactDOM.render(
 <React.StrictMode>
   <App></App>
 </React.StrictMode>,
 document.getElementById('root')
); 
serviceWorker.unregister();

Our application will have the following structure:

App as the main app component
Dashboard component that stores data and manages the app state
Map, Line, Stock, and other chart components that manage chart render according to applications data and state.

Let’s create the <Dashboard /> component in the dashboard-app/src/components/Dashboard.js file with the following content. (We’ll create the <Map /> component later):

import React from 'react';
import { Layout } from 'antd';
import { useCubeQuery } from '@cubejs-client/react';
import Map from './Map';
const Dashboard = () => {
 const { resultSet } = useCubeQuery({
   measures: ['Orders.count'],
   dimensions: ['Users.state'],
   timeDimensions: [
     {
       dimension: 'Orders.createdAt',
       dateRange: 'last year',
     },
   ],
 });
if (!resultSet) {
  return “Loading…”;
}
 const data = regions.tablePivot().map(item => [item['Users.state'], parseInt(item['Orders.count'])])
 return (
   <Layout>
     <Map data={data} />
   </Layout>
 );
};
export default Dashboard;

In the above snippet, we did several things. We imported useCubeQuery React hook first.

import { useCubeQuery } from "@cubejs-client/react";

Next, to render the amount of orders in each state, we need to change the data into Highcharts’ format, where the first element is the state key and the second element is the value.

[
    ["us-ca",967],
    ["us-ny",283],
    ["us-wa",239],
    ["us-il",205],
    ["us-tx",190]
]

We’re using resultSet.tablePivot() to access data returned from the backend and to prepare it for rendering.

const data = regions.tablePivot().map(item => [item['Users.state'], parseInt(item['Orders.count'])])

Now, we’re ready to pass our data to the Map chart. Let’s create a new dashboard-app/src/components/Map.js file with the following content.

import React, { useState, useEffect } from 'react';
import Highcharts from 'highcharts';
import HighchartsReact from 'highcharts-react-official';
import highchartsMap from 'highcharts/modules/map';
import mapDataIE from '@highcharts/map-collection/countries/us/us-all.geo.json';
highchartsMap(Highcharts);
const staticOptions = {
 chart: {
   styledMode: true,
 },
 credits: {
   enabled: false,
 },
 title: {
   text: 'Orders by region<small>Highcharts Map API</small>',
   useHTML: true,
 },
 colorAxis: {
   min: 0,
 },
 tooltip: {
   headerFormat: '',
   pointFormat: `
     <b>{point.name}</b>: {point.value}`,
 },
 colorAxis: {
   minColor: '#FFEAE4',
   maxColor: '#FF6492',
 },
 series: [
   {
     name: 'Basemap',
     mapData: mapDataIE,
     borderColor: '#FFC3BA',
     borderWidth: 0.5,
     nullColor: '#FFEAE4',
     showInLegend: false,
     allowPointSelect: true,
     dataLabels: {
       enabled: true,
       format: '{point.name}',
       color: '#000',
     },
     states: {
       select: {
         borderColor: '#B5ACFF',
         color: '#7A77FF',
       },
     },
   },
 ],
};
export default ({ data }) => {
 const [options, setOptions] = useState({});
 useEffect(() => {
   setOptions({
     ...staticOptions,
     series: [
       {
         ...staticOptions.series[0],
         data: data,
       },
     ],
   });
 }, [data]);
 return (
   <HighchartsReact
     highcharts={Highcharts}
     constructorType={'mapChart'}
     options={options}
   />
 );
};

Inside the Map.js file, we imported useState, useEffect hooks, and a bunch of Highcharts components. Then, we defined chart options based on Highcharts Map API specs.
In staticOptions, we can set map styling, source, data, event handlers, and other options.

Highcharts has a wide selection of SVG maps to use. We’ve picked this one.

Lastly, we merged our staticOptions and props.data and then passed it to the Highcharts component.

That’s all for our <Map/> component.

Now, we just need to update the ‘dashboard-app/App.js’ to include the <Dashboard /> component:

+ import Dashboard from './components/Dashboard';
- <Header />
- <Layout.Content>{children}</Layout.Content>
+ <Dashboard />

...and we’re ready to check out our first chart!

Navigate to http://localhost:3000 in your browser, and you should be able to see the map chart that we’ve just built.

A similar workflow can be used to create other chart types, as in the GIF below.

Define the static chart options, according to Highcharts API documentation.
Add data to options.series.
Pass options to the Highcharts component.

The full source code of the above dashboard is available on Github, and you can check the live demo here.

I hope you’ve found this tutorial helpful. If you have any questions or any kind of feedback, please let me know in this Slack channel.