1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Manage Logical Table Sources
  4. How Are Dimension Logical Table Sources Selected to Answer a Query?

How Are Dimension Logical Table Sources Selected to Answer a Query?

After the fact logical table sources are selected, Oracle Analytics selects the best dimensional logical table sources to answer a query.

See How Are Fact Logical Table Sources Selected to Answer a Query?

Oracle Analytics uses the following criteria to select the dimension logical table source. The criteria are listed from the highest precedence to the lowest precedence:

  • Logical table source priority group - A higher priority dimension logical table source group is used before a lower priority dimension logical table source group. A lower group number indicates higher priority. See About Assigning Logical Table Sources Priority Order.

  • Lower join cost - If all dimension table sources have the same priority assigned to them, then the dimension logical table source with the lowest join cost is selected before dimension logical tables sources with higher join costs.

  • Higher level - If the priority group and join cost are the same, then the higher level logical table source is used because that logical table source could require joining fewer rows.

  • Number of elements at this level setting - If the grains aren't comparable, then the number specified for the Number of elements at this level field is considered.

    For example, suppose you've the following two logical table sources with grains that aren't comparable: LTS1(year, city) and LTS2(month, state). If you've 10 years, 100 cities, 120 months, and 9 states, the worst case size of LTS1 is 10 x 100 = 1000, and the worst case size of LTS2 is 120 x 9 = 1080. In this scenario, LTS1 is selected because the source with the lowest estimated number of total elements is assumed to be the fastest.