Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...
share|improve this question
    
vote for option 2. –  sufleR Nov 16 '12 at 7:44

1 Answer 1

I would recommend going with option #2. Date is a special case in the star schema world: the benefits you gain from joining to that dimension so heavily outweigh the snowflaking that you'll want to do it.

Option #1 means that you'll lose a lot of the analytical benefits of having a data warehouse to begin with: you can't easily do things like looking for how customers might differ in patterns based upon age, for example.

Option #3 would involve a huge explosion in your dimension tables: you'd be carrying around a lot of extra attributes for no reason. Normally this isn't bad--the whole point of a star schema is to denormalize and simplify your dimensional structure--but this just happens to be a rare case in which you'd be doing it so often for so many attributes, and every time you update a row in the dimension, you might have to update all of those attributes as well. That's a negative performance effect on your incremental dimensional loads without really making the model easier for business users to understand. It might even make things worse: their eyes could glaze over if you have 70+ date-related attributes (think standard year, different types of fiscal years, etc.) for each date on the dimension. It'd get much more difficult to find the actual dimensional attributes in the midst of 140 or more date-related columns.

To help you sleep at night, you could also create two graphical models: one which is the real data model, and one which is the "dateless" data model. The "datless" model would strip the connections between other dimensions and Date, and would look more like a natural star schema.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.