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


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,


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


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 ?

  • 4
    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? 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. Aug 11 '14 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.