1. Date dimension
2. Hour Bucket dimension
3. Fact table grain: shop, date, hour bucket
Fact measure: duration in hours
^ then model in RPD
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).