Type 2 Dimensions

Type 2 dimensions are where the elements are intended to have a lifespan rather than be a single element for each natural key

This requires more than just the natural key to be applied to exist in the relationship between dimension objects and fact objects.

This does add an overhead to the fact processing as it cannot readily be done without reference to the dimension object.

e.g.

An object containing addresses of accounts

We can infer from this object that the valid addresses are:

Account 1: 1 Acacia Avenue from 01/01/2000 until 01/02/2010 and 101 High Street from 02/02/2010 onwards

Account 2: 7 Block Lane from 01/01/2000 onwards

Account 3: DUMMY from 02/03/2010 until 02/04/2011 and 12 Acme Road from 03/04/2011 onwards

That is the generation is valid until the startpoint of the next generation.

(in this case the start point is the start of the date (00:00:00) and the end point is the end of the previous date (23:59:59) to the startpoint of the following generation)

This example is limited to date however a lower granularity of time would be the norm.

Current Time Event

The current time event is where an instance of a fact object is considered to have occurred at current time

That the dimensional reference is the latest at process time

The instance has no element that denotes the actual event time that is to be considered.

Consider the fact object

And the presented Dimension Object (current)

In the same way as type 1 previously we hash the key elements of the fact instances

However we require more than just the hash key to identify the dimension instance when considering type 2 reference

In this case we MUST reference the dimension object

And look up the generation and apply that to the facts also – if we fail to find a generation as in the case of poor referential data then we apply a generation of zero – in the knowledge that this instance will be generated by the bucket process.

So we get as a result the fact instances of:

Subsequently the resultant fact object would relate to the base dimension (i.e. not the presented latest) by Dimensional key and generation with respect to type 2.

This same fact object is related to the presented latest dimension object by Dimensional key only with respect to type 1.

This way we can view the data as both type 1 and type 2 without any reprocessing.

This also allows for What if? Scenarios to be applied to the data without reprocessing.

Point in Time Event

A point in time event is where a fact instance has a specified point in time when we are to consider it occurred.

This is somewhat more difficult and requires more processing

Considering the same dimensional object we need to build an object where we can relate the event time to a point in time on the dimensional history.

A temporal dimension object such as the following:

And the fact object:

Where there is no end point that can be considered to be forever (or a significant time in the future)

Again we hash the key and relate that hashed key to the temporal dimension by dimensional key and event point in time (tax point) being between startpoint and endpoint

Giving us

Again we can relate this fact object to the base dimension as type 2 or the presented latest dimension as type 1.

The temporal dimension can be discarded post processing.

Hash Key and Generation Together

Additionally it may be of some use to hash the key again together with the generation

This has the advantage of a single relationship between fact object and dimension object.

For instance:

Dimensional object

Fact Object

This again adds some complexity – but the advantage is a single element relationship between the fact object and dimensional object for both type 1 and type 2 considerations.

Contents   Next