Set Priority Group Numbers for Logical Table Sources
You can set priority group numbers to determine which logical table source to use for queries for which there is more than one logical table source that can satisfy the requested set of columns.
For example, you might have user queries that are fulfilled by both a data warehouse and an OLTP source. Often, access to an operational system is expensive, while access to a data warehouse is cheap. In this situation, you can assign a higher priority to the data warehouse to ensure that all queries are fulfilled by the data warehouse if possible.
The priority group of a given logical table source doesn't always ensure that a particular query is fulfilled by that source. Priority group assignments are only one of many factors used by the Oracle BI Server to determine which logical table source to select for a given query. However, the logical table source priority is the most significant metric and is considered before any other cost metric.
To assign priority group numbers, rank your logical table sources in numeric order, with 0 being the highest-priority source. You can assign the same number to multiple sources. For example, you can have two logical table sources in priority group 0, two logical table sources in priority group 1, and so on. Often, only two priority groups are necessary (0 and 1).
Assigning priority groups is optional. All logical table sources are set to priority 0 by default. It's important that you don't use priority groups as a method fine tuning the choice of logical table sources used to answer queries. Oracle BI Server tries to automatically use the most optimal logical table sources, but only within the same priority group. When you set a different priority group to each logical table source, it might cause Oracle BI Server to use suboptimal logical table sources.
In some situations, you might want to allow users to reverse the normal logical table source priority ranking at query time. To accomplish this, you can use a combination of session variables and request variables with logical table source priority groups. This feature provides a way to dynamically select a source at run time, depending on user preference.
To enable this dynamic selection, you must first create the REVERSIBLE_LTS_PRIORITY_SA_VEC
session variable in the repository. Create this variable as a string vector session variable that uses a row-wise session initialization block. REVERSIBLE_LTS_PRIORITY_SA_VEC
should list the subject areas for which you want to allow users to reverse the logical table source priority ranking. You must define this variable to enable priority ranking reversal.
After you've defined the set of subject areas for which you want to allow priority ranking reversal, users can include the request variable REVERSE_LTS_PRIORITY
with their queries to reverse the logical table source priority ranking. You can set this request variable to 1 to reverse the logical table source priority, or 0 to keep the normal logical table source priority.
As an alternative to using a request variable at query time, you can define a predetermined set of subject areas for which the logical table source priority is permanently reversed. To do this, create the session variable REVERSED_LTS_PRIORITY_SA_VEC
in the repository. Create this variable as a string vector session variable that uses a row-wise session initialization block. REVERSED_LTS_PRIORITY_SA_VEC
should list the subject areas for which you want the logical table source priority set to permanently reversed.
REVERSIBLE_LTS_PRIORITY_SA_VEC
You could create a table called SA_TABLE that contains two columns: SUBJECT_AREA_NAME and REVERSIBLE. This table could contain rows mapping subject area names to their reversible values (1 or 0), as follows:
SUBJECT_AREA_NAME | REVERSIBLE |
---|---|
my_sa_1 |
1 |
my_sa_2 |
0 |
Then, you would create a string vector session variable called REVERSIBLE_LTS_PRIORITY_SA_VEC with a row-wise session initialization block. The initialization string for this initialization block is similar to the following:
SELECT 'REVERSIBLE_LTS_PRIORITY_SA_VEC', SUBJECT_AREA_NAME FROM SA_TABLE WHERE REVERSIBLE=1
The image shows the Session Variable Initialization Block dialog for this example.