Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.