data-modelingdata-warehousestar-schema

Time Dimension: loading date

I have a simple question that it may be very easy for those Business Intelligence Architects, but I am struggling to find out the correct way.

I have a fact-table with 2 or more potential date (let's say opening-date and closing-date) and a measure called revenue.

I would like to know the best practice to connect the Date-Time Dimension to my fact table.

I can see two ways:

  1. Connect the fact table with "loading date":

enter image description here

With this solution, for each day (therefore, every day) I will have a record; hence I could have the same record many times in a period.

enter image description here

  1. The second way is to connect the Time Dimension to both Opening-Date and Closing-Date: enter image description here

And the ETL should be set up with a date-check, in which if the master key change (the master key should be ID+OD+CD)

enter image description here

The doubt I have now is the navigability of the fact-table: in the first case I have all the date, and I can pick up the desired loading-date to see the data that I need; instead, in the second case, I have to build a complex join between the Time-Dimension and the fact-table, paying this join in the Data Visualization tool.

What is the best practice? Is there a best solution?

Solution

  • It looks like you need to implement data modeling technique called "Role Playing Dimension".

    Basically, that's option number 2 you mentioned. In my opinion, that's the way to go.