4 ms·

The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting dat

The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting database). Once you reimagine the data in the OLAP style then these kinds of queries are simplistic. EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.

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 ago
  Yes, 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 ago
  I 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.

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 ago
Yes, 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 ago
I 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.