Slowly Changing Dimension - Type 1

[former_member190674]

Slowly Changing Dimension – Type 1 is useful when you need not to maintain the historical data. Below are the two cases explained for implementation of SCD -Type 1 using ‘Auto Correct Load’ Option available in the target table.

Case 1: New record from source

The new records from source will be passed/written to the target table as ‘Insert’ records.

Case 2: Existing record with update in non-key columns

The update value from the source will overwrite the existing values in the target table. These records will be passed as ‘update’ records.

How to implement?

1. Map your source to the target table. If needed any transformations use a query/any other you like.
2. Map your output schema of the last transform to the target table.
3. Open the target table and navigate to the ‘Options’ tab.
4. Under ‘Advanced’ section, in ‘Update Control’ set ‘Auto Correct Load’ option to ‘Yes’. This should set the option ‘Allow merge or upsert’ option to ‘Yes’ by default.
5. Save your mapping. Run the job.

Screenshots of mapping:

Data in the table before implementation of SCD - Type 1:

Mapping of the Dataflow:

Query Transform Mapping:

Target Table Options:

Data in the after before implementation of SCD1:

Note:

Company Name changed from Tgw to Microsystems Inc for Customer ID 12345

Phone Number changed from 1-837-853-9045 to 1-837-853-9055 for Customer ID 12345

New entry for CustomerId 12370

How it Works?

  Dataflow generates a merge statement when ‘Auto Correct Load’ option is set to yes. If you set the second option ‘Allow merge or upsert’ to No manually, the dataflow generates a transact sql code.

Thanks

Santhosh