Forum Discussion

BI_is_Fun's avatar
BI_is_Fun
New Member
1 year ago
Solved

Challenges moving Oracle Fusion data into a data warehouse for reporting?

I’m fairly new to working with Oracle Fusion and want to learn from others who have had to integrate it with their data warehouse environment. If you’ve extracted Fusion data (via OTBI, BIP, API...
data pipeline
data warehouse
  • ssrithar's avatar
    ssrithar
    7 months ago

    BI_is_Fun 

     

    Beyond the issues already mentioned in your post (reporting gaps, slow queries, API limits, schema changes, etc.), here are additional real-world challenges teams face when extracting Oracle Fusion data into a data warehouse:

     

    • Limited Joinability Across Subject Areas - OTBI and BIP subject areas are siloed
    • Inconsistent Business Definitions - Same KPI or field name may mean different things in different subject areas
    • Frequent Logic Changes in Upstream Systems- Fusion configurations or setups may change
    • Access & Security Model Limitations -  OTBI/BIP reports reflect user security roles — which can limit access to full datasets during extraction
    • Lack of True CDC (Change Data Capture)
    • Version Skew Between Environments

    What can be done different

    • Build schema drift detection into the pipeline early on
    • REST APIs or BI Cloud Connector (BICC) wherever possible, and only use OTBI for ad hoc needs or pre-built summaries.
    • Design robust watermarking strategies (composite keys, last run logic)

     

    If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

     

     

deborshi_nag's avatar
deborshi_nag
Icon for Super User rankSuper User
7 months ago

Hello BI_is_Fun 

 

The typical mechanisms of extracing data out of Oracle Fusion (ERP/HCM/SCM) are the following -

 - OTBI (ad‑hoc analytics over subject areas)
 - BI Publisher (BIP) (pixel/extract reports, schedule/burst to SFTP/OCI)
 - REST/SOAP APIs (ERP/HCM/SCM endpoints, UCM for files/attachments)
 - Extracts (HCM Extracts, ERP Extracts)
 - Fusion Analytics Warehouse (FAW) (Oracle’s managed DW/semantic model)
 
In regards to the other points you mentioned -
 
Reporting-side challenges (OTBI/BIP) chalenges:
- Subject area gaps and missing attributes
What works: Use REST APIs for entities that are absent in subject areas.
- Slow queries & performance governors
What works: In the warehouse, create summary tables (e.g., GL balances by period, BU, CC, product) and leverage Power BI aggregations.
- Limited or inconsistent history
What works: Treat Fusion as operational source, not the system of analytical history.
- Security context bleed-through
What works: Once data lands in your DW/Fabric Lakehouse, re-apply row-level security in the semantic model, not at extraction time.
 
ETL/ELT-side challenges
- Volume, throttling & pagination (APIs)
What works: Partition and parallelize by business key/date windows (e.g., per BU, per period).

- Incremental loads & late-arriving data

What works: Maintain CDC watermarks per entity

- Schema drift (quarterly updates)

What works: In the DW, stage → bronze → silver → gold (medallion) to isolate breakage and preserve raw captures.
- Data quality, master/reference data
What works: Implement DQ rules (nulls, referential integrity, allowed value sets) and reconciliation checks
- Attachments & large binaries
What works: Separate metadata vs. binary pipelines.
 
Hope this helps - please appreciate leaving a Kudos or accepting as a Solution! 
shankar_bic's avatar
shankar_bic
Regular Visitor
5 months ago

This is one of the most common challenges in the Oracle + Microsoft stack. A few things that have helped teams like yours:

If you don't actually need a warehouse: Many teams assume they need to replicate Oracle Fusion data into a warehouse before Power BI can use it. That's not always true. Platforms like BI Connector (a Power BI certified connector for Oracle Fusion) let Power BI query Oracle Fusion's OTBI analysis, BIP data models, and custom queries directly. This eliminates the ETL pipeline entirely for reporting use cases. 

 

If you do need a warehouse: BI Connector also supports exporting Oracle Fusion data into data warehouses and lakehouses (Snowflake, Databricks, Fabric, Azure Data Factory etc.). The advantage over raw BICC extraction is that it handles the schema mapping and incremental loads automatically.

Common pitfalls to avoid: Oracle Fusion's BICC can be brittle with schema changes during quarterly updates. REST API extraction works but requires handling pagination, token refresh, and rate limits. If you're building this from scratch, budget 2-3x the time you think it'll take.

The general pattern we see: start with a direct Power BI integration for immediate reporting needs, then build out the warehouse pipeline for blended analytics over time.