Show items with no data in Holistics

There will be cases when you want to show items with no data.

For example, you have 2 tables/models: users and order via 1-n relationship (one user can have multiple orders and one order can only belong to a user).
In Holistics, for 1-n relationship, we will construct the query started from orders and left joined with users (as stated in this doc: https://docs.holistics.io/docs/datasets#how-paths-are-constructed).
This means users without any orders would not appear in the report.

To explain for this mechanism, we chose to do it that way to fix two problems:

  • Always joins from the model with more granular data ensure that we don’t run into issue like fan-out/double aggregation on measure.
  • Show anomaly where the data doesn’t conform to the relationship. For example: Orders without a user (or a deleted user) will show up with a null user column when you aggregate them by user.

In the meantime, there are 2 workarounds:

  • Add a count(Users.id) field to your visualization. This will force it to show all users.
  • (Not recommended) Set the relationship as 1-1 (behaves like n-n), so Holistics will apply a FULL JOIN and display all the users. Do note that this leaves you open to fan-out issues (https://docs.holistics.io/docs/fan-out-issue).
1 Like