Dimensional modelling is the modelling process behind informational databases, similar to how the relational data model is the process behind relational databases. It divides a business query into facts (metrics) and dimensions (how deep does the metric go).
Dimensional modelling is used only for read-only databases, and consists of star schemas. Hence it is sometimes known as star schema design. A dimensional model consists of:
- A Fact Table
- Dimensional Table(s)
- (Optional) Hierarchies in said dimensional tables
Fact Table
A fact table contains the actual business measures (the metrics we are interested in), called facts. The fact table also contains foreign keys pointing to dimensions.
It is technically a relation, since it connects to other dimensions:
%%🖋 Edit in Excalidraw, and the dark exported image%%
Example: Fact Table
Construct a fact table for the business query:
How much revenue did the product G generate in the last three months, broken down by month for the south eastern sales region, by individual stores, broken down by promotions, compared to estimates and to the previous version of the product
The fact is revenue, and we want to include links to the dimensions: time, region, store and promotions as well as product: #TODO
The level of detail of a fact table is determined by the level of detail of all the dimensional tables linked by foreign keys. For example, if a business query asks for sales over the last month, if the time dimension only has the level of detail of days, then it is impossible for a fact table to provide sales over the last month, in hours. We can only go as deep as the ‘highest’ level of detail in any dimensional table
Dimensional Table
A dimensional table is simply all the data related to a specific dimension, that is, a sorting value in the business query. The primary key of a dimensional table must appear as a foreign key in the fact table for it to be utilised.
%%🖋 Edit in Excalidraw, and the dark exported image%%
Dimensional Hierarchy
Dimensional hierarchies add extra level of detail, allowing more detailed business queries to be constructed. The more attributes that categorise dimension exist, the larger the dimensional hierarchy.
The highest level of detail is given by the attribute at the bottom of the dimensional hierarchy. In the Time dimensional table above, we have a dimensional hierarchy given by: Year > Month > Day. Day is the lowest in the hierarchy, and thus is the highest level of detail.