SSAS TimeSpan in Aggregation
I have a timespan in a datawarehouse representi开发者_开发知识库ng time spent by a user doing a task (not a time dimension, but a measure). In SQL I have this set as datetime. When this is pulled into SSAS it converts to a Date Type, and this is not usable in a cube measure aggregation. Do I need to convert the timespan into an integer (seconds), or is there a better way to do this?
EDIT:
I changed the data type in SQL to time(7) and it pulled into SSAS as a WChar, which is not summable.
I solved this as so:
- In SSIS I converted the timespan into elapsed seconds and stored it as an integer
- in SSAS I treated this measure as a SUM called "Duration In Seconds Sum", but hid it
- in SSAS I made a calculation called "Time Spent Sum" with the following expression:
iif([Measures].[Duration In Seconds Sum] = 0,
null,
Format(Int([Measures].[Duration In Seconds Sum]/86400), "0:") +
Format(
TimeSerial(0, 0, [Measures].[Duration In Seconds Sum]
- (Int([Measures].[Duration In Seconds Sum]/86400) * 86400)),"HH:mm:ss"
)
)
精彩评论