Semi-Additive Measure Date and Time Dimension

1
votes

I have a fact table that i need to do a last child aggregation on, and there is a DimDate as well as DimTime attached..

I can (and have done in the past) semi-additive over a single 'time' dimension, but two is foxing me a bit... is it possible, is it supported in SSAS 2012? (or tabular, but not too fussed)

my requirements are:

  • report at Year..... all the way down to minute (or second)
  • if two facts are on the same day (DimDate)... the one with the greatest timestamp (DimTime) should be used for LastChild, not a sum of both.
  • performant, so 10 years, 365 days, 24 hours, 60 mins seems not sensible for a single dimension... just over 5m records. What about seconds?

if anyone can link me to some resource / book that would be amazing... i am seriously struggling with google here!

thanks!

1
sql-serverssasssas-tabular

1 Answers

0
votes

Semiadditive measures in multidimensional AS can only use one dimension as the one for the "last" element. Hence the only possibility would be to combine time and date into one dimension, but probably into different hierarchies.