Average aggregates by 15 minute, hour, day, month, year
2 posters
Page 1 of 1
Average aggregates by 15 minute, hour, day, month, year
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?
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
Re: Average aggregates by 15 minute, hour, day, month, year
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.
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.
Re: Average aggregates by 15 minute, hour, day, month, year
That makes sense. Thank you very much.
mwassmer- Posts : 4
Join date : 2012-08-01
Similar topics
» 15 month fiscal year
» SSAS 2008R2 - Creating "Year over Year Growth %" or "Year To Date"
» Store Business Hours in Time Of Day dimension?
» Sales list year over year performance
» Building Aggregates from Aggregates
» SSAS 2008R2 - Creating "Year over Year Growth %" or "Year To Date"
» Store Business Hours in Time Of Day dimension?
» Sales list year over year performance
» Building Aggregates from Aggregates
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum