In this blog post we will see, how to implement SCD (Slowly Changing Dimension) in Birst tool.
Introduction
Dimension/Attribute that changes over the period of time are called Slowly changing dimension(SCD). It helps users to track historical change of an item to any given date.
SCD Types
SCD is categorized into three types namely Type 1, Type 2, Type 3.
SCD Type 1
In this type the attribute value will be overwritten by the new value, it is mainly used when dealing with data corrections.
SCD Type 2
In this type Old values will not be replaced but a new record will be inserted in a table. So at any point of time, the difference between the old values and new values can be retrieved and compared easily. Effective date and current indicator is noted in this dimension.
Birst Administrator with appropriate ACL (Access Control List) permissions can use Type 2 SCD.
SCD Type 3
In this type the Old values is overwritten with the new value, also a additional column need to be added to the table to track effective date of change.
Setting Type 2 SCD in Birst
Upload the Data Source into Birst Space and enable source.
Go to Hierarchies > Define Source.
Add New hierarchy and set level key for the SCD source.
Click Advanced Option and enable Type 2 Slowly Changing Dimension and click OK
Go to Define Source > Mange source and select appropriate level and grain for the source and Process data.
Once the data is processed successfully, Go to Designer, in default subject area Birst automatically add two columns to indicate the attribute start date and current flag.
The below report shows the current location of Employee (E_ID) is 100.
To review the historical data of the employee we have a field suffixed by Is Current to the hierarchy, which shows the current and previous flag of the dimension.
