All of my Business Intelligence clients have had intelligent and enthusiastic technical experts who were intimidated by dimensional modelling. I have to confess that I’m also still intimidated. I’m less intimidated than I used to be, but I know in my heart that I’m a much better modeller in third normal form than I am for dimensional models.
Dimensional Modelling is hard. It lacks both the mathematical precision and the maturity of Codd’s Normal Forms. That’s because the dimensional model is about the unknowable future needs of the analysts, rather than just laying-out an established set of data.
My first piece of advice is to follow an expert’s steps. I find the script from Lawrence Corr‘s Agile Data Warehouse Design useful.
Uncover and use the words that your stakeholders use. Use examples to determine if different words mean the same thing(synonyms) and, more importantly, when the same word is being used to describe different things (Homonyms).
Do just enough design up front to identify the things that are shared across different activities (conformed dimensions).
Use examples to declare the grain of your fact tables. Gather feedback on those examples from the people who are going to use the model.
My most important advice is to design and build your model incrementally. Even if you deliver the perfect model the first time, you change the information landscape when you deliver the model. That changed landscape makes the model no longer perfect. Once you understand that there is no perfect dimensional model, you’re left with the less stressful task of designing a model that is useful, or updating a model so that it is more useful.
The secret to dimensional design is delivering change regularly in response to your stakeholders’ evolving needs.