Kimball and Inmon DW Models

When it comes to Data Modelling you cannot go past the contributions of Ralph Kimball and Bill Inmon. There are numerous articles/white papers that go to great detail in dissecting their modelling techniques. There are serious fanatics on both camps. Rather than getting caught up in the debate, this post gives a snippet of both Kimball and Inmon’s model and let you choose which model is right for you next BI project.   

 The basis of this post is the illustration shown in Figure 1, pay special attention to the definition of Data Warehouse and Datamarts in both these models.

Figure 1 - Kimball and Inmon Models
Figure 1 – Kimball and Inmon Models

Kimball Model

  • Kimball’s model follows a bottom-up approach. The Data Warehouse (DW) is provisioned from Datamarts (DM) as and when they are available or required.
  • The Datamarts are sourced from OLTP systems are usually relational databases in Third normal form (3NF).
  • The Data Warehouse which is central to the model is a de-normalized star schema. The OLAP cubes are built on this DW.

Inmon Model

  • Inmon’s model follows a top-down approach. The Data Warehouse (DW) is sourced from OLTP systems and is the central repository of data.
  • The Data Warehouse in Inmon’s model is in Third Normal Form (3NF).
  • The Datamarts (DM) are provisioned out of the Data Warehouse as and when required. Datamarts in Inmon’s model are in 3NF from which the OLAP cubes are built.

Strengths and Weakness

Both these models have their own strengths and weakness. Kimball’s model is more scalable because of the bottom-up approach and hence you can start small and scale-up eventually. The ROI is usually faster with Kimball’s model. Because of this approach it is difficult to created re-usable structures/ ETL for different datamarts. On the other hand Inmon’s model is more structured and easier to maintain while it is rigid and takes more time to build. The significant advantage of Inmon’s model is because the DW is in 3NF; it is easier to build data mining models.

Both Kimball and Inmon models agree and emphasise that DW is the central repository of data and OLAP cubes are built of de-normalized star schemas.

In conclusion, when it comes to data modelling, it is irrelevant which camp you belong to as long as you understand why you are adopting a specific model. Sometimes it makes sense to take a hybrid approach.

Figure 2 - Hybrid Model
Figure 2 – Hybrid Model

Benny Austin

http://twitter.com/#!/DataEnthusiast/status/79054835198070784

14 thoughts on “Kimball and Inmon DW Models

  1. Benny,
    I think your diagram has it wrong. The Kimball Bus method is actually what you’ve referred to as Inmon’s. Inmon has nothing to do with star schemas. Both diagrams are Kimball-esque.

    1. Agree. I also question DW DB in 3NF. Enterprise OLTP datasource should already be in 3nf. DW is a data source for reporting and a result of ETL. Denormalized for speed.

  2. Argument I’ve heard in favour of Inmon is that you can choose to store history in datawarehouse and then have the flexibilty to have data mart supporting current + history or current only view of entities. History can be implemented in Kimballs design in Data marts. Can u please provide reference to examples where Inmon’s design could address a problem that Kimball’s design couldn’t or where only Inmon’s design could solve the problem?

    1. Tejas, Data mining models require data in 3NF and here Inmon’s approach would be a definite winner.

  3. Argument I’ve heard in favour of Inmon is that you can choose to store history in datawarehouse and then have the flexibilty to have data mart supporting current + history or current only view of entities. History can be implemented in Kimballs design in Data marts. Can u please provide reference to examples where Inmon’s design could address a problem that Kimball’s design couldn’t or where only Inmon’s design could solve the problem

  4. My concerns with Inmon’s design
    You always need an additional structure/data storage/transformation i.e. DN Star Schema/Datamart in Inmon’s design to do any reporting. This encourages building dimension and facts that are report/project/cube specific. Conformed dimnesions are valuable because they provide consistent view of entities across reports/projects/cubes. It seems to me that purpose of Datamart is also reports/projects/cubes specific. Hence it there are two different cubes which need the same dimension then you need to duplicate the same structure in both the datamarts.

    1. Tejas, agreed conformed dimensions are good and a must for a good DW solution. I believe it is a design consideration rather than choice of methodology. Dimensions can be modelled as conformed in both Inmon and Kimball approach. Cube needs to be built around a specific domain rather than a specific report.

      1. Hi Benny, excellent article. Can you please provide some kind of reference about how to model conformed dimensions when using Inmons approach? I’ve been looking all over but can’t find anything. I’d rather avoid having the same dimensions physically present in different data marts, but I want to use Inmons approach for the rest of the solution. Thanks!

      2. Andreas, conformed dimension is a term popularized by Kimball. It’s unlikely you’ll find reference of conformed dimension in Inmon’s model. From a design perspective, it’s better to have Facts that share common dimensions in same datamart with exception of date dimension. Date dimension in different datamart should then conform to the same structure, keys, display values, sort order etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s