Post reply

Semi-Additive over Date AND Time Dimensions?

  • October 16, 2014 at 9:10 am

    #397514

    Hi,

    I am continually thinking that this must be possible... but how do you do semi-additive measures over DimDate and DimTime, i just can't figure it out.

    The only way i can potentially see this working is with a Date and Time dimension merged... but that would just be hideous! Assuming you go down to the minute, it's still over half a million members per year...

    So, if you have a fact with a grain of say... hour. How do you attach two dimensions (Date and Time), and still get it to be semi-additive...

    Thanks!

  • October 17, 2014 at 1:23 am

    This was removed by the editor as SPAM

  • October 17, 2014 at 1:51 am

    #1753050

    Hi,

    I've considered this in the past, but not really been able to get it to work....

    I considered that if you set up a DimDate as the 'time' dimension within SSAS, and then associated it to your Fact tables accordingly and set a measure as LastChild etc.... you could THEN take the resulting measure (here assume 'TheRealSemiAdditive') and put some sort of wrapper around it such as the following for each other dimension that you want to NOT aggregate across..

    CREATE DYNAMIC SET CURRENTCUBE.[CurrentTimeRange]

    AS [Time].[Minute].[Minute];

    CREATE MEMBER CURRENTCUBE.[Measures].[LastValue]

    AS ([Measures].[TheRealSemiAdditive], tail(existing CurrentDateRange,1).item(0))

    Of course include 'NonEmpty' if you want the very last value populated...

    I don't know, i'm just revisitng this topic because I thought about it the other day, but I'm pretty sure i tried to go down this route one before without much joy.

    This just seems a bit hacky, and probably slow? Not sure there's another option?

    I'll have a ponder and come back 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply