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

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

1 - About

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

Advertising

3 - Use in the layers

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

3.2 - 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))
Advertising

4 - Support

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