Aggregating data at different levels of granularity

Dear All,

We have a FACT Table which is recording data for each staff each day. The measure stored is the number of hours worked.

The report produced is a list of staff who have not completed 12 hours in a day with the date on which they have not completed 12 hours, however the users also want to see, the total number of hours the employee has worked in a week.

How can we produce a report which shows like ID, Name, Date, Hours Worked (Less than 12) and Hours Worked during the week. The fact table is at a daily grain and computes the measure at the daily grain.

I though of creating a fact table which is daily grain but I compute the worked hours for the week and store it each day.

This by design is not correct because the measure is not true to the grain. Thanks for the advise folks!

It is two queries summarized and joined on employee. You can create an aggregate if you want, but a view can do the same thing.

Why do you need to store the total number of hours worked in a week? You can just calculate it when you run your report
If you do need to store the total hours worked in a week then you'd need another fact table to hold it because, as you stated, hours per week is at a different grain to hours per day

if your issue is performance then you might want to create aggregated fact at week level other wise weekly reporting can be done using daily fact.

More importantly, why do you want to see a report for folks who didn't work at least 12 hours in one day!!??