Practical Implementation

Care must be taken to ensure that elements of a key do not contain null values

Say we have the object that we have implemented as a relational database table:

Account no	Address	Startpoint	DHkey	Generation
1	1 Acacia Avenue	01/01/2000	c4ca4238a0b923820dcc509a6f75849b	1
1	101 High Street	02/02/2010	c4ca4238a0b923820dcc509a6f75849b	2
1	52 Festive Road	30/04/2011	c4ca4238a0b923820dcc509a6f75849b	3
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c	1
3	DUMMY	02/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	0

A possible presentation view could be generated

The row_number() over() windowing function results in each dhkey being given row numbers ordered by the reverse of the generation

In this case the derived object AO would look like this

Account no	Address	Startpoint	DHkey	Generation	version
1	1 Acacia Avenue	01/01/2000	c4ca4238a0b923820dcc509a6f75849b	1	3
1	101 High Street	02/02/2010	c4ca4238a0b923820dcc509a6f75849b	2	2
1	52 Festive Road	30/04/2011	c4ca4238a0b923820dcc509a6f75849b	3	1
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c	1	1
3	DUMMY	02/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	0	1

The where version = 1 clause resolves this dimensional view

Account no	Address	Startpoint	DHkey	Generation
1	52 Festive Road	30/04/2011	c4ca4238a0b923820dcc509a6f75849b	3
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c	1
3	DUMMY	02/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	0

the lead() over() windowing function results in the startpoint minus 1 day of the following instance of the dhkey (if exists) being returned as the endpoint

In this case the derived object would look like this

Account no	Address	Startpoint	DHkey	Generation	endpoint
1	1 Acacia Avenue	01/01/2000	c4ca4238a0b923820dcc509a6f75849b	1	01/02/2010
1	101 High Street	02/02/2010	c4ca4238a0b923820dcc509a6f75849b	2	29/04/2011
1	52 Festive Road	30/04/2011	c4ca4238a0b923820dcc509a6f75849b	3
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c	1
3	DUMMY	02/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	0

To add new instances to the dimensional object we would first build the has key on the source dataset

Raw dataset:

2	7 Block Lane	01/01/2000
3	10 Upping Street	01/03/2010

Add the key

Account no	Address	Startpoint	RawDataKey
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c
3	10 Upping Street	01/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3

We determine what elements we require to ‘track’ as changes – as some elements may be irrelevant attributes

In this case we just track address – getting the max generation of each key – if no generation (i.e.null) or max generation is zero the rawdata is returned with an incremented generation

Inserting the resultant dataset into the account_object table would give us

Account no	Address	Startpoint	DHkey	Generation
1	1 Acacia Avenue	01/01/2000	c4ca4238a0b923820dcc509a6f75849b	1
1	101 High Street	02/02/2010	c4ca4238a0b923820dcc509a6f75849b	2
1	52 Festive Road	30/04/2011	c4ca4238a0b923820dcc509a6f75849b	3
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c	1
3	DUMMY	02/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	0
3	10 Upping Street	01/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	1

From the raw data account 2 has not changed

Account 3 has now received a valid reference to override its dummy status

It should be noted that a Dummy instance may be updated with the attributes of the version 1 replacement

However its generation remains indicative of its origins and to ensure referential integrity

Account no	Address	Startpoint	DHkey	Generation
1	1 Acacia Avenue	01/01/2000	c4ca4238a0b923820dcc509a6f75849b	1
1	101 High Street	02/02/2010	c4ca4238a0b923820dcc509a6f75849b	2
1	52 Festive Road	30/04/2011	c4ca4238a0b923820dcc509a6f75849b	3
2	7 Block Lane	01/01/2000	c81e728d9d4c2f636f067f89cc14862c	1
3	10 Upping Street	01/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	0
3	10 Upping Street	01/03/2010	eccbc87e4b5ce2fe28308fd9f2a7baf3	1

Care must be taken – especially when dealing with a candidate object for Type 2 that generation ordering or startpoint ordering is used where appropriate.

The generation does not necessarily denote temporal validity but rather receipt order.

Contents Next