Using Dynamic Union Tables

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

It is quite often the case that you may have a lot of different tables in a database, which actually relate to the same entity.

For example, you can have “per client” tables with the same data, but related to different customers: elon_musk_table, john_doe_table, steve_jobs_table, etc. In this case, it would make sense to create a single Cube for customers, which should be backed by a union table from all customers tables.

It would be annoying to union all required tables manually. Luckily, since Cube supports modelling data in JavaScript, we have the full power of JavaScript at our disposal. We can write a function, which will generate a union table from all our customers’ tables:

// model/utils.js
const customerTableNames = [
  { name: "Albert Einstein", tablePrefix: "albert_einstein" },
  { name: "Blaise Pascal", tablePrefix: "blaise_pascal" },
  { name: "Isaac Newton", tablePrefix: "isaac_newton" },
  { name: "Charles Darwin", tablePrefix: "charles_darwin" },
  { name: "Michael Faraday", tablePrefix: "michael_faraday" },
  { name: "Enrico Fermi", tablePrefix: "enrico_fermi" },
  { name: "Thomas Edison", tablePrefix: "thomas_edison" },
];
 
export function unionData() {
  return customerTableNames
    .map(
      (p) => `select
                  name,
                  email,
                  id,
                  order_id,
                  created_at,
                  '${p.name}' customer_name
                  from ${p.tablePrefix}_customer
                  `
    )
    .join(" UNION ALL ");
}

Then we can use the unionData() function inside the Customers cube. customer_name would become a dimension to allow us to break down the data by certain customers.

import { unionData } from "../utils";
 
cube(`customers`, {
  sql: unionData(),
 
  measures: {
    count: {
      type: `count`,
    },
  },
 
  dimensions: {
    customer_name: {
      sql: `customer_name`,
      type: `string`,
    },
  },
});

Authenticate requests to Cube with AWS Cognito Implementing Entity-Attribute-Value Model (EAV)