If you wish to show data that is distributed and aggregated over time in a day ? for example what is the average call volume by hour of day over the past month then you need to create a time specific dimension in your datamart fact table.

This will support the roll-up of time periods into more summarized groupings for reporting and analysis, such as 15-minute intervals, hours, or AM/PM. They also could reflect business-specific time groupings, such as the weekday morning rush period for flight activity. This time dimension will let you roll up data based on the hour of the day across multiple days.

Timebased Reporting Example:




Average calls per hour of day


Issue:
If using a traditional time stamp to capture event data such as call volume then it is difficult to aggregate this for every day over the past month etc. Since the timestamp will include that date as well making it difficult to separate out time as an isolated attribute.

Isolate the Time Attribute
When building your fat table it is best to create an isolated time attribute based on the timestamp of the underlying data. This time stamp can be as granular as you wish. For example the image below gives a very simple view of the relationship between your fact and time dimension table:






The time dimension key in the fact dimension could be based on seconds from midnight.