Add 'BETWEEN_SUPPORTED' sql rule to the OTBI RPD to improve date range reporting performance

Brian Deans

Organization Name

UK Home Office

Description

The on-premise version of the OBIEE RPD contains a number of specific sql support Feature options set at Physical data source level. (Screenshot image "obiee RPD - Feature- BETWEEN support.jpg" attached as an example.

After a recent discussion with an Oracle Engineer via an OWC, it was confirmed to us that the cut-down version of the OBIEE RPD that was ported to create the original OTBI RPD does not have the BETWEEN_SUPPORTED option included.

This proposal is to add and enable the BETWEEN_SUPPORTED feature in the OTBI RPD. 

 

Use Case and Business Need

Our client has legislative requirements to report upon and publish a large number of metrics. Many of these are required for all records with a date within a month range or a 3 month range.

Current date range filtering options with reporting often results in there reports failing to complete within the permitted time limit.

The business need is for a solution which would result in improved speed and consistency of any reporting based on a date range filter. (For any of the fusion modules for which we currently maintain OTBI reporting, this is a fairly substantial set of reports.)

More details

As stated, our client has legislative requirements to report upon and publish a large number of metrics. Many of these are required for all records with a date within a month range or a 3 month range.

These reports use a "Between" filter on the relevant date field, with low and high date values.

At an xml code level this translates to the statement (as an example): "AND ("- Payment Information"."Check Date" BETWEEN date '2020-12-01' AND date '2020-12-31')"

However, if I look in the session log for the report execution, the physical sql generated by the BI server and submitted to the database is as follows: " AND ( NOT ( (V360131283.CHECK_DATE < TO_DATE('2020-12-01', 'yyyy-mm-dd hh24:mi:ss') ) ) )  AND ( NOT ( (V360131283.CHECK_DATE > TO_DATE('2020-12-31', 'yyyy-mm-dd hh24:mi:ss') ) ) )"  

This sql appears to be inefficient, inconsistent and counterintuitive. Our client's reports breach the report execution time limit inconsistently for the same date ranges when executing it. These reports can sometimes run to a finish for larger date ranges, whilst timing out for smaller date ranges which are bounded within the same larger ones.

Adding a features which supports the use of the "between" function at sql level would make all such date-range filtered reporting more efficient, more consistent and more reliable.

Original Idea Number: c88a33e538