Help with Time dimension in data mart

architect · #1 (**permalink**) 10-26-07, 15:58

Designing a data mart for a messaging application. End users want to be able to display a histogram with the ability to show message volume in 5, 10, 15, 20, 25, 30, 1 hr intervals. In this case, would you populate the Time dimension with date/time data in 5-min. intervals. So each hour would be 12 records in the time dim. This results in approx. 105,120 rows per Year. I'm thinking of keeping the granularity to a Day in the time dimension, and just using an attribute in the fact table to note the time in the 5-minute increment.

Any ideas?

-a

r937 · #2 (**permalink**) 10-26-07, 16:24

conceptually, it is six of one and a half dozen of the other

(do that twice, and you gots a two-four

)

have you scoped out what kind of sql will drive the graphs both ways?

architect · #3 (**permalink**) 10-26-07, 17:37

Just had a meeting...turns out, we need down to the minute granularity. I'll be keeping the granularity on the Time dimension to Day; and then storing the Hour and Minute as facts in the fact table.

-a