Aggregating data at different levels of granularity

View previous topic View next topic Go down

Aggregating data at different levels of granularity

Post  Hkbidw on Mon Mar 14, 2016 3:18 am

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!


Hkbidw

Posts : 1
Join date : 2016-03-14

View user profile

Back to top Go down

Re: Aggregating data at different levels of granularity

Post  ngalemmo on Mon Mar 14, 2016 8:14 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Aggregating data at different levels of granularity

Post  nick_white on Mon Mar 14, 2016 8:17 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Aggregating data at different levels of granularity

Post  Vishwas on Wed Mar 16, 2016 2:47 pm

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.

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Aggregating data at different levels of granularity

Post  BoxesAndLines on Thu Mar 17, 2016 3:21 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Aggregating data at different levels of granularity

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