My company wants to acquire a new tool (probably Microstrategy) for business intelligence (BI) purposes. My role is to design/customize the star schema for the tool for our pharmaceutical manufacturing company. What are the basic things that I need to know about a star schema design? Should I explore the requirements (with regards to sales) before designing the star schema?
You shouldn't jump right into designing a star shema. You should always explore business
requirements before modeling structure, whether that's a star schema, a normalized model or a flat
You really have 3 masters to serve - one is the business, one is Microstrategy and one is sound dimensional modeling practice. Ralph Kimball's books are the best for the latter and I would work with the Microstrategy team to understand what it needs. Since most of us don't code perfect dimensional models due to various reasons, the tools have become a lot more adept at working with various modeling styles -- including a star schema -- in the past several years. Don't let this lull you into poor modeling techniques however. It will be much more difficult to develop the end user layer the more you stray from sound dimensional modeling practice.
This was first published in July 2003