Designing the data warehouse / data marts

1
Designing the data warehouse/ data marts

• Part 2

2
Creating the Physical Model
Phase 4 Creating the Physical Model

• Translate the dimensional design to a physical
  model
• for implementation
• Define storage strategy for tables and indexes
• Perform database sizing
• Define initial indexing strategy
• Define partitioning strategy

3
Physical Model Design Tasks

• Define naming and database standards
• Perform database sizing
• Design tablespaces
• Develop initial indexing strategy
• Develop data partition strategy
• Define storage parameters

4
Data Staging Area

• The construction site for the warehouse
• Required by most implementations
• Composed of ODS, flat files, or relational server
  tables
• Frequently configured as multitier staging

Extract
Transport(Load)
Transform
5
Remote Staging Model
Data staging area within the warehouse environment
Warehouse environment
Oper. envt.
Extract,transform,transport
Transport(Load)
Transform
Data staging area in its own environment,
avoiding negative impact on the warehouse
environment
Staging envt.
Warehouse envt.
Oper. envt.
Transport(Load)
Extract,transform,transport
Transform
6
Onsite Staging Model

• Data staging area within the operational
  environment,
• possibly affecting the operational system

WH envt.
Operational environment
Warehouse
Extract
Transport(Load)
Transform
7
Extracting Data
Data mapping
Transform
Operational databases
Warehouse database
Data stagingarea

• Routines developed to select fields from source
• Various data formats
• Rules, audit trails, error correction facilities