SCD question

I have a HR data mart.
I have a Position Dimension which is a SCD.
I have fields: RowStartDate, RowEndDate and a RowIsCurrent

If a position say p1 stops being used it just stops coming through in the source data.
The record for p1 still has a RowIsCurrent = 'Y' and a RowEndDate of some future date.

So what I can't do from the position dimension is report on all current positions. I haven't been asked for this but I may do in the future.

What is the best way to model this?
Change the RowIsCurrent and RowEndDate or have another field indicating that the position is still current?

My thinking is that the field RowIsCurrent = 'Y' shows the current record for this postion and although the position is not being used anymore (at the moment anyway) this is still the curent record for this positon.

Interested in your thoughts.

Cheers,
John

Model is fine. When a position is dropped you would just update the expire date of the current row.

The problem is the process does not tell you that a positition is no longer used. Without that information, no model is going to help. You need to improve the process.

You coud expire the row when it stops appearing in the source data if you consider that a reliable indicator that the position is no longer being used.

ngalemmo wrote:Model is fine. When a position is dropped you would just update the expire date of the current row.

The problem is the process does not tell you that a positition is no longer used. Without that information, no model is going to help. You need to improve the process.

Are you saying that if I detect the position is no longer used I should set RowEndDate = today and RowIsCurrent to 'N'?

I would leave current to 'Y'... it is, after all, the most current information about the position.

You just need to be careful if and when the position is reinstated. You do not want to change the expire date on the old row. The fact that the expire date is not in the future should be enough to indicate it should not be updated when a superceding row is created.

ngalemmo wrote:I would leave current to 'Y'... it is, after all, the most current information about the position.

You just need to be careful if and when the position is reinstated. You do not want to change the expire date on the old row. The fact that the expire date is not in the future should be enough to indicate it should not be updated when a superceding row is created.

Sorry for being slow - I want to be clear on this.
Are you saying leave current to Y but change RowEndDate from a future date to todays date?

Yes.