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`,
},
},
});