Time Dimension in Data Warehouse

0
votes

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.

1
mysqldatabase-designdata-warehousedimensional-modeling
Would you share with us few fact table rows. It would be great to visualize a problem. Thus, what makes problem for you? Many joins - performance or administration? Are you suggesting flat table design? - fenix
@fenix Updated the question as per your comment. It certainly is a pain to create reports as I've around 10 such fields. I'm not clear whether it would lead to performance issues. Thanks. - Bazooka
I don't know the volume of your data, I don't know desired performance, so, in my head I will use current model. You can always try to make junk dimension (dim_id, dim_att1, dim_att2 ... dim_attN), and than test your speed with querying fact table directly instead of using joins. However, as I see, mySQL is not best choice for data warehousing, but I'm still not sure what's your issue? Performance or something else? - fenix
OK. We don't have a very large volume of data now, but expecting it to grow. My major concern here is that would be "academically" the most correct way to tackle this problem. I'm new to Data warehousing and don't know all the best practices yet. - Bazooka
Theoretically? :) Few solutions, you can start with role-playing dimension. See references: kimballgroup.com/data-warehouse-business-intelligence-resources/… biwithjb.wordpress.com/2013/05/06/… kimballgroup.com/2004/10/design-tip-61-handling-all-the-dates - fenix

1 Answers

0
votes

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.