4 ms·
Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that. For days as my example has, would the Time table be generated for say 1
by pbowyer 7y ago
Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that.
For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.
- JamesSwift 7y agoYes, the time table is a pregenerated set of all possible moments in time for whatever resolution you care about. So, for example, if you cared about day resolution it might be date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short ... 20200101 2020 1 1 1 1 january jan 20200102 2020 1 2 1 1 january jan ... 20200401 2020 4 1 2 14 april apr ... You would FK on the `date_key` and add as many columns as you need to support querying against the dimension. I also like to add a proper `datetime` representation of the date so I can easily do a date range query.
- jacques_chester 7y agoI found Kimball's book to be interesting and helpful. I think the Kimball approach gets bogged down in temporal evolution (Slowly Changing Dimension type 1? 2? 3? 4? 5?), but that's more about the underlying absence of meaningful bitemporalism than the dimensional schema approach per se.