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:
- Create a date model
- Add Relationship between the date model with the relevant models' date fields.
- 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):
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
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_dis 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
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
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)
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)
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:
Use the date modeling approach above, we can replicate the effect.