Data Warehousing Tutorial Part - 5

Moving from Logical to Physical Design

In a sense, logical design is what you draw with a pencil before building your warehouse and physical design is when you create the database with SQL statements.

Logical models use fully normalized entities. 

The entities are linked together using relationships.

 Attributes are used to describe the entities. 

The UID distinguishes between one instance of an entity and another.

A graphical way of looking at the differences between logical and physical designs 

Physical Design

Physical design is where you translate the expected schemas into actual database structures. At this time, you have to map:

• Entities to Tables
• Relationships to Foreign Keys
• Attributes to Columns
• Primary Unique Identifiers to the Primary Key
• Unique Identifiers to Unique Keys

You will have to decide whether to use a one-to-one mapping as well.

Physical Design Structures

Translating your schemas into actual database structures requires creating the following:

• Tablespaces
• Partitions
• Indexes
• Constraints

Tablespaces

Tablespaces need to be separated by differences. For example, tables should be separated from their indexes and small tables should be separated from large tables. 

Partitions

Partitioning large tables improves performance because each partitioned piece is more manageable. Typically, you partition based on transaction dates in a data warehouse. For example, each month. This month's worth of data can be assigned its own partition.

Indexes

Data warehouses' indexes resemble OLTP indexes. An important point is that bitmap indexes are quite common. 

Constraints

Constraints are somewhat different in data warehouses than in OLTP environments because data integrity is reasonably ensured due to the limited sources of data and because you can check the data integrity of large files for batch loads. Not null constraints are particularly common in data warehouses. 

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