Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
SAP Data Services

SAP Data Services Design Pattern: SCD Type 1 - Full Loading

Updated on Oct 02, 2020

In this tutorial we will show you the SAP Data Services design pattern for a Slowly Changing Dimension of Type 1.

Overview

Design Pattern: Slowly Changing Dimension of Type 1

In dimensional modeling, Type-1 dimensions are those that do not preserve the historical changes in attribute values. Whenever one attribute value changes, the old value is simply updated with the new value.

Pre-requisite knowledge

To know more about dimensional modeling, see the article on Dimension Modeling

To know more about Slowly Changing Dimensions, see this article.

Implementation Notes

Source and Target Description

For the purpose of demonstrating SCD Type 1 implementation in SAP Data Services (formerly known as BODS), we have chosen the Product table from our Retail data schema. We assume our source is an OLTP database for transaction processing and our target is a dimensional data warehouse.

Source Table

ColumnData TypeNullablePrimary Key
PROD_IDNumber(20, 0)NY
NAMEVarchar(50)NN
PRICENumber(10, 2)NN
LAST_UPDATED_DATEDATEYN

Target Table

ColumnData TypeNullablePrimary Key
PROD_KEYNumber(20, 0)NY
SOURCE_PROD_IDNumber(20, 0)NN
NAMEVarchar(50)NN
PRICENumber(10, 2)NN
LOAD_DATEDATEYN

The meaning of 'Full Loading'

In this Implementation, we will consider the "full loading" scenario. When we say "full load", we mean that every time we read the source table, we actually read the entire table irrespective of the fact whether or not we *need* to read the full table. This is opposite to reading the source table incrementally, or reading only the portion of the data that we really *need*.

Clearly, Full Loading is not always required/desired. For example, if your source table is huge in size - you may not want to read the entire table every time. In such scenario, you will only want to read the portion of the data that got changed in source. Such loading is called "Incremental" or "delta" loading. We will deal with that scenario in the next article. To know more about incremental loading in general, read this article.

Getting More help

We strongly encourage you to go through all our design pattern videos in Youtube here.