Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
The architecture of the Kimball Approach
2.1.
Properties of the Architecture
3.
Kimball’s Bottom-up Approach
3.1.
First Step
3.2.
Second Step
3.3.
Third Step
4.
Advantages of the Kimball Approach
5.
Disadvantages of the Kimball Approach
6.
Kimball Vs Inmon
7.
Frequently Asked Questions
7.1.
What is the primary difference between Inmon and Kimball data warehousing?
7.2.
What does OLAP stand for?
7.3.
What exactly is a data mart?
7.4.
Why are data marts necessary?
7.5.
What is the best schema for a data mart?
8.
Conclusion
Last Updated: Mar 27, 2024

Kimball Approach In Data Warehouse Designing

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Data is one of a company's most valuable assets, and it may have a significant impact on its long-term performance.

Data warehousing increases the speed and efficiency of accessing different data sets, making it easier for corporate decision-makers. Data Warehousing helps in extracting insights that will help them develop business and marketing strategies that set them apart from their competitors.

This article will discuss the Design of the Data Warehouse using the Kimball Approach. We have discussed the Inmon Approach in our first part.

The architecture of the Kimball Approach

As proposed by Ralph Kimball, the bottom-up method calls for the creation of many data marts to satisfy the analytical needs of departments, followed by virtual integration of these data marts for consistency through an information bus.

Properties of the Architecture

  • The data is normalised into a star schema, which is a popular concept.
  • This approach makes query writing quick and easy, allowing you to get your report out as soon as possible.
  • The star schema is used to organise data from a specific topic area or process.
  • A dimensional data warehouse is formed when shared attributes link many data marts.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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!!

 

Live masterclass