Forum Discussion
Data Modeling - Dimension Date
Hi,
I have 3 fact tables with 2 different Date type
| Date Fact 1 and 3 | Date Fact 2 | |
| 01.01.2000 | 2016 Q1 | |
| 01.01.2000 | 2016 Q2 | |
| 01.01.2020 | 2016 Q3 | |
| 30.01.2017 | 2016 Q4 | |
| 30.01.2000 | 2017 Q1 | |
31.01.2017 | 2017 Q2 | |
| 07.02.2000 | 2017 Q3 | |
| 28.02.2020 | 2017 | |
| 01.03.2000 | 2018 |
The user is asking to being able dynamically to filter the date by year then through a second filter by quarter.
I am looking for the best data model suitable for the case and the creation of Dimensions who could help me in achieving the user goal.
I wonder if i should create a DimDate with all dates day by day, year column and quarter column but the result would be in the case of Fact table 2 a many to many relationship or 2 Dimensions, year and Quarter, after extracting those from all fact tables and creating in each table the corresponding columns in order to relate them.
Thanks for your advice
The approach I take is to add a date column to the fact table. This allows you to create a relationship with DimDate.
To create a date column in the fact table, you can use any date in the period. I prefer to use the first date of the period since all months have day 1, but not all months have day 31. For example, "2016 Q1" would be 2016-01-01 (assuming your quarters are based on a calendar year).
2 Replies
The approach I take is to add a date column to the fact table. This allows you to create a relationship with DimDate.
To create a date column in the fact table, you can use any date in the period. I prefer to use the first date of the period since all months have day 1, but not all months have day 31. For example, "2016 Q1" would be 2016-01-01 (assuming your quarters are based on a calendar year).
Many thanks for your advice.
