I ran into ‘time’ difficulties on populating fact tables with timeIds and dateIds. Even a relatively simple query was taking too long to fetch correct values into table. I guess doing 10 million row joins between two tables (10m_ratings and fact_rating) joined to dime_time/dime_date is a bit too much for my laptop. Maybe I stared at it too much.
The only thing that I can think of is regenerating both fact tables and including the original timeStamp in both of them this time. This will save a join and, hopefully, make populating these fact tables speedy enough not to drive me nuts. I can remove these columns when they are not needed anymore.
Properly indexing fields used in the UPDATEs from prior posting will help tremendously as well.
This is not even the cool part of this project but more of a foundation so that I know how things work. Commercial products do these things for you anyways I think.
This revised schema better illustrates this change.