Hello etl'ers. I have a table in our system with a foreign key that changes periodically. They way that this was implemented on the oltp
database is as follows.

create table widget
(
widgetid int,
name varchar(255)
);

create table widget_update
id int,
userid int,
startdate date
);

So basically... the userid in the widget_update table along with the startdate determines which user has the widget during a time frame.
I would like to use a type 2 slowly changing dimension from this but I guess I may have been mistaken on how it works.
Say we have specific widget, widget id 48. It has 7 versions listed below.

userid| startdate
-------+------------
96 | 2005-01-01
271 | 2006-01-01
455 | 2006-01-16
371 | 2007-01-01
271 | 2007-01-13
96 | 2007-01-20
455 | 2007-01-21


I use the startdate field as the stream date field. I set
all widget fields to Update except the userid which i set to type 2 insert. Kettle
creates the following versions which is not what i had expected. It seems there is alot of overlapping of date_from and date_to...
which is not what i expected, i figured that there would be a single non-overlapping date range for each version of the row.


userid| date_from | date_to | version
-------+---------------------+-------------------------+---------
96 | 1900-01-01 00:00:00 | 2007-01-21 00:03:00 | 1
271 | 2006-01-01 00:02:00 | 2007-01-01 00:12:00 | 2
455 | 2006-01-16 00:10:00 | 2199-12-31 23:59:59.999 | 3
371 | 2007-01-01 00:12:00 | 2199-12-31 23:59:59.999 | 3
96 | 2007-01-20 00:09:00 | 2199-12-31 23:59:59.999 | 3
455 | 2007-01-21 00:03:00 | 2007-01-01 00:12:00 | 2

Ideally I would have liked to seen something like this.

userid| date_from | date_to | version
-------+---------------------+-------------------------+---------
96 | 1900-01-01 00:00:00 | 2007-05-31 00:03:00 | 1
271 | 2006-01-01 00:02:00 | 2006-01-15 00:12:00 | 2
455 | 2006-01-16 00:10:00 | 2006-12-31 23:59:59.999 | 3
371 | 2007-01-01 00:12:00 | 2007-01-12 23:59:59.999 | 4
271 | 2007-01-13 00:12:00 | 2007-01-19 23:59:59.999 | 5
96 | 2007-01-20 00:09:00 | 2007-01-20 23:59:59.999 | 6
455 | 2007-01-21 00:03:00 | 2199-01-01 00:12:00 | 7


Further more i also thought that if the startdate changed on the oltp system, such as i added a userid with 30 startdate 2005-01-20, that it would rearrange
the previous versions that were in conflict. Wheres my flaw?