Oracle Fusion Cloud provides two primary ways to access data: OTBI (Oracle Transactional Business Intelligence) and direct SQL via BI Publisher or SQL developer tools. Understanding the strengths of each - and when to use one over the other - is one of the most practical skills for Oracle Fusion reporting teams.
OTBI and direct SQL are not competitors. They're complementary tools designed for different use cases - and the most effective Oracle Fusion reporting teams use both.
Side-by-Side Comparison
| Dimension | OTBI | Direct SQL |
|---|---|---|
| Primary users | Business analysts, power users | SQL developers, BI developers |
| Schema knowledge required | Low - subject areas abstract the tables | High - must know table names and join keys |
| Effective-date handling | Automatic | Manual - must add BETWEEN predicates |
| Custom calculations | Limited to column formulas | Full SQL expressions, CASE, analytics |
| Cross-module joins | Limited by subject area boundaries | Unlimited - join any tables |
| Formatted outputs | Built-in pivot tables, charts, dashboards | Requires BI Publisher or external tool |
| Scheduled delivery | Native scheduling and bursting | Via BI Publisher scheduler |
| Performance tuning | Limited - black-box SQL generation | Full control - hints, indexes, CTEs |
| Security enforcement | Automatic via data roles | Requires explicit design |
When OTBI is the Right Choice
Business users who need ad-hoc analysis without SQL skills. OTBI's drag-and-drop interface and subject area abstractions make it accessible without deep Oracle schema knowledge.
Reports with consistent formatting requirements, pivot tables, charts, or dashboard embedding. OTBI's presentation layer handles layout without additional development effort.
OTBI automatically applies Oracle Fusion data roles - users only see data they're authorized to see, without the developer needing to build security into the query.
When Direct SQL is the Right Choice
When a report requires joining HCM, Finance, and Procurement data in a single query, direct SQL is the only option. OTBI subject area boundaries prevent these cross-module joins.
BI Publisher reports with complex layouts, bursting logic, multi-dataset reports, or RTF templates require SQL-based data models. Direct SQL gives full control over the data set structure.
When OTBI-generated SQL performs poorly, rewriting in direct SQL allows addition of query hints, better join order control, and use of analytical functions that OTBI cannot generate.
During implementation, migration, or audit activities, direct SQL allows querying at the raw table level to reconcile totals, validate records, and troubleshoot discrepancies quickly.
Using OTBI and SQL Together
The most effective Oracle Fusion reporting workflows combine both tools. A common pattern:
1. Use OTBI Answers → explore the data, understand the fields 2. View OTBI SQL → extract the generated query as a starting point 3. Refine in SQL → optimise joins, add logic OTBI cannot express 4. Deploy via BIP → publish as a formatted BI Publisher report
OTBI-generated SQL is also an excellent way to discover which underlying tables and join conditions are needed for a given subject area - making it a useful starting point for custom SQL development.
Accelerate Oracle Fusion SQL Development with FusionLens SQL
Explore the Oracle Fusion tables behind any OTBI subject area - with column descriptions, data types, and module context.
Build direct SQL equivalents of OTBI reports faster - autocomplete knows Oracle Fusion table and column names across all modules.
Export SQL results to compare directly against OTBI outputs - validate row counts and reconcile differences in one step.