Inmon vs. Kimball
From application data to corporate data
Imagine you're organizing a family photo album. There are two ways to do it. Ralph Kimball and Bill Inmon are like two friends giving you different advice on how to structure it.
Kimball says: “Group the photos by important events, such as birthdays, vacations, or Christmas. That way, when you want to remember something specific, you just open the album for that event and find the photos easily.”
Inmon says: “First, categorize all the photos into sections like 'Family,' 'Vacations,' and 'Birthdays.' Once they're organized, you can create themed albums with the photos you need.”
Both approaches have their pros and cons, but each answers different questions. Now, let's apply this analogy to the world of Data Warehouses.
Key Differences Between Kimball and Inmon Approaches
The Kimball approach (Bottom-up) focuses on building dimensional Data Marts optimized for fast analysis. Its data structure is based on star or snowflake schemas, making access easier for business users. This model prioritizes query speed and simplicity for operational decision-making.
On the other hand, the Inmon approach (Top-down) first creates a highly normalized corporate Data Warehouse in 3rd Normal Form (3NF). This DWH serves as the single source of truth for the entire organization.
Unlike Kimball’s model, Inmon does not use star or snowflake schemas, meaning it is not designed to be consumed directly by analytical tools. To facilitate data consumption, dimensional Data Marts must be created on top of it.
The Relationship Between OLTP, Inmon, and Kimball
It’s important to understand that both Online Transactional Processing (OLTP) systems and Inmon’s Data Warehouse follow the principles of 3rd Normal Form (3NF), but with different goals.
OLTP systems are designed to handle day-to-day transactions with speed and accuracy, so their normalized data structure minimizes redundancy and facilitates data updates. For example, a sales OLTP system separates customer, product, and billing information into different related tables to avoid duplication and ensure consistency.
However, this same normalization can be a drawback for analysis because obtaining consolidated information requires joining multiple tables, making queries complex and slow.
The Inmon approach, by building a corporate Data Warehouse in 3NF, adopts a similar structure to transactional systems, but aims to integrate data from multiple sources while ensuring data quality and consistency at an enterprise level. Despite this, Inmon’s Data Warehouse is not designed for direct querying, as the excessive normalization makes analytical queries inefficient.
To solve this issue, dimensional Data Marts are built following Kimball’s model, which denormalizes the data into optimized structures such as fact and dimension tables. This enables BI tools to query information quickly and easily.
In summary:
OLTP: Operational systems in 3NF for daily transactions.
Inmon: Corporate Data Warehouse in 3NF for enterprise integration.
Kimball: Dimensional Data Marts for fast and efficient analysis.
Why Can't Inmon Be Consumed Directly?
Inmon’s model follows a highly normalized approach, meaning data is spread across multiple related tables to reduce redundancy and ensure consistency. While this design enhances data integrity, it also increases query complexity, requiring multiple table joins in SQL.
For example, if you want to generate a sales report in an Inmon-based Data Warehouse, you would need to join several tables such as:
Customer(contains customer details),Product(product details),Sales(sales records with foreign keys),SalesDetails(individual products within each sale).
This makes direct analysis difficult, which is why dimensional Data Marts are created to simplify queries by restructuring the data into easy-to-query schemas, such as star or snowflake schemas.
Pros and Cons of Each Approach
Kimball (Dimensional Approach):
Pros:
Easy for business users to query and understand.
Faster implementation compared to Inmon.
Optimized for reporting and dashboarding tools.
Provides quick insights for decision-making.
Lower initial cost and complexity.
Cons:
May lead to data redundancy across different Data Marts.
Less focus on enterprise-wide data consistency and governance.
Difficult to adapt to changing business requirements.
Requires careful planning to avoid siloed Data Marts.
Inmon (Normalized 3NF Approach):
Pros:
Ensures high data quality and consistency across the organization.
Provides a single source of truth by integrating data from multiple sources.
Easier to scale and accommodate future data sources.
Strong governance and compliance capabilities.
Reduces data duplication and storage costs over time.
Cons:
Requires significant time and resources to implement.
Complex query structures, making it challenging for business users to access data directly.
Higher initial costs due to extensive ETL processes and data modeling.
Typically needs additional Data Marts for easy data consumption.
Adding an ODS Before the Data Warehouse
Some companies prefer to add an Operational Data Store (ODS) as an intermediate layer before the Data Warehouse. An ODS collects real-time data from transactional systems and makes it available for operational reporting without overloading source systems.
Benefits of an ODS:
Provides quick access to recent data.
Reduces the load on operational systems.
Serves as a staging area before loading data into the DWH.
Enables near real-time operational decision-making.
Typical data flow with an ODS:
Operational systems (CRM, ERP, sales)
ODS (quick operational data access)
Inmon Data Warehouse (corporate view)
Kimball Data Marts (optimized reports).
The Hybrid Approach: Combining Inmon and Kimball
Many organizations choose to combine both approaches to take advantage of their strengths:
First, build a corporate Data Warehouse following Inmon’s approach, integrating data from multiple systems in a normalized format.
Then, create Data Marts using Kimball’s approach, providing fast and accessible reports for business users.
This hybrid model offers a solid and reliable foundation (Inmon), while ensuring the flexibility and speed needed for data analysis (Kimball).
Conclusion
Both Inmon and Kimball approaches have their place, depending on the organization’s needs and goals. If the priority is to deliver fast reports to specific business areas, Kimball is the ideal choice. If the goal is long-term data quality and governance, Inmon is the better option.
In many cases, combining both models allows organizations to leverage their strengths, achieving a balance between governance and speed.
We hope this newsletter has helped clarify the differences between these two approaches and how to apply them in your company.