DWBI.org wants to publish your tech tutorial!

Help us build a comprehensive collection of beautifully-written tutorials about Data Analytics, Big Data and Business Intelligence. You don’t have to be an experienced writer. If you have technical knowledge and a knack for explaining things, our editors will help with writing and publication. Submit a writing sample to become a our community author, get published on our rich knowledge base with over a million unique page views each year, and make up to USD $100. Email us at service@dwbi.org to know more.

What we look for

  • Technical expertise and best practices
  • Correct and comprehensive commands
  • Clear explanation
  • Friendly, concise, and informative style

What do you get

  • Based on the size, importance and quality of your article, we will pay you USD $30 ~ USD $50.
  • Chance to publish your article in DWBI.org
  • Recognition as a technical author

Contact us at service@dwbi.org

In this tutorial we will show you the SAP Data Services design pattern for a Slowly Changing Dimension of Type 1. We have also included one hands-on video below to take you through the actual Data Services job for demonstrating SCD Type 1 design.

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.

This article is part of a series of articles on ETL design patterns using various ETL tools. We strongly encourage you to visit our ETL Design Pattern overview page to check other design patterns

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 TypePrecisionScaleNullablePrimary Key
PROD_IDNumber200NY
NAMEVarchar500NN
PRICENumber102NN
LAST_UPDATED_DATEDateNANAYN

Target Table

ColumnData TypePrecisionScaleNullablePrimary Key
PROD_KEYNumber200NY/N
SOURCE_PROD_IDNumber200NN
NAMEVarchar500NN
PRICENumber102NN
LOAD_DATEDateNANAYN

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.


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.

  • ETL Design Pattern

    ETL Design Pattern is a framework of generally reusable solution to the commonly occurring problems in the context of Extraction, Transformation and Loading (ETL) activities of data in a data warehousing environment. We have presented the design...

  • SAP Data Services Design Pattern: SCD Type 1 (Full)

    In this tutorial we will show you the SAP Data Services design pattern for a Slowly Changing Dimension of Type 1. We have also included one hands-on video below to take you through the actual Data Services job for demonstrating SCD Type 1...

  • SAP Data Services Design Pattern: SCD 1 (using Table Comparison Transform)

    In this tutorial again, we will show you the SAP Data Services design pattern for a Slowly Changing Dimension of Type 1, this time using table comparison transform. We have also included one hands-on video below to take you through the actual Data...