Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024

Data Warehouse Modeling

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

The process of developing the schemas for the data warehouse's detailed and summarized information is known as data warehouse modeling. The purpose of data warehouse modeling is to create a schema that describes the reality, or at least a portion of the reality, that the data warehouse must support.

Data warehouse modeling is essential in building a data warehouse for two essential reasons.

The first reason is to visualize the relationships among the warehouse data, and the second reason is to optimize the schema to make a well-structured data warehouse.

Also See, Multiple Granularity in DBMS and Checkpoint in DBMS

Data Modeling 

The process of developing a visual representation of an entire information system or sections to express connections between data points and structures is known as data modeling.

Must Read, Super Keys in DBMS

Why is data modeling important?

Data modeling ensures that all data objects required by the Database are correctly represented. Omission of data will lead to inaccurate reports and produce incorrect results. The omission of data will result in incorrect reporting and bad outcomes. Data modeling aims to illustrate the types of data utilized and stored within the system, the relationships between them, how they can be grouped and organized, and their formats and attributes.

The Data Model gives a clear picture of business requirements.

The life cycle of Data Modeling

  • Gathering Business Requirements
  • Conceptual Data Modeling
  • Logical Data Modeling
  • Physical Data Modeling
  • Development of Schema / the database
  • Maintenance of data Model time to time as per requirement

Types of the data model

Data modeling facilitates the creation of a conceptual model and the establishment of relationships between items.

  • Conceptual Data Model 
  • Logical Data Model 
  • Physical Data Model



 

Conceptual Data Model

Conceptual models are usually built as part of gathering early project requirements.

The conceptual model defines what the system contains. 

This data model focuses on finding the data used in a business instead of the processing flow.

The main objective of this data model is to organize and establish business rules and concepts.

It includes entity classes, properties and constraints, relationships, and the necessary security and data integrity requirements.

  • Its primary purpose is to establish entities, attributes, and relationships between two entities.
  • Business stakeholders or data architects create it
  • Its purpose is to create various business rules.

Logical Data Model

The logical Data Model map of rules and Data Structures includes the required data, such as tables, columns, etc.

A logical data model consists of tables, documents, descriptions, etc. The document structures are defined in this model. 

This data model is always present in the root package object.

This type of data model helps create the physical model base. There is no secondary or primary key defined in this model.

  • It defines the structure of data elements and their relationships also.
  • Business analysts and data architects create it.

Physical Data Model

In a physical data model, we care about how the system can store the actual data. 

It manages the replication, shards, etc., physically.

It defines the components and services which are required to build a database. It is created by using the database language and queries.

The physical data model provides database column keys, constraints, and RDBMS features.

  • We create various schemas, abstraction of schemas, and different mapping types in these data models.
  • Database administrators and developers create it.
  • It is the actual implementation of the Database.
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

Data Warehouse Modeling 

According to the definition by Bill Inmon, "Data Warehouse is a subject-oriented, integrated, non-volatile and time-variant collection of data in support of management's decision." 

A data warehouse is a storage and reporting system for data. Data is often collected from various sources before being transported to a data warehouse for long-term storage and analysis. This data is organized so that users from various divisions or departments within an organization may access and evaluate it as needed.

How  Data warehouse helps to improve business processes

  • ' structured' data is simpler to report on in a data warehouse.
  • With a data warehouse, we can report from multiple data sources simultaneously.
  • Historical reporting and trend analysis are feasible with a data warehouse.
  • A data warehouse minimizes reporting errors and saves a lot of time.

Types of Data Warehouses Models

So, mainly there are three different types of data warehouse models

  1. Enterprise warehouse
  2. Data Mart
  3. Virtual warehouse



Enterprise Warehouse

An Enterprise database brings together various functional areas of an organization and brings them together in a unified manner. An enterprise data warehouse structures and stores all company's business data for analytics querying and reporting. It collects all of the information about subjects spanning the entire organization.

The goal of the Enterprise data warehouse is to provide a complete overview of any particular object in the data model.

It mainly contains detailed summarized information and can range from a few gigabytes to hundreds of gigabytes, terabytes, or maybe beyond.

Data Mart

It is a data store designed for a particular department of an organization or company. Data Mart is a subset of the data warehouse usually oriented to a specific task.

Data that we use for a particular department or purpose is called data mart.

Reason for creating a data mart

  • Easy access of frequently used data
  • It improves end-user response time
  • It can be easily creation of data mart
  • Less cost in building a data mart.

Types of data mart

There are two types of data mart

  1. Dependent Data Mart
  2. Independent Data Mart


Dependent data mart

The dependent data mart is built by drawing data from a central data warehouse.

Independent data mart

The independent data mart is built by drawing from operational or external data sources or both.

The Independent data mart is created without the help of a data warehouse.

Virtual warehouse

A virtual data warehouse gives you a quick overview of your data. It has metadata in it. It connects to several data sources with the use of middleware. They are quick because they allow users to filter the most critical data from various older applications.

A virtual warehouse is easy to set up, but it requires more database server capacity.

 

You can also read about the Multiple Granularity Locking and Recursive Relationship in DBMS.

FAQs

  1. Why is data modeling necessary?
    Data modeling facilitates the integration of high-level business processes with data rules, data structures, and the technological execution of physical data. Data models bring your company's operations and data usage together in a way that everyone can understand.
     
  2. What is the goal of an enterprise data warehouse?
    The goal of the Enterprise data warehouse is to provide a complete overview of any particular object in the data model.
     
  3. What are the reasons for creating a data mart?
    The first reason is that we can easily access the frequently used. We don't need to look it up in the entire data warehouse.
    The second reason is that it improves end-user response time.

Key Takeaways

This blog taught us about data modeling, types, and warehouse models. The process of developing the schemas for the data warehouse's detailed and summarized information is known as data warehouse modeling.

In data modeling, the process of developing a visual representation of an entire information system or sections to express connections between data points and structures is known as data modeling. Then we learned about different types of data warehouse models.

Also read -  Aggregation in DBMS
Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Topics covered
1.
Introduction
2.
Data Modeling 
2.1.
Why is data modeling important?
2.2.
The life cycle of Data Modeling
2.3.
Types of the data model
2.4.
Conceptual Data Model
2.5.
Logical Data Model
2.6.
Physical Data Model
3.
Data Warehouse Modeling 
3.1.
How  Data warehouse helps to improve business processes
4.
Types of Data Warehouses Models
4.1.
Enterprise Warehouse
4.2.
Data Mart
4.2.1.
Reason for creating a data mart
4.2.2.
Types of data mart
4.3.
Virtual warehouse
5.
FAQs
6.
Key Takeaways