data-warehousekimball

Calendar Dimension in Datawarehouse Modeling

In my datawarehouse model , I have Dim_Date containing a list of dates. I have a fact table Employee_Leaves containing the list of leaves taken by employees.

My fact table has two dates Fk_Leave_Start_Date & Fk_Leave_End_Date .

I modeled like this : enter image description here

I don't know if I'm doing this correctly. In my Talend Job , to load the fact table I put the same Date dimension twice in my datasources to retrieve the ID that will be put in the fact for both dates. It's working correctly and even when I moved to reporting everything is working correctly.

I want to know if I'm following Kimball's best practices or not ? Because I saw some of my colleagues doing two separate Calendar dimensions ? And I don't know if the type of relationship I'd have (1 to many or many to many?)

Solution

  • According to the Kimball methodology, the decision to use two separate dimension tables for dates depends on the requirements and characteristics of the data. Typically, two separate date dimensions are created when there are different types of dates with distinct attributes or when there are multiple hierarchies within the date dimension.

    1. Transaction Dates vs. Reporting Dates: If you have a scenario where there are transaction dates ( order dates, delivery dates) that represent the actual occurrences of events and reporting dates (e.g., fiscal calendar dates, accounting periods) used for reporting and analysis, it can be beneficial to have separate dimensions for each. This allows you to maintain the transactional timeline's integrity while providing reporting flexibility.

    2. Calendar Dates vs. Fiscal Dates: Many organizations use a fiscal calendar that differs from the standard Gregorian calendar. In such cases, it may be helpful to have separate dimensions for calendar dates ( day, month, year) and fiscal dates (fiscal day, fiscal month, fiscal year) to support reporting and analysis based on the fiscal periods.

    3. Multiple Hierarchies: Sometimes, you may need to analyze data using different hierarchies within the date dimension. For example, you might have hierarchies like day-month-year, week-month-year, and quarter-year. Creating separate dimensions for each hierarchy can simplify querying and reporting based on different time perspectives.

    In your case, as @Nickw mentioned, you need to have two joins for start_date and end_date between your fact table and the dim_date.