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 attribute. That is, even though the value of that attribute may change numerous times, at any time we are only concerned about its current and previous values.

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.

Let us consider we have customer source data with Address details. We are interested to capture only the current and previous address information for each customer. Find below the implementation Data Flow.

Implementation

Data Flow SCD3

Lets assume we are using an ETL batch load control table to log the last extraction date for customer records from source. Next we assign the last extraction date to a global variable to capture the Changed Data from the source.

CDC Extraction Query

Next we lookup the target dimension table using the source customer key and get the surrogate key,current address from the dimension table as return values from function call lookup_ext.

Lookup Target Dimension Table

Next we check if the incoming source record exists in the dimension table or not. If the record does not exist in the dimension table we flag it for INSERT. If the record exist in the dimension table but the Present address is not same as the current address coming from source system we flag it for UPDATE. Else we mark the record as REJECT.

Evaluate Record Type

Next for the records marked for Insertion i.e WHERE REC_TYPE='I', we map the PRESENT_ADDRESS to the incoming ADDRESS from the source and make the PREVIOUS_ADDRESS as NULL.

Query- INSERT format

Next we use a Key_Generation transform to generate the surrogate key for the incoming new records from the source and insert into the target Dimension table.

Surrogate Key Generation

Now for the UPDATE path Query transform for those records marked for Update i.e WHERE REC_TYPE='U', we map the PRESENT_ADDRESS to the incoming ADDRESS from the source. Also we map the PREVIOUS_ADDRESS to the PRESENT_ADDRESS coming from the lookup dimension table.

Query- UPDATE format

Next we use a Map_Operation transform to changed the OPCODE for the incoming NORMAL records to type UPDATE and map it to the target dimension table.

Map_Operation- UPDATE Records This is how our target dimension table data for SCD Type 3 implementation looks like.

SCD_TYPE_3


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.

  • SCD implementation from Source based CDC table

    Consider a scenario where we want to capture all or any changes in the source table to be maintained as history in our data warehouse dimension table. So if we consider day end batch load we will miss out any intermediate changes to the master data as...

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...

  • Data Services Flatfiles Tips

    Often we come across scenarios where we have the flat file definition in an excel sheet and we need to create corresponding File Format in SAP Data Services. Alternatively we import file format definition from a Sample Source file.

  • Data Services Metadata Query Part 3

    This article is a continuation of the previous topic related to Data Services Metadata Query. Let us explore more into the Data Services Repository Metadata.

  • Handling XML source files in SAP Data Services

    This article will demonstrate how to read data from XML based source files using SAP Data Services. Here our objective is to load employee and department information respectively from the source XML based file.

  • Data Services Scenario Questions Part 1

    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.

  • Fools Guide to BODS - Registering Repository to CMC

    In our earlier article, we have learnt how to create a repository in BODS. Once that part is done, The final process before launching the BODS application is registering the repository with...

  • 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,...

  • Fools Guide to BODS - Introductory Tutorial

    This article is a guide to the readers who are totally ignorant about the tool SAP Business Objects Data Services. It is presented in a very simple way so that readers would be familiar with common terms, terminologies and would be able to run...

  • SAP Data Services Custom Function

    We can create our own functions by writing script functions in SAP Data Services scripting language using the smart editor. User-script functions return a value.