Configuring Type 2 SCDs in EDITable
Last updated
Last updated
Type 2 SCDs can be optionally added using EDITable to track changes in your database. Before enabling Type 2 SCDs, please note that they can be configured for specific databases that have the necessary fields. In this section, we will go over how to configure them from start to finish.
To set up SCDs, the database must contain a few specific column types so that it enables and supports them. After including them, we can configure the columns and the appropriate keys in the EDITable visual. In this section, we will look at all of them in detail.
We will use the below database to demonstrate the implementation of Type 2 SCDs in EDITable:
The ID_New column is assigned in the Primary Keys field, while other columns are assigned to the Columns field, in the Visualizations pane.
To configure SCDs, your database must contain the highlighted columns as shown below. The Future/Effective date column is optional.
These 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 a system-generated key 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 or when changes were made to the database.
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.
The database should contain a VARCHAR/text type column for setting up an active flag 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 lets you add an effective date/timestamp column alongside the start date column to record when the change actually took effect. To do this, create another date column in your database and then choose it from the 'Future Dates' dropdown menu.
The Start Date indicates when the record was added/updated and became valid in the system (operational perspective).
The Effective Date represents the actual date when the change took effect in the real world (business perspective).
These two dates are usually the same in most cases, but they can differ due to internal policies, business rules, delays in data entry, processing, etc.
Adding the Effective Date allows you to query historical data based on when the change was valid in the business context, not the system context. This enables accurate tracking that can be helpful for reporting, compliance audits, and ensuring downstream systems use the correct business timeline.
Before enabling SCDs, we have to ensure the columns are assigned the appropriate properties to support Type 2 SCDs. This is done in step 2, in the 'Manage Columns' window, after loading your table and configuring the source.
Best practices for configuring the columns in the EDITABLE visual:
Ensure that the surrogate key is assigned as the primary key in the Visualizations pane.
Enable the 'Required' option for the above fields so that these are always available.
Disable 'Allow Edits' for the surrogate key to avoid accidentally editing it. This ensures that each surrogate key is distinct and helps to identify the row.
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.).
After configuring the columns, click Save to save the configuration.
You can always return to this window and reconfigure the columns if necessary by selecting the Manage Columns option in the Home tab.
The next step is to enable Type 2 SCDs.
In the next step, you can enable Type 2 SCDs.
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.
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.
The table now looks as shown below with all active records.
Let's assume a few records in the table have been edited. To view the change history of the record, you need to follow the steps below:
Select the record: Choose the record you are interested in.
View History: Once you select the record, click on History 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. The history of the particular record is shown below:
At a time, you will be able to view the history of a single row.
This way, you can set up Type 2 SCDs for your database.
Note:
The action of configuring SCDs is permanent and cannot be undone once enabled and saved.
If you have missed configuring SCDs and wish to set it on a later date or you need to edit the existing configuration, you will need to reset all the visual settings by clicking on Reset in the Settings window. Note that this resets all the settings, including the source and column configurations.
To reset the SCD configurations, or to enable SCDs at a later date, you need to click on Reset.
To view the existing SCD configuration, you can go to Settings -> Slowly Changing Dimensions.
The following features are not supported if you have enabled Type 2 SCDs for your table:
Soft delete
Audit log
Persistence mode in approval workflows
