EDUCBA

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.

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
0 Shares
Share
Tweet
Share