Oracle Fusion OTBI Reports for Beginners: Complete Step-by-Step Guide 2026

What Is OTBI in Oracle Fusion?

OTBI stands for Oracle Transactional Business Intelligence.

It is the self-service reporting and analytics solution available within Oracle Fusion Cloud Applications.

OTBI allows you to analyze Oracle Fusion transactional data using predefined Subject Areas instead of directly accessing database tables.

Technically, OTBI runs on the same Oracle Analytics (formerly OBIEE) semantic-layer engine used across Oracle's BI stack. In an on-premise OBIEE install, a developer can open the RPD (repository) in the BI Administration Tool and modify the physical, business, and presentation layers directly. In Oracle Fusion SaaS, that RPD is Oracle-managed and not exposed to customers — Subject Areas, joins, and calculations are pre-built and delivered by Oracle. This is the single biggest architectural difference beginners coming from on-prem OBIEE/Fusion Middleware backgrounds need to internalize: you consume the semantic layer, you don't administer it.

Simplified OTBI Architecture

Oracle Fusion Application → Transactional Data → Application View Objects → OTBI Metadata Layer → Subject Areas → OTBI Analysis → Tables / Pivot Tables / Graphs → Dashboards

When you create an OTBI report, you normally don't write:

SELECT *
FROM some_table;

Instead, you select business-friendly fields such as:

from the relevant OTBI Subject Area.

What Is an OTBI Subject Area?

The Subject Area is probably the most important concept to understand when learning OTBI.

Think of a Subject Area as a predefined reporting model containing related business information.

For example, an HCM-related Subject Area might contain information like this:

Instead of searching through hundreds of Oracle database tables and understanding their joins, Oracle provides these business objects through Subject Areas.

OTBI Subject Area Naming

A common characteristic of OTBI transactional Subject Areas is the suffix:

Real Time

For example:

Depending on the Oracle Fusion modules implemented in your organization, you may see Subject Areas related to:

The exact Subject Areas available to you depend on your Oracle Fusion modules, release, roles, and data-access security.

Note: "Real Time" is a convention for transactional Subject Areas, not a hard rule. Some delivered Subject Areas built for embedded KPIs, historical snapshots, or specific analytical use cases don't carry the suffix. When exploring the catalog, don't assume a Subject Area is stale data just because it lacks "Real Time" in the name — check its description and refresh behavior instead.

OTBI Terminology Beginners Should Know

1. Subject Area

A logical collection of business-related reporting information.

Example:

Workforce Management - Worker Assignment Real Time

2. Dimension

A dimension normally represents the context through which data is analyzed.

3. Attribute

Attributes provide descriptive information about dimensions.

Example:

4. Fact

Facts usually contain measurable transactional information.

5. Analysis

An Analysis is what we commonly call an OTBI report.

Subject Area + Columns + Filters + Formulas + Views = OTBI Analysis

6. Dashboard

A Dashboard combines multiple analyses and other content into one user-friendly page.

Example:

HR Dashboard

OTBI vs BI Publisher

Beginners often confuse OTBI and BI Publisher.

Both are reporting technologies available in Oracle Fusion, but they solve different types of problems.

The easiest way to remember this is:

OTBI = Interactive Analytics
BI Publisher = Pixel-Perfect / Complex Reporting

Example 1

Show invoice totals by supplier and allow users to filter by Business Unit.

Recommended Tool: OTBI

Example 2

Generate a customer invoice PDF with company logo, tax information, invoice lines, and payment instructions.

Recommended Tool: BI Publisher

Example 3

Run complex SQL joining multiple Oracle Fusion objects and create a scheduled CSV extract.

Recommended Tool: BI Publisher

How to Access OTBI in Oracle Fusion

Depending on your roles and Oracle Fusion environment, you can generally navigate through:

Navigator → Tools → Reports and Analytics

From here, you can access:

My Folders | Shared Folders | BI Catalog | Create Analysis

Creating Your First OTBI Report

Suppose the requirement is:

Create a report displaying employees with their person number, name, assignment status, and department.

Typical OTBI Development Flow

Requirement → Identify Subject Area → Create Analysis → Select Columns → Apply Filters → Check Results → Format Report → Save Analysis

Step 1: Create a New Analysis

Navigate to OTBI and select:

New → Analysis

OTBI will ask you to select a Subject Area.

For our employee example, we could use:

Workforce Management - Worker Assignment Real Time

Step 2: Understand the Analysis Editor

Once the Subject Area is selected, you'll see the OTBI Analysis Editor.

The important sections include:

Criteria | Results | Prompts | Advanced

For beginners, you'll spend most of your time initially in:

Criteria + Results

Step 3: Add Columns

Inside the Subject Area, expand the required folders.

You might select columns such as:

Conceptually, this is similar to writing:

SELECT person_number,
       employee_name,
       department_name,
       business_unit,
       assignment_status,
       hire_date
FROM employee_information;

But OTBI automatically handles the underlying metadata and joins — and that abstraction is doing real work. A single "Worker Assignment Real Time" row can be the product of joins across a dozen-plus underlying application tables (person, assignment, job, position, grade, department hierarchy, business unit, etc.). The SQL snippet above is illustrative only, to build intuition — it is not what actually executes.

Step 4: View the Results

Click:

Results

OTBI executes the analysis and displays the output.

Example:

Step 5: Add Filters

Without filters, your report may return considerably more data than required.

Suppose the requirement says:

Display only Active employees.

Use:

Assignment Status = Active

The result now contains only active assignments.

Using Multiple Filters

Suppose the requirement becomes:

Show Active employees belonging to India Business Unit.

Use:

Assignment Status = Active AND Business Unit = India BU

Conceptually:

WHERE assignment_status = 'Active'
  AND business_unit = 'India BU'

Common OTBI Filter Operators

What Is "Is Prompted" in OTBI?

One of the most useful filter options is:

is prompted

Instead of hardcoding:

Business Unit = India BU

you can configure:

Business Unit is prompted

The user can then select the Business Unit when running the report or dashboard.

Example:

This makes the report reusable.

Instead of creating multiple reports:

India Employee Report | USA Employee Report | UK Employee Report

you can create one:

Employee Report

and allow the user to select the Business Unit dynamically.

Column Sorting in OTBI

You can sort columns in:

Ascending | Descending

Multiple sorting levels can also be configured.

Example:

Column Formatting

OTBI allows you to control how columns appear.

For example, instead of displaying:

Person Display Name

you might change the heading to:

Employee Name

Conditional Formatting

Suppose you create an invoice analysis.

You want invoices greater than ₹1,000,000 to be highlighted.

Use a condition like:

Invoice Amount > 1,000,000

Conditional formatting is useful for:

OTBI Views

An OTBI analysis doesn't have to contain only a table.

Common views include:

Table View

A Table View is useful for detailed transactional information.

Pivot Table

A Pivot Table is useful when summarizing information.

Graph View

Suppose management wants:

Total invoice amount by supplier.

Instead of giving them hundreds of rows, you can display a graph.

ABC Supplier ██████████████ | XYZ Supplier ██████████ | PQR Supplier ███████ | LMN Supplier █████

What Is a Compound Layout?

The Compound Layout allows several views of the same analysis to appear together.

Example:

Employee Analysis

Creating Calculated Columns in OTBI

OTBI allows formulas to be applied to columns.

Suppose we have:

Invoice Amount | Paid Amount

and need:

Outstanding Amount = Invoice Amount - Paid Amount

Calculated columns can also be used for:

CASE Expressions in OTBI

You can also create logical expressions.

Example:

CASE
    WHEN "Invoice Amount" >= 1000000 THEN 'High Value'
    WHEN "Invoice Amount" >= 500000 THEN 'Medium Value'
    ELSE 'Low Value'
END

Result:

What Are Dashboard Prompts?

A Dashboard Prompt allows users to filter multiple compatible analyses dynamically.

Example:

This is useful when one dashboard contains several reports.

Creating an OTBI Dashboard

Once several analyses are created, they can be added to a Dashboard.

For example:

Finance Dashboard

could contain:

A typical dashboard design can be represented like this:

Finance Dashboard

Saving OTBI Reports

OTBI content is stored inside the BI Catalog.

You'll normally see:

My Folders | Shared Folders

My Folders

Useful for:

Shared Folders

Used for organizational content.

A safer development approach is:

OTBI Security

OTBI data is affected by Oracle Fusion roles and data-security configuration.

Two users can run the same analysis and see different data.

When troubleshooting, check:

In practice this traces back to two things: the duty roles / job roles assigned to the user (which determine whether a Subject Area is even visible) and the HCM/Financials data security policies and data roles tied to their assignment (which determine which rows within that Subject Area they can see — e.g., which Business Units, LDGs, or Person Security Profiles apply). A missing analysis is almost always a role/Subject Area visibility issue; missing rows within a working analysis is almost always a data security profile issue. Separating those two failure modes early saves a lot of troubleshooting time.

Can We Write SQL in OTBI?

For standard Oracle Fusion SaaS OTBI reporting, you don't directly query transactional database tables using unrestricted database SQL.

Instead, OTBI uses:

There is one important nuance technical consultants should know: every OTBI Analysis, once built, has a corresponding Logical SQL statement, visible in the Advanced tab of the Analysis Editor. This is the query the Oracle Analytics engine generates internally against the semantic layer — it is exposed for troubleshooting and understanding, not for you to hand-author from scratch or run against the physical schema. You can copy it, read it to understand which logical columns/tables were pulled in, and use it to explain unexpected results — but you cannot edit the RPD-level physical SQL behind it in Fusion SaaS.

If your requirement needs complex database-oriented reporting logic — custom joins outside delivered Subject Areas, PL/SQL-driven calculations, or multi-step data preparation — BI Publisher (with a SQL or PL/SQL-based data model) is usually the better fit.

How Does OTBI Get Oracle Fusion Data?

At a simplified technical level:

What Is OTBI Data Lineage?

Data Lineage helps technical consultants understand how an OTBI field relates to underlying Oracle Fusion objects.

Conceptually:

This is useful when comparing:

What Are OTBI Agents?

OTBI provides Agents for scheduled or condition-based delivery.

Example:

A simple use case:

Example OTBI Requirement

Business Requirement

Finance wants an analysis containing:

Users should be able to select:

The output should also display invoice totals by supplier.

Step 1 — Identify the Appropriate Subject Area

Verify that the selected Subject Area contains:

Step 2 — Add Required Columns

Select the required columns.

Step 3 — Add Filters

Use:

Step 4 — Create Table View

Create a detailed table.

Step 5 — Create Pivot View

Summarize:

Step 6 — Add Graph

Create:

Step 7 — Create Compound Layout

Combine:

Common OTBI Mistakes Beginners Make

Mistake 1: Selecting the Wrong Subject Area

Always understand:

Mistake 2: Adding Too Many Columns

Avoid adding 100+ columns unless genuinely required.

Mistake 3: Running Without Filters

Prefer meaningful restrictions such as:

Mistake 4: Treating OTBI Like SQL

Start with:

Mistake 5: Combining Unrelated Facts

Different dimensions and facts can operate at different grains.

Mistake 6: Ignoring Security

Check roles and data access before assuming the report is incorrect.

Mistake 7: Using OTBI for Every Requirement

Sometimes a better solution is:

Basic OTBI Performance Tips

1. Select Only Required Columns

Avoid unnecessary fields.

2. Apply Meaningful Filters

Prefer date, business unit, status, or organization filters where relevant.

3. Understand Subject Area Grain

Wrong combinations can cause:

This is the most common real-world OTBI defect: mixing a fact at a finer grain (e.g., invoice distribution line) with a dimension at a coarser grain (e.g., supplier) without the right aggregation rule causes fan-out — the same header amount appears to repeat once per line, and a naive SUM overstates the total. Before trusting any total, verify the fact's default aggregation rule and cross-check the row count against the Fusion UI for a small, known data set.

4. Avoid Unnecessary Complex Formulas

Complex calculations can increase processing.

5. Test Incrementally

Use this approach:

OTBI Troubleshooting Approach

A simple troubleshooting flow:

Useful comparison sources:

A Good OTBI Learning Path for Beginners

OTBI Practice Exercises

Exercise 1 — Basic Employee Report

Create:

Filter:

Exercise 2 — Department Headcount

Create:

Display using:

Exercise 3 — Dynamic Employee Report

Create prompts for:

Exercise 4 — Transaction Summary

Create:

Exercise 5 — Dashboard

Create three analyses:

Add prompts for:

OTBI Interview Questions for Beginners

1. What is OTBI?

OTBI is Oracle Transactional Business Intelligence, the self-service analytics capability used to create real-time analyses and dashboards using Oracle Fusion Subject Areas.

2. What is a Subject Area?

A Subject Area is a logical grouping of related business dimensions, attributes, facts, and metrics used to create OTBI analyses.

3. What is the difference between OTBI and BI Publisher?

OTBI is mainly used for interactive real-time analytics and dashboards, while BI Publisher is better suited for pixel-perfect reports, document output, complex data models, and many scheduled extract requirements.

4. Can we write database SQL directly in OTBI?

Not as unrestricted direct database SQL against Oracle Fusion SaaS transactional tables. OTBI uses Subject Areas and the Oracle BI metadata layer. You can, however, view the auto-generated Logical SQL for an analysis on the Advanced tab — useful for troubleshooting, not for authoring queries from scratch.

5. What is a dashboard prompt?

A Dashboard Prompt allows users to dynamically provide values that can filter compatible analyses displayed on a dashboard.

6. What is an OTBI Agent?

An Agent is an OTBI feature used to automate scheduled or condition-based delivery and actions associated with analytical content.

7. Why can two users get different OTBI results?

Because Oracle Fusion application and data security can restrict the information visible to each user.

8. What is the difference between a dimension and a fact?

A dimension provides descriptive context such as Department or Supplier, while a fact generally represents measurable information such as Invoice Amount, Quantity, or Headcount.

9. What is OTBI Data Lineage?

Data Lineage helps identify how OTBI Subject Area attributes relate to Oracle Fusion application metadata and underlying data sources.

10. What does Real Time mean in OTBI?

OTBI transactional Subject Areas expose operational application information through Oracle Fusion's reporting metadata layer.

Frequently Asked Questions

Is OTBI difficult to learn?

The basic concepts are not difficult. The challenging part is understanding Subject Areas, reporting grain, facts, dimensions, security, and the business process behind the data.

Is SQL required to learn OTBI?

SQL is not mandatory for basic OTBI reporting, but SQL knowledge is very helpful for:

Can functional consultants create OTBI reports?

Yes. OTBI is designed for both business and functional users, while technical consultants are especially useful for complex calculations, security, Data Lineage, performance, and troubleshooting.

Is OTBI real time?

OTBI is designed for real-time transactional analysis using Oracle Fusion application Subject Areas.

Can OTBI combine multiple Subject Areas?

Yes, but this requires understanding compatible dimensions and reporting grain. Beginners should first become comfortable with one Subject Area.

Can custom fields be used in OTBI?

Depending on configuration, BI-enabled flexfields can expose additional attributes for analytics.

OTBI vs BI Publisher: Which One Should a Beginner Learn First?

If you're planning to become an Oracle Fusion Technical Consultant, you should learn both.

A good sequence is:

Real Oracle Fusion projects often use multiple technologies:

Final Thoughts

OTBI is one of the fundamental skills for anyone working with Oracle Fusion Cloud reporting.

A strong Oracle Fusion consultant understands:

Start with simple reports.

Learn how Subject Areas are organized.

Understand why a particular field is available.

Compare the result with the Oracle Fusion UI.

Experiment with filters, pivot tables, formulas, graphs, and dashboard prompts.

Once these fundamentals are clear, move into:

For an Oracle Fusion Technical Consultant, OTBI is not just another reporting tool.

It is an important part of understanding how Oracle Fusion converts transactional application data into business analytics.

Learn. Practice. Build. Troubleshoot.