2 Replies Latest reply on Jun 20, 2017 1:32 PM by John_K

    Duration To Hourly Buckets

    3214100

      Hello Everyone,

       

      We have the below requirement of bucketing the duration into hourly buckets.

       

         

      SHOP_CODEPUNCH_IN_TIMEPUNCH_OUT_TIME
      11114/17/2017 12:004/17/2017 12:00
      11114/25/2017 12:004/25/2017 12:00
      111211/19/2016 9:0011/19/2016 13:25
      111211/19/2016 14:1011/19/2016 17:40
      11134/10/2017 9:004/10/2017 14:00
      11134/17/2017 10:004/17/2017 12:00

       

       

      Output should look like :

       

              

      ShopBusiness Date9 to 1010 to 1111 to 1212 to 11 to 22 to 33 to 44 to 55 to 66 to 77 to 88 to 99 to 10
      11114/17/20170000000000000
      11114/25/20170000000000000
      111211/19/201611110.410.83110.660000

       

       

       

      Any suggestion are appreciated.

       

       

      Thanks !!

        • 1. Re: Duration To Hourly Buckets
          Thomas Dodds

          My suggestion:

           

          1. Date dimension

          2. Hour Bucket dimension

          3. Fact table grain:  shop, date, hour bucket

               Fact measure: duration in hours

           

          ^ then model in RPD

          • 2. Re: Duration To Hourly Buckets
            John_K

            Agree totally - the most flexible (and correct) way to do this would be model as a separate dimension. However if you absolutely cannot do that (for whatever reason) then you can build a logical column on this and simply subtract the two dates, convert to hours and "bucket" with a case statement. If you don't have RPD access then you can of course do this in the presentation layer (which has bucketing mechanisms anyway) and save as a saved column (if your version of OBIEE supports that).