How to avoid most common mistakes in dimensional modelling

As a consultant, I have encountered several common mistakes in dimensional modelling that I often hear about in training sessions. I’d like to share them with you as well as some quick ways to avoid those mistakes. 

1. Overcomplicating the Model: One of the most common mistakes in dimensional modelling is creating overly complex models. This can happen when designers try to include too many dimensions or too much detail in a single fact table. To avoid this, keep your model aligned with the business requirements and focus on the key dimensions that will provide the most value to your organisation. 

2. Inconsistent Naming Conventions: Another common mistake is using inconsistent naming conventions for tables and columns in the model. This can make it difficult for users to understand the relationships between tables and can lead to confusion when querying the data. When designing the model, make sure to establish clear naming conventions and stick to them throughout the model. 

3. Incomplete or Inaccurate Data: Dimensional models are only as good as the data that is included in them. The model will not provide reliable insights if the data is incomplete or inaccurate. To avoid this, it’s important to carefully vet the data before including it in the model and to ensure that it’s accurate and complete. 

4. Failing to Account for Changes Over Time: Another common mistake is failing to account for changes in the data over time. Dimensional models are often used to analyse data over time, so it’s important to account for changes in the data, such as changes in dimension values or changes in the granularity of the data. Make sure to use slowly changing dimensions (SCD 0-7) when appropriate. Here is an example use of SCD2.

5. Including Non-Aggregatable Data: Finally, another common mistake is including non-aggregatable data in the model. Non-aggregatable data is data that cannot be easily summarised or rolled up, such as free-form text or images. When designing a dimensional model, it’s important to exclude this type of data or find ways to summarise it meaningfully. 

This table provides a quick summary of the above: 

By following these tips, you can avoid some common mistakes in dimensional modelling and create more effective and reliable models that provide valuable insights for your organisation. 

If you’re struggling to create effective dimensional models or want to learn more about avoiding these common mistakes, consider signing up for our course on Dimensional Modelling. Our expert instructors will provide you with the knowledge and skills you need to create effective dimensional models that will help drive insights and improve decision-making in your organisation.