Is it acceptable to update fact row with latest status?
2 posters
Page 1 of 1
Is it acceptable to update fact row with latest status?
I am creating a fact table for every interview slot that's created.
If the interview is re-arranged, a new interview slot is created. There is a status on the interview. So in this scenario it would begin as 'Arranged' and be changed to 'Re-arranged'.
The new slot created would have a status of 'Arranged'. If attended, the status would change to 'Attended'.
Eg.
Interviews
ID Slot Date Time Person Interviewer Room Status
1 1 10-Mar-2016 10:30 Steve Baldwin James Edwards 17 Arranged
2 1 10-Mar-2016 10:30 Steve Baldwin James Edwards 17 Re-arranged
3 2 23-Mar-2016 13:30 Steve Baldwin James Edwards 23 Arranged
4 2 23-Mar-2016 13:30 Steve Baldwin James Edwards 23 Attended
I do not have a requirement to track the changes of the status, and just need the current status.
So I only need to see this
Interviews
ID Slot Date Time Person Interviewer Room Status
1 1 10-Mar-2016 10:30 Steve Baldwin James Edwards 17 Re-arranged
2 2 23-Mar-2016 13:30 Steve Baldwin James Edwards 23 Attended
With this in mind, is it reasonable to update the fact row with the current status. I know it is frowned upon to update facts, but would it acceptable in this scenario? Or maybe I have my design wrong?
Any advice would be appreciated, thank you
If the interview is re-arranged, a new interview slot is created. There is a status on the interview. So in this scenario it would begin as 'Arranged' and be changed to 'Re-arranged'.
The new slot created would have a status of 'Arranged'. If attended, the status would change to 'Attended'.
Eg.
Interviews
ID Slot Date Time Person Interviewer Room Status
1 1 10-Mar-2016 10:30 Steve Baldwin James Edwards 17 Arranged
2 1 10-Mar-2016 10:30 Steve Baldwin James Edwards 17 Re-arranged
3 2 23-Mar-2016 13:30 Steve Baldwin James Edwards 23 Arranged
4 2 23-Mar-2016 13:30 Steve Baldwin James Edwards 23 Attended
I do not have a requirement to track the changes of the status, and just need the current status.
So I only need to see this
Interviews
ID Slot Date Time Person Interviewer Room Status
1 1 10-Mar-2016 10:30 Steve Baldwin James Edwards 17 Re-arranged
2 2 23-Mar-2016 13:30 Steve Baldwin James Edwards 23 Attended
With this in mind, is it reasonable to update the fact row with the current status. I know it is frowned upon to update facts, but would it acceptable in this scenario? Or maybe I have my design wrong?
Any advice would be appreciated, thank you
Scott- Posts : 17
Join date : 2016-03-07
Re: Is it acceptable to update fact row with latest status?
There is nothing that prevents you from updating a fact row. And it is actually more common than you might think.
Re: Is it acceptable to update fact row with latest status?
Thank you for your reply, I was under the impression it was sacrilegious to update a fact row other than an accumulating snapshot fact. I will go down this route, thanks again.
Scott- Posts : 17
Join date : 2016-03-07

» What to do when facts change? Update the fact table with update and deletes? or use SCD type 2?
» How many measures in a fact table is acceptable?
» Update dim ID in fact rows or create new fact row?
» SCD and Fact update
» Status Fact?
» How many measures in a fact table is acceptable?
» Update dim ID in fact rows or create new fact row?
» SCD and Fact update
» Status Fact?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum