Maintaining the master data change over time to ease the reporting for end-user is an unavoidable step in a data model. To handle this, we need to maintain a dimension as a slowly changing dimension which stores current data and the history data with a reference date.

There are three ways to maintain the slowly changing dimension which is listed below:

Type 1: Overwriting the existing data

Type 2: Creating an additional record with the current value

Type 3: Creating an additional field that stores the value in the target as previous value and the current value is updated in the original field with the effective date of the value.

In the above list type, 2 is the most used method to handle the history values as it stores each new value as an additional record with start date and end date value for that record. Let’s see about implementing type 2 SCD using Alteryx with a simple example.

To find the changes in the fields we can use the join tool and match the corresponding fields. For example, in the above tables Employee with Employee and Designation with Designation. We will get different outputs from all the three anchors from the join tool.

L- Anchor

It gives the records from the target table that has new values in the source, in which we need to update the end date as previous day date.

R-Anchor

It gives the records with new values from the source table, in which we need to add the start date as the current date and last date as 31-12-9999.

J-Anchor

It gives the records from the target that has no changes.

After separating records in the table, we can use the formula tools at the L anchor to update the end date and R anchor to add the start date and the end date.

We can update the end date in source records using below formula:IF [End Date] == ToDate(2958465) THEN DateTimeAdd(DateTimeToday(),-1,”days”) ELSE [End Date] ENDIF

To add the start date and end date in the records from the target, you can use the below formulas to create new fields in the R anchor output.

Start Date – DateTimeToday() i.e., today date

End Date – ToDate(2958465) i.e., numeric constant to date conversion

*Note: “2958465” is the numeric constant for the date 31-12-9999

To combine all the records, we can use the union tool and create a single table with start date and end date for the data. In the union tool, we can use the Auto config by name option to unite the tables.

Workflow

Output

As you can see in the output that the table that how easy to achieve type 2 SCD using Alteryx. With this method, we can easily store all the historical data of the changes in the master data.

Read more about similar Self Service BI topics here and read more blogs from Alteryx category here.