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

View previous topic View next topic Go down

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

Post  heilet on Mon Mar 16, 2009 10:53 am

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!

heilet

Posts : 1
Join date : 2009-03-16

View user profile

Back to top Go down

Number of hours is a fact

Post  Jeff Smith on Tue Mar 31, 2009 4:03 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Number of hours is a fact

Post  bgray on Tue Mar 31, 2009 9:24 pm

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.

bgray

Posts : 8
Join date : 2009-02-10

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum