About Logical Table Sources
Logical table sources define the mappings from a single logical table to one or more physical tables.
Use the physical to logical mapping to specify transformations that occur between the Physical layer and the Business Model and Mapping layer, and to enable aggregate navigation and fragmentation.
You can view logical table sources in the Business Model and Mapping layer.
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 such as if a query asks for the appropriate level of aggregation on that column.
This section contains the following topics:
How Fact Logical Table Sources Are Selected to Answer a Query
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. A lower number indicates a higher priority. See Set 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 aren't comparable, the number specified for the Number of elements at this level field is considered.
For example, assume 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.
-
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 aren't load-balanced across multiple logical table sources.
How Dimension Logical Table Sources Are Selected to Answer a Query
After the appropriate fact logical table sources have been selected, the system selects the best dimensional logical table sources to answer the query.
Oracle BI Server 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 is used before a lower priority dimension logical table source. 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 could require joining fewer rows.
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.
In Model Administration Tool, set the DIMENSION_LTS_JOIN_RESTRICTIONS 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 Oracle BI Server 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.
Consistency Among Data in Multiple Sources
It's 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 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 doesn't include years with no sales. All years in the year dimension must exist for the null values to be included in the result.