Data Pipeline for Bringing Data from Oracle Fusion to Azure Databricks

Question

Hello,

I am trying to bring Oracle Fusion (SCM, HCM, Finance) Data and push to ADLS Gen2. Databricks used for Data Transformation and Powerbi used for Reports Visualization.

I have 3 Option.

Option 1 :

Option 2 :

Option 3 :

May someone please help me which is best enterprise cost effective approach and why. Or any Other way to achieve this effectively.

Please help.

Thanks a lot

Answer 1

Hello Uma !

Thank you for posting on Microsoft Learn Q&A.

If Azure Databricks is going to be your main analytics platform, the pattern of option 3 is usually the most cost-effective and simplest because options 1 and 2 only really make sense if you already need autonomous data warehouse for other Oracle analytics workloads.

Option 1 :

Pros

• open for delta sharing
• works well if your primary analytics stack is on OCI and Databricks is just another consumer

ADW can offload some transformations before Databricks.

Cons

• you pay for ADW compute + storage + OCI Data Integration / FDI
• every Databricks read over delta sharing incurs egress charges from OCI to Azure and extra latency.
• more moving parts to operate and secure

Option 2 :

Pros

• simple architecture just a JDBC connection from Databricks
• no need to configure delta sharing

Cons

• still paying for ADW even though Databricks will do most transformations
• heavy analytical reads over JDBC across clouds
• you’re not leveraging Delta Lake or lakehouse patterns until you copy it to ADLS anyway
• JDBC is not ideal for very large data volumes and you’ll end up implementing your own partitioned pulls, retries...

Option 3 :

Pros

• no ADW so you only pay for OCI Object Storage + Azure storage + the data movement compute
• storage on both sides is cheap and all heavy transformation happens once the data is in ADLS/Delta close to Databricks.
• architecture is clean and lakehouse friendly:
  • bronze: raw BICC files in ADLS
  • silver/gold: transformed delta tables in Databricks
• easier to standardise security, monitoring and governance on the Azure side.

Cons

• depends on a third party accelerator : license cost and vendor lock in
• you still need to handle incremental loads, schema drift, error handling

I’d aim for option 3 style architecture but implemented with native tooling where possible, for example:

1. BICC to OCI object storage (as you already have)
2. you can use either:
   • ADF / Synapse pipelines with an OCI object storage connector / REST to copy files into ADLS Gen2
   • a custom copy process (function / container) if you need more control
3. land raw data in ADLS (Bronze), then use Databricks for all transformations (silver/gold) and expose delta tables to Power BI

Answer 2

BICC is suitable for certain use cases, but it has several limitations and is not particularly user-friendly.  BICC uses PVOs which cause a huge operational gap among the users: IT/DW teams: Its a multi-hop(BICC-file system-OCI-ADW-delta share) process which is brittle and fixing any breaks is cumbersome.  You'll spend more time fixing things than actually getting value from data. BI & Data analysts:  Try to consume data via PVOs, struggle to understand schema, deal with missing or too many fields. Creating dashboards require back & forth and increases time to data.  Business users:  Struggle to find actionable insights in dashboards, try to make the best judgment, leading to inaccurate decisions, unresolved problems, and missed opportunities You are also spending a lot of money on OCI, ADW et al.  

Check out BI Connector(https://www.biconnector.com/oracle-fusion-data-warehouse-integration). It is a Power BI/Fabric/ADF certified connector which offers a more direct and cost-efficient approach to bring your Oracle Fusion to your Lakehouse/Data Warehouse without any of the above shortcomings.   Another advantage is that BI Connector allows you to directly bring Oracle Fusion data into Power BI(https://www.biconnector.com/powerbi-oracle-fusion-connector/) without any intermediate DW/Lakehouse.  You get a 2-in-1 solution.