Ralph Kimball vs. Bill Inmon approaches to data warehouse design
Learn about the debate between the Ralph Kimball and Bill Inmon approaches to data warehouse design.
I'll define approach as a combination of methodology (build-out schedule) and architecture (data flows and structures)....
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Both Bill Inmon and Ralph Kimball have made tremendous contributions to our industry.
I have an entire presentation on this subject, but I'll try to boil it down here. There are fewer differences than people think between the two approaches to data warehouse design. It's mostly a matter of different focuses (Inmon - architecture, Kimball - modeling) and slightly different terminology.
I strongly believe that customization and extension are required for each situation. Neither approach is comprehensive to answer all questions nor remove your need to apply judgment.
You'll have two basic decisions to make and numerous follow-on decisions:
- Enterprise vs. Data Mart-Oriented Architecture
- Enterprise vs. Bottoms-up Oriented Methodology
Example follow-on decisions:
- Build out scope
- Business involvement
- Definition of data marts - units of work or physical expansiveness of use with ETL tool in ETL
- Processes data access options and manner of selection - by use, by enterprise, by category
- Data retention and archival definition of data marts - units of work or physical expansiveness of use of ETL tool in ETL processes
- Granularity of data capture integration strategy – virtual and physical metadata handling modeling technique(s) need, utility and physical nature of data marts
- Operational reporting and monitoring - real-time data warehouse, EAI, BAM performance management
- Persistence, need and physical nature of data staging
- Physical instantiation of operational data stores - single-source, multi-source
- Program development team engineering technology selection process - framework, best-of-breed
- Source work effort distribution - source team, data warehouse team, shared
- Use of operational data stores for source systems - selective, complete
Especially for "large" (or potentially large) data warehouses, I favor EDW architecture and a quasi-bottoms-up methodology. It's like how I conclude the presentation: "The best approach seems to be a hybrid quasi enterprise architecture and hybrid, but rapid, development methodology with some up-front work and use of standards with broadly defined enterprises and federation techniques used to unite large enterprises."
Best practices say an organization would be best served by an Enterprise Architect with whom BI Architects collaborate their projects. The Enterprise Architect (using the Inmon approach) ensures the EDW is in 3rd normal form, data relationships are accurately represented, and all data sources are appropriately rationalized and integrated. This step must be done before a BI project can consume disparate data. So, the EDW must be built first, which means the Inmon approach has more weight and value.
The least risky time to use the “bottom-up” approach is when there can be only one data source. Still, this would not negate the need for the BI Architect to collaborate with the Enterprise Architect, who should have project oversight, in the event another project comes into the picture that leverages any of the same data objects. A BI project cannot know everything about the Enterprise, and is foolish to believe they have a prevue of the current and future state of all Enterprise data objects and relationships.
The whole point is: It is essential for a BI project’s design (i.e. Kimball approach) to have collaboration with the Enterprise Architect (the Inmon approach) from the time of project inception! Data is an enterprise asset.
There are two benefits for doing so:
1. To plan future enhancements, flexibility, and scale-ability, the Enterprise Architect needs to vet a project’s design against the “as is” and “to be” EDW design, since it is always a work in progress. It is the Enterprise Architect’s job to review the BI design for strategy and adherence to best practices.
2. To avoid costly BI project re-work; as an exclusive “bottom-up” approach may result in redundancy, or at the very least, incomplete corporate data being reported. In either event, how much more would the project cost before it would start being regarded as “value-added”? How long might it take before incomplete corporate data becomes noticed? I’ve seen this happen for many months!
Really, it’s not about egos, who wants kudos for a job well done, or whose idea it was.
It’s all about us working together when we should. Ultimately, it is about doing the right thing for the Enterprise – the company as a whole.