Hi,
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 @ http://en.wikipedia.org/wiki/Surrogate_key
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
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
MSBI (SSIS/SSRS/SSAS) Online Training:
1 comment:
Nice information thank you,if you want more information please visit our link MSBI online training Hyderabad
Post a Comment