I'm trying to learn about dimensional models and star schemas. Say I have a Sales fact table recording the total sales at retail stores, with, say, four dimensions, Date, Customers, Stores, and Promotions (as in sales promotions like coupons). (Pseudo-schema below).
I really like the idea of prepopulating the Date dimension with attributes like day of the week, so that you can easily filter the facts by seemingly complex conditions like "saturdays in the second quarter for the past 10 years".
Now, say I want the Promotion dimension to have Start_Date and End_Date attributes which denote the start and end of the promotion. How do I do this? I thought about it and can come up with three less than ideal solutions:
1) Make Start_Date and End_Date regular, atomic attributes (either ISO8601 strings or the DB's datetime object) and only allow limitted filtering.
2) Make Start_Date and End_Date foreign keys to the Date table, breaking the star schema, but allowing the same filtering capabilities as the full Date dimension.
3) Include attributes like Start_Date_Day_of_Week, Start_Date_Quarter, maintaining the star schema but increasing the dimension size and essentially duplicating the structure of the Date dimension in the Promotions dimension.
Sales Table ----------- Date key (FK to Dates table) Promotion key (FK to Promotions) Customer key (FK to Customers table) Store key (FK to Stores) Sales Amount Date Table ---------- Date Key (PK) Month Day of week Day of month Day of Quarter Day of year Holiday? Quarter Day since Epoch Month since Epoch etc... Promotion Table --------------- Promotion Key (PK) Type (Coupon code, 2-for-1 sale, 50% off, etc) Start_Date End_Date Customer Table -------------- Customer Key (PK) Name Age Sex etc... Stores Table ------------ Store Key (PK) Address City State Zip etc...