Model available time per day in timesheet data warehouse: fact or dimension or neither?

Hi
I am new to data warehousing and related subjects and as a first project I am busy analyzing our timesheet data. I would like some design ideas for the following problem: I need to compare each consultant’s weekly (and monthly and annual) utilization, i.e. time logged compared to available time.

The data warehouse consists of one fact table (FactActivityLog) and 5 related dimension tables, the most important one for this problem being DimDate. In all similar examples that I have found online the issue is either comparing data in one fact table to data in the same table but for a different period (e.g. compare current year’s sales to previous year’s sales data) or comparing it to data in another fact table (compare sales to forecast). My problem is that I don’t know where to store the available time data.

I have come up with these possible solutions:
• Add an AvailableHours column to the DimDate table (not 100% sure what I would do from there, but it’s a start).
• Create another fact table FactAvailableHours and then follow online examples for comparing data from two fact tables (i.e. sales to forecast).
• Don’t store available hours in the data warehouse, but rather try to calculate it in the SSAS cube. (I’m using SSAS 2008).

Is there a best way of doing this? Any suggestions would be appreciated, thanks in advance!

I would say that the Number of Hours belongs in a fact table. I don't completely understand what it is you are trying to do but I think you have detailed information for the Time sheets. It sounds like the Grain of the information is by Day - one record per day per consultant. If so, then the you would have a measure or fact of # of Hours. The Available hours could be a Measure or it could be a an attribute of your Date Table. For example, if the Available hours is always 8 hours per work day, then you could create a column in Date Dimension populated with 8. But it seems a waste of space, as 1 day = 8 hours, which could be handled as a calculation.

I prefer your second option of creating a separate fact table for Available Hours. If you dig into your business requirements I suspect you will find that a consultant's availability varies as a result of things such as vacation, sick time, holidays, training and etc. This data tends to be different between consultants. If you don't already have it, you will likely need a "Time Entry Type" dimension that would be referenced by both fact tables (timesheet entry and availablility) which would allow you to net out vacation time charged from vacation time planned and not adversely affect your utilization metrics. If you have consultants in different locations, you might find that holiday schedules differ between locations so you may need a way to handle that too. If you just declare 8 hours of availability Monday-Friday and assign it in a dimension table your hands are tied.

Thanx for sharing

Data Entry India