1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Manage Logical Table Sources
  4. Work With Logical Table Source Priorities

Work With Logical Table Source Priorities

This topic provides information to help you understand and assign logical table source priorities.

Topics:

About Assigning Logical Table Sources Priority Order

Priority numbers determine which logical table source is used to answer a query.

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.

Although the logical table source priority is the metric that the Oracle Analytics query engine considers before any other cost metric, the table source's priority group doesn't always determine that a particular query is fulfilled by that source. The Oracle Analytics query engine uses other factors to determine which logical table source to use for a query. See How Are Fact Logical Table Sources Selected to Answer a Query? and How Are Dimension Logical Table Sources Selected to Answer a Query?

To assign priority group numbers, you rank your logical table sources in numeric order, with 0 being the highest-priority source. You can assign the same number to multiple sources to create a priority group. For example, you can have two logical table sources in priority group 0, two logical table sources in priority group 1, and so on. In most cases only two priority groups (0 and 1) are needed.

Assigning priority groups is optional.

It's important that you don't use priority groups as a method of fine tuning the choice of logical table sources used to answer queries. The Oracle Analytics query engine 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 the Oracle Analytics query engine to use suboptimal logical table sources.

Set the Logical Table Sources Priority Order

Sometimes a logical table contains more than one table source that can be used in a query. In such cases, you can set priority numbers to determine which logical table source is used in a query.

You can assign the same priority number to more than one source table to create a priority group.
See About Assigning Logical Table Sources Priority Order.
  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer.
  4. In the Logical Layer pane, browse for and double-click the table with the sources that you want to assign priority groups to.
  5. In the logical table, click the Sources tab.
  6. In the sources list table, click the source table that you want to assign query groups to and then click Detail view to open the properties pane.
  7. Scroll to General and click the Priority field and enter a priority group number.
  8. In the sources list table, click another source table that you want to assign query groups to and then click Detail view to open the properties pane.
  9. Scroll to General and then click the Priority field and enter a priority group number.
  10. Click Save.

Reverse the Table Source Priority Ranking at Query Time

You can use session variables and request variables with logical table source priority groups to reverse the logical table source priorities at query time. This method provides a way to dynamically select a source at run time, depending on user preference.

  1. To enable the dynamic selection, first create the REVERSIBLE_LTS_PRIORITY_SA_VEC session variable in the semantic model. 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.
  2. After you've defined the set of subject areas where 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.
  3. 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. 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 where you want the logical table source priority set to permanently reversed.
  4. 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 - my_sa_1; REVERSIBLE - 1
    • SUBJECT_AREA_NAME - my_sa_2; REVERSIBLE - 0
  5. Then, 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