Query redirection provides a mechanism for BI Server to determine the set of logical table sources (LTS) applicable to a logical request whenever a request can be satisfied by more than one LTS.
The Oracle BI repository shipped in Oracle Fusion applications contains metadata content for real-time reporting analysis (using Transactional Business Intelligence) and historical reporting (using BI Applications).
The logical and physical layer within the Oracle BI repository is shared between Transactional Business Intelligence and BI Applications.
The subject areas in the presentation layer can be built for real-time analysis using the transactional tables, for historical analysis using the Business Analytics Warehouse, or for both. Based on the subject area, the underlying query should use the common logical and physical content and be able to source the data from the transactional tables, the historical reporting, or both. The underlying feature that facilitates this is called query redirection.
Two settings are pre-configured in the metadata content for query redirection to work as expected:
Set Priority group in the LTS.
Setting Priority group numbers in the LTS enables you to determine which logical table source should be used for queries in cases where there are multiple logical table sources that can satisfy the requested set of columns in the query.
The values being used for priority group are 0 through 5 for BI Applications and Transactional Business Intelligence respectively. The lower the priority group value, the higher priority it takes for being selected as the underlying source.
Set the session variable (REVERSED_LTS_PRIORITY_SA_VEC) and Initialization Block (IB_REVERSED_LTS_PRIORITY_SA_VEC)
A string vector session variable (REVERSED_LTS_PRIORITY_SA_VEC) is defined and initialized with subject area names for which the logical table source priority should be permanently reversed.
An initialization block (IB_REVERSED_LTS_PRIORITY_SA_VEC) is pre-configured to use the "FBI_OLTP" connection pool. The FBI_OLTP connection pool is based on two repository variables FBI_OLTP_DSN and FBI_OLTP_USER. See “Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition” for further information on connection (pool) setup and Setting Priority Group Numbers for Logical Table Sources.
Depending on the type of implementation, you might need to customize query redirection. Typically, you might need these customization types :
Add a Transactional Business Intelligence subject area.
Create a Transactional Business Intelligence subject area in Presentation layer.
Ensure that the underlying logical sources, on which the Presentation layer is based, has at least one real-time source.
Add the new Transactional Business Intelligence subject area into the query redirection initialization block variable (IB_REVERSED_LTS_PRIORITY_SA_VEC), similar to other subject areas that are already part of the variable.
Modify the existing Transactional Business Intelligence subject area name.
Identify the Transactional Business Intelligence subject area for which you want to modify the name.
Modify the Transactional Business Intelligence subject area name in query redirection initialization block variable (IB_REVERSED_LTS_PRIORITY_SA_VEC).
Add a logical table source apart from the regular Oracle BI repository modeling that is needed to add a new logical table source. Set the Priority Group to a value based on the LTS being added. For an aggregated source, initialize the priority group to a value less than 5. For a nonaggregated or real-time source, initialize the priority group to a value greater than the aggregated source LTS.
Reorder logical table source: Exercise caution when reordering logical table sources, because reordering could affect how the query redirection works. Use the following general guidelines to aid you in setting logical table source priorities:
Keep all of the logical table sources with a lower priority group on top followed by the logical table sources with the next-highest priority groups.
Note: | The lower the priority group value, the higher the priority it takes for being selected as the underlying source. |
When more than one LTS has the same priority group mapped to a column being selected, the LTS with a lower cost is chosen. When the cost is the same, the LTS list higher in the priority group is used for the query.
When you are running a query with multidimensions and with a fact, the LTS that is joined (physically) in the physical layer would be used for query generation.
For more information, refer to the section Managing Logical Table Sources in “Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition”.