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.
- 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’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.
16 thoughts on “Kimball and Inmon DW Models”
great article, the Top-Down schema helped me understand the concepts perfectly.
however I think that the bottom-up model schema is a bit unclear or maybe you have a different view:
– DW should be virtual (the queries are done directly to the DataMarts). if not what do you store in the DW in your schema since both aggregated and transactional data are available in the DataMarts?
– Also you say that DataMarts are normalized in 3NF however the DM should be a single Star Schema where usually large fact tables representing numerical measures and counts are in 3NF and smaller dimensions are de-normalized to 2NF which basically as a whole it will be a de-normalized schema
In Kimball Diagram what does the data mart before Data Warehouse mean?
My understanding was that Data Mart exists after DW in both Kimball and Inmon and main difference being that Inmon stores mainly atomic data in DW and data mart contains additional info that is generally calculated whereas Kimball does all of it in DW.
Inmon Model on high level seems ok but it does not seem to match Inmon latest point of view that he described in his DW 2.0 book.
I agree with Hybrid approach however I would include Data Vault as Data Warehouse which is something Inmon likes (which doesn’t surprise me as it seems to achieve better results than 3NF).
Key challenge is Data Quality and Master Data and I think this should be part of the BI Diagram together with Power Users that have much more ad-hoc needs.
Here is what I have in mind http://www.katieandemil.com/microsoft-bi-architecture-diagram-with-explanation
On a related note, here is an article in support of the hybrid model discussed in this post http://www.kimballgroup.com/2012/08/01/design-tip-148-complementing-3nf-edws-with-dimensional-presentation-areas/
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.
Thanks Grady. Realized that diagram pasted under Inmon was actually a hybrid model which has since been corrected.
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.
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?
Tejas, Data mining models require data in 3NF and here Inmon’s approach would be a definite winner.
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
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.
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.
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!
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.
Excellent post. Simple and clear.