The following scenario is common: the Logical Fact is based on Transaction Tables, with Transaction date stamped on those tables, and the Logical Dimension is Date-effective. These scenarios can be resolved using a BETWEEN join, where the Logical Fact Transaction date is between the Effective dates of the Date effective dimensional entity. Special support in Composite view object API handle this kind of join.
To leverage this feature provided by the Composite view object API, you must pass specific hints through Custom Properties in the view link between the Fact entity and Dimension entity at the Oracle ADF Model layer. Following are the properties (name-value pairs) that must be set in the view link explicitly for the Composite view object API to understand the hints being passed and interpret them correctly.
EFFECTIVE_DATE_FILTER_DRIVING_ATTR = Transaction Date
EFFECTIVE_DATE_FILTER_END = Dimension Effective End Date
EFFECTIVE_DATE_FILTER_START = Dimension Effective Start Date
As shown in Figure 58, Custom Properties, the values for the custom properties represent entity attribute names and should be expressed as fully qualified names. The Composite view object API interprets these Custom Properties and generates a query similar to that shown below.
WHERE <Fact_Entity>.Transaction_Date IS BETWEEN <DE_Dimension_Entity>.EFFECTIVE_START_DATE AND <DE_Dimension_Entity>.EFFECTIVE_END_DATE
This feature does not call for any change in the Oracle BI repository. Do all configurations mentioned in the Oracle ADF Model layer.