Average aggregates by 15 minute, hour, day, month, year

View previous topic View next topic Go down

Average aggregates by 15 minute, hour, day, month, year

Post  mwassmer on Mon Aug 06, 2012 4:42 pm

I have a large number of temperature sensors, each of which reports its average reading over the past minute. I'm building a set of charts that will enable the user to view the 1-minute data, as well as the average temperature of each sensor by 15 minutes, by hour, by day, by month, and by year.

I have three questions:
1) Should I go with a single time/date dimension table with 1440 * 365 = 525,600 rows per year, or should I go with two dimension tables -- a date dimension table with 365 rows per year and a time dimension table with 1440 rows?
2) Even if I go with two dimension tables, I'd like to treat the date/time two dimensions as a single hierarchy so that the overall date/time grain is 1 minute (instead of a 1 minute grain in the time dimension table and a 1 day grain in the date dimension table). What is the recommended approach for doing this? I guess I'm wondering if two dimensions can be linked together in some way to create a single continuous hierarchy.
3) If some of my temperature sensors report their average temperature over the past minute and others report their average temperature over the past 5 minutes, can the data from both sensors be included in the same fact table, or does this violate the rule that the fact table should have a single grain? In other words, do data with different sampling resolutions have different grain?

mwassmer

Posts : 4
Join date : 2012-08-01

View user profile

Back to top Go down

Re: Average aggregates by 15 minute, hour, day, month, year

Post  ngalemmo on Mon Aug 06, 2012 10:43 pm

1. Two dimensions. There are only a few rare cases where time and date are combined. Yours is not one of them.

2. Why? The entity that relates time and date together is the fact table.

3. You can include both in the same fact if you include a duration measure.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Average aggregates by 15 minute, hour, day, month, year

Post  mwassmer on Tue Aug 07, 2012 5:33 pm

That makes sense. Thank you very much.

mwassmer

Posts : 4
Join date : 2012-08-01

View user profile

Back to top Go down

Re: Average aggregates by 15 minute, hour, day, month, year

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