Setting Priority Group Numbers for Logical Table Sources
You can set priority group numbers to determine which logical table source should be used 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 can be 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 does not always ensure that a particular query will be 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 is important that you do not 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 have 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. This request variable can be set 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 also define a predetermined set of subject areas for which the logical table source priority should be 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 to be permanently reversed.
See Creating Session Variables.
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 could be 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.
