Recently I was tasked with building a cube and SSRS reports that displayed time based fact measures. Things like average delivery time, start time, etc for a delivery route. Since I had never dealt with time based measures before I did some scouring on the web and ran across this post http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx . Thank goodness for this because I was having a heck of a time figuring out how I was going to get this data into SSAS and be able to display it since SSAS ignores anything date or time related for a fact measure.
So after following the instructions in the post, it worked brilliantly! I was able to browse the data at the date level and it displayed the time the driver loaded his truck and the time the driver made it to his first customer and so forth. I was ecstatic! However I ran into a small issue when I wanted to browse the data using a geography based dimension. This dimension did tie to the fact table using the a non key id, but I noticed that the time measure was doing some funky things when rolling up and down. It finally dawned on me that I had defined my time measure as an AverageofChildren. Since this aggregate function only works with date time, I was stuck. But only for a moment… I decided to load the same time fields again, except this time I would not specify a format of hh:mm. I would just let the field load as a float (decimal). Now I was able to aggregate the time up and then divide it by the row count to get an average start time up and down the geography hierarchy. Again I was ecstatic. That is until I tried to build a reporting service report against the data….