As dimensions are based on ‘natural keys’ – Facts are required to have the natural keys of any relative dimension.
Facts and Dimensions should wherever possible be processed independently – that is without reference to one another
This allows for significant performance improvements over other modelling strategy.
A fact object containing account facts or KPIs may look something like this
|
Account no |
invoice |
Tax point |
Value |
Value to date |
|
1 |
1 |
01/02/2000 |
100 |
100 |
|
2 |
1 |
01/02/2000 |
20 |
20 |
|
1 |
2 |
02/03/2010 |
30 |
130 |
This example is simple in as much as the natural key of account address dimension is the account number
So no actually processing is necessary other than simply hashing (if required) the account no if TYPE 1 is to be considered.
This is the same for all dimensional keys relative to the fact object.
|
Account no |
AddressHkey |
invoice |
Tax point |
Value |
Value to date |
|
1 |
c4ca4238a0b923820dcc509a6f75849b |
1 |
01/02/2000 |
100 |
100 |
|
2 |
c81e728d9d4c2f636f067f89cc14862c |
1 |
01/02/2000 |
20 |
20 |
|
1 |
c4ca4238a0b923820dcc509a6f75849b |
2 |
02/03/2010 |
30 |
130 |
c4ca4238a0b923820dcc509a6f75849b and c81e728d9d4c2f636f067f89cc14862c being the Md5 hashes of account no 1 and 2 respectively .
So the resultant facts table may look something like this
|
Dim0Hkey |
Dim1Hkey |
Tax point |
Value |
Value to date |
|
6469c5a75309898f66eba215f45dc0f0 |
c4ca4238a0b923820dcc509a6f75849b |
01/02/2000 |
100 |
100 |
|
f4f44df34b5c3bcc165699468c5a9dbf |
c81e728d9d4c2f636f067f89cc14862c |
01/02/2000 |
20 |
20 |
|
6469c5a75309898f66eba215f45dc0f0 |
c4ca4238a0b923820dcc509a6f75849b |
02/03/2010 |
30 |
130 |
All keys are derived from the facts themselves without reference to dimensions.