When is a record "current"?

Hi all,

Being new to this forum I would like to start with a discussion that is currently going on in our BI team. It is about the meaning of "current" in a SCD.

The situation is that we have an employee dimension that has 5 attributes that are being followed SCD2. Each row has a rowstartdate, rowenddate and rowiscurrent attribute. The rest of the attributes is SCD1. One of these SCD1 attributes is the employement end date. The dimension contains a lot of employees that are no longer employed (but do have facts attached to them).

Now my question: if a employee is getting an employement end date (so no longer employed), does he/she still have a record marked as "current"? 

I mean, the employee is no longer with us and could thus be classified as "not current". Following that, the record could get a rowenddate that is equal to the employement end date and there would be no new record. The attribute "rowiscurrent" would then be changed to "N". But this means handling this SCD1 attribute differently from other SCD1 attributes just because this specific attribute means the "end" of the employee. Which seems a bit strange thing to do...

Or is the right approach to have the employment end date changed in all records of the employee (just like any other SCD1 attribute) and that's it? So the current record stays the current record (and will probably stay current for ever as the employee is no longer with us). 

What is common practice in this case?

And another question following this one. If one of the SCD1 attributes of this no longer employed employee changes (no idea why someone would change SCD1 attributes of such an employee in the human resources source administration but just suppose it happens) would that change have to be done in the datawarehouse too? I suppose this could depend on the question whether the last known record is being classified as current of not? Or doesn't this matter at all and would these kind of changes have to be processed always, no matter the contents of the rowiscurrent attribute?

A lot of questions for a first post I know but I still hope for some answers...

Cheers,
Erik

#1. Yes. Current record and employee active are two different concepts.

#2. Ask the business. If they don't care, don't update. If they do care, update.

The concept of current record on SCDs is: most up-tp-date version among all the records that shares a natural key on that dimension; it's metadata. Active/inactive means another thing.

As for reflecting updates on records of non-active employees, I'd recommend you doing it to avoid inconsistencies between the OLTP and the analytical systems.

/* If tracking rehiring is not important to your design, you may skip this part */
Finally, an advice: if you use a national ID like SSN to uniquely identify each employee, treating the employment end date or even the hire date as SCD1 attributes will lead to information loss in the cases when you rehire someone: since the value of someone's national ID won't be different by the time he/she joins the company for the second (or third, fourth...) time, updating the hire/term dates would make would make you lose whatever you had on previous records.

If tracking all the periods when an employee have worked for the company is important, you may treat both the hire and termination dates as SCD2, and create an ActiveFlag attribute, also as SCD2. This way, whenever a contract ends, you'd have a new version of the employee record with the term date filled, and activeflag = 'N'. By the next time the employee gets hired, there'll be a new version of his record, with a new hire date, a null/dummy termination date, and the activeflag switching back to 'Y'.

(sorry about my English :-)

Thanks a lot! Especially as your answers confirm what I already thought to be the correct approach.

I will have to think on the second part of your answer Demitri. Rehiring only rarely occurs in our company but it still is a thing to consider.

Cheers,
Erik