Physical Design and Logical Design in Datawarehouse

For a Datawarehouse project ,the client will define their business requirement and functionality of their business.Once this stage is over we need to design the logical and physical part of Datawarehouse.

First lets talk about the Logical design.During the logical design phase, you defined a model for your data warehouse consisting of entities, attributes, and relationships.We deal only with defining the types of information that you need.Here we check for the logical relationship among objects.

The process of logical design involves arranging data into a series of logical relationships called entities and attributes. An entity represents a chunk of Data Warehousing Schemas information. . An attribute is a component of an entity that helps define the uniqueness of the entity. In relational databases, an attribute maps to a column.Also there is a unique identifier to define each records uniquely.

During the Physical design process, we convert the logical design into physical by using database structures. At this time, you have to map:

•  Entities to tables
•  Relationships to foreign key constraints
•  Attributes to columns
•  Primary unique identifiers to primary key constraints
•  Unique identifiers to unique key constraints

Once the physical design is made we need to create some the below structures:

• Tablespaces:It’s a collection of datafiles.Each table belongs to a tablespace.
• Tables and Partitioned Tables: Using partitioned tables instead of nonpartitioned ones addresses the key problemof supporting very large data volumes by allowing you to decompose them into smaller and more manageable pieces.
•  Views: It’s a collection of one or more tables or views
•  Integrity Constraints: Its used to enforce the business constraints such as Not null for employee id
•  Dimensions: A dimension is a schema object that defines hierarchical relationships between columns or column sets

By building a datawarehouse in this way we can make a design that allows for growth and changes as the needs ofusers change and evolve.