OBIEE Federation & Subject Area Conflicts: Causes, Fixes & Workarounds

What is OBIEE Federation?

OBIEE federation is the ability to query across multiple subject areas or data sources as if they were a single unified data model. Instead of writing separate queries for each subject area and stitching results together, federation lets you write one logical SQL statement that transparently joins data from different business areas.

Federation in OBIEE On-Prem vs. OTBI Cloud

The key difference: In OBIEE on-prem, you control the RPD (Repository) and can explicitly define how subject areas relate to each other. In OTBI cloud, Oracle maintains the RPD and doesn't expose federation capabilities—you must use alternative approaches.

Common Federation Conflict Errors

SELECT
  Workforce."Workforce"."Employee Number",
  Absence."Absence"."Absence Reason"
FROM Workforce, Absence
WHERE Workforce."Workforce"."Employee Number" = Absence."Absence"."Employee Number"

SELECT
  p."Person"."Person Number",
  a."Assignment"."Assignment Number"
FROM "Compensation"."Compensation Details" p
WHERE a."Assignment"."Full Time Equivalent" = 1

OBIEE On-Prem Federation Solutions

If you run OBIEE on-prem (self-managed), you have direct control over the RPD and can implement federation in three ways:

1. Virtual Private Databases (VPD) in the RPD Physical Layer

VPD allows you to query multiple physical tables as if they were one logical table. In the RPD:

1. Create a logical table that combines columns from multiple physical tables
2. Define row-wise security rules that determine which physical table provides data based on context (user, organization, date)
3. Users see one unified logical table; the RPD routes queries to the appropriate physical table at runtime

Use case: Querying Employee data from different legal entities or payroll systems that are structured identically but stored separately.

2. Cross-Database Joins in the RPD

If your subject areas source from different databases (e.g., HCM schema + Payroll schema), you can configure cross-database joins in the RPD's physical layer:

1. Ensure both databases are registered in the Connection Pool
2. Create a physical join between tables across databases using a shared key (e.g., PER_ALL_PEOPLE_F.PERSON_ID = PAY_PEOPLE.PERSON_ID)
3. The RPD will automatically route the query to both databases and perform the join in the BI server

Gotcha: Cross-database joins require the connection pool to support distributed queries. Test throughput and latency—joining across databases can be slow.

3. Conformed Dimensions in the Logical Layer

Conformed dimensions are shared logical dimensions that multiple subject areas reference. This is the cleanest federation approach:

1. Create a logical dimension table (e.g., "Person") that multiple logical fact tables can join to
2. In the logical layer, use logical joins to connect fact tables through the shared dimension
3. When a user queries across subject areas, the RPD intelligently joins through the conformed dimension

Example: Both "Workforce" and "Absence" subject areas join through a conformed "Person" dimension. A query for "Employees with Active Absences" automatically joins through Person.

OTBI Cloud Workarounds

Since OTBI cloud doesn't expose federation (Oracle maintains the RPD as read-only), you must use alternative approaches to combine data from multiple subject areas:

Method 1: BI Publisher Data Model (Recommended)

Use BI Publisher to create a custom data model that combines logical SQL queries from multiple subject areas, linked by common keys (Person Number, Assignment Number, etc.).

Steps:

1. In BI Publisher, create a SQL Query that fetches Workforce data (filtered to needed columns)
2. Create a second SQL Query for Absence data
3. Link the two queries using a Data Link on PERSON_ID
4. Run a test report; BI Publisher will join the results in the application layer

-- Query 1: Workforce (Primary Query)
SELECT
  ppf.PERSON_ID,
  ppf.PERSON_NUMBER,
  ppf.FIRST_NAME,
  ppf.LAST_NAME
FROM PER_ALL_PEOPLE_F ppf
WHERE TRUNC(SYSDATE) BETWEEN ppf.EFFECTIVE_START_DATE
  AND ppf.EFFECTIVE_END_DATE
ORDER BY ppf.PERSON_ID;

-- Query 2: Absence (Linked Query)
SELECT
  pab.PERSON_ID,
  pab.ABSENCE_ID,
  pab.ABSENCE_TYPE_ID,
  plc.LOOKUP_CODE as ABSENCE_REASON,
  pab.START_DATE,
  pab.END_DATE
FROM PER_ABSENCE_ENTRIES pab
LEFT JOIN PER_LOOKUPS plc
  ON pab.ABSENCE_TYPE_ID = plc.LOOKUP_TYPE
  AND plc.LOOKUP_TYPE = 'ABSENCE_TYPE'
WHERE TRUNC(SYSDATE) BETWEEN pab.EFFECTIVE_START_DATE
  AND pab.EFFECTIVE_END_DATE
ORDER BY pab.PERSON_ID;

-- Data Link: Query 1.PERSON_ID = Query 2.PERSON_ID

Pros:

• Full control over query logic and join behavior
• Works with any OTBI subject area
• Can use raw SQL with date-effective filters optimized for your use case

Cons:

• Requires BI Publisher license
• Application-layer join (slower for large result sets)
• Need to maintain data links if schema changes

Method 2: Find a Pre-Built Combined Subject Area

Oracle often provides combined subject areas that already join commonly-needed data sources. Before building a custom data model, check if Oracle has created a pre-built subject area:

• Workforce + Absence: "Absence Management" or "Employee Absence Details" subject area (may include absence reasons, accruals, and employee data)
• Workforce + Compensation: "Workforce + Compensation" or "Total Rewards" subject area
• Assignment + Payroll: "Payroll Integration" or "Assignment Payroll" subject area

To discover available subject areas:

1. Open OTBI (BI Answer or OTBI home)
2. Click New → Logical SQL
3. In the query builder, browse the Subject Area dropdown — all available subject areas are listed with counts of available columns
4. Search for keywords like "Absence", "Compensation", "Total Rewards" in the dropdown

Method 3: Oracle Analytics Cloud (OAC) Semantic Modeler

If you have Oracle Analytics Cloud (OAC), you can use the Semantic Modeler to define custom business models that span multiple OTBI subject areas, with cross-SA joins.

Steps:

1. In OAC, create a new Business Model
2. Add tables from multiple OTBI subject areas as data sources
3. Define relationships between tables using logical join expressions
4. Publish as a new subject area for Answers or OTBI

Use case: You need true federation across multiple OTBI subject areas without rebuilding queries each time.

Caveat: OAC Semantic Modeler is a separate product with separate licensing. Check your Oracle contract.

Scenario: Combining Workforce + Absence Data

A common federation use case: "Show me all employees with their active absences, grouped by absence reason."

The Wrong Way (Will Fail in OTBI)

SELECT
  w."Worker".PERSON_NUMBER,
  w."Worker".FIRST_NAME,
  a."Absence".ABSENCE_REASON,
  COUNT(*) as ABSENCE_COUNT
FROM "Workforce"."Worker" w, "Absence"."Absence Details" a
WHERE w."Worker".PERSON_ID = a."Absence Details".PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN a."Absence Details".START_DATE
    AND a."Absence Details".END_DATE
GROUP BY w."Worker".PERSON_NUMBER, w."Worker".FIRST_NAME,
         a."Absence".ABSENCE_REASON;

The Right Way (BI Publisher Data Model)

SELECT
  ppf.PERSON_ID,
  ppf.PERSON_NUMBER,
  ppf.FIRST_NAME,
  ppf.LAST_NAME,
  ppf.EMPLOYEE_NUMBER
FROM PER_ALL_PEOPLE_F ppf
WHERE ppf.PERSON_TYPE_ID IN (
  SELECT person_type_id
  FROM PER_PERSON_TYPES
  WHERE system_person_type = 'EMP'
)
  AND TRUNC(SYSDATE) BETWEEN ppf.EFFECTIVE_START_DATE
    AND ppf.EFFECTIVE_END_DATE
ORDER BY ppf.PERSON_NUMBER;

SELECT
  pab.PERSON_ID,
  pab.ABSENCE_ENTRY_ID,
  plc.MEANING as ABSENCE_REASON,
  pab.START_DATE,
  pab.END_DATE,
  (pab.END_DATE - pab.START_DATE + 1) as DAYS_ABSENT
FROM PER_ABSENCE_ENTRIES pab
LEFT JOIN PER_LOOKUPS plc
  ON pab.ABSENCE_TYPE_ID = plc.LOOKUP_TYPE
  AND plc.LOOKUP_TYPE = 'ABSENCE_TYPE'
WHERE TRUNC(SYSDATE) BETWEEN pab.EFFECTIVE_START_DATE
  AND pab.EFFECTIVE_END_DATE
ORDER BY pab.PERSON_ID, pab.START_DATE;

Scenario: Combining Compensation + Assignment Data

Another federation scenario: "Show me all assignments with their salary bands and compensation history."

The Challenge

Compensation subject area (PAY_ schema) and Assignment subject area (PER_ALL_ASSIGNMENTS_M) are at different grains:

• Assignment is 1 record per {PERSON_ID, ASSIGNMENT_ID, EFFECTIVE_DATE}
• Compensation may be 1 record per {PERSON_ID, GRADE_ID, EFFECTIVE_DATE} or per {PERSON_ID, PAYROLL_ID, EFFECTIVE_DATE}

Direct join can produce duplicates or missing rows.

Solution: Use Conformed Join Keys

SELECT
  pam.ASSIGNMENT_ID,
  pam.PERSON_ID,
  pam.EFFECTIVE_START_DATE as ASSIGN_START,
  pam.EFFECTIVE_END_DATE as ASSIGN_END,
  pcs.GRADE_ID,
  pcs.GRADE_NAME,
  pcs.MIN_SALARY,
  pcs.MAX_SALARY,
  pcs.MIDPOINT_SALARY,
  ppf.PERSON_NUMBER,
  ppf.FIRST_NAME || ' ' || ppf.LAST_NAME as FULL_NAME
FROM PER_ALL_ASSIGNMENTS_M pam
LEFT JOIN PER_ALL_PEOPLE_F ppf
  ON pam.PERSON_ID = ppf.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN ppf.EFFECTIVE_START_DATE
    AND ppf.EFFECTIVE_END_DATE
LEFT JOIN PER_COMP_SALARY pcs
  ON pam.GRADE_ID = pcs.GRADE_ID
  AND TRUNC(SYSDATE) BETWEEN pcs.EFFECTIVE_START_DATE
    AND pcs.EFFECTIVE_END_DATE
WHERE TRUNC(SYSDATE) BETWEEN pam.EFFECTIVE_START_DATE
  AND pam.EFFECTIVE_END_DATE
ORDER BY ppf.PERSON_NUMBER, pam.ASSIGNMENT_ID;

Identifying Which Subject Area Contains a Column

When building federated queries, you need to know which subject area has a specific column. Use this workflow in OTBI:

1. Open OTBI Answers (or go to BI Answer home)
2. Click New → Logical SQL
3. In the query builder, click on the Subject Area dropdown
4. Start typing the column name or keyword (e.g., "Absence Reason")
5. OTBI will filter subject areas that contain matching columns
6. Expand each subject area to see the full column list

Alternative: Browse via Column List

1. Open a subject area in the query builder
2. Expand "Column Definitions" (usually on the left panel)
3. Scroll through all available columns in that subject area
4. If not found, switch to a different subject area and repeat

Grain Conflict Detection

Grain conflicts occur when you mix measures from fact tables with different dimensional granularities, causing inaccurate aggregates or "no fact table at requested level" errors.

Example: Worker vs. Assignment Grain

How to Avoid Grain Conflicts

Rule 1: Identify the grain of each measure

• Headcount (Person grain) cannot be mixed with FTE (Assignment grain) without causing double-counts
• Always ask: "What is the lowest level of detail in this metric?"

Rule 2: Aggregate consistently

• If mixing grains, aggregate to the coarsest grain explicitly in your query
• Example: If mixing Person and Assignment grains, group by PERSON_ID only, and use SUM(FTE) to aggregate assignments per person

Rule 3: Use conformed dimensions

• In a properly federated query, all measures should roll up through the same dimensions (Person → Department → Company)
• If a measure breaks this hierarchy, it may indicate a grain conflict

RPD Consistency Checks (On-Prem Only)

For OBIEE on-prem deployments with custom federation rules, always run consistency checks:

Using the RPD Client

1. Open Oracle BI Administration Tool
2. Connect to your RPD repository
3. Go to Tools → Check Global Consistency
4. A detailed report will show:
• Broken logical joins (physical joins not found)
• Orphaned logical tables (not in any subject area)
• Unresolved foreign keys
• Circular join paths
• Ambiguous dimensions (multiple paths to same table)

Common Consistency Errors

Quick Reference: Federation Approach by Scenario

Frequently Asked Questions