Tracking changes in dimension is referred as slowly changing dimensions.
It contains data history
In the source system a lot of changes are daily made :
These sorts of changes need to be reflected in the dimension tables and in several cases, the history of the changes also needs to be tracked.
By remembering history, we are then able to look at historical data and compare it to their current situation.
A change does not require tracking
With Type II SCD, a new version of the dimension record is created, and the existing version is marked as history.
Each row does not correspond to a different instance of an entity but a different “state”, a “snapshot” of the instance at a point in time.
To accommodate this, extra metadata is required for the dimension table, including an effective date column and an expiration date column. These columns are used to differentiate a current version from a historical version as follows:
The user must identify the columns whose history will be tracked (by creating a new version) whenever their values are changed. These columns are known as trigger columns.
Facts or type 2 dimensions may contain effective and expiration dates. It is common to leave them as dates/timestamps for the reason their ONLY use is to filter queries in a temporal context.
With Type III SCD, a current value field is created to keep the current value of dimension record apart from its previous value.
To accomplish this, two columns are created for each data field:
SCD2 permits a complete As-Was (past) and As-Is (current) rapport.
Tactical | As-Is |
Strategical | As Was |
The version of ISO (and ANSI) standard SQL (ISO 9075 SQL:2011) has a particular interest on the fact that this version of standard SQL includes support for:
which together provide support for bitemporal data.
And these “temporal extensions” can be used to design an SCD type 2.