--URL - [Link]

v=XqdZF0DJpUs

Hello all, my name is Kiran and in this DEMO Session I will be talked about what
are the slowly changing dimensions in the SQL Server. Why do we use it ?

=====================================================================
Slide 3 - WHat is SCD -- Page Introduction
=====================================================================
So , What is SCD ?, SCD stand for Slowly Changing Dimensions in the context of a
data warehouse.

Slowly Changing Dimensions are used to track changes in data over time in a data
warehousing environment. In a data warehouse,
you often deal with historical data, and some attributes of your data may change
slowly or sporadically, rather than having constant updates.

for example,

Product dimension for an e-commerce company that has ten rows or ten products.
as we know Product price won't change overnight or every day, it change while
competitive price hike or due to hike in raw material cost but,
it won't be that frequent.

So in that case, how we should capture that change ?

so - It is defined by SCD i.e. nothing but slowly changing dimensions.

We are going to discuss the standard SCD types that are implemented these days
in the It industry.

There are different Types of SCD like

Type1 ..Type2 .. Type3 ... Type4 and Type6 we will see these in depth

=====================================================================
Slide - 4 ADVANTAGES OF SCD
=====================================================================

1. Historical Data Tracking:

SCD allows you to maintain a historical record of changes to dimension data
over time.
This is invaluable for tracking changes in attributes,
which is crucial for historical reporting, trend analysis, and auditing.

2. Improved Decision-Making:
SCD enables organizations to make more informed decisions by providing
historical context.
For example, it's easier to analyze sales trends or customer behavior
changes when you have access to historical data.

3. Better Data Quality:

SCD helps in maintaining data integrity by preserving historical records.
This ensures that historical reports or analytics are accurate,
as they are based on the data as it existed at the time, reducing data
anomalies.
4. Auditing and Compliance:
SCD supports audit requirements and compliance by keeping a record of all
changes to dimension data.
This can be crucial in industries with regulatory compliance needs, such as
healthcare or finance.

5. User-Friendly Reporting:
With SCD, end-users can access historical data without the need for complex
data manipulation.
This simplifies the process of creating historical reports and reduces the
burden on report developers.

=====================================================================
Slide - 5 DISADVANTAGES OF SCD
=====================================================================

1. Increased Storage Requirements:

SCD can significantly increase storage needs, especially when preserving
historical data for changes over time.
This can lead to higher infrastructure costs for data warehousing.

2. Complex Data Transformation:

Implementing SCD can be complex, particularly when dealing with Type 2 or
hybrid SCD.
The ETL (Extract, Transform, Load) processes become more intricate
as they need to handle historical data preservation and tracking changes
effectively.

3. Performance Overhead:
Maintaining historical data and tracking changes can introduce performance
overhead.
As more historical records accumulate, queries on dimension tables can
become slower.
Organizations need to optimize their data warehouse infrastructure to
mitigate this.

4. Data Maintenance Overhead:

SCD necessitates ongoing data maintenance efforts to ensure historical
data integrity.
This maintenance can become resource-intensive, especially when handling
large datasets.

5. Data Quality Challenges:

While SCD can improve data quality, it also requires strict data quality
control to manage historical records.
Incorrect data updates or deletions can lead to issues in historical
reporting and analytics.

*****************************************************************
Slide - 6 SCD ZERO [SCD 0]
*****************************************************************
Now we can start with our first example of SCD Zero.

In this case, if there is a change in the source system, we are not going to
implement that in the warehouse table.

I will explain you all this with the help of an employee table.

So we have an employee table which has the details of an employee.

It has four columns, ID, Name, Salary and Fax Number.

The details that are being reflected are the source details, those are being
passed as it is to the warehouse table, which is having same four columns.

All the details are same in the warehouse for first time insertion.

Now suppose in future the Fax number of Vivek has changed in the source system.

In the system, the Fax number has changed and the same data is being sent to the
warehouse.

But Maybe five years before Fax number was relevant to me.

But right now all my communication is done over the email electronically or
using the
cell phone or the address mailing communication.

So I don't want to take the pain of either modifying this row or adding an
additional
row.

So what I'll do is I'll just reject the change, assuming as if nothing has
changed to
this particular row.

So in SCD Zero, which is a passive approach.

Also, if something has changed to the source dimension, we don't implement that
in the
warehouse table.

And you can see that if we did not make any change to the warehouse table as
well, we
retained the previous value.

When do we usually use this?

We usually use this for columns that are not relevant to my warehouse anymore.

They were possibly relevant like a few years back or in a different process, but
right
now they are not relevant.

So I don't want to go through the pain of updating Data warehouse for something
that is not relevant to me anymore.

SCD Zero means no change.

*****************************************************************
Slide - 7 SCD ONE [SCD 1]
*****************************************************************

Let's go ahead and understand SCD One.

Now, in SCD One, what we do is, we maintain the latest snapshot.

We do not maintain any history.

So whenever you talk about SCD One, the thing you should understand is that you
will maintain only the latest snapshot and no history.

So here we have the same employee table and we are going to use this employee
table throughout
this video to explain different type of slowly changing dimensions.

So here we have Vivek.

His salary is 10,000.

He's a developer.

We are going to pass the same information to the warehouse table.

Now what happens is we realize that ideally the source system, the person who is
maintaining
the source system, he realized that he made a mistake.

The name of the guy is Vivek Goel.

He is a lead and his salary is 40,000.

So there was an issue in the system.

The guy was actually a lead, his salary was 40,000 and the name is Vivek Goel.

So in that case, what we will do is we will just go ahead and update our
warehouse table.

*****************************************************************
SLIDE - 8 SCD ONE [SCD 1]
*****************************************************************

Now, it reflects the correct details.

Vivek 40,000 and lead.

At the same time, we have lost the previous information.

The early information which said that Vivek was a developer with salary 10,000
that does not exist in my warehouse table anymore.

We only have the latest snapshot of the source system.

Now, this usually is used in two cases.

Either we see that certain columns possibly had wrong value, so we don't want
the old value.

It's not actually relevant to us.

Or the second possibility is that only the latest snapshot serves the purpose
for us.

We don't care about the history.

So two things to remember in SCD one is that we maintain the latest snapshot and
we don't care about the history.

It's overwritten by the latest information.

Now, how do we maintain the history if you want to maintain the history?

*****************************************************************
SLIDE 9 - SCD TWO [SCD 2]
*****************************************************************

This we use SCD Two, which is actually very popular, and in most of the
warehouses and DATAMARTS, you will either see SCD One or SCD Two.

These are very popular.

In some of the cases you will see SCD Four as well.

And we will discuss in detail what are they as we proceed.

So in SCD Two, every time there is a change in your source system dimension, you
add an additional row in your warehouse table.

Let's say it with the same employee table example.

So we have Vivek.

He joined the Gurgaon office on 10th August 2013.

His salary is 10,000.

We passed the same information to the warehouse table.

Now you will see that the warehouse table has certain additional columns and we
will understand what they are.
So if you see these four columns are passed as it is, the date effective date is
replaced by from date.

This is the date where when he started working from Gurgaon office.

And right now we are assuming that he will work till Infinity or till the
maximum date in the Gurgaon office.

And then there is a column called Current Flag which actually signifies if this
is the latest row, if this is the current value.

Now, what happened?

*****************************************************************
SLIDE 10 - SCD TWO [SCD 2]
*****************************************************************

After two years, he got transferred to California.

His salary has also increased.

What will happen in my warehouse table?

Now, I told you in SCD Two, for every change, we introduced an additional row.

So we introduced an additional row where the salary is 40,000.

Now, Vivek is working in California office.

He started working on 10th Jan 2015 and now I'm assuming that he will work from
California till the last day.

And this is my active record now.

So the source system, if you see the active part, is California.

So my current flag is Y And the previous flag is N because he's not working in
Gurgaon anymore.

He's working in California.

And this to date, which was set to Infinity for Gurgaon, we changed it to one
day before
he joined in California.

So from 10th August 2013 to 9th January 2015, he worked in Gurgaon.

That's not his current location.

His current location is California and this is signified by the current flag
value Y.

*****************************************************************
SLIDE 11 - SCD TWO [SCD 2]
*****************************************************************

Now, what happened?

After six months, he moved to a different office in Houston.

His salary is the same.

Now, can you guess what will happen?

Yes, we will add an additional row and we will update certain components.

Now, the current flag reflects Y that the person is in Houston.

He moved there on 25th July 2015 and he will be there until the end of date.

We updated the last day of his stay in California as well, which is minus one of
the form dates.

So initially, if somebody asks me that, okay, what was the salary VIVEK in 2014?
So I can tell him that,at that time he was working in Gudgaon and his salary was
10,000.

If somebody asked me March 2015, I can specify that, Okay, he was working in
California and his salary was 40,000.

So we are maintaining the historical information in SCD Two at the same time
We have a current flag indicator using which we can identify which is our latest
row.

So before we go ahead on SCD Three, let's do a quick recap of zero one and two.

The SCD zero - if something has changed in the source dimension, we are not
going to update our warehouse or Mart dimension.

The possible explanation for that is that the column or the dimension that has
been updated is not relevant for oue Data warehouse.

In SCD One we are not maintaining any history, we are just keeping the latest
snapshot.

So we don't want to store the garbage information or you will see actually cases
where you only

want to analyze on the latest snapshot and you don't care about the previous
information.

For example, let's say that today one dollars is equals to Rs80.

So whatever analysis I want to do is I want to do it on the current amount.

I don't care about the amount that was there on five days back.

I just want to do analysis on my current data.

So that is SCD 2.

Where you are maintaining only the latest snapshot and no history.

In SCD Two what you're doing is your latest row is being designated or denoted
by current flag
is equal to Y and you are adding an additional row every time there is a change
in source dimension.

You don't care about any changes In SCD Zero, you maintain the latest snapshot
in SCD One, and in Scd Two you maintain the
historical data by adding an additional row every time.

*****************************************************************
SLIDE 12 - SCD THREE [SCD 3]
*****************************************************************

Let's go to SCD Three.

SCD three is very rarely used and as I explained you will know why it's being
rarely used.

What happens in SCD three is instead of adding an additional row you have an
additional column.

So we have the same example of Vivek.

His salary is 10,000.

He is working from Gurgaon office starting date 10 Aug 2013.

So we will store the same information in the data warehouse table.

Now you see you have an additional row.

The dimension that can change here is office.

So since this is his first location, this is his first job.

The previous office and the current office is same and he is working there from
10th August 2013.

*****************************************************************
SLIDE 13 - SCD THREE [SCD 3]
*****************************************************************

Now after two years he moved to California.

What will happen now is, current office, it will change to California.

So from 10th January 2015, he is working from California.

His previous office was Gurgaon.

His salary now is 40,000.

*****************************************************************
SLIDE 14 - SCD THREE [SCD 3]
*****************************************************************

Now again he started working in Houston so, we will be losing the Gurgaon
locations entry.

His previous office will become California and the current office will become
Houston.

So what's happening here is we are losing the history information.

We are not maintaining that, so this kind of scd is actually implemented in case
if we have prior knowledge that our dimension will not change that frequently.

Though it's very, very rare, that you are going to implement this, because
assume that there is another dimension along with office,
there is another column called Designation.

Then you will have to add two columns, previous Designation and Current
Designation.

Similarly, if you have Manager ID, you will have to add two columns, for each of
the column that you want to capture the historical data.

So it's usually not recommended to use slowly changing dimension three.

The most frequently used are one and two and in some cases you will use four as
well.

*****************************************************************
SLIDE 15 - SCD FOUR [SCD 4]
*****************************************************************

In slowly changing dimension four we have the concept of something called

History table.

So it's actually a mix of one and two.

What happens is, we have a table where we will maintain the latest snapshot as
we did in SCD One,

and we will have a history table where we will maintain all the historical data
as in SCD two.

So let's see how it will work.

So we have the same data of Vivek.

And we have got updates for Vivek's entry -

- 1st entry where he was working in Gurgaon, then 2nd entry where he was working
in California & finally 3rd entry where he settled in Houston.

So we will have the source system here with 3rd entry for Vivek, where he is now
working in Houston location.

So in the warehouse table we can see, the current office is stored as mensioned
in SCD One.

It will contain these four rows -Id, name, Salary, Office.

The Historical table will be like SCD Two.

It doesn't have the current flag because that is already being reflected by my
latest neighbor.

So I don't care about that flag anymore.

Now when he moves to California, this table will instead of reflecting Gurgaon,

it will start reflecting California with the correct salary and one row will be
added to my history
table as well.

And we can see that during 2014 and early 2015 he was working in Gurgaon, but
then he shifted in California.

So we are able to get both the information here, the latest snapshot as well as
well as the Historical table.

When again he moves to Houston it will again update the latest changes in Data
wareouse.

The current office will be Houston.

We have the latest snapshot in the latest table and the History table will have
an additional record.

So SCD Four is Scd One with SCD Two.

SCD Two is maintained in a history table.

SCD One is maintained in a different table which only has the latest snapshot.

I can tell you the example where SCD Four is used.

If you take the example of investment banking, the investment bank has a lot of
products that are being traded.

And these products changes very frequently.

For example, people who are buying and selling Bitcoins using USD, and the price
of Bitcoin, it actually refreshes every two minutes.
So every two minutes you are adding a row to your dimension table.

But the trading at that particular moment it happens on the latest whatever the
latest prices changes.

But for our analysis purpose you want to maintain the history of that as well.

The same goes for these currency transactions, USD to INR, USD to AUD to
Canadian currency.

These values, they change very frequently.

So right now, say yesterday 2 hours back a dollar was costing rs. 80, now it's
costing rs. 83.

The transactions that will happen will happen on the current amount only.

Say a person transferred $5,000 when it was Rs83.

The amount that he got, it will be generated on the current snapshot, on the
latest value.

But, it's always worthwhile to maintain the history, so that, we can go back and
see that, Okay,

if he would have transferred a couple of days back, this is the amount of money
that he would have made, all that kind of stuff.

It's just an example everyone.

But the point is if you want to maintain all that using SCD Two in a single
table, your table will grow huge.

Imagine you have 1 million products that you can actually trade and these
products are changing very frequently.

Say each product is changing ten times in an hour.

Then you will have 10 million rows in 1 hour, and that will explode or it will
increase with every hour.

So what you need to do is you need to create two separate tables,

one table that will always have 1 million rows with the latest snapshot and then
your history table, it can keep on increasing.

*****************************************************************
SLIDE 16 - SCD SIX [SCD 6]
*****************************************************************

Then we 3have something called SCD Six which is a mixture of SCD one, two and
three.
So we have got updates for Vivek's entry -

- 1st entry where he was working in Gurgaon, then 2nd entry where he was working
in California & finally 3rd entry where he settled now in Houston.

So we will have the source system here with 3rd entry for Vivek, where he is now
working in Houston location.

So in the warehouse table you will see, now the current office is stored as
mensioned in SCD One.

Then we will have the SCD Two where you are maintaining the history.

Then we will have two columns here like SCD Three, i.e. the office is being
maintained by the current office and the historical office.

Then also we will have our SCD TWO, the from date & to date alongh with the
current flag.

Even SCD Six is not that frequently used guys.

The most frequently used are SCD one and Two.

You will occasionally see in certain domains and certain systems SCD Four as
well.

And in very rarely systems you will see SCD Three and SCD Six.

*****************************************************************
SLIDE 17 - LEARNINGS
*****************************************************************

Let's quickly understand what we have learnt.

As of now, SCD Zero is passive method.

Even if your source system has changed, we are not going to change our
warehouse.

That is because possibly the change that has been made to the source system is
irrelevant to me.
----------------------------------------------------
The SCD one, we are overwriting the old value.

We are just keeping the latest snapshot.

----------------------------------------------------
In scd two, we are creating a new additional record.

Every time our source dimension change, we are maintaining the history.

We can see the latest snapshot with current flag is equals to y.

----------------------------------------------------
In SCD three, we are creating an additional column.
It's rarely used because to capture every column change you will have to add an
additional column

and if you have multiple changes, the previous history is being lost.
----------------------------------------------------
In SCD four, we are using history table.

So we are using two tables here.

First table which maintains the latest snapshot like SCD one and then
using another history table which has the historical data.

It's actually used when you have two different kind of reports, one kind of
reports which
are in huge numbers and they only run for the latest snapshot
and
then there are certain reports, that take into consideration the historical
table but you can run them as a part of bad
job in the night.

So we want to make sure that the latest data or the latest report performance
that is not happening.

So using the history table in that case of our dimension, it's huge & they are
changing very fast.

In that case you want to have two tables, one with the latest snapshot and
then one with the history tables and the history tables can keep on increasing.
----------------------------------------------------
The final is the hybrid approach where we are using SCD one, two and three.

This is also not used very frequently but maybe once in a while you will come
across a scenario where this actually fits well.

So these are the slowly changing dimensions that are used in data warehousing.

*****************************************************************
Q & A
*****************************************************************

Anyone has any questions?

------------------
one question from my side -

- Just let me know, Which SCD types we are following in our each projects?
Take your time and get back to me, or if you know it you can tell me now also.

*****************************************************************
Thanks
*****************************************************************
*****************************************************************
Demo on SIMON DB
*****************************************************************

SELECT employee_id, employee_associate_id, employee_preferred_name FROM

emp_hr_db.dbo.e_ge_employee
where employee_associate_id = '307021'

--------------------------------------------------------------------------------
---------

SELECT employee_associate_id , flag, valid_from , valid_to, egeh.changed_fields,

egeh.employee_job_code_id_id ,
egeh.* FROM emp_hr_db.dbo.e_ge_employee_history egeh
where egeh.employee_associate_id = '307021'

*****************************************************************

I hope it was clear to you all, in case anyone still have any doubts, they can
come to me personally at any time.

Thank you.

Have a good one.

Bye.