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