Dimensions are based on ‘natural keys’
A natural key is the candidate element (field) or elements (fields) that uniquely identify an instance (record) in the dimension in a temporal manner.
For all instances of a dimension there will be a start point – usually a point in time for which that instance is valid from.
A change in the attributes of an instance will require a new instance with a start point of the new instance that is a new generation of the original instance.
Only new instances are created – no updates or deletes under normal conditions
This provides the ability to perform controlled reversion. e.g.
An object containing addresses of accounts
|
Account no |
Address |
Startpoint |
Natural_key |
Generation |
|
1 |
1 Acacia Avenue |
01/01/2000 |
1 |
1 |
|
2 |
7 Block Lane |
01/01/2000 |
2 |
1 |
|
1 |
101 High Street |
02/02/2010 |
1 |
2 |
The account 1 changed address on 02/02/2010 and thus ‘generated’ a new instance with an incremented generation.
From this object it can be derived that Natural key 1 has a current generation of 2 and a historical generation of 1
It can also be derived that NK 1 generation 1 is valid from 01/01/2000 until 01/02/2010.
Implementing a view that only presents the latest generation provides a type 1 latest position.
If the latest instance turns out to be in error then it can be removed and the type 1 view will revert as a consequence.
E.g. latest view
|
Account no |
Address |
Startpoint |
Natural_key |
Generation |
|
2 |
7 Block Lane |
01/01/2000 |
2 |
1 |
|
1 |
101 High Street |
02/02/2010 |
1 |
2 |
Latest view post deletion of offending instance
|
Account no |
Address |
Startpoint |
Natural_key |
Generation |
|
1 |
1 Acacia Avenue |
01/01/2000 |
1 |
1 |
|
2 |
7 Block Lane |
01/01/2000 |
2 |
1 |
Composite natural key
A natural key may not necessarily be a single element – but made up of multiple elements
e.g. an object where the natural key is the house number and postcode combined
|
House Number |
Postcode |
Door Colour |
Startpoint |
Natural_key |
Generation |
|
1 |
OL4 5RT |
RED |
01/01/2000 |
1+OL4 5RT |
1 |
|
2 |
HX4 1QP |
BLUE |
01/01/2000 |
2+HX4 1QP |
1 |
|
1 |
OL4 5RT |
BLACK |
02/02/2010 |
1+OL4 5RT |
2 |
In this example an arbitrary attribute of door colour has been used
We can see as before the same point in time change as before
However the natural key is a combination of house number and postcode
Hashing Natural Key
A hash is a one way repeatable encoding process with a very high fidelity
Hashing the natural key is a simple way of reducing a composite natural key to a single easily manageable element.
e.g. using simple md5 hash
|
House Number |
Postcode |
Door Colour |
Startpoint |
Natural_key |
Generation |
|
1 |
OL4 5RT |
RED |
01/01/2000 |
6469c5a75309898f66eba215f45dc0f0 |
1 |
|
2 |
HX4 1QP |
BLUE |
01/01/2000 |
f4f44df34b5c3bcc165699468c5a9dbf |
1 |
|
1 |
OL4 5RT |
BLACK |
02/02/2010 |
6469c5a75309898f66eba215f45dc0f0 |
2 |
Hashing the key results in a more simple key that is easily repeatable and less prone to error with large composite keys
As an extreme example the following may be considered as a natural key:
"The quick brown fox jumps over the lazy dog"
This ‘key’ would hash using the simple md5 hash to:
9e107d9d372bb6826bd81d3542a419d6
The keys become consistent regardless of the complexity of the natural key.