Microstrategy Blog

I can’t stress enough the importance of planning your data warehouse properly. I don’t think that I’ve come across a developer who has “done it right” the first pass…or, to clarify, an architect who said “you know what would have been nice, if we would have…”

Things to think on.

• Aggregate Tables – Sometimes it just makes things quicker.
• Pick a date – It’s really helpful to have one Primary date to that you can easily marry your facts. Have this date tied to a lookup heirarchy…I’m open to other suggestions on this though, I’m sure there are some creative ways of dealing with Multiple date fields on one record (eg. Post Date, Create Date, Transaction Date, etc)
• Proper Lookup Heirarchies – Don’t put everything everywhere and expect it to work. You don’t want to have conflicting messages, or multiple joins from table to table. Try to keep one to one relationships.
• COALESCE is your friend in SQL…the idea is if a lookup doesn’t have a match in your data warehouse, have the ID be 0, and have that ID attached to an UNKNOWN. Don’t filter out nulls at the table join level. This is the most important lesson I learned in development from a friend of mine, it keeps things clean…

Anyone else have any input…this is just a prelim list here. We’re human, so we can learn from others mistakes…let’s use that.