Duration To Hourly Buckets

Question

Hello Everyone,

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

SHOP_CODEPUNCH_IN_TIMEPUNCH_OUT_TIME11114/17/2017 12:004/17/2017 12:0011114/25/2017 12:004/25/2017 12:00111211/19/2016 9:0011/19/2016 13:25111211/19/2016 14:1011/19/2016 17:4011134/10/2017 9:004/10/2017 14:0011134/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 1011114/17/2017000000000000011114/25/20170000000000000111211/19/201611110.410.83110.660000

Any suggestion are appreciated.

Thanks !!

John_K · Answer

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

Thomas Dodds · Answer

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