OBIEE 10G - Complex (Join|Operator) (Physical and Logical)

Bi Server Architecture With Client

About

In 10g, A complex join is a metadata of sql join statement in the repository. In 11g, they doesn't exist anymore.

Obiee Foreign Key Complex Join

Use in the layers

In the Business Model

In the business model, OBI recommends using complex logical joins instead of foreign key logical joins.

When Complex joins are used in the business layer then they are really acting as placeholders. They allow the OBI Server to decide on which are the best joins define in the Physical Layer to satisfy the request.

In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will then decide the best joins to use to meet the requirements.

In the Physical Layer

For all others relationships other than a Primary Key-Foreign Key Relationships (expression other than equal to perform an equi join), you have to use a complex join otherwise you have to use a foreign key

Example in the Scott Schema :

  • with a between operator
SELECT ename, dname, grade
  FROM emp, dept, salgrade
 WHERE emp.deptno = dept.deptno
   AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal
  • with the operators ⇐ and >=
SELECT ename, dname, grade
  FROM emp, dept, salgrade
 WHERE emp.deptno = dept.deptno
   AND emp.sal <= salgrade.hisal
   AND emp.sal >= salgrade.losal
  • with a Cast function
emp.deptno = cast(dept.deptno as varchar(100))

Support

Syntax error

[nQSError: 27002] Near <(>: Syntax error [nQSError: 26012}

The expression as below are not allowed in a complex join.

DIM_DATUM.DATMD_ID = DECODE(FACT.DATMD_ID,-1,99991231,FACT.DATMD_ID)

To resolve this problem, integrate your expression in a opaque view or change your table structure.





Discover More
Bi Server Architecture With Client
OBIEE - Physical Layer

The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information the physical data sources. The most common way to...
Bi Server Architecture With Client
OBIEE - Defining a Non-Aggregated Measure (Baseline column) of a Fact Table

If requirements exist to use fields without aggregation on a fact table, the fields should be defined in a dimension logical table using the following guidelines: Determine if there is a dimension logical...
Bi Server Architecture With Client
OBIEE - Defining the Logical Key of a Fact Table

If foreign key logical joins are used, the primary key of a logical fact table should then be comprised of the foreign keys. If the physical table does not have a primary key, the logical key for a fact...
Obiee Foreign Key Complex Join
OBIEE - Foreign Key or Physical Join

A foreign key repository join is used only to define a Primary Key-Foreign Key Relationships in the Physical Layer. It consist of a column or a set of columns and refers to the primary key column in...
Obiee New Complex Join
OBIEE - How to define a One to Many Relationship

How to model a One to Many Relationship in OBIEE ? Starsnowflake schemasdimensional schemaone-to-many relationshipsdimension tablesfact tables You must in the logical business model from OBIEE designed...
Obiee Foreign Key Complex Join
OBIEE - Joins definitions in the repository (complex, foreign, logical table source)

You have three way to define joins when you model your repository: You can define: a Complex join a Foreign key a logical join property: From 11g, joins in the Physical and Business Model...
Logical Business Model
OBIEE - The Business Model and Mapping (BMM)

The Business Model and Mapping layer (known also as Business Model): is the layer in the middle of the logical business model. permit to model logical dimensional models which are a reorganization...
Bi Server Architecture With Client
OBIEE 10G/11G - Driving Table

You can specify a Driving table parameter in a complex join. Driving tables are for use in optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small...
Obiee Foreign Key Complex Join
OBIEE 10G/11G - Logical Join

A logical join is a join defined in the BMM layer. He is defined between a logical fact table and a logical dimension table. Two types of logical join exists: standard logical join logical foreign...



Share this page:
Follow us:
Task Runner