By Julia Gusman, Managing Consultant
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.
- 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 organization.
- 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.
- 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.
- 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.
- 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 summarized 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 summarize it meaningfully.
This table provides a quick summary of the above:
| Common Mistake | How to Avoid |
| Overcomplicating the Model | Keep the model as simple as possible and focus on key business questions. |
| Inconsistent Naming Conventions | Establish clear naming conventions for tables and columns in the model and stick to them throughout. Use descriptive and consistent names to help users understand how to use the fields for reporting and analysis. |
| Incomplete or Inaccurate Data | Carefully vet the data before including it in the model to ensure it is accurate and complete. Work with data stewards to identify any missing or incorrect data and develop strategies for addressing it. |
| Failing to Account for Changes Over Time | Make sure to use the appropriate type of slowly changing dimensions correctly. Pay attention to the correct use of the surrogate keys. |
| Including Non-Aggregatable Data | Identify non-aggregatable data, exclude it from the model, or load it into separate tables. Use aggregation hierarchies or text analytics to group non-aggregatable data into higher-level categories that can be aggregated. |
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 organization.
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 organization.
Sign up today and take your dimensional modelling skills to the next level!
