Is it possible to have times as measures in a cube?
We're trying to view employee start times by day, aggregating as an average over time. Even with a No Aggregation measure type I'm getting an error when deploying saying that StartTime is a String value. It is stored as time(7).
Is this at all possible? It doesn't seem like such a crazy thing to want to do?
We are using SQL Server 2008 R2 Enterprise.
The basic issue is that the SQL type time is mapped to string in SSAS.
So the way I did this came from JNK's comments, so all props to him.
In order to use time formatting correctly, I return the following
(SELECT DATEDIFF(n, CAST('' AS Time), dbo.FactWorkDaySummary.StartTime) / 1440.0) AS StartTime
in my view.
In my cube I have a calculated measure to calculate the average appropriately, and set the Format_String property to "short time". This works, because dividing by 1440.0 converts the time into a float representing the fraction of the day that those minutes represent, which is the representation of the datetime. This can also, handily, be averaged easily.
Just make sure you don't display the date, because it's `30/12/1899', which isn't really applicable.
