How do you validate and test your dimensional models in Excel?

1 Why validate and test your models?

Before you use your dimensional models for reporting or analysis, you need to make sure they are accurate, consistent, and reliable. Validating and testing your models can help you identify and fix any errors, inconsistencies, or anomalies in your data or design. This can improve the quality and performance of your models, as well as the confidence and trust of your users.

2 How to validate your models?

Validating your models is an important step to ensure they follow dimensional modeling principles and best practices. This includes confirming that the grain of your fact tables is accurate, the relationships between fact and dimension tables follow a star or snowflake schema, the attributes of dimension tables are descriptive, complete, and normalized, and the keys of fact and dimension tables are unique, consistent, and surrogate. Excel's built-in features like data validation, conditional formatting, and pivot tables can be used to validate models. For instance, data validation can check if keys are unique and consistent, conditional formatting can identify any missing or invalid values, and pivot tables can assess the grain and relationships of your tables.

3 How to test your models?

Testing your models is essential to verifying that they produce the expected results and meet the business requirements. You should check the accuracy of your facts, which should match the source data and calculations, as well as the completeness of your dimensions, which should cover all possible values and scenarios for your facts. Additionally, you should assess the usability and performance of your models to ensure they are easy to understand, query, and report on, as well as fast and efficient to load and query. Excel's built-in features such as formulas, functions, charts, filters, and slicers can be used to test your models. For example, formulas and functions can be used to compare your facts with the source data and calculations; charts can be used to visualize your dimensions and facts; while filters and slicers can be used to test the usability and performance of your models.

4 How to document your models?

Documenting your models is essential for creating and maintaining a comprehensive description of your data warehouse design, structure, and metadata. This can help you and your users understand, communicate, and maintain your models. When documenting, make sure to include the business requirements and objectives of the data warehouse project, the source systems and ETL processes, the conceptual, logical, and physical models of the data warehouse (including names, definitions, types, and relationships of tables, columns, and keys), the assumptions, rules, and calculations for facts and dimensions, as well as the reports, dashboards, and queries that use the data warehouse. Excel’s built-in features such as comments, notes, and diagrams can be used to document your models - for example comments or notes to explain the meaning/purpose of tables/columns or diagrams to illustrate the schema/relationships of your data warehouse.

5 How to improve your models?

Enhancing and optimizing your data warehouse design, structure, and performance is essential for meeting the changing and growing needs of your business and users. To improve your models, you can add new facts and dimensions to capture new business events and attributes, update existing facts and dimensions to reflect changes in the source data or business rules, refine the grain and attributes of your facts and dimensions to provide more detail and flexibility, revise the schema and relationships of your data warehouse to reduce complexity and redundancy, or tune the ETL processes and queries to increase speed and efficiency. Excel's built-in features such as data analysis tools, what-if analysis tools, and macros can also be used to improve your models; for instance, you can use data analysis tools to perform statistical and trend analysis on your facts and dimensions, what-if analysis tools to explore different scenarios and outcomes, or macros to automate and streamline your ETL processes and queries.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?