Referential Integrity – Buckets and Dummies

We have seen how to disconnect dimension and fact object processing to increase performance

With this strategy the question of referential integrity arises.

Referential integrity can be impacted by a number of factors not least – late arriving reference data for dimensions – incorrect data in facts or dimensions resulting in an invalid key.

Let us consider the dimension object account address keyed on account number

Presented as

And a fact object of

When the fact is processed the resultant object of

We now have a referential integrity issue as the account 3 has no instance in the dimension object so joining the two objects would exclude account 3 from the results.

Buckets

To eliminate referential integrity issues we use buckets

Buckets are simply objects that contain the natural key elements and the hash key as generated by the fact for a specific dimension object as well as the date or timestamp of the fact instance.

In this simple case the account address bucket object would take the form of:

As each fact is processed the bucket is populated with all the keys and natural key elements

So if there are a number of facts processed that are related to this dimension then we may end up after they are all completed with an object containing elements of the following:

Dummies

Using the bucket we take a unique list of elements where the hkey does not exist in the dimension object thus accepting the earliest date or timestamp available :

We then create the missing instance into the dimension object as a dummy instance with the earliest start date (or current timestamp if not available) with a generation of 0

The Dimension object would thus be presented as

In this way we have eliminated the referential integrity issue and we can easily identify the exceptions by the generation number.

Late Arriving Reference

Having resolved the referential integrity issue we may or indeed should receive in the future the correct reference data

Instances derived from the dimension object build process always have a positive non zero generation applied to them.

So say we receive the data for account 3 to use the same example – the process would create a valid instance in the dimension object with the process timestamp as the start point

This object would then be presented out as :

Now when joining the related fact objects those elements that previously returned DUMMY address data would now return the valid address element without any reprocessing.

Contents   Next