开发者

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" ) )

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜