I am going to go through a series of posts for dimensional modeling. I have been doing data warehousing for what feels like awhile now, and it sucks to learn some of it the hard way. One little misstep can result in wasted time.
Star Schema or not to Star Schema:
Star schema...for real...star schema...sometimes it's hard to jump out of working on an operational database and do dimensional modeling because it simply doesn't look right. Let's face it, you're conditioned. A Star Schema is what makes the model easy to report upon, every dimension has one record that relates to the fact table -- join each dimension to the fact, and there is no wrong way to look at that data.
There are cases when you need to break this, but put it in your noggin' that you are violating the moral code, for a better cause...don't get in the habit of breaking it because you can. Sometimes things look as if they would not fit into a Star schema, but then I do some transformations and find that there actually is a way. Think, don't just do. Remember, you are conditioned. I am not talking about snowflaking, but when one tends to gravitate away from dimensional modeling standards.
Dimension Galore Syndrome (Too many dimensions):
I know some consulting firms will show things like, for instance, in a school, a course can have a department, division, college, session...and so on, as separate dimensions, but in reality they can be within the course dimension for the fact of registration. Why? Because examining the source system shows me they are each a one-to-one on the course. If I followed what some firms do, I will end up with so many joins and as the fact table grows what do you think will happen? If I see that these codes define the course dimension, then that is where they should be. Don't think too hard in the model, ETL will make it happen how you see fit (given you take the time to understand the source database/system)
