OBIEE - Logical Table Source (LTS) (Physical and Logical Column Mappings)

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - 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).
Advertising

3 - Properties

3.1 - General tab

Logical Table Source

3.1.1 - 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:

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.
Advertising

3.1.2 - Joins

3.2 - Content Tab

3.2.1 - Aggregation Content

You can define the aggregation (ie the group by) of:

It helps you to define an aggregate navigation

3.2.2 - Fragmentation content

3.2.3 - 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).
Advertising

3.2.4 - Select distinct values

If the values for the source are unique, select the option Select distinct values.

4 - How to

4.1 - 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:

Optionally, if you want to permanently reverse a subject area, you can set the REVERSED_LTS_PRIORITY_SA_VEC session variable.

4.1.1 - 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

4.1.2 - 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.

4.1.3 - 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.

5 - Support

5.1 - 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.

5.2 - 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

6 - Documentation / Reference