What are SCD (Slowley changing dimension)

[#1]

Can any one explain about the SCD (Slowely changing dimension).

[swuehl]

http://en.wikipedia.org/wiki/Slowly_changing_dimension

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions

IntervalMatch and Slowly Changing Dimensions

Slowly Changing Dimensions

[#3]

There are often two used types of SCD - SCD1 and SCD2

SCD1: This one is very often used if you have a product dimension. Often a productname can changes - Eg, it can be named Cola and have productID 1 - But then it changes name to Coca-Cola - But its the same product, so it still have productID - Then you wanna use SCD1 so you can update the name but still hold the same KEY in your dimension.

SCD2: This one is often used when you want historical overview of your data. It could example be an adresse. If a person have an adresse for year 2005-2010 but then changes adresse in 2010. Then you also want to see where he lived in 2005-2010 then you use SCD2. SCD2 often consists of 2 datetypes as ValidFrom and ValidTo and a IsCurrent Row.

You could also use it if u have a salesperson who works in one department and then changes to another, but you still want to be able to see how much he sold in the old department.

Hope you get the picture

[maxgro]

a good article about scd from one of the architects of data warehousing

Slowly Changing Dimensions - Kimball Group

[fkeuroglian]

See this useful post

Good luck

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions

IntervalMatch and Slowly Changing Dimensions

Fernando

[#6]

Suppose we have the following table
ID	COL1	COL2	COL3
1	A	201406	100
2	B	201406	200
3	C	201406	300
For a table, we simply have Key fields and non-Key fields. In our scenario, ID is the key, while others are non-key fields.
We can simply treat non-key fields as dimensions.
Once the dimensions change, how can we track it? That's the reason we need slowly change dimension.
Suppose, base on ID=1, if COL3's value updates, what can we do next?
We can simply update COL3, based on ID = 1.
UPDATE TABLE SET COL3 = 400 WHERE ID=1
SCD1
ID	COL1	COL2	COL3
1	A	201406	400
2	B	201406	200
3	C	201406	300
It's simple, but we can't trace back its status before we modified.
Therefore, we want to use other approach to record the change, and it's called slowly dimension change.
SCD2(BEFORE)
ID	COL1	COL2	COL3	START_DATE	ENABLE_FLAG
1	A	201406	100	20140601	1
2	B	201406	200	20140601	1
3	C	201406	300	20140601	1
UPDATE TABLE SET ENABLE_FLAG = 0 WHERE ID=1
INSERT INTO TABLE (COL1, COL2, COL3, MAX(START_DATE)+1) SELECT COL1, COL2, COL3, MAX(START_DATE)+1 FROM TABLE WHERE ID = 1 AND ENABLE_FLAG=1;
SCD2(AFTER)
ID	COL1	COL2	COL3	START_DATE	ENABLE_FLAG
1	A	201406	100	20140601	0
2	B	201406	200	20140601	1
3	C	201406	300	20140601	1
1	A	201406	400	20140602	1
START_DATE is used to trace back the exact date SCD happens, ENABLE_FLAG indicate the current valid flag.

[#7]

Thanks to All it is So help ful one.