I have a problem of conception and I would like to get your ideas on how I'll be able to do what I am aiming.
My goal is to create a datawarehouse, with three dimensions and one fact table so far.
Dimensions :
- Tasks (id, name, status - 0 or 1 -)
- Time (as usual)
- Organization (Company, dept, office)
I would like to know what would be the best design for my fact table having as main focus my tasks status.
First idea was
task_id
time_id
organization_id
status
With this design, if I do a count of my tasks and if I have two entries one with status set to 0 and another one with status set to 1 for the same task it will return 2 but in fact it is the same tasks.
My conclusion were that Tasks is a SCD, and then I guess I should keep track of the change in the Tasks dimension but how do I design - put my status values into - my fact table then to benefit this.
If I haven't been clear enough please let me know,
UPDATE AFTER COMMENT :
So my measure will be an integer status that is either 0 or 1 that can be updated with the time. At d-day value can be 1 a d - 2 it could have been 0 and I want to store those changes. According to wikipedia then my fact table should have this schema
task_id
time_id_beg
time_id_end
organization_id
status
My metrics or fact that I want to know is for exemple for a d-date how many percentage of the tasks that should have been realized (status = 1) is really realized.
Am I hiting the right way or am I totally lost ?
