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

Bi Server Architecture With Client

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

Properties

General tab

Logical Table Source

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.

Joins

Example: OBIEE 10G/11G - How to model a bridge table (Many-to-Many relationship) with the joins property of a Logical Table ?

Content Tab

Obiee Fragmentation Content One Column

Aggregation Content

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

It helps you to define an aggregate navigation

Fragmentation content

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

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

Documentation / Reference





Discover More
Bi Server Architecture With Client
OBIEE - (Fragment|ation) (Partitioning)

The fragmentation (known also as partitioning) is the most powerful feature of OBIEE. You can fragment (or partition) any logical table (fact table as dimension table) in more than one logical table source...
Obiee Fragmentation Content One Column
OBIEE - Aggregate (Navigation|Table|Content)

The aggregate navigation capability of the Oracle BI Server allows queries to use the information stored in aggregate tables automatically. When users request information at a high “grain” of aggregation,...
Obiee Aggregate Lts
OBIEE - Aggregate Navigation with level-based fragmentation

OBI Server features have the ability to retrieve queries from an aggregate table defined through level of the hierarchy instead of the fact table. This ability is a part of the aggregate navigation feature....
Obiee 11g Connection Pool Connection Script Timesten
OBIEE - Aggregate Persistence (Script)

The Aggregate Persistence functionality is the ability to: create and populate aggregate tables, and map them to the metadata repository in order to use the aggregate navigation capability of the...
Bi Server Architecture With Client
OBIEE - Fragment

A fragment is just an other name for a logical table source in the context of fragmentation. For instance, you splits a table in two child tables (History and current). In OBIEE, the original table...
Obiee Fragmentation Content One Column
OBIEE - Grain - Level of (summarization|aggregation)

Grain definition in the context of OBIEE. The following list describes the different grains in navigating a query: Query grain. The grain of the request. Aggregation grain. The grain of the aggregate...
Obiee Logical Table Source No Fragmentation Content Warning
OBIEE - Logical Table

A logical table source is a table where all its logical table sources have the same dimension combinations (domain) by logical level. The check box “This source should be combined with other sources...
Obiee 10g Logical Level Key
OBIEE - Logical level

The Logical level or level are the key level of the hierarchy and defines the grain of each table (logical table) (Fact as Dim) when set in the content tab. The level key: defines the unique...
Obi Edition
OBIEE - Outer Join

Outer Joins in OBIEE: Outer joins in logical table sources are always included in a query, even if the table source is not used. If possible, create one logical table source without the...
Bi Server Architecture
OBIEE - Query Compiler

The query compiler is responsible of the compilation of a logical sql in the query processing process of BI Server. The query compilation is composed of the following five phases: parsing, , navigation,...



Share this page:
Follow us:
Task Runner