Time Dimension vs Measures in Minutes

View previous topic View next topic Go down

Time Dimension vs Measures in Minutes

Post  LR on Mon Aug 16, 2010 4:59 pm

Hi All,

I would like to design the time in a multidimensional model.
The classic calendar date dimension (day, week, month, year) should be applied here. Besides that, I have a fine grain: the minute.
More specifically, let's suppose I have 10.000 industry machines which generate measures every 5 min.
Moreover, I have to keep measures up to 4 years.

What kind of model do you recommend for this solution?

Thanks in advance,

LR







LR

Posts : 5
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Time Dimension vs Measures in Minutes

Post  ngalemmo on Mon Aug 16, 2010 5:48 pm

Is there anything significant about the time of day (other than the time itself) that impacts analysis and reporting? If the answer is no, then simply storing the time as a value in the fact table (i.e. a degenerate dimension) should suffice. I would still have a date dimension.

If there are attributes related to time of day, and if such attributes are independent of the date itself, then there should be a time of day dimension, probably one row per minute for 24 hours (1440 rows).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Time Dimension vs Measures in Minutes

Post  LR on Mon Aug 16, 2010 10:58 pm

Hi ngalemmo!

I got it!
I appreciated a lot your help.

Another quick question: Since my fact table may keep 4,204,800,000 rows (5 min data for 4 years * 10.000 devices), what are the best approaches to handle it effectively? Partitioning? How?

Thanks!

LR

LR

Posts : 5
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Time Dimension vs Measures in Minutes

Post  ngalemmo on Tue Aug 17, 2010 11:51 am

Partitioning, of course. The most common method is to partition by date. Choose a time interval that best suits you load strategy. I would not have a partition larger than a month, and would probably consider 2 week intervals, given the volume. Date partitions allow wide time frame queries to work in parallel and gives you fairly even data distribution. They also provide an easy way to roll off old data and allow you to implement partial backup strategies. If you have a tiered disk architecture, date partitions make it easy to move older data (say, after 2 years) to less expensive, slower disk storage.

If you are using Oracle, define local indexes and use the parallel option.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Time Dimension vs Measures in Minutes

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