I have created a cube based on a helpdesk application. I have moved the data to a warehouse database and created a separate table for the fact table. For one helpdesk request there may be multiple technicians and multiple values for time spent on the case. WIth this being the case, in my fact table i have had to repeat the helpdesk request for each technicians time entry. The time spent by duration is a measured value.

I also need to have a measured column with timetoclose (datesubmited - dateclosed). The problem occurs if i also put this value in the fact table as it is repeated for each request and when i sum the time it will sum the time multiple times.

How can i ensure that the timetoclose is not added multiple times when there is more than one row in the fact table?
Any help would be very much appreciated.
dave

Thank you Jamie. I have read your post a few times.
I understand your point about 2 fact tables. I will try this after I try option 1 (adding dimention TimeToClose).

What I have done is create a dimension table TimeToClose where I have TimeToCloseID field ,minutes column, range column. When populating my fact table i have included the timetocloseID field with the appropriate value depending on the time duration.

There are currenly 4041 requests in the table, however after I added this dimension and no other dimensions the cube only shows 333 requests. I checked my fact table i have a measurement 'theCount' which counts distinct requests in the fact table. After I add category dimension my cube still only shows 333 requests total. So, in this case i have timetoclose dimension and category dimension and cube only shows 333 requests (as opposed to 4041 requests).

I am not sure why it shows so few. I would have expected that 'thecount' would still show 4041 requests .

Any help would be appreciated in explaining this to me. I do not know why this dimension would be any different than one called category.

help?

Please ignore the last post. I had to add the all Time toClose property to see all of them. My mistake.

I will try your recommended option later tonight. Thank you
dave