EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Data Warehouse Tutorial Data Warehouse Modeling

Data Warehouse Modeling

Swati Tawde
Article bySwati Tawde
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated March 21, 2023

Data Warehouse Modeling

What is Data Model?

Data Warehouse Modeling is the first step for building a Data Warehouse system, in which the process of crafting the schemas based on the comprehensive information provided by the client/ business owners and the enhancement of the crafted schema is performed, by wrapping all the available facts about the database for the client to visualize the relationships between various components of the Data Warehouse such as the databases, tables, contents of the tables including indexes, views and to get a working product, as a well-structured system consents to form an efficient Data Warehouse that aids in lessening the overall cost of employing the Data Warehouse in the business decision-making processes

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Need for Data Warehouse Modeling

  1. Business requirements collection
  2. Improving the performance of database
  3. Provides documentation of the source and target system

1. Business Requirements Collection

A data warehouse is typically designed to determine the entities required for the data warehouse and the facts which must be recorded with the data architects and business users. This first design has a lot of iteration before the final model is decided.

We have to overcome the prevalent disadvantages in the design phase at this point. Since an existing system implements a data warehouse, architects sometimes incorporate a large proportion of the old system into the new design, saving time or disclosure.

The logical model effectively captures company needs and serves as a foundation for the physical model.

2. Improving the Performance of Database

Checking efficiency is an essential characteristic of a data store. In a data warehouse, enormous information is involved, so it is very essential to use a data model product for metadata and data management used by BI consumers.

The physical model adds indexing to optimize the efficiency of the database. The schemes are also sometimes modified. For instance, if a star schema promises quicker data recovery, it can change to a snowflake scheme

3. Provides Documentation of The Source and Target System

In developing an ETL system, the physical and logical models of the source and target systems are very important to check.

This documentation is offered by information modeling as a reference for the future.

Conceptual Data Model

The highest relationship among the distinct entities is determined by a conceptual data model.

It is the first step towards creating a top-down data model that represents the business organization’s precise presentation.

Designs the total database structure and lists the subject areas

Comprises the kinds and interactions of entities. Symbolic notations (IDEF1X or IE) represent the connection between the topic fields. In an information model, cardinality shows the one to one or many relationships

Model of Relationship Data

Modeling relative information in transaction-oriented OLTP schemes is used. A relational data model has significant features:

  • Uses of key
  • Data Redundancy
  • Relationship among data

Uses of key

The main key in a table is the key. It is used as a single identification. A non-zero column is a primary key. For the main key, the foreign key is used. They link the information from one table to another table and connect.

Data Redundancy

The relationship information model applies information integrity laws

Data redundancy is eliminated. A piece of information is not repeatedly collected. This ensures consistency of the data and restricted data storage

Relationship Among Data

All data are stored in tables and each relationship has columns and rows.

A header and a body should be on the table. The header is the table list of columns and the table consists of the rows. The tuple is the single value that is produced by a column and row intersection.

Multi-Dimensional Model

An organization that reflects the significant entities of a company and the connection between them is a logical perspective of a multidimensional data model. The databases and tables are not limited to a natural database. The E-R diagrams are not depicted.

  • Attributes
  • Facts
  • Dimension

a. Dimension

  1. An aspect is a data collection consisting of individual information components that do not overlap
  2. You can set, group and filter information for viewing and browsing purposes by end-users.

b. Facts

  1. A table of columns used to respond to company issues for numeric reasons.
  2. The measures are additive, semi-additive and non-additive

c. Attributes

  1. The abstract conditions are designed to facilitate the summary of information in a study
  2. They can also be described as column headings that are not included in a report calculation.

Advantages

Below are some of the advantages described.

  1. Standardization of dimensions makes it easy to report across business areas.
  2. Dimensional is also for storing data to make it easier to get data from the data when the data is stored in the database.
  3. The company is very understandable for the dimensional model. This model is based on company terms so that the company understands the meanings of each reality, aspect, or feature.
  4. For quick information querying, dimensional models are deformalized and optimized. Many relationship database platforms acknowledge this model and optimize query performance plans.
  5. Dimensional models can accommodate change conveniently. Tables of dimensions can include additional columns without influencing the use of these tables by current company intelligence apps.

Best Practices

Before beginning the data model, a complete analysis of client company needs should be carried out It should be extremely important to meet the customers to discuss demands and techniques of information modeling and to have the company subject specialists immediately confirm it.

The company should understand the data model, whether in a graphic/metadata format or as business rules for texts.

Recommended Articles

This is a guide to Data Warehouse Modeling. Here we discuss the Data Model, why is it needed in Data Warehousing along its advantages as well as types of models. You may also look at the following article to learn more –

  1. Data Warehouse tools
  2. Types of Data Warehouse
  3. Guide to Oracle Data Warehousing
  4. Different Methodologies of Data Warehouse Testing
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more