Using ‘Version Number’ in Astera DW Builder

While configuring dimension roles in dimension properties, I came across ‘Version Number’. Can someone explain how it works?

In Astera DW Builder, there are various types of Record identifiers when using slowly changing dimensions. The most common ones are Effective/Expiry Dates or Current_Record which you can always find in Kimball methodology. ‘Version Number’ is also one of them.

The way a record identifier works is that whenever a new row is inserted due to historical changes, its Version No is 1+ of the Version No of the previous row (Version No. of the previous row +1). In other words, Version No will always be the maximum number for the current record. This can be better explained through an example.

The example below is fictitious as CityName doesn’t usually change. Assume that it does for the time being. Suppose we are using SCD2 for the CityName. Every time a change in the name is made, a new record is inserted with the ‘Version No’ value one greater than the previous value. Look at the last row with the maximum value of 5, showing the most recent record.

Here ‘CitiesKey’ is the surrogate key and ‘CityID’ is the Business Key.