Using dynamic measures

This functionality only works with data models written in JavaScript, not YAML. For more information, check out the Data Modeling Syntax page.

Use case

We want to understand the distribution of orders by their statuses. Let's imagine that new order statuses can be added in the future, or we get a list of statuses from an external API. To calculate the orders percentage distribution, we need to create several measures that refer to each other. But we don't want to manually change the data model for each new status. To solve this, we will create a schema dynamically.

Data modeling

To calculate the number of orders as a percentage, we need to know the total number of orders and the number of orders with the desired status. We'll create two measures for this. To calculate a percentage, we'll create a measure that refers to another measure.

const statuses = ["processing", "shipped", "completed"];
 
const createTotalByStatusMeasure = (status) => ({
  [`total_${status}_orders`]: {
    title: `Total ${status} orders`,
    type: `count`,
    filters: [
      {
        sql: (CUBE) => `${CUBE}."status" = '${status}'`,
      },
    ],
  },
});
 
const createPercentageMeasure = (status) => ({
  [`percentage_of_${status}`]: {
    title: `Percentage of ${status} orders`,
    type: `number`,
    format: `percent`,
    sql: (CUBE) =>
      `ROUND(${CUBE[`total_${status}_orders`]}::NUMERIC / ${
        CUBE.total_orders
      }::NUMERIC * 100.0, 2)`,
  },
});
 
cube(`orders`, {
  sql_table: `orders`,
 
  measures: Object.assign(
    {
      total_orders: {
        type: `count`,
        title: `Total orders`,
      },
    },
    statuses.reduce(
      (all, status) => ({
        ...all,
        ...createTotalByStatusMeasure(status),
        ...createPercentageMeasure(status),
      }),
      {}
    )
  ),
});

Result

Using the measures defined above, we can explore the orders percentage distribution and easily create new measures just by adding a new status.

[
  {
    "orders.percentage_of_processing": "33.54",
    "orders.percentage_of_shipped": "33.00",
    "orders.percentage_of_completed": "33.46",
  },
];

Source code

Please feel free to check out the full source code (opens in a new tab) or run it with the docker-compose up command. You'll see the result, including queried data, in the console.

Passing dynamic parameters in a query Using dynamic union tables