MSBI (SSIS/SSRS/SSAS) Online Training

Thursday, March 7, 2013

SSIS : SCD Type 2 Design (Data load) –Part I


In any data warehouse data model; Dimensional load is key part and by using SSIS we can easily handle SCD concepts. Before we proceed with SCD, let’s discuss about basic scenario why we need this SCD.

Surrogate key

Ø  It is sequence generate key

Ø  Similar to Primary key to the dimension

Ø  Normally we use in Type – 2 Dimensional load


Business key

Unique key – called the Business Key

Surrogate keys

Surrogate keys are unique system identifier of entity or rather state of an entity. These keys are invisible to users, and it’s only used within system.


You can refer theoretical description on Wikipedia @


Here is a quick example of my understanding;

We are using AdventureWorksDW2008R2 database, simply check the any dimension table, let’s say DimProduct table.

Select ProductKey,ProductAlternateKey,EnglishProductName, StandardCost, ListPrice, DealerPrice,StartDate,EndDate

from DimProduct

where EnglishProductName = 'Sport-100 Helmet, Red'

In this table we have two keys, Product Key and ProductAlternateKey, here ProductKey is Surrogate key and ProductAlternateKey is Business Key.

ProductAlternateKey uniquely identifies the product in our application, but over the period of time the rates have been changed of the product, and which rate is current rate is defined by EndDate, and to uniquely identify different state of product in the table we have ProductKey.

This concept is also known as Type 2 Slowly changing dimension concept, which saves historical state of Entity by inserting new records

1 comment:

akhila priya said...

Nice information thank you,if you want more information please visit our link MSBI online training Hyderabad