Configuring Type 2 SCDs in EDITable
Last updated
Last updated
You can choose to add Type 2 SCDs using EDITable for tracking changes in your database.
Click on the link as shown in the image below:
Enable support for SCDs using the toggle button as shown below and start configuring them.
The database below is used to demonstrate the implementation of Type 2 SCDs in EDITable:
To configure SCDs, your database must have the highlighted columns shown above. These fields are explained below in detail.
Let's revisit some of the key terms used while configuring SCDs.
Every row in the table would have a business key and a surrogate key.
The business key/natural key is an entity's unique identifier. In the above example, each employee can be viewed as an entity identified by a unique ID (Employee ID) known as the business key.
It must be unique to an entity, mandatory, and non-null.
It is an attribute of the entity derived from the data.
As shown in the above database sample, your database should have a numeric/text/alphanumeric column that can be used as a business key and meets all of the above requirements.
The surrogate key is system-generated and identifies each unique record in the table.
Unlike business keys, surrogate keys are not derived from data and are not attributes of entities.
A surrogate key is automatically incremented by one whenever a new record is created to track the change.
By default, EDITable selects the primary key as the surrogate key. Therefore, the database must include a numeric data type column that has a unique value for each record. This column should be assigned to the 'Primary Keys' field in the Visualizations pane so that EDITable can automatically detect it as the surrogate key.
It’s important to note that while an entity's business key can be identical or non-unique across rows in the table, the surrogate key must be unique for each row.
This date indicates the day when the updated information became active.
This date represents the last day until that row's information is valid.
This is an additional flag column to signify the record that is currently active. This column uses a simple indicator (0/1 or true/false) to denote whether a specific record is presently active or a valid version.
For setting up an active flag column, the database should contain a VARCHAR/text type column with the default value 'true' set for all rows. You can then configure this column in the 'Manage Columns' window with the checkbox type.
EDITABLE enables you to add a timestamp column in addition to the start date column. You can achieve this by creating a date column in your database and then selecting it from the 'Future Dates' dropdown menu. This retains the initial row for each entity in the change history while adding subsequent rows that reflect the effective start and end dates of the changes.
Best practices while configuring the 'Manage Columns' window in the EDITable visual:
Check the option : 'Required' so that these fields are always available.
Disable 'Allow Edits' for the surrogate key (primary key) to avoid accidentally editing it.
Ensure to choose appropriate 'Input Type' for the columns (Date type for Start Date, Effective Date and End Date, Check box type for Active Flag column, etc.)
Assign the surrogate key column as the primary key in the Visualizations pane. Assign the business key in the 'Columns' field well with other fields.
Now that we have created a database with all of the necessary fields and how to use them, we will begin adding them to the pop-up window, as shown below:
Click on Save to save the choices. Close Settings to return to the table.
Select a Record: Choose the record you are interested in. To view the history of that Row
View History: Once you select the record, you will be able to view its history. This history includes all the changes that have been made to the record over time.
Most Recent First: The changes are displayed in order, with the most recent changes at the top. This way, you can quickly see the latest updates first.
At a time, you will only be able to view the history of a single row, not more than two rows can be displayed simultaneously.
The history of the particular record.