Role Playing Models

Hi All,

Star schema modelling is a common form of modelling used in BI reporting layers.

For instance see the following for definitions and usage:

When creating a star schema you can link the same dimension table multiple times to a fact table. This happens quite frequently, and since within Holistics each model is treated as the same table you can’t link the same dimension table multiple times to a fact table, or else you end up with a circular dependency.

At the moment you have to create a whole new table, defining the same columns and custom dimensions time and time again in order to do this. This is obviously time consuming but also extremely hard to maintain as you have to make the same change in multiple places when an update is required to the model.

A better way to do this is to have a mechanism in place to reference another model (like a database view) that we can rename and use as such. Then we can reference multiple times, but only ever have to update the base model to get any changes pushed through.

For instance this could take place in the dataset builder when you pull in a model you can give it a an alternative name, allowing you to import the same model multiple times but join as a different table. Then in the SQL that is generated you can use the alternative name as an AS rename i.e.

SELECT *
FROM
  my_fact_table
  INNER JOIN my_dim_table AS from_table ON my_fact_table.from_col = from_table.id
  INNER JOIN my_dim_table AS to_table ON my_fact_table.to_col = to_table.id

And within the explore/report creation page you can have each table listed with their alternative names as different tables you can bring in columns from.

I believe Looker achieves the same in an explore:

Again the benefits to the approach are:

  1. Single definition of a model
  2. Star schema designs are much easier
  3. Renaming a model for context, rather than using as a general table i.e. origin and destination geo models can reference the same underlying geo model