Kimball’s Bottom-up Approach
Kimball’s Approach to Data Warehouse Designing mainly consists of the following three steps:
Step 1: Identifying the critical business processes and queries.
Step 2: A dimensional model is created by loading data into it.
Step 3: Development of the final Physical Model.
First Step
The Kimball method for creating a data warehouse begins with identifying the critical business activities and questions the data warehouse must answer.
The data warehouse's primary data sources (operational systems) are analysed and recorded. Data is gathered from many sources and loaded into a staging area using ETL software.
Second Step
A dimensional model is used to load data. The main difference is that the dimensional model proposed by Kimball for data warehousing is not normalised.
The star schema is the foundation of dimensional modelling. Numerous dimensions in the star schema usually surround a fact table. The fact table contains all of the essential measures for the topic area and the foreign keys from the many dimensions surrounding the fact.
The dimensions have been denormalized, allowing the user to drill up and down without having to link to another table. The organisation will create several star schemas to meet various reporting requirements.
Third Step
The final model is created after the dimensional model. To achieve the integration in the Model, the concept of 'conformed dimensions' is proposed by Kimball.
Conformed dimensions: The critical dimensions shared across all facts, such as customer and product, will be built once and used by every fact. This ensures that a single idea or concept is applied consistently.
The 'enterprise bus matrix' is another essential feature of the Kimball model.
The Dimensions of the enterprise bus matrix in the document:
- Vertical list: distinct facts.
- Horizontal list: the conformed dimensions.
It is noted in the document if the dimensions play a foreign key role in the fact. This documentation serves as a reference point for how the star schemas are formed and what remains to be built in the data warehouse.
Advantages of the Kimball Approach
The following are the main advantages of the Kimball method:
- The first part of the data warehousing project will be provided fast because it is simple to set up and build.
- The star schema is simple to understand for business users and implement for reporting. Most BI (business intelligence) solutions are compatible with the star schema.
- The data warehousing environment has a small footprint; it takes up less space in the database and makes system maintenance more manageable.
- A small group of developers and architects is all that is required to keep the data warehouse running smoothly.
- Because data marts are designed for departmental or business process-level reporting, it works very well for department-level metrics.
- Drill-across, in which a BI tool generates a report by traversing various star schemas, may be achieved successfully with conformed dimensions.
Disadvantages of the Kimball Approach
The following are some of the Kimball method's drawbacks:
- Because data is not fully integrated before addressing reporting purposes, the core of the 'one source of truth’ is lost.
- Over time, redundant data might produce data update abnormalities.
-
Adding columns to the fact table can slow things down. This is because tables are built to be very deep. The fact table will get significantly more prominent and perform poorly if more columns are added. As a result, changing the dimensional model as the business requirements vary is difficult.
- Because the model targets business processes rather than the enterprise, it cannot meet all enterprise reporting demands.
- Integrating legacy data into a data warehouse is a time-consuming procedure.
Kimball Vs Inmon
Each of these strategies offers its own set of advantages in different contexts. Although Inmon's method is time-consuming, it is simple to maintain. Kimball's method saves time but is difficult to maintain, typically repetitive, and subject to modifications.
Inmon prefers strategic and enterprise-wide data integration requirements, whereas Kimball prefers initial business support and individual company data integration requirements.
Kimball's approach is best when you have a small generalist team, while Inmon's is best when you have a more extensive and more specialised group.
The Kimball technique can be used when you quickly require a first data house and a reasonable cost. On the other hand, the Inmon technique can be used if you have a high startup cost and require a longer time to meet your business needs.
Read about, Spring Boot Architecture
Frequently Asked Questions
What is the primary difference between Inmon and Kimball data warehousing?
In a dimensional data warehouse, Kimball employs a dimensional model such as star schemas or snowflakes to arrange the data. Still, in an enterprise data warehouse, Inmon uses an ER model.
Kimball employs the dimensional model for all data, whereas Inmon exclusively uses it for data marts.
What does OLAP stand for?
OLAP (online analytical processing) software is used to do multidimensional analysis on massive volumes of data from a data warehouse, data mart, or other centralised data storage at rapid rates.
What exactly is a data mart?
Data Marts are a subset of the data warehouse's information content that fulfils the needs of a specific department or business function. Data marts are frequently created and managed by a single department inside a company.
Why are data marts necessary?
Data Mart enables faster data access. Data Mart is simple to use because it was created with the needs of its customers in mind. As a result, a data mart can speed up corporate processes.
What is the best schema for a data mart?
Star and Snowflake Schema are used in Data Mart. It's a system that runs from a central location.
Conclusion
This article has discussed the Kimball approach for Designing the Data Warehouse. We extensively discussed Kimball’s bottom-up approach. We also ran through the advantages and disadvantages of the Kimball approach in data warehousing. We discussed when to use Kimball and when to use the Inmon approach.
We hope this article has helped you. You can learn about different Data Warehousing Tools.
Head over to our practice platform Coding Ninjas Studio to practice top problems, attempt mock tests, read interview experiences, interview bundle, follow guided paths for placement preparations, and much more!!
We wish you Good Luck! Keep coding and keep reading Ninja!!