Hour of Day - SSAS

Question

Hi, I would like to analyse data per hour using SSAS. The built in date dimension does not create any hour attributes.

Currently I am creating a new table with HourOfDay and HourOfDayName fields and will use this table to create a date dimension.

Could any one tell me if there is a common way of achieving time of day based analysis using SSAS05.

Thanks

Answer 1

Typically you create a separate day and time dimension. This is done to prevent the dimension from growing to be too large. You can add special descriptive attributes to the time dimension to designate time periods that are relevant to your business or type of analysis.(Different shifts in a factory for example). Then you would just use the time dimension to slice the data like any other dimension.

You can also build more interesting analysis paths by pivoting your design and use a time period i.e. duration as a measure. This often requires creating a new fact table or using Relational views.