What are the types of Approach in DWH?

Bottom up approach: first we need to develop data mart then we integrate these data marts into EDW

Top down approach: first we need to develop EDW then form that EDW we develop data mart

Bottom up

OLTP             ETL                Data mart                   DWH        OLAP

Top down

OLTP             ETL                DWH                Data mart             OLAP

Top down

*   Cost of initial planning & design is high

*   Takes longer duration of more than a year

Bottom up

*   Planning & Designing the Data Marts without waiting for the Global warehouse design

*   Immediate results from the data marts

*   Tends to take less time to implement

*   Errors in critical modules are detected earlier.

*   Benefits are realized in the early phases.

*   It is a Best Approach

Data Modeling Types:

*   Conceptual Data Modeling

*   Logical Data Modeling

*   Physical Data Modeling

*   Dimensional Data Modeling

1. Conceptual Data Modeling

·         Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the INITIAL PLANNING PHASE

·         Conceptual data model is created by gathering business requirements from various sources like business documents, discussion with functional teams, business analysts, smart management experts and end users who do the reporting on the database. Data modelers create conceptual data model and forward that model to functional team for their review.

·         Conceptual data modeling gives an idea to the functional and technical team about how business requirements would be projected in the logical data model.

2. Logical Data Modeling

·         This is the actual implementation and extension of a conceptual data model. Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.

3. Physical Data Modeling

·         Physical data model includes all required tables, columns, relationships, database 

properties for the physical implementation of databases. Database performance, indexing 

strategy, physical storage and demoralization are important parameters of a physical model.

Logical vs. Physical Data Modeling

Logical Data Model	Physical Data Model
Represents business information and defines business rules	Represents the physical implementation of the model in a database.
Entity	Table
Attribute	Column
Primary Key	Primary Key Constraint
Alternate Key	Unique Constraint or Unique Index
Inversion Key Entry	Non Unique Index
Rule	Check Constraint, Default Value
Relationship	Foreign Key
Definition	Comment

Dimensional Data Modeling

·         Dimension model consists of fact and dimension tables

·         It is an approach to develop the schema DB designs