Using a Date Dimension model

Date Dimension is a popular data modeling practice when working with date/calendar data. Date Dimension is a table that has one record per each day. Depends on the period used in the business you can define start and end of the date dimension.

Some of the common scenarios that using Date Dimension helps with:

• Avoid scenario where you don't have continuous date values in your data
• Help compare metrics from different tables by time dimension

Creating Date Dimension Model in Holistics

In Holistics, you can create and use Date Dimension through 3 steps:

1. Create a date model
2. Add Relationship between the date model with the relevant models' date fields.
3. Include the date model in the interested datasets

1. Create Date Model

Go to Data Modeling and create a SQL model name dim_dates (you can name it whatever you want):

SELECT
  *
FROM generate_series (
  '2017-01-01'::date,
  '2025-01-01'::date,
  '1 day'::interval
) date_d

The above code is in PostgreSQL, but the general logic still applies to other database types

You may add more columns to extract other information from the date and create a full date reference table (for example, weekday, week number, month name etc...), but we will go with one column for simplicity's sake.

Note that this step only needs to be done once. Skip if you already have a date dimension model.

2. Link Date Model with Relevant Models

Go to dim_dates model and create a 1-many relationship between the date_d field with relevant models.

• You can create multiple relationships
• Make sure date_d is on the one-end of the one-many relationship.

The final result will look something like the image below

3. Include Date Model in Relevant Datasets

Edit your existing dataset and include dim_dates model.

Then, in the Explore UI, drag in the Date field from the dim_dates model to report the numbers along with it. Note that if in the dim_dates model you use a future date instead of current_date as the end of the date series, you will need to add a filter to show only rows up to the current date:

When adding the exploration to a dashboard, remember to filter on the Date dimension instead of the Order Date or Sign Up Date:

Use Case: Compare metrics from different tables by date

Let's go through a use case in Holistics that utilizes Date Dimension effectively.

Suppose you have an Ecommerce dataset with orders and users model having a relationship like so:

You want to place number of signups daily and number of orders daily in the same report:

Using the dataset above, you may drag in two measures Signups Count, Orders Count, and for date dimension you may use Sign Up Date:

Unfortunately, this is not the correct result. Because of the relationship orders.user_id - users.id in the dataset, using Sign Up Date here will give you number of orders created by users who registered at a certain date (Which is not what you want)

select
  u.sign_up_date
  , count(distinct u.id) as signups_count -- Correct
  , count(o.id) as orders_count -- Incorrect
from orders o
left join users u on o.user_id = u.id

Similarly, if you use Order Date, you will get the correct orders count, but for users you would get number of users who placed orders at a certain date (Also not what you want)

select
  o.order_date
  , count(distinct u.id) as signups_count -- Incorrect
  , count(o.id) as orders_count -- Correct
from orders o
left join users u on o.user_id = u.id

The correct reporting logic would be:

• First, aggregate number of signups to daily level
• Next, aggregate number of orders to daily level
• Finally, combine the two aggregations
• What we want, in SQL term, is something like this:

with aggr_orders as (
  select order_date, count(id) as orders_count from orders group by 1
)

, aggr_users as (
  select sign_up_date, count(id) as users_count from users group by 1
)

-- combine
select
  coalesce(ao.order_date, au.sign_up_date) as report_date
  , ao.orders_count
  , au.users_count
from aggr_orders ao
full join aggr_users au on ao.order_date = au.sign_up_date

Use the date modeling approach above, we can replicate the effect.