Tell me more ×
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. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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, but even with a No Aggregation measure type I'm getting an error when deploying saying that StartTime is a String value.

Is this at all possible? It doesn't seem like such a crazy thing to want to do...

share|improve this question
How are you storing StartTime? As a varchar()? – Max Vernon Mar 11 at 20:52
@MaxVernon No, as a Time(7) – Jeff Mar 11 at 20:53
1  
What versions of SQL Server source data and SSAS are you using? – JNK♦ Mar 11 at 20:54
1  
Well, per the SSAS docs TIME gets mapped to a string in SSAS... – JNK♦ Mar 11 at 20:58
1  
So your options are, make a new source field with a new data type (like a numerical type, maybe seconds since midnight as an int?) or something else I don't know about :) – JNK♦ Mar 11 at 21:00
show 4 more comments

1 Answer

up vote 2 down vote accepted

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

 
discard

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.