Slowly Changing Dimensions - Correction

About two years ago, I wrote this post on slowly changing dimensions.  Several months later I learned in a Kimball class that my understanding of a Type 3 Slowly Changing Dimension (SCD) was incorrect.  Allow me to use this post to right the ship.

My original explanation (incorrect):

A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD.  In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD.  Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD.  Each row also contains a column with the current value.  So, continuing with the example above (from the other post), while you will be able to see that the older fact rows pertain to a time in which the employee was a Junior Analyst, you will also be able to see (while looking at this same historical dataset) that the employee is now a Senior Analyst.

My updated explanation (correct):

A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD.  In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD.  Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD.  Each row also contains a column with the prior value.  So, continuing with the example above, while you will be able to see that the newer fact rows describe this employee as a Senior Analyst, you will be able to see that his/her prior position was Junior Analyst.

So, a type 3 SCD will look something like this:

key_employee	employee_first_name	employee_last_name	position	prior_position
1	John	Smith	Junior Analyst	(null)
2	John	Smith	Senior Analyst	Junior Analyst

My original explanation above describes a type 6 slowly changing dimension, which will look something like this:

key_employee	employee_first_name	employee_last_name	position	current_position
1	John	Smith	Junior Analyst	Senior Analyst
2	John	Smith	Senior Analyst	Senior Analyst

These links to Ralph Kimball's website provide some great information on slowly changing dimensions:

1.) Type 1
2.) Types 2 and 3
3.) Types 0, 4, 5, 6, and 7

Image courtesy of adamr / FreeDigitalPhotos.net

Kimball Conference Lessons Learned

I recently had the distinct privilege of attending Ralph Kimball's Dimensional Modeling In Depth class, as described in this post. Learning directly from icons such as Ralph Kimball and Margy Ross has been a huge blessing and a very enjoyable experience.  One of the most eye-opening (and valuable) experiences has involved tweaking my understanding regarding concepts that I thought I understood...but found that I didn't. Some of these misunderstandings have even come out in this blog, so I'll use this post to correct some of those...

1.) Junk Dimensions - The examples of junk dimensions that I have provided included the word "junk" in the name.  Margy Ross suggests not naming it as such, which makes a lot of sense.  Encountering a table with junk in the name may cause some confusion (perhaps even concern) for an analyst who is not well-versed in dimensional modeling.

2.) Snowflake Schema - The terms portion of my website provides the following definition for a snowflake schema

Occasionally there are reasons to join one dimension table to another dimension table. A schema in which this occurs is referred to as a snowflake schema. The ERD, in this case, will show this “second layer” of dimension tables as being similar in appearance to a snowflake.

This dimensional modeling class proved to me that this definition is a bit misleading.  Joining one dimension table to another, such as the one on the terms portion of my website, is referred to as an outrigger.  A snowflake schema involves an attempt to completely denormalize a dimension.

3.) Type 3 Slowly Changing Dimension - In this post, I described Type 3 slowly changing dimensions as being a hybrid between type 1 and type 2.  In reality, this hybrid is actually referred to as a type 6 (I need to update the other post).  So, what is a type 3 slowly changing dimension?  I'll save that explanation for a future post; however, the type 3 is not the hybrid that I thought it was.

One of the advantages of attending a course like this is that you get to bounce your knowledge against some of the most brilliant minds in the industry.  In some cases they help to affirm what you already know.  In other cases they correct what you already "know"...which turns you into a stronger asset for your organization and for the industry. 

For more information on data warehousing concepts visit www.brianciampa.com.  For data that can be used to practice modeling and/or ETL, click on Career in Data Warehousing and then click Grow.  Also, if you need a fresh approach to marketing your data warehousing skillset, consider The Data Warehouse Portfolio.

Image Courtesy of David Castillo Dominici / FreeDigitalPhotos.net

Slowly Changing Dimensions Explained...Using Twitter

I recently changed my twitter profile picture and after seeing the results I realized that this would be a great way to explain slowly changing dimensions.  When I uploaded the new picture and then viewed my timeline of tweets (my tweets) there was no trace of the old picture.  I could not tell that it had ever been used.  Even the tweets that had been associated with the old picture at one point now displayed the new one.  This is the behavior of a type 1 slowly changing dimension.  History is not kept.  Only the dimension values that are current are displayed for ALL facts.

Now, imagine that the old picture remained for those tweets that were sent when it was active and that the tweets sent AFTER the new picture was uploaded contained the new picture.  Viewing my timeline of my tweets will show me when I uploaded new pictures.  Twitter does not work this way, but if it did this would mimic the behavior of a type 2 slowly changing dimension.  As history changes, new rows are added to the dimension table.  Any fact rows that occur AFTER that change will point to the new row.  The fact rows that occurred before that change will continue to point to the older row.

Now, imagine that the timeline on twitter showed two pictures for each tweet.  One picture represented the picture that was current at the time of the tweet.  The other picture represented the picture that is the most current.  The latest tweets would probably show the same picture (most current one) twice.  If this were the case, we could easily see that when I tweeted about something two months ago, my profile used one picture but now it uses another.  This would mimic the behavior of a type 3 slowly changing dimension, which is a hybrid between type 1 and type 2.  A type 3 slowly changing dimension will add a new row as dimension objects change (as in a type 2).  Each dimension row, however, in that history will contain a column (or columns) that represent the current value.  So, if a new row is added, the current value columns for the historical rows will be updated with the new current information.  An analyst can easily see that at the time of a certain fact, the dimension values were one thing but now, they are something else.

Of course, writing ETL so that it populates a type 2 or type 3 slowly changing dimension is more complex than a type 1.  Creating a dimension to include history when the source system itself does not include history may cause difficulty if a dimension needs to be reloaded (although there are ways around this as well).  Consider all of the available options and make a decision that will turn your user's data into something that is truly valuable.

For some hands-on examples of slowly changing dimensions take a look at this post.  Also, remember that you can read this blog and access other data warehousing information by going to http://www.brianciampa.com/.

Slowly Changing Dimensions Solutions To Practice

In the last post I provided a script that allowed you to practice interacting with slowly changing dimensions.  The answers to the three questions that I proposed are...


1.) John Farmer earned $62,000 in salary (wages, benefits, and bonus) from Information Systems and $12,200 from Human Resources.  A SQL statement like this (although variations of it will answer the question as well) will answer this question...



select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid),
sum(b.actual_benefits_paid),
sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.employee_number = 2546
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

2.) James Couch's salary dropped 36.6% from the first to the second position.  It dropped 36.4% from the second to the third position.  From a business perspective this seems odd, but the point is to see there there was a change of some sort.  A SQL statement like this will answer this question...

select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid) + sum(b.actual_benefits_paid) + sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.employee_number = 2547
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

3.) Assuming that a current appointment is defined as a record with a null value for the appointment_end_date (in reality, a developer would need to verify this with a subject matter expert) the following SQL statement will display salary dollars for active positions only...

select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid),
sum(b.actual_benefits_paid),
sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.appointment_end_date is null
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

As the dimensions (descriptors of measures) move through time, a type 2 slowly changing dimension allows the measures to be grouped by each of those changes.  For example, the salary dollars can not only be displayed per person.  As the person progresses through the organization by moving to new positions, the salary dollars can be grouped by each stop along the way. 

From a technical perspective this amounts to the dimension's natural key expanding from the employee number to the employee number, position title, department, and appointment begin date (or whatever defines an appointment in your organization).  From a business perspective this example allows an analyst to view an individual's effect on the organization as she progresses through that organization.

Image: digitalart / FreeDigitalPhotos.net

Practice Using Slowly Changing Dimensions

A few weeks ago we looked at slowly changing dimensions.  I've provided a script that will create a table called dim_employee_scd and fact_salary_scd.  These are two slightly altered versions of the same tables in the star that we have been using (if you are just joining us, don't worry, they can stand alone too). 

In this case, the dim_employee_scd acts as a slowly changing dimension.  As always, don't read too much into the dates and/or salary amounts.  This data is entirely fictitious.  Run the script (here are instructions if needed), play with the data and see if you can answer these questions...

1.) How much did John Farmer receive in salary from Information Systems as opposed to Human Resources?
2.) As a percentage, how many more (or fewer) dollars did James Couch receive in salary when he moved from the first to the second and then from the second to the third position?  Remember, even if the numbers decrease although it appears that he was promoted, that's ok.  This is fictitious data and the point is to see there there was a change of some sort.
3.) Try to display salary dollars for active only positions, which may require that you make an assumption about the business rule.

We'll look at some solutions in the future.

Analysis Using Conformed Dimensions

In the last post we practiced examining data from different fact tables via conformed dimensions.  While the syntax may be different from solution to solution, something like the select statement below will allow you to compare salary dollars from the fact_salary table to sales dollars from the fact_transaction table per month for each person.

SELECT                    decode(paid.calendar_year, null, sales.calendar_year, paid.calendar_year) Year,

                                decode (paid.calendar_month_number, null, sales.calendar_month_number, paid.calendar_month_number) Month,

                                decode(paid.last_name, null, sales.last_name, paid.last_name) LastName,

                                decode(paid.first_name, null, sales.first_name, paid.first_name) FirstName,

                                paid.amtpaid,

                                sales.salesamt

FROM

(SELECT                   d.calendar_year,

                                d.calendar_month_number,

                                c.last_name,

                                c.first_name,

                                sum(b.actual_wages_paid) amtpaid

FROM                      fact_salary b,

                                dim_employee c,

                                dim_day d

WHERE                    b.key_date_paid = d.key_day

                                AND b.key_employee = c.key_employee

GROUP BY d.calendar_year, d.calendar_month_number, c.last_name, c.first_name) paid

FULL OUTER JOIN

(SELECT                   d.calendar_year,

                                d.calendar_month_number,

                                c.last_name,

                                c.first_name,

                                sum(a.amount) salesamt

FROM                      fact_transaction a,

                                dim_employee c,

                                dim_day d

WHERE                    a.key_day = d.key_day

                                AND a.key_sales_person = c.key_employee

GROUP BY d.calendar_year, d.calendar_month_number, c.last_name, c.first_name) sales

                ON                           paid.calendar_year = sales.calendar_year

                                                AND paid.calendar_month_number = sales.calendar_month_number

                                                AND paid.last_name = sales.last_name

                                                AND paid.first_name = sales.first_name

In order to take advantage of this approach, an analyst must view the data from each fact table at the same grain.  In the select statement above, notice that each of the inner selects group by the same dimension attributes (calendar_year, calendar_month_number, last_name, and first_name) and then the outer select statement joins on those attributes.  Since there may be some instances in which an individual has sales dollars but no salary dollars and vice versa, an Oracle full outer join is used.  If you are confident that one of the select statements will return all of the possible individuals for all possible time periods for that person, then a left or right outer join may be used.  In reality a reporting tool will probably take care of this on the back-end, so that a data analyst will not need to be familiar with this exact syntax (although it may be beneficial to conceptually understand how the data is being pulled).

From a business perspective, this kind of analysis will allow a decision maker to view productivity vs. cost for each instance of a business entity (i.e., individual, department, region, line of business, etc.).  This is yet another way in which data warehousing can help data become truly valuable.

Image: vichie81 / FreeDigitalPhotos.net

Slowly Changing Dimensions

In a prior post, before we took a break to practice a bit, we looked at the difference between natural and surrogate keys.  At first glance, you may wonder why a surrogate key cannot also serve as a natural key.  The answer to this excellent question leads us into a discussion of something called slowly changing dimensions (SCD). 

As time goes on, we would be foolish to expect that the descriptive data of our facts will not change.  It most definitely will change.  As people get promoted, then the job title fields in the employee dimensions will be affected.  As business grows and additional lines of business are created, then the dimensions that contain that data will need to be updated.  There are a few ways of dealing with this depending on the reporting needs of the organization.

Type 1 SCD: I typically describe a type 1 SCD as a dimension that does not keep history.  Once a piece of data changes (i.e., an employee is promoted from a Junior Analyst to a Senior Analyst) the old data is overwritten with the new.  Those analyzing the data will never know, from this data set, that this person was ever a Junior Analyst.

Type 2 SCD: I typically describe a type 2 SCD as a dimension that does keep history.  Once a piece of data changes, a new row, containing the updated data, is added to the dimension table.  The original row is still there.  All future fact records that pertain to that descriptive piece of data will have a foreign key pointing to the new record.  The older records in the fact table will still point to the original dimension record.  So, using the example above, if an employee is promoted from a Junior Analyst to a Senior Analyst, two rows will exist for that employee in the dimension table.  One will show this employee as being a Junior Analyst and the other a Senior Analyst.  All of the records in the fact table that describe facts that occurred after the promotion will point to the Senior Analyst row.  Those prior to the promotion will point to the Junior Analyst row.  Those analyzing this data will be able to see that this person was promoted.

Type 3 SCD: A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD.  In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD.  Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD.  Each row also contains a column with the current value.  So, continuing with the example above, while you will be able to see that the older fact rows pertain to a time in which the employee was a Junior Analyst, you will also be able to see (while looking at this same historical dataset) that the employee is now a Senior Analyst.

So, let’s return to our original question.  Why can’t a natural key be used as the primary key of a dimension table?  Technically it can, but that’s a bad idea.  Consider a data warehouse that was built so that the natural keys were used as the primary key of the dimension table.  If a fact table has employee details, the foreign key pointing to the dimension row in the employee dimension will be the organization’s employee number.  This is fine if the employee dimension is a type 1 SCD.  However, if the need arises for this to become a type 2 SCD then this becomes a problem.  Now you run into a situation where the new design calls for there to be, possibly, multiple rows in the dimension table with the same primary (natural) key since the natural key has changed.  Also, what happens when the organization expands and decides to change all of the employee numbers from five digits to ten digits?  Now all of your keys related to this dimension in the data warehouse (foreign key values in the fact table and primary key values in the dimension table) must change.  However, if surrogate keys are used, then implementing this change will be a much smoother process. 

Image: photostock / FreeDigitalPhotos.net