What are the pros and cons of using a dimensional model (Kimball) vs a normalized model (Inmon)?

由人工智能和领英社区提供技术支持

Data modeling is the process of designing how data will be stored, organized, and accessed in a database or data warehouse. Depending on the purpose and scope of your data project, you may choose to use a dimensional model (Kimball) or a normalized model (Inmon) as your data architecture. But what are the differences between these two approaches, and what are the pros and cons of each one? In this article, we will compare and contrast the Kimball and Inmon methods, and help you decide which one suits your data needs better.

此文章中的业界达人

由社区从 7 条内容中精选。了解更多

1 Kimball's dimensional model

The dimensional model, also known as the star schema or the snowflake schema, is a popular data modeling technique developed by Ralph Kimball. It consists of two types of tables: fact tables and dimension tables. Fact tables store the quantitative data, such as sales, revenue, or clicks, while dimension tables store the descriptive data, such as product, customer, or date. The fact tables are linked to the dimension tables by foreign keys, forming a star-like or snowflake-like structure. The dimensional model is designed to optimize the performance and usability of data analysis and reporting, by reducing the number of joins, simplifying the queries, and providing a consistent and intuitive view of the data.

添加您的观点

加载更多内容

2 Inmon's normalized model

The normalized model, also known as the enterprise data warehouse (EDW) or the relational model, is another common data modeling technique developed by Bill Inmon. It consists of applying the principles of normalization to the data, which means dividing the data into multiple tables that have minimal redundancy and maximal integrity. The normalized model is designed to optimize the quality and consistency of the data, by avoiding data duplication, ensuring data accuracy, and facilitating data integration and maintenance. The normalized model is usually implemented as a centralized and standardized data repository that serves as the single source of truth for the entire organization.

添加您的观点

Carlos Fernando Chicata

Algunas insignias de community Top Voice | Ingeniero de datos | AWS User Group Perú - Arequipa | AWS x3
(已编辑)
举报内容
The base of EDW is implement on data model the 3 normal form with time handling. Use 4NF or using superior techniques is not very common and maybe move to ensemble data modeling a little. But you can use any normal forms to implement it.

已翻译

赞
Nicholas Whiting

Senior Fellow | CXO Advisor | Principal Consultant | Architect / Engineer | Flight Instructor - IT Governance, Digital Transformation, Data and ML Programs, Cybersecurity, Application Modernization, and Cloud Adoption
举报内容
The Inmon approach's emphasis on data consistency and integrity makes it particularly suitable for organizations with stringent compliance requirements, such as finance and healthcare sectors. Its design facilitates robust audit trails and data lineage, ensuring that data remains accurate and compliant with regulations. Additionally, Inmon's centralized approach can aid in implementing organization-wide data governance policies, making it easier to control access and ensure data security across the entire data ecosystem.

已翻译

赞

3 Pros and cons of Kimball

The Kimball approach offers several advantages, such as improving the speed and efficiency of data analysis and reporting, enhancing user-friendliness and understandability, and allowing for flexibility and scalability of the data model. However, it also has several drawbacks, such as compromising the quality and consistency of data, increasing the complexity and cost of data loading and transformation, and limiting the scope and depth of data analysis. By minimizing complexity and size of queries, providing a clear representation of business processes, and enabling incremental development, the Kimball approach can enable faster analysis and reporting. However, it can also introduce data redundancy, inconsistency, and fragmentation across different fact tables and dimension tables, require extensive ETL processes to populate and update tables, and focus on specific business questions rather than providing a holistic view of the data.

添加您的观点

Nicholas Whiting

Senior Fellow | CXO Advisor | Principal Consultant | Architect / Engineer | Flight Instructor - IT Governance, Digital Transformation, Data and ML Programs, Cybersecurity, Application Modernization, and Cloud Adoption
举报内容
While the Kimball approach offers agility by allowing incremental development and easier incorporation of new data sources, this flexibility can sometimes lead to challenges in maintaining data quality. In rapidly evolving business environments, where changes in data structures and definitions are common, ensuring consistency across multiple data marts can be problematic. Therefore, organizations leveraging Kimball must invest in strong data validation and reconciliation processes to mitigate potential inconsistencies and maintain high data quality standards.

已翻译

赞

4 Pros and cons of Inmon

The Inmon approach offers several advantages, such as ensuring data quality and consistency, facilitating data integration and maintenance, and supporting the scope and depth of data analysis. However, there are also some drawbacks to consider. For instance, it can reduce the speed and efficiency of data analysis and reporting due to the complexity of queries. Additionally, it can make the data less user-friendly and understandable due to its abstract representation that may not match the business logic. Lastly, it can limit the flexibility and scalability of the data model by imposing rigid rules and standards.

添加您的观点

Carlos Fernando Chicata

Algunas insignias de community Top Voice | Ingeniero de datos | AWS User Group Perú - Arequipa | AWS x3
(已编辑)
举报内容
Use normal form techniques more advance, more reduce the redundacy in data and get more tables; then you have these points: Cons: Use several joins to get a entity. In this case, the performance maybe affect. Pros: Support the reusability of table easier on data model, that affect possitively the integration and quality control of the data. In my opinion; this is the reason to use the 3NF on the data model: to balance the performance aspect with the integration and quality aspects.

已翻译

赞

5 How to choose between Kimball and Inmon

There is no definitive answer to which data modeling technique is better, as it depends on various factors. However, some general guidelines can help you make an informed decision. For example, if you have a relatively small and simple data set, the Kimball approach may be best, as it can be used for descriptive and prescriptive analytics, such as dashboards and reports. It can also be accessed and updated frequently by different users and applications. On the other hand, if you have a large and complex data set, the Inmon approach may be more suitable. This approach is mainly used for exploratory and predictive analytics and needs to be integrated and maintained consistently by a centralized data team.

添加您的观点

Robert Stanley

Ecosystem Architecture - Modern Hybrid Infrastructure and Data Architecture Designed for Analytics
(已编辑)
举报内容
A final solution may include a bit of both. Data can be warehoused in an RDBMS with a "Normalized" PDM. Although not always as disciplined as 3rd Form. There's a point of diminishing return for "over-governance" but largely managed here. Not always the most agile due to extensive rigor to ensure accurate analysis and reporting downstream. Thay serves the IT folks. But, as mentioned, the normalized model may not be intuitively obvious for business community so... With the Normalized/Governed PDM as input, an access layer is added that more closely resembles a Star/Snowflake model. This presentation is much more intutive for user community. Key... Any underlying PDM quality issues are magnified in this layer.

已翻译

赞

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?

添加您的观点

Carlos Fernando Chicata

Algunas insignias de community Top Voice | Ingeniero de datos | AWS User Group Perú - Arequipa | AWS x3
举报内容
Remember that both techniques can complement each other because it work in different part of the data architecture; Kimball approach for data mart or Inmon approach for data warehouse: depend how you design the data architecture; this is a main point!

已翻译

赞

Data Modeling

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the pros and cons of using a dimensional model (Kimball) vs a normalized model (Inmon)?

1

2

3

4

5

6

1 Kimball's dimensional model

2 Inmon's normalized model

3 Pros and cons of Kimball

4 Pros and cons of Inmon

5 How to choose between Kimball and Inmon

6 Here’s what else to consider

Data Modeling

给文章评分

感谢您的反馈

更多Data Modeling相关文章

更多相关阅读内容