RathiRam

 Profile Answers by RathiRam 

• Aug 24th, 2011

 

A logical design is conceptual and abstract. You do not deal with the physical implementation details yet. You deal only with defining the types of information that you need.
Your logical design should result in (1) a set of entities and attributes corresponding to fact tables and dimension tables and (2) a model of operational data from your source into subject-oriented information in your target data warehouse schema.

You can create the logical design using a pen and paper, or you can use a design tool such as Oracle Warehouse Builder (specifically designed to support modeling the ETL process) or Oracle Designer (a general purpose modeling tool) or Visio.

During the physical design process, you convert the data gathered during the logical design phase into a description of the physical database structure. Physical design decisions are mainly driven by query performance and database maintenance aspects.
Following points are necessary during the physical design


1. Entities to tables
2. Create the Fact table and Dimension table in the database(Oracle or SQL Server …)
3. Relationships to foreign key constraints
4. Attributes to columns
5. Primary unique identifiers to primary key constraints
6. Unique identifiers to unique key constraints
7. Create the index
8. Create the table space
9. Create the materialized views if it required
10. Take care of partition of the database