I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas. In my warehouse, I've got two different fact tables that tracking daily changes in dollar values. Each entity in these fact tables have an underlying Sales Order and Line to which they relate. These SOs and Lines then have other related dimensions, such as customer, product, etc. About 12 sub-dimensions total so far.

My question is if I should be rolling all these sub dimensions up directly into the fact tables, or if I should use a little snowflaking in my warehouse, and have them branching off the Sales Order and Lines dimension instead.

The first option obviously follows a star-schema model better. However, if changes are made such as adding additional dimensions, it becomes more maintenance, basically having to do the ETL twice for each fact table, rather than just the once on the SO dimension. As well, if a new fact is added that relates to Sales Orders, I'd have to go through the whole process again.

As this is my first DW/OLAP project, I'm not familiar on where the line should be drawn on snowflaking, and other people's thoughts would be highly appreciated.

  • 2
    I think this is going to depend almost entirely on your expected use cases for reporting from the DW. If you expect to be filtering on SO to get to your facts, and maybe grouping SOs based on their criteria, snowflake makes sense. If you expect to be filtering on the aspects of SOs like customer/product etc. directly to get facts, then it will be faster to report with a star schema. Bear in mind the goal is to enable the business to get information, so the information needs of the business should drive the design. – JNK Mar 26 '13 at 20:51

The reason you can't see "where the line should be drawn" is that very notion of "snowflaking" is bankrupt (no matter how much it's been written about).

The simplest approach is the best: get your design in BCNF and start using it. Add constraints for correctness, views for convenience, procedures for sanity, and indexes for speed. Do not assume performance will be a problem, or that joins cause performance problems. Wait until they do -- if they do -- and address them then. At most, you'll need to materialize that nice view or possibly write it overnight to a secondary table.

There's a huge sticky myth out there that there's something special about data warehouses. There is not. If your tables are normalized, you may be surprised how well the server "rolls up your dimensions", which is known everywhere else as "executes your query".

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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