Data Modeling for Data Warehouse

Data modeling refers to the process of handling and designing the data model within a data warehouse platform. It consists of making an appropriate database schema so as to transfer the data that can be stored and of useful to user. Data warehouse modeling is used for two reasons,first is that relationship within the warehouse data can be visualised through the schema and second is that the cost is reduced and efficiency is increased with the help of well-designed schema which allow effective data warehouse structure to occur. Data modeling is different in data warehouse than in operational database systems. Thus making it efficient against complicated queries

Data Modeling Life Cycle

In this life cycle, we are going to see the step-by-step process of converting the requirements of the business to fulfil the goal of handling the data within IT system. The motive is to create a storage area for business and for that logical and physical data modeling helps. The three steps use in data modeling life cycle are as follows −

Conceptual Data Model

A conceptual data model refers to an organized view of a relationship i.e. the highest level relationship is being found between the entities. The motive is to establish entities and relationships among them.

Features of Conceptual data model −

  • Only data such as entities and their relationship are shown

  • Other data is hardly available in the actual database.

  • No attributes and primary keys are specified.

  • It is basically used for business audiences.

Logical Data Model

It shows the information in a structure without knowing the physical appearance of them. The main motive is to create a single view by using business data structure, processes and relationships.

Feature of Logical data model −

  • Presence of attributes for each entity.

  • Entities are present with the relationship.

  • Key and non-key attributes are present.

  • Attribute names are user friendly.

  • More detailed than conceptual data model.

  • Primary key and foreign key relationship are present.

  • FK Relation(Referential Integrity is specified).

Physical Data Model

It refers to the visualization of database structure i.e. how the model will look in a database after replicating table structures, columns, data types,keys, constraint and relationship between the table. Its main purpose is to show the logical data model in a physical structure of relational database system hosted by data warehouse. It also improves query performance using new data structures.

Characteristics of physical data model −

  • Entities and Attributes are referred to as tables and columns respectively.

  • Data Types, length and values are to be exact for columns.

  • Primary and Foreign keys are used to define relationships between tables.

  • Specification of all tables and columns.


This article consists of data modeling in data warehouse which is used to handle and design the data model within a data warehouse. Schema is used to visualise the relationship and increase the efficiency which leads to cost reduction. Data modeling life cycle consists of three parts. First is conceptual data model which refers to organise view of relationship. Second is Logical data model which create a single view and information of structure without its physical appearance. Third is physical data model which visualise the logical data model to a physical data model and thus improve query performance.

Updated on: 22-Aug-2023


Kickstart Your Career

Get certified by completing the course

Get Started