Dimension table is a table which contains only non-quantifying data and category of information which are key viewpoints for analysis.
The data might be stored in a hierarchical way where data analysis can be done from different levels of hierarchy. Hierarchy is a level of granularity which would enable us to perform drill down at a different granular level.
For example, a location dimension has data of country -> state -> city, you can generate the report at a country level then, you can drill down into state level from here you can drill down into city level.
Dimension table structure:
A dimension table contains primary key and non-quantifying columns. If the primary key does not exist in source table then surrogate key would exist.
Example of location dimension for hierarchy:
A report can show the data at a country level, state level and city level.
Dimension table types:
- Based on what type of data it stores there is two major types dimension table,
- Based on the origin of forming the dimension there is one dimension category,
- Based on how frequently the data in the dimension can be divided into 2 types