About
Logical table sources define the mappings from a single logical table to one or more physical tables. A logical table contains one or more logical table source. The mapping between physical columns and logical columns are done in this element.
The definition of the logical table source is used:
- to specify transformations/calculations (in the column mapping tab),
- to enable aggregate navigation and fragmentation (in the content tab)
The Content tab of the Logical Table Source is used to define :
- any aggregate table content definitions (specify the grain of the physical tables),
- fragmented table definitions for the source,
- and WHERE clauses (if you want to limit the number of rows returned).
Articles Related
Properties
General tab
Priority group
You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.
For example, you might have user queries that can be fulfilled by both a data warehouse and an OLTP source. Often, access to an operational system is “expensive,” while access to a data warehouse is “cheap.” In this situation, you can assign a higher priority to the data warehouse to ensure that all queries are fulfilled by the data warehouse if possible.
Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.
| Priority Group | Designation |
|---|---|
| 0 | the highest-priority source (the default) |
| 1 | a lowest-priority source |
You can change the order of the priority with the system session variables:
- OBIEE - System session variables (reserved variables) (permanently)
Unfortunately, the priority group is a parameter that is applied on the subject area level, not on the logical table source level.
Why you may still have trouble to run a data model using this parameter:
- A bad priority group value
- A logical column is only mapped to a one physical table source (and not to the other)
- A logical table source table is mapped to the other (bad) physical table source
- The Logical Level is not defined in one logical table source. Therefore no join can be done.
- Your analytic report uses two different subject area and they have not the same priority group value.
Joins
Content Tab
Aggregation Content
You can define the aggregation (ie the group by) of:
- or by logical column
It helps you to define an aggregate navigation
Fragmentation content
This source should be combined with other sources at this level
You use this option to define that this table source is a fragment of the data at this level and must be combined with an other table to complete the domain.
For example:
- one logical table source might point to records for people with last names A-M, while a second logical table source might point to records for people with last names N-Z.
- one table is an aggregation of a set of the data and the other table is an aggregate view of the detailed table that complete the domain (ie add the other fragment).
Select distinct values
If the values for the source are unique, select the option Select distinct values.
How to
Dynamically select/reverse a source at run time
You might want to allow users to reverse the normal logical table source priority ranking at query time to allow them to pick up the source (for instance between an OLTP and a OLAP database).
To accomplish this, you must:
- set up the REVERSIBLE_LTS_PRIORITY_SA_VEC session variables to list the subject areas for which you want to allow users to reverse the logical table source priority ranking.
- and set a request variable REVERSE_LTS_PRIORITY with a value of 1 to reverse the logical table source priority ranking.
Optionally, if you want to permanently reverse a subject area, you can set the reversed_lts_priority_sa_vec session variable.
REVERSIBLE_LTS_PRIORITY_SA_VEC
REVERSIBLE_LTS_PRIORITY_SA_VEC is string vector session variable that uses a row-wise session initialization block that can trigger a table:
| SUBJECT_AREA_NAME | REVERSIBLE |
|---|---|
| my_first_subject_area | 1 |
| my_second_subject_area | 0 |
with for instance the following sql:
SELECT 'REVERSIBLE_LTS_PRIORITY_SA_VEC', SUBJECT_AREA_NAME FROM SA_TABLE
WHERE REVERSIBLE=1
REVERSE_LTS_PRIORITY
REVERSE_LTS_PRIORITY can be set to:
- 1 to reverse the logical table source priority,
- or 0 to keep the normal logical table source priority.
REVERSED_LTS_PRIORITY_SA_VEC
To permanently reverse the logical table source priority of a subject area, you can set up the session variable REVERSED_LTS_PRIORITY_SA_VEC in the repository:
- as a string vector session variable that uses a row-wise session initialization block.
- that list the reversed subject areas.
Support
nQSError: 46043 - No matching table for expr
Foutdetails
Foutcodes: OPR4ONWY:U9IM8TAC:OI2DL65P
Toestand: HY000. Code: 2053177616.
[NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 46043] Internal error: No matching table for expr F1 Fact Target.FIND_ID,
File server/Query/Optimizer/SmartScheduler/PhysicalRequestGenerator/Src/SQOSPSimplifyJoin.cpp, line 118.
(HY000)
SQL-opdracht: SELECT Kpi."Kpi Code" saw_0, Organisation."Organisation Business Line" saw_1,
Time."Week Code" saw_2, "F1 - Aggregate".Planning saw_3 FROM SubjectArea ORDER BY saw_0, saw_1, saw_2
When filling the “Where clause” content box, be sure that the expression (in this case the column F1 Fact Target.FIND_ID) belongs to the physical tables of the logical table source. Indeed, you can select any physical column that you want.
Resolution: Use the wizard to fill in this area.
nQSError: 15018 - Incorrectly defined logical table source
Toestand: HY000.
Code: 2053177616.
[NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 15018] Incorrectly defined logical table source (for fact table "My Logical Fact Table") does not contain
mapping for [My Dimension.My Logical Column]. (HY000)
The logical table does not have a logical table source that maps to the column “My Dimension.My Logical Column”. Be sure that the physical table of the logical table source has a relation with the logical table source of the column “My Dimension.My Logical Column”.
The join is made between logical fact and logical dimension from the same logical level