Travel expense data missing from OTBI reports in travel-mgmt module after data migration

ERP Oracle Fusion Cloud
, , , , , , , ,
1

After migrating travel expense data from our legacy system to Oracle Fusion Cloud 22D, we’re finding significant gaps in our OTBI reports. The Travel Expense Analysis subject area shows approximately 60% of expected records missing. The strange part is that all the expense reports are visible in the Travel and Expenses UI, and users can view their complete history without issues.

When I run queries directly in OTBI against the Travel Expenses Real Time subject area, I get partial data - recent expenses from the last 3 months appear, but historical migrated data (about 18 months worth) is largely absent. The ETL incremental refresh jobs show successful completion status with no errors in the logs. I’ve verified row-level security settings and confirmed my test user has appropriate data access roles.

Query example returning incomplete results:

SELECT expense_report_id, employee_name, report_date
FROM "Travel Expenses Real Time"
WHERE report_date BETWEEN '2023-08-01' AND '2024-12-31'

This query should return around 3,400 expense reports based on transactional data, but OTBI only shows 1,280 records. Has anyone dealt with OTBI subject areas not reflecting migrated historical data even though the operational tables contain complete information?

2

Before requesting a full refresh, verify your subject area mappings are correct. The Travel Expenses subject area pulls from multiple fact tables, and if your migrated data landed in unexpected tables or with non-standard attributes, the subject area queries might be filtering it out unintentionally.

3

Also review your row-level security configuration carefully. Even though you mentioned checking this, I’ve seen cases where migrated data has different security context attributes than organically created data, causing unexpected filtering in OTBI. Check the LEDGER_ID, ORG_ID, and BUSINESS_UNIT_ID values on your migrated records versus native records.

4

Here’s a comprehensive solution addressing all three critical areas:

OTBI Subject Area Mapping: The Travel Expenses Real Time subject area in Fusion sources primarily from the EXM_EXPENSE_REPORTS_F fact table and joins to dimension tables including EXM_EMPLOYEES_D, EXM_EXPENSE_TYPES_D, and date dimensions. Your issue stems from the analytics warehouse (EXM_* tables) not being populated during migration even though operational tables (AP_EXPENSE_REPORTS_ALL, AP_EXPENSE_REPORT_LINES_ALL) contain complete data.

Verify the mapping by querying the analytics fact table directly:

SELECT COUNT(*), MIN(report_date), MAX(report_date)
FROM EXM_EXPENSE_REPORTS_F
WHERE report_date < '2024-11-01';

If this returns zero or minimal rows for your historical period, the analytics warehouse wasn’t seeded during migration. The subject area queries are working correctly - they simply have no data to report on.

ETL Process Validation: OTBI’s incremental ETL processes (running via scheduled jobs in the Scheduled Processes work area) only capture changes since the last ETL run. They don’t perform initial historical loads. During your migration, the operational tables were loaded, but the corresponding analytics warehouse load wasn’t triggered. The successful ETL job completions you’re seeing are processing the small volume of new transactions created post-migration, which explains why recent 3-month data appears correctly.

To resolve this, you need to initiate a full analytics warehouse refresh. Navigate to Tools > Scheduled Processes and search for “Expense Report Analytics” jobs. However, standard user-initiated jobs won’t backfill 18 months of historical data effectively. Log an SR with Oracle Support requesting a full refresh of the Travel Management analytics warehouse, specifically mentioning the EXM subject areas. Provide them with the date range of your migrated data (August 2023 onwards) and specify you need historical fact table population, not just incremental updates.

Row-Level Security Review: Your migrated data may have incomplete or inconsistent security attributes compared to native Fusion transactions. Query your operational expense data to verify security context:

SELECT DISTINCT ledger_id, org_id, business_unit_id
FROM ap_expense_reports_all
WHERE creation_date < '2024-11-01';

Compare these values against native Fusion records created post-migration. If your migrated records have NULL or unexpected values in security context columns, OTBI’s row-level security will filter them out even if the analytics warehouse is properly populated. If you find inconsistencies, you’ll need to update the security attributes on your migrated operational records before requesting the analytics refresh, otherwise the refreshed warehouse will inherit the same security issues.

The complete resolution path: (1) Validate and correct security attributes on operational data, (2) Request Oracle Support to perform full analytics warehouse refresh for Travel Management, (3) After refresh completes (typically 4-8 hours for 18 months of data), verify OTBI reports show complete historical data, (4) Resume normal incremental ETL schedule. This should restore full visibility of your 3,400 expense reports in OTBI analyses.

5

You’ll need to request a full analytics warehouse refresh through an SR with Oracle Support. They can run backend processes to fully reload the travel expense fact and dimension tables from your operational data. Alternatively, if you have the right privileges, you can try running the “Full Refresh” option in the Analytics Warehouse Administration task, but for travel management data spanning 18 months, Oracle Support involvement is usually necessary. Make sure to schedule this during off-hours as it’s resource-intensive and can take several hours depending on data volume.

6

This sounds like a classic case where the operational data exists but the analytics warehouse hasn’t been properly seeded with historical data. OTBI uses a separate analytics schema that’s populated through ETL processes. During migration, you need to not only load the transactional tables but also ensure the analytics tables are populated. Check if your migration included loading the EXM_EXPENSE_REPORTS_F fact table and related dimension tables. The incremental ETL only picks up changes after the initial load - it won’t backfill historical data that was added directly to operational tables.