Abstract

As it turns out, the particular problem described by Jeff is non-trivial, but can be solved quite elegantly in a single SQL statment. This may be a compelling alternative to the multi-step, multi-pass solution proposed in his article.

Type 1 and Type 2 Slowly Changing Dimensions

In his article, Jeff describes a method to load a slowly changing dimension (SCD) table from an audit trail. This would be quite straight forward in case we are dealing with a Type 2 slowly changing dimension. In that case, each row in the audit trail would also yield one row in the dimension table. If the dimension would be a Type 1 slowly changing dimension, the matter would only be slightly more complicated - in this case only the most recent version of each object would be loaded into the dimension table.

A Hybrid SCD Type 1/2

The interesting thing about the problem described in the article is that the dimension table is a hybrid Type 1 / Type 2 dimension. That is, for some attributes, history needs to be tracked in the dimension table (Type 2 attributes), whereas only the most recent data is required for other attributes (Type 1 attributes).

The data shows the history for the employees with empkey 14 and 15. All rows with the same empkey value form a time line of data change events. Each row represents a change event, updating some of the employee's data. The valid_from and valid_to columns are used to record when the data change event occurred, so the values in these columns change for each row. For the other columns, I used bold markup to make it easier to spot the change.

SCD Type 1 and 2 Attributes

In Jeff's article, the columns name and ssn end up as SCD Type 1 attributes, and the columns gender and state as SCD Type 2 attributes. I used color highlighting to mark up groups of consecutive rows where the values for the gender and state columns did not change. After loading the dimension table we must end up with one row for each such group, capturing all change events for these columns. The ssn and name must always contain the most recent data. I highlighted the most recent row using grey markup.

As you can see, only the changes in the gender and state columns are recorded, ignoring any changes in the name and ssn columns. Instead, these columns get the values of the most recent change.

Jeff's Solution

Jeff's article describes a step-wise solution to this problem. To give you an idea and some context, here's a quote from the article describing the mindset in developing this approach:

While an ETL tool like Informatica or DataStage would be handy, any of this could be done fairly easily in straight SQL. We’ll also keep all the steps simple, easy to understand and discrete. It’s possible to create enormous heaps of nested SQL to do everything in one statement, but it's best to keep everything understandable. We create and label "tables," but whether you choose to actually create real database tables, or the tables are just record sets inside a flow, the process remains essentially unchanged.

The actual steps that make up the described solution can be summarized as follows:

Rank all rows according to the timeline

Join consecutive rows by rank in case the there is a change in the SCD attributes

Break the joined rows back into two rows (Jeff calls this 'semi-pivot')

Add the last and first version for each object to the set

Add another rank number to the result set

At this point, the rows where the new rank number is odd contain the valid_from date, and rows with an even number contain the valid_to date. Join each odd row to its consecutive even rows.

Join to the last version of each object to fill in for the SCD Type 1 attributes, and set a flag for the latest version of the object.

I admit I have a hard time understanding Jeff's method, and I am completely puzzled as to how he invented it. My apologies if my summary isn't exactly crystal clear - I encourage you to read the original article, "The Problem with History" yourself if you are interested in the details.

My Alternative

First of all, let me say that I am impressed with Jeff's creativity. I would never think of this solution and I am not really sure I even understand it. I also am not categorically opposed to Jeff's initial mindset: I too think that it is sometimes better to avoid complex SQL statements in favor of a series of relatively simple ones.

That said, when I first read Jeff's article, my first impression was: "Wow...that are a lot of moving parts". My second impression was: "Darn, that's a lot of multiple passes over the same data set". All the time, I had this hunch that it wouldn't be to hard to do it in two statements (one SELECT...INTO, one UPDATE), or perhaps even in one (SELECT...INTO).

Preparation

I started by setting up the test data in MySQL 5.1. I'm including the script here for the readers' convenience:

Now I would certainly not qualify this as a simple statement. At the same time, I feel this does not resemble the "enormous heaps of nested SQL" so dreaded by Jeff. Let me explain how it works, and you can judge for yourself.

Explanation

I will know do a step-by-step explanation of my statement. I hope it clears up any doubt you might have as to how my solution solves the problem.

SCD Type 1 attributes: Isolating the Most Recent Change

In the introduction, I mentioned that the loading the dimension table would be easy in case it was either a Type 1 or a Type 2 slowly changing dimension. Well, we know that, come what may, we will always need the most recent row for each empkey to supply values for the Type 1 attributes name and ssn. So, we start by attacking that part of the problem.

The subquery curr1 finds us the highest value for the valid_from column for each distinct empkey. The GROUP BY empkey clause ensures we get exactly one row for each distinct value of empkey. From all rows with the same empkey value, the MAX(valid_from) bit finds the largest valid_from value.

The combination empkey, valid_from is the primary key of the employee audit trail. This means we can now use the empkey, MAX(valid_from) pair from the curr1 subquery to point out the most recent row for each distinct empkey. The INNER JOIN with the hrsource table (dubbed curr) does exactly that.

Grouping consecutive rows with identical Type 2 attributes

We must now deal with the second part of the problem, the SCD Type 2 attributes.

If you look back at the color highlighting in the sample data set, you may realize this is basically a grouping problem: for each distinct empkey we need to group rows with identical values in the Type 2 columns gender and state. From the point of view of the dimension table, no change occurred within this group, so we should store it as one row in the dimension table, and reconstruct the change dates by taking the minimum valid_from and maximum valid_to values from the group.

We must be careful though: we can't simply make groups of all distinct combinations of the SCD Type 2 columns. Look for example at the rows having 14 for the empkey column. The rows with the valid_from values 2005-08-08, 2006-02-13 and 2006-12-25 all have the identical combination (M, MI) in the Type 2 columns gender and state respectively. However, only the first two of these belong together in a group. The row with 2006-12-25 in the valid_from column does not belong to the group because another change event occurred on 2006-07-05, which lies between 2006-02-13 and 2006-12-25.

So, we must sharpen up the definition of the problem. It is not enough to make groups of identical combinations of Type 2 attributes: we must also demand that the rows in the group are consecutive.

The first step in attacking this problem is generate combinations of the audit trail rows so that we have the first row of each group along the first row of the next group. The following fragment solves part of that problem by combining each row (alias: prv) with all more recent rows (alias: nxt) that have the same empkey value but different values in the Type 2 columns gender and state:

(Please ignore the part I struck out - it's not relevant at this point)

Note the usage of the LEFT JOIN. It ensures that if prv is the most recent row, and there is by definition no row in nxt that is more recent, the prv row is still retained. Had we used an INNER JOIN, we would have lost that row, messing up the result.

If we run this fragment in isolation, we can certainly see rows that pair the first row of a group with the first row of the next group. For example, if you run this query:

The first row highlighted in green is a desired combination because the valid_from date of nxt is closest to that of prv. It is this row from nxt that marks the end of the group of rows starting with prv. For the rows highlighted in red, the nxt part indicates a change that occurred beyond that point and are thus not desired.

To get rid of these undesired rows, we simply have to demand that no row indicating a change in the SCD Type 2 attributes occurs between prv and nxt. In my query, the following fragment is responsible for that part:

This fragment essentially states that there must not be any row in between prv and nxt that indicate a change in the SCD type 2 attributes as compared to prv. Note that the first part of the join condition is identical to the one we used to join nxt to prv. The extra's are that we also ask that inb lies before nxt. Because we used a LEFT JOIN, the result row is retained in case no such inb row exists. The WHERE inb.empkey IS NULL condition explicitly filters for these cases. By definition, this means that the nxt part in the result row marks the end of whatever group the prv belongs to.

Now, we still see the individual rows. However, because we have paired them with the row that definitely marks the end of the group it belongs to, we can now lump them together using GROUP BY. This explains the final bits of the query:

GROUP BY prv.empkey, nxt.valid_from

For each value in empkey, this GROUP BY clause essentially rolls up the group of rows that showed no change in the SCD Type 2 attributes until the occurrence of the nxt row. In the SELECT list, we can now use the MIN function to find the date of the first row in the group.

Note that the GROUP BY list is the thinnest one possible. The SELECT list contains many more columns that do not appear in the GROUP BY list. However, it is perfectly safe to do so in this case. For more details on this matter, please read my Debunking Group By Myths article.

Glueing the SCD Type 1 and Type 2 Solutions together

We developed the solution as two separate queries - one to take care of the SCD Type 1 attributes, one for the SCD Type 2 attributes. These two are simply joined together over the empkey. The code to do this was displayed struck out in the first query fragment that deals with SCD type 2 attributes.

Calculating the Result columns

The only part of the query that was not discussed yet is the SELECT list:

Note that we select the name and ssn columns from the curr table. This ensures these columns will always reflect the data of the most recent change which is typical for SCD Type 1 attributes. The gender and state columns on the other hand are drawn from prv. They reflect the change history for each individual employee.

The valid_from column is aggregated using the MIN function. This essentially yields the date of the first row of each group of consecutive rows having the same empkey and values in the SCD Type 2 columns.

The valid_to date is calculated from the valid_from date of the first row of the next group of rows that indicates a change of values in the SCD Type 2 columns. This is the relevant code:

nxt.valid_from - INTERVAL 1 DAY

Now, to be completely fair - this is in fact cheating. It implies I trust the data to be such that the value for the valid_to date is always exactly one day less than the value for the valid_from date for the next row in the history. This was certainly the case with the sample data, but we cannot really rely on that. That said, it is not that hard to get the 'real' valid_to date, but it would make this example a lot harder to understand.

Another thing about the calculation of valid_to is the treatment of NULL values. For each history associated with one distinct empkey value, the last row will have NULL here, and this is substituted by a large date instead. Dimension tables tend to avoid nullable columns and in this case the maximum value '9999-12-31' expresses that this row is the current row. Using that same logic, and exrta flag column is added to indicate whether the row is the current row.

5 comments:

This would be my version using CTE's and window functions introduced in PostgreSQL 8.4:

WITH scd1 AS (-- get the scd1 (last value) using the DISTINCT ON with an ORDER BY SELECT DISTINCT ON (empkey) empkey , name , ssn FROM hrsource ORDER BY empkey , valid_from DESC ) , scd2 AS (-- detect the matations in gender and state with the LAG window function-- compare the previous value with the current-- the IS DISTINCT FROM operator (<>) treats NULL as a 'normal' value SELECT empkey , valid_from , valid_to , gender , state , CASE WHEN gender IS DISTINCT FROM LAG(gender) OVER timeline THEN 1 ELSE 0 END AS gender_mutation , CASE WHEN state IS DISTINCT FROM LAG(state) OVER timeline THEN 1 ELSE 0 END AS state_mutation FROM public.hrsourceWINDOW timeline AS (PARTITION BY empkey ORDER BY valid_from))-- combine scd1 and scd2-- filter scd2 on only the changed values-- compute the valid_to as next valid_from with the LEAD window function-- if not found then use maximum date '9999-12-31'SELECT scd2.empkey , scd1.name , scd1.ssn , scd2.gender , scd2.state , scd2.valid_from , LEAD(scd2.valid_from,1,'9999-12-31'::date) OVER timeline AS valid_to FROM scd1 JOIN scd2 ON scd1.empkey = scd2.empkey AND 1 in (gender_mutation,state_mutation)WINDOW timeline AS (PARTITION BY scd2.empkey ORDER BY scd2.valid_from)

Window functions can be emulated in MySQL, see this article:http://www.oreillynet.com/pub/a/mysql/2007/04/12/emulating-analytic-aka-ranking-functions-with-mysql.html

I still think that for the particular topic at hand my solution is the simplest one, but I understand the benefit of windowing functions (esp. when reporting on a DWH). I know they have been implemented in PG and I've been meaning to check them out.

Your emulation query looks interesting. I haven't looked at it in detail, but I will do so shortly - it looks I could learn quite a lot from it.

I made the scd in Kettle using the Analytic Query step twice and a Group by step. I cannot post the picture in the comment. So I'll post the url with the xml, so you can load in Kettle: http://public.me.com/delostilos

Search This Blog

About Me

My name is Roland Bouman. If you like you can follow my @rolandbouman feed on twitter.
I'm a Web- and BI Developer and Information Analyst. I'm currently working as a Software Engineer for Pentaho, a world-leading Open Source Business Analytics Suite and Big Data Platform. In the past, I have worked for Inter Access, MySQL AB, Sun Microsystems and Strukton Rail.
I authored two books: Pentaho Solutions (Wiley, ISBN: 978-0-470-48432-6) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9).
I'm proud to be elected Oracle Ace for my MySQL expertise.