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
add comment

1 Answer

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?

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.