Understanding Slowly Changing Dimensions (SCD) in SAP BODS

Slowly Changing Dimensions (SCD) are essential for managing and tracking changes in dimension tables within a data warehouse. SAP BusinessObjects Data Services (BODS) provides robust tools to handle SCDs efficiently. This blog will focus on implementing SCD Types 1, 2, and 3 in SAP BODS, detailing the specific transforms used for each type.

SCD Type 1: Overwriting Data

SCD Type 1 involves overwriting old data with new data, without keeping any historical records. This method is used when changes are corrections rather than true changes.

Source Table: source_customer

CustomerID	CustomerName	Address
1	John Doe	Address1
2	Jane Smith	Address2

Target Table: target_customer

CustomerID	CustomerName	Address
1	John Doe	OldAddress
2	Jane Smith	OldAddress

Source: Test_Effective_date
Query Transform (QR_MAP): Map the source data to query transform without any transformation.
Table Comparison Transform (TBL_CPM): Compare the source data and the target table data.
Map Operation Transform (MP_OPR): Insert new data and update old data.
Key Generation Transform (KEY_GEN) (optional): Generate surrogate keys (if needed).
Target Table: Target_Customer

SCD Type 2: Creating New Records

SCD Type 2 involves creating a new record for each change, preserving historical data. This approach is useful for tracking changes over time.

Source Table: source_customer

CustomerID	CustomerName	Address
1	John Doe	NewAddress1
2	Jane Smith	NewAddress2

Target Table: target_customer

CustomerID	CustomerName	Address	Start_Date	End_Date	Is_Current
1	John Doe	OldAddress	2020-01-01	9999-12-31	Y
2	Jane Smith	OldAddress	2020-01-01	9999-12-31	Y

Source: Source data table (source_customer).
QR_MAP: Query Transform to map the source data.
TBL_CMP: Table Comparison Transform to compare source and target data.
HIS_PRES: History Preserving Transform to handle historical data and new updates.
KEY_GEN: Key Generation Transform to generate surrogate keys.
TARGET: Target table (target_customer) to load the transformed data.

SCD Type 3: Adding New Columns

SCD Type 3 involves adding new columns to track the previous values of certain attributes. This method is used when changes are infrequent, and only the current and previous values are needed.

Source Table: source_customer

CustomerID	CustomerName	Address
1	John Doe	NewAddress1
2	Jane Smith	NewAddress2

Target Table: target_customer

CustomerID	CustomerName	Address	Previous_Address
1	John Doe	OldAddress	AddressBeforeOldAddress
2	Jane Smith	OldAddress	AddressBeforeOldAddress

Source Table (source_customer)
QR_JOIN (Join Source with Target on CustomerID)
QR_INSERT (Filter new data where SCD3.CustomerID is null)
QR_UPDATE (Filter updated data where SCD3.CustomerID is not null and source.Address ≠ SCD3.Address)
MP_UPDATE (Update target table by setting map operation to 'normal to update')
KEY_GEN (Generate surrogate key for new rows)
Target Table (target_customer)

Summary

SCD Type 1: Overwrites existing data, keeping only the latest information. Transforms used: Query Transform, Table Comparison Transform, Map Operation Transform.
SCD Type 2: Creates new records for changes, preserving historical data. Transforms used: Query Transform, Table Comparison Transform, Map Operation Transform, History Preserving Transform.
SCD Type 3: Adds new columns to track changes, storing both current and previous values. Transforms used: Query Transform, Table Comparison Transform, Map Operation Transform.

Conclusion

Implementing SCDs in SAP BODS requires a clear understanding of the business requirements and the type of historical tracking needed. By utilizing the appropriate transforms, such as Query Transform, Table Comparison Transform, Map Operation Transform, and History Preserving Transform, one can efficiently manage and track changes in dimension tables, ensuring data integrity and accuracy.

Proper handling of SCDs enhances data warehousing strategies, enabling accurate historical data analysis and better decision-making.