Time Dimension vs Measures in Minutes

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

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).

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

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.