hi guys, as you may already know dimensions
in data warehouse may contain attributes with static data but how can we manage a
dimension whose attribute values changes, well today i'm going to
introduce you to slowly changing dimensions
and at the end of this video you will know what is a slowly changing dimension
the importance of slowly changing dimensions
and the types of slowly changing dimensions that exist
so what is a slowly changing dimension it is a dimension
that tracks both current and historical data
over time in a dimensional data warehouse
and there are several types of slowly changing dimensions
i'm gonna introduce you to five different types
and the first one i'm gonna talk about is type zero
with slowly changing dimension type zero the dimension attribute values
never change. This is typically seen in most attributes in a date dimension
this is a date dimension and we have attribute day name
month name we all know that day name never changes
as well as month name so if you have these attribute values in your
table then you don't expect these values to change
and this type of dimension is called the type zero
the second type of slowly changing dimension is type 1
with type 1 if there is any change in any of the attribute values
all values in the dimension is overwritten
with the new values so in type 1 the attribute values always reflect the
most recent information and here no history
is kept. This is an example of how type 1 looks like, so we have the
original dimension table and then the employee
name Bill Lion is changed instead of keeping track of when Bill
Lion changed his name to something else we
decide to just replace Bill Lion with the new name
which is Bill Tiger. So this will be our new dimension we
overwrite the old values with the new values
this is called type one the third type of
slowly changing dimension i'm going to discuss with you
is the type 2. here in order to track changes in a dimension we
add a new row to the dimension table with the updated values this is the most
widely used slowly changing dimensions in a data warehouse
and here is an example of how it looks like
we have our original dimension table here and because we
are dealing with type 2 we introduce at least three additional
columns we have start date end date and current status all the
attributes are still current so it's yes for all of them
however if employee name changes for bill
Liom then we introduce a new row and capture the new name for bill lyon Bill Lion
which is Bill Tiger and because we already have three
additional columns we are able to track when bill
Lion changed his name so this status is no, and it's the status is yes for
bill tiger so this is how slowly changing dimension type 2 works
and like i said this is the most popular type of slowly changing dimension
implemented in dimensional data warehouses the fourth
type of slowly changing dimension we have
is the type 3. here instead of introducing a new row
we introduce a new column i must say that this is
less frequently used because there is a limit the number of columns you can add
to a table and you also need to go through
management in order to be added columns each time
a dimension attribute values changes so this is how
it looks like so here Bill Lion decides to change his
name so we would introduce a new column and capture the new name there which is
Bill Tiger, so we have the initial column existing and then a
new column is added and we just keep on adding new columns
if any of these attributes changes next we have the type 4
this type of slowly changing dimension is used when a group of
attributes or an attribute in a dimension
rapidly changes so if we have one particular attribute
changing more frequently then we need to split
that attribute out of the main dimension table and create a mini
dimension for it so suppose that we had employee age
in this employee dimension and then we
realized that the age of the employees just keep changing
rapidly then we need to take it out of this
original employee dimension and create a new dimension for
it and have its own key and then what you need to do
is represent both primary keys in the fact table together with
all the other attributes of the fact table
these are types of slowly changing dimensions i want to introduce to you
guys today there is a saying that practice makes
man perfect if this is new to you then i recommend that
you keep watching this video and you keep
practicing it so that you can become more proficient
in using these slowly changing dimensions whichever that you want
or as recommended by the business thank you so much for watching this
video and have a great day bye