Change the Default Selection Criteria for Dimension Logical Table Sources

You can change the default logical table source selection criteria to favor dimension logical table sources that are at the same level as the fact logical table source before considering the higher level logical table source.

Create a session variable and name it DIMENSION_LTS_JOIN_RESTRICTIONS. Set this session variable to PREFER_SAME_LEVEL.

If a suitable dimension logical table source at the same level as the fact logical table source doesn't exists, then the Oracle Analytics query engine selects the highest level dimension logical table source that's joinable to the fact. These factors are only considered after priority group and join cost.

The PREFER_SAME_LEVEL value for the DIMENSION_LTS_JOIN_RESTRICTIONS session variable sets the following criteria for selecting the dimension logical table source to answer the query:

• Logical table source priority group

• Lower join cost

• Same level as the fact logical table source

• Higher level than other dimension logical table sources if no other logical table source is at the same level as the fact logical table source

When DIMENSION_LTS_JOIN_RESTRICTIONS is set to NONE, the default value, you can join fact logical table sources to a higher level dimension logical table source even if there is another joinable dimension logical table source at the same level as the fact.