Reg: Hrs Metric for Difference Between two dates excluding weekends and Holidays

Question

Hi Team,

How to calculate the Hrs metric for the Difference Between two dates for business Hrs (between 10 to 5 PM) also excluding weekends and Holidays in OBIEE reports .

Any suggestions/help for achieving this requirement OBI version we are using (12.2.1.4.0)

Regards,

[Deleted User] · Answer

Your time dimension should be modelled to include your notion of business days and business hours so that you can calculate this in a performant way.

Business days and business hours aren't standardized, so no tool can do this for you automagically. Doing it in code with nasty CASE WHENs is horrible and extremely heavy in performance.

The way to go is to already model your data accordingly so that every layer can simply resuse this in a performant way.