Data Warehouse (part 4): Dimensionality Design

To start building a data warehouse, we need to first understand thoroughly business purposes, then the according data model should be in use.

We would focus on dimensional data model here in this part

Recapping some key points:

• RDBMS use logical relationships to relate data across tables. Official relationships handled through keys:

→→primary vs. foreign keys

→→natural vs. surrogate keys

A natural key is one or more existing data attributes that are unique to the business concept. It might be cryptic or understandable. They travel from source systems with the rest of the data.

A surrogate key is a system generated (could be GUID, sequence, unique identifier, etc.) value with NO business meaning that is used to uniquely identify a record in a table. The key itself could be made up of one or multiple columns (i.e. Composite Key). It is generated by the database itself or a supplemental “key management” system (= Data warehouse primary key).