Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 44 Oracle Analytics and AI Sharing Center
- 20 Oracle Analytics and AI Lounge
- 278 Oracle Analytics and AI News
- 56 Oracle Analytics and AI Videos
- 16.2K Oracle Analytics and AI Forums
- 6.4K Oracle Analytics and AI Labs
- Oracle Analytics and AI User Groups
- 103 Oracle Analytics and AI Trainings
- 20 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
Duration To Hourly Buckets
Hello Everyone,
We have the below requirement of bucketing the duration into hourly buckets.
| SHOP_CODE | PUNCH_IN_TIME | PUNCH_OUT_TIME |
| 1111 | 4/17/2017 12:00 | 4/17/2017 12:00 |
| 1111 | 4/25/2017 12:00 | 4/25/2017 12:00 |
| 1112 | 11/19/2016 9:00 | 11/19/2016 13:25 |
| 1112 | 11/19/2016 14:10 | 11/19/2016 17:40 |
| 1113 | 4/10/2017 9:00 | 4/10/2017 14:00 |
| 1113 | 4/17/2017 10:00 | 4/17/2017 12:00 |
Output should look like :
| Shop | Business Date | 9 to 10 | 10 to 11 | 11 to 12 | 12 to 1 | 1 to 2 | 2 to 3 | 3 to 4 | 4 to 5 | 5 to 6 | 6 to 7 | 7 to 8 | 8 to 9 | 9 to 10 |
| 1111 | 4/17/2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1111 | 4/25/2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1112 | 11/19/2016 | 1 | 1 | 1 | 1 | 0.41 | 0.83 | 1 | 1 | 0.66 | 0 | 0 | 0 | 0 |
Any suggestion are appreciated.
Thanks !!
Answers
-
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
0 -
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).
0