Logical table sources define the mappings from a single logical table to one or more physical tables.
The physical to logical mapping can also be used to specify transformations that occur between the Physical layer and the Business Model and Mapping layer, as well as to enable aggregate navigation and fragmentation.
You can view logical table sources in the Business Model and Mapping layer pane, and from the Sources tab of the Logical Table dialog.
Logical tables can have many physical table sources. A single logical column might map to many physical columns from multiple physical tables, including aggregate tables that map to the column if a query asks for the appropriate level of aggregation on that column.
This section contains the following topics:
The system uses criteria to select the fact logical table source to answer a query.
The following criteria is listed from the highest precedence to the lowest precedence:
Logical table source priority group. A higher priority logical table source is used before a lower priority logical table source, even if the higher priority source is at a more detailed grain. Note that a lower number indicates higher priority. See Setting Priority Group Numbers for Logical Table Sources.
The grain of the logical table source . A higher-grain logical table source is used before a lower-grain logical table source, given that the priority group numbers are the same.
Number of elements at this level. If the grains are not comparable, the number specified for the Number of elements at this level field is considered.
For example, assume you have the following two logical table sources with grains that are not comparable: LTS1(year, city) and LTS2(month, state). If you have 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.
First logical table source listed. If all other criteria are equal, the first logical table source listed is selected, as shown in the Business Model and Mapping layer.
Every column in a query is sourced from a single logical table source based on these expected performance factors. Queries are not load-balanced across multiple logical table sources.
After the appropriate fact logical table sources have been selected, the system selects the best dimensional logical table sources to answer the query.
The following criteria are used by the system to select the dimension logical table source, listed from the highest precedence to the lowest precedence:
Logical table source priority group. Like fact logical table sources, a higher priority dimension logical table source is used before a lower priority dimension logical table source. Note that a lower number indicates higher priority.
Lower join cost. The dimension logical table source with the lowest join cost is selected before dimension logical tables sources with higher join costs, given that the priority group numbers are the same.
Higher level. If the priority group and join cost are the same, the higher level logical table source is chosen, because that logical table source will likely require fewer rows to be joined.
You can optionally 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.
To do this, set the DIMENSION_LTS_JOIN_RESTRICTIONS session variable to PREFER_SAME_LEVEL.
If there is no suitable dimension logical table source at the same level as the fact logical table source, then the Oracle BI Server selects the highest level dimension logical table source that is joinable to the fact. Note that 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.
It is important to ensure that the data in your sources is consistent.
For example, your year-level logical table source and your month-level logical table source for your time dimension should cover the same time period.
Be aware that consistency issues with data in your sources might become apparent when you issue queries that override null suppression, in other words, when you create an analysis in Oracle BI Answers and select Include Null Values. For example, some aggregate tables might not include the dimension records that correspond to the null fact values, such as a yearly sales aggregate table that does not include years for which there were no sales. All years in the year dimension must exist for the null values to be included in the result.