EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Data Warehouse Modeling

By Swati TawdeSwati Tawde

Home » Data Science » Data Science Tutorials » Data Warehouse Tutorial » Data Warehouse Modeling

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

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
All in One Data Science Bundle (360+ Courses, 50+ projects)360+ Online Courses | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (3,220 ratings)
Course Price

View Course

Related Courses
Business Intelligence Training (12 Courses, 6+ Projects)Data Visualization Training (15 Courses, 5+ Projects)

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 Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Data Warehouse Tutorial
  • Basic
    • What is Data Warehouse
    • Data Warehouse tools
    • Career in Data Warehousing
    • Benefits of Data Warehouse
    • Data Warehouse Architecture
    • Data Warehouse Design
    • Data Warehouse Implementation
    • Data Warehouse Features
    • Data Warehouse Modeling
    • Data Warehouse Software
    • Types of Data Warehouse
    • 10 Popular Data Warehouse Tools
    • Data Lake Architecture
    • Three Tier Data Warehouse Architecture
    • Data Warehouse Process
    • Database Parallelism
    • What is OLTP
    • What is OLAP
    • OLAP Tools
    • Types of OLAP
    • Operations in OLAP
    • MOLAP
    • HOLAP
    • Data Warehouse Schema
    • Data Warehouse Components
    • Snowflake Schema
    • Snowflake Architecture
    • What is Star Schema
    • Galaxy Schema
    • What is Fact Table
    • Kimball Methodology
    • Data Warehouse Testing
    • Operational Data Stores
  • ETL
    • What is Data Mart
    • What is Data Cube
    • What is a Data Lake
    • What is Data Integration
    • What is ETL
    • What is ETL Testing
    • ETL Testing Tools
    • ETL architecture
    • Dimension Table
    • Multidimensional Data Model
    • Fact Constellation Schema
    • ETL Process
  • Interview Questions
    • Data Warehouse Interview Questions
    • ETL Interview Questions
    • ETL Testing Interview Questions
    • Data Warehousing Interview Questions

Related Courses

Business Intelligence Course

All in One Data Science Course

Data Visualization Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA Login

Forgot Password?

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

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

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

🇮🇳 Independence Day Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More