SCD question

View previous topic View next topic Go down

SCD question

Post  RoyalWulf on Wed Aug 10, 2011 1:29 am

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


RoyalWulf

Posts : 9
Join date : 2010-04-18

View user profile

Back to top Go down

Re: SCD question

Post  ngalemmo on Wed Aug 10, 2011 10:12 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD question

Post  VHF on Wed Aug 10, 2011 11:12 am

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: SCD question

Post  RoyalWulf on Wed Aug 10, 2011 5:14 pm

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'?

RoyalWulf

Posts : 9
Join date : 2010-04-18

View user profile

Back to top Go down

Re: SCD question

Post  ngalemmo on Wed Aug 10, 2011 6:55 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD question

Post  RoyalWulf on Wed Aug 10, 2011 7:17 pm

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?

RoyalWulf

Posts : 9
Join date : 2010-04-18

View user profile

Back to top Go down

Re: SCD question

Post  ngalemmo on Wed Aug 10, 2011 8:36 pm

Yes.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD question

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum