Data Warehousing Tutorial Part - 4

Logical vs. Physical

 The logical design is more conceptual and abstract than the physical design. In the logical design, you look at the logical relationships among the objects. In the physical design, you look at the most effective way of storing and retrieving the objects.

Your design should be oriented toward the needs of the end users. End users typically want to perform analysis and look at aggregated data, rather than at individual transactions. Your design is driven primarily by end-user utility, but the end users may not know what they need until they see it. A well-planned design allows for growth and changes as the needs of users change and evolve.

Type of Data Modeling?

1) Conceptual Data Model
2) Logical Data Model
3) Physical Data Model

Conceptual Data Model - Design Step 1

This is First step for DWH Designing.

Conceptual Data Model is the first step in Data Warehouse design. In conceptual data model, very high level relationships between dimension and fact table is depicted. Conceptual data model not necessarily includes keys, attributes of tables. Conceptual data model gives a very high level idea of proposed Data Warehouse design including possible fact and dimension table. Conceptual data model is the stepping stone to design logical data model of Data Warehouse.


Characteristics of Data Warehouse Conceptual Data Model

1. It shows only high level relationship between tables.
2. It does not show primary key or column names
3. It is the stepping stone of Logical Data Model

Logical Data Model - Design Step -2

This is Second step for Designing  
Good Logical data model in data warehouse implementation is very important. Logical data model has to be detailed (though some might not agree) as it represents the entire business in one shot and shows relationship between business entities. Logical Model should have following things to make it detailed and self explanatory.

1. All entities to be included in data warehouse
2. All possible attributes of each entity
3. Primary keys of each entity ( Natural Keys as well as Surrogate Keys )
4. Relationships between each every entity

Characteristics of Data Warehouse Logical Data Model

1. It has all the entities which will be used in data warehouse
2. It shows all possible attributes of all entities
3. It depicts the relationships between all entities

Physical Data Model - Design Step -3

This is third step for Designing for DWH Design

Physical Data Model is the actual model which will be created in the database to store the data. It is the most detailed data model in Data Warehouse data modeling. It includes

1. Tables names
2. All column names of the table along with data type and size
3. Primary keys, Foreign Keys of a table
4. Constraints

Physical Data Model can be converted to actual SQL DDL statement by using different tools. ERWIN is the famous tool to do this.

Continue with Data Warehousing Tutorial Part - 5 .. :)