Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  mwassmer 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  mwassmer Tue Aug 07, 2012 5:33 pm

That makes sense. Thank you very much.

mwassmer

Posts : 4
Join date : 2012-08-01

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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