Rules of Creating Dimension Tables and Fact Tables
Why we need both of them in Data Warehouse?
Because Data Warehouse is used to make reports for business decisions.
Every report is made of two parts: Fact and Dimension.
Here is a picture of fact tables and dimension tables in a star schema in data warehouse.
So, this blog we will talk about what are the rules of creating dimension tables and fact tables.
First of all, we need to illustrate a definition.
What is surrogate key?
It is the primary key in demension tables.
Rules of creating dimension tables:
- Primary key (surrogate key, auto-increase number, only unique number in data warehouse)
- Business key (the key can be linked back to data source, with business meaning)
- Attributes (descriptive information from data source)
There are two kinds of data: Master data and transactional data.
Master data refers to the entity (e.g. employee) whereas transactional data refers to all the transactions that are carried out using that entity.
Master data is limited whereas transactional data can be billions.
In dimension tables, most data is master data.
Rules of creating fact tables:
- Primary key (surrogate key/alternate key, auto-increase number)
- Foreign key (primary key/surrogate key/alternate key from dimension tables)
- Measure (addictive number/semi-addictive number)
Tips: No descriptive data in Fact tables.
If you are interested in or have any problems with fact tables and dimension tables, feel free to contact me .
Or you can connect with me through my LinkedIn.
Originally published at http://jacquiwu.com on August 30, 2019.