Modelling dimensions to facts can be done in a number of ways one is to use surrogate keys.
This involves assigning a unique key to each instance in a dimension usually an incremental number.
The fact object is then required to look up the surrogate key using the key elements of the dimension object.
This must be done for all dimensional objects that are relative to the fact object.
So for instance if you have a fact that is related to 10 dimensional objects then there are 10 lookups to perform.
These lookups must take account of referential failure – it is common to left join a fact object to a dimensional object by means of the relative elements to identify surrogate keys – assigning a -1 as a surrogate key in the case of referential failure.
This has a number of downsides not least:
The process can be slow – very slow
Any referential failure is final i.e. the -1 reference is on the fact object for eternity without reprocessing.
Any loss of dimensional data is fatal as the surrogate keys cannot be recreated.
So we need a reliable performant and safe way to model dimensions and facts: