1

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?)

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
userrr
  • 197
  • 7
  • 1
    You only ever have one physical date dimension table in a datawarehouse. You could have views on top of it, if you felt that having a sperate entity for each role the date dimension is playing aided clarity. In some BI tools you'd have a different instance of the date dimension for each role it is playing - but that's logical modelling within the BI tool rather than physical tables – NickW Jun 21 '23 at 13:42
  • @NickW so what I did is correct ? – userrr Jun 21 '23 at 13:43
  • I don't know - I can't tell from your diagram if there are 2 relationships between the fact and dimension, or only 1. There should be 2 – NickW Jun 21 '23 at 13:56

1 Answers1

1

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 (e.g., 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 integrity of the transactional timeline while providing flexibility for reporting.

  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 (e.g., day, month, year) and fiscal dates (e.g., fiscal day, fiscal month, fiscal year) to support reporting and analysis based on the fiscal periods.

  3. Multiple Hierarchies: In some cases, you may need to analyze data using different hierarchies within the date dimension. For example, you might have hierarchies such as 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.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60