CODEWITHSUNDEEP
mysqldatabase-designdata-warehousedimensional-modeling
Bazooka
Bazooka

Reputation: 1458

Time Dimension in Data Warehouse

I've a fact table that stores multiple date fields in it's rows. I would like to keep the design flexible and link all of these fields with the time dimension. However, the problem is that my reports end up having too many joins in their queries (one for each date field). How do I mitigate this problem ?

I have one idea of storing both the time dimension references (fast searching) and date fields (efficient retrieval). What would be the possible problems in doing so ?

Generalizing this idea, should we do it for other fields in the fact table as well ?

The table structure 

acc_num | acc_approved_date| acc_rejected_date| file_gen_date

Proposed changes while linking to the date dimension

acc_num | acc_approved_date_id| acc_rejected_date_id| file_gen_date_id

However this creates problems of having too many joins to the date dimension table while creating the reports that captures all of these dates. I'm proposing a hybrid of the two where I store both the dates and the ids for these fields.

Upvotes: 0

Views: 1279

Answers (1)

Rich
Rich

Reputation: 2279

You'd only have joins to the date dimension table if you wanted to find out something about the date (a name of the month and year for example) or wanted to filter on the date. Doing it by multiple date keys is the correct way of doing it- for all dimensions you want to filter by or include in your query results, you need a join.

Upvotes: 0

Related Questions