What would be the benefits of having a time dimension in a star schema over having the time attributes in the fact table itself?
For example:
I have a transaction data with user information for each transaction, country where the transaction took place and dates of when it occured.
Option 1 Correct me if I am wrong, but this is probably the widely used approached and most recommended by many:
A transaction fact table containing
transaction_ID
(PK),user_id
(FK) andcountry_id
(FK), and date_id (FK)User dimension containing
user_id
(PK) and the other user attributes, let's sayname
&phone_number
.- Dates dimension that consists of
date_id
(PK),date
,day
,month
,year
,quarter
.
Option 2 Something that I just thought about instead of choosing Option 1, but unsure about:
A transaction fact table containing
transaction_ID
(PK),user_id
(FK) andcountry_id
(FK),date
,day
,month
,year
,quarter
.User dimension containing
user_id
(PK) and the other user attributes, let's sayname
&phone_number
.
What would be the benefits of having Option 1 over Option 2? I am not aware of the reasons why joining with another Date dimension would be a better option even though it is most widely used approach. Thanks a lot!