Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
3  
I'm curious what kind of metrics/measurements you are planning for your fact table? See the sections on "Measure types" and "Types of fact tables" in the following article for some ideas. en.wikipedia.org/wiki/Fact_table –  Jeff Mar 24 '11 at 1:49
    
Yes. Your task dimension is totally SCD. How often do you update your task dimension. Is it possible to update your task dimension every time a job is completed using a script? What environment are you in windows with AS or Unix/Linux with something else? –  ndefontenay Mar 29 '11 at 13:42
    
Tasks is a fact table, not a dimension, so it can't be an SCD. Status is a dimension, and a slowly-changing one at that. Though in some cases it may be maintained as just an attribute of the fact table, meaning a simple VARCHAR field, rather than an FK out to an explicit dimension table. –  Jon of All Trades Aug 11 at 19:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.