This tutorial teaches you how to use the "History Preserving Transform" in SAP Data Services (BODS) by demonstrating a practical use of this transform for the implementation of SCD Type 2. We have also provided hands-on video below so that you can see how to do it yourself.

This article is part of our comprehensive data services tutorial "Learning SAP Data Services - Online Tutorial", you may want to check that tutorial first if you have not already done so.

SCD Type 2

SCD stands for slowly changing dimension. An SCD Type 2 mapping tries to capture the changes of a dimension table and store the history of changes in different rows in the target table. We also mark each row with a flag with value 'Y' or 'N' signifying if the row is latest or old.

When loading the target table, we extract only changed record sets from the source so that we do not need to process extra records unnecessarily. In order to extract only the changed records (also known as incremental loading), we use a Query Transform in the data flow.

Table Comparison Transform

In the previous tutorial we have already learnt how to use a table comparison transform. We use the same table comparison transform here so that we can specify the compare columns against the primary key columns which is used for joining.

Table comparison transform basically gives us the Op-code or operational code which helps us identify if the row is already present in the target or not.

History Preserving Transform

In order to correctly implement an SCD Type 2 dimensional table, we need to populate columns like - active flag, valid from, valid to etc. While active flag only tells us if the record is the current record or not, valid from and valid to columns tell us the date ranges within which the record was valid.

History preserving transform lets us specify the valid from and valid to date columns as well as the active/current flag columns.

Video: History Preservation Transform in BODS

Hands-on tutorial video that shows you how to use History Preservation Transform in SAP data services (BODS)


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Exception Handling While Reading Multiple XML Files in Data Services

    This article will demonstrate loading multiple XML files using SAP Data Services including Exception Handling.

  • How to use SAP Data Services Case Transform

    In this tutorial, we will learn how to use SAP data services Case Transform. Case transform provide us a means to implement "If... Then... Else" logic in data services. In the end a short video is provided with this tutorial to give you a hands-on...

  • How to use Data Services Reverse Pivot Transformation

    In this article, we will learn how to use SAP Data Services Reverse Pivot Transform. The Reverse Pivot transformation combines data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each...

  • How to use Data Services Pivot Transformation

    In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the...

  • How to implement SCD Type 3 in Data Services

    In this tutorial we will learn how to implement Slowly Changing Dimension of Type 3 using SAP Data Services. SCD type 3 design is used to store partial history. Here we are only interested to maintain the "current value" and "previous value" of an...

  • Fools Guide to BODS - Designer

    In our earlier article on Data Services we have learnt how to register the local repository with CMC. In this part of the article we will start using Data Services Designer.

  • Data Services Scenario Questions Part 2

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • How to install SAP BODS - Standalone

    This article is a step by step guide on how to install standalone SAP BODS Batch Job Server, Client Components, Web based Administrative Console and how to configure the same.

  • Auditing in SAP Data Services

    A proper data reconciliation process must be in place in any data Extraction-Transformation-Load (ETL) process. A successful reconciliation process should only indicate whether or not the data is correct. But data reconciliation is not easy....

  • SAP BODS Cluster Installation

    This article is a step by step guide on how to configure SAP BODS for High Availability using Windows Cluster services. To take advantage of fail-over support for SAP BusinessObjects Data Services services in a Windows Clustering Environment,...