How to use TRUNC in OBIEE Physical Joins

We probably might have end up in a scenario where in we need to use TRUNC within our physical join. Eg: TIME_DIM has a TIME_KEY which is of data type ‘DATETIME’ and a key in fact which is again of type ‘DATETIME’ that stores the exact Date and Time value.

There is a way to get this resolved:

The above would introduce TRUNC within the physical query and it would ensure that the join happens only on DATE factor and time is discarded. Please see below a snap of the query that was generated by OBIEE after making above changes:

But make sure that the data type of TIME_KEY and RPT_WO_COMPLETION_TIME is not ‘DATE’. If the data type is ‘DATE’ in RPD physical layer, then it would apply direct ‘CAST’ function instead of ‘TRUNC’.

 Hope this helps to resolve your issue without an ETL change but this will have a minor impact on your performance.

Please note, if you try to edit your existing RPD physical join to include 'Cast' function, you would probably get a syntax error and it does not allow you to add this function to your join. I believe this is an OBIEE product defect. You will have to remove the existing physical join and then re-establish the join with 'Cast' function and then you would be successfully able to edit it.