June 24th, 2025
Integrate Oracle Fusion Cloud with Data Lakehouse or Warehouse: The Ultimate Guide to Streamline Your Data Flow using BI Connector With Fabric
Introduction
Enterprises worldwide rely on Oracle Fusion Cloud applications to run their businesses. Most companies use at least one application from four main Fusion Cloud modules—Enterprise Resource Planning (ERP), Human Capital Management (HCM), Supply Chain Management (SCM), and Customer Experience (CX).
Enterprises using Fusion Cloud applications enjoy plenty of advantages that almost no other alternative provides. This includes Financials, General Ledger, Payables, Receivables, Procurement, Inventory Management, Projects, Talent Management, Payroll, CRM, or everything from A to Z that a business needs to run smoothly. It’s no surprise that these organizations’ mission-critical data live in Oracle Cloud.
However, getting data out of Oracle Fusion Cloud is still a huge challenge. This applies to data warehousing and data lake strategy use cases. In this blog post, we’ll explore the top use cases for Data Lakehouse integration. We’ll also look at the bottlenecks companies face, the tools available, and the most efficient way to streamline data flow between Oracle Fusion Cloud and your Data Lakehouse or Warehouse.
Oracle Fusion Cloud + Data Lakehouse Integration: Top Use Cases
When it comes to data, all enterprises face a common challenge. They must create and maintain a unified data repository. This is used for operations, analytics, compliance, and machine learning, among other purposes.
Each day, companies generate growing volumes of data across various formats. These include texts, audio, images, videos, files, and documents. Even with technological advances, companies still struggle to manage it all.
In this section, we’ll examine the top use cases for integrating Oracle Fusion Cloud with a Data Lakehouse or Warehouse.
Single Source of Truth
Creating a single source of truth remains the largest use case for Oracle Fusion Cloud and Data Lake/Warehouse integration. Enterprises have maintained a single source of truth for years in a central data warehouse on platforms such as SQL Server, Azure SQL or Snowflake. These companies are already bringing their data from different sources (Salesforce, ServiceNow, ADP, or others) into their Data Warehouse platforms. However, when they adopt Oracle Fusion Cloud, they struggle to bring Fusion data into the warehouse.
Migrating from EBS to Fusion Cloud: The Missing Piece
In recent years, the concept of a Data Lake or Lakehouse gained traction. This is due to its flexibility to store unstructured data. The Data Lake strategy helps companies maintain a unified data source without concerns about data formats.
A common scenario occurs when companies migrate from Oracle e-Business Suite (EBS) to Fusion Cloud. These businesses need solutions to consolidate data from both On-prem EBS and Fusion Cloud in one central warehouse. The EBS side is simpler to solve. It comes bundled with an on-prem Oracle database that’s directly accessible. Enterprises can easily move EBS data to their data lakehouse or warehouse.
Their only challenge is moving data from Fusion Cloud to the same destination. This remains the unsolved part of the puzzle. Additionally, maintaining a centralized enterprise-level schema simplifies BI reporting. It helps companies rely on a single data source and avoid duplicate data modeling in their analytics tools.
Modern BI and Analytics Capability
The next most prominent use case is from a BI and analytics standpoint. The Oracle Fusion Cloud applications are bundled with the Oracle Transactional Business Intelligence (OTBI) reporting module. However, the module has limited data visualization capability. This limitation drives users to bring Fusion data into user-friendly tools such as Power BI.
Visualizing Oracle Fusion data is already a challenge. In addition, users often need to blend it with data from other sources. This further increases the demand for external analytics tools
AI and ML Projects
One of the fastest-growing use cases in recent years is Oracle Fusion Cloud integration for AI-driven insights. Many enterprises aim to extract actionable intelligence from Fusion data.
The main driver behind this trend is the growing demand for Predictive Analytics.
Companies especially want predictions for Financial Planning, Demand and Supply Management, Talent Acquisition, Inventory Maintenance, and Fraud Detection.
Historical Data Capture is crucial for AI and ML projects. Capturing Oracle data from Fusion Cloud applications is a top priority for these organizations. This capability allows them to forecast trends. They can also make proactive decisions to achieve strategic business goals.
Oracle Fusion Cloud + Data Lakehouse Integration: Bottlenecks Faced
In this section, we’ll take a quick look at the main bottlenecks. These are the common issues companies face when integrating Oracle Fusion Cloud with a Data Lakehouse.
Fusion Database Inaccessible
When enterprises purchase Oracle Fusion Cloud ERP, SCM, HCM, or CX applications, Oracle creates a database behind the scenes. This database powers the applications on the Oracle Cloud.
Fusion users usually understand these raw database tables well. They refer to them as “base tables.” With that knowledge, they can easily find the fields needed for BI or reporting purposes. However, unlike on-prem Oracle applications such as Oracle EBS, the cloud application databases are not directly accessible. Oracle EBS includes a bundled database that users can access directly.
To access Fusion Cloud database tables, users must create Data Models. This involves writing SQL queries within the BI Publisher module.
Oracle Transactional Business Intelligence (OTBI) Limitations
Oracle Fusion’s native reporting module is OTBI. This tool allows users to create a view of the data they want using Subject Areas. “Subject Areas” refers to a different set of out-of-the-box schemas. Oracle creates and exposes these schemas in OTBI for Fusion Cloud users. These schemas are completely different from the base tables.
Some users mistakenly assume the Subject Areas are base tables. They still manage to find the data they need. Then, they create an Analysis report or a view using that data. However, in the bigger picture, the OTBI doesn’t solve the problem of integrating the Fusion Cloud with a Data Lakehouse. Further, users find it difficult to use the OTBI for data visualization as well. For this reason, only a few users rely on OTBI to create a simple view of the data they need.
Oracle Business Intelligence Cloud Connector (BICC) Limitations
Oracle BICC is the native tool that Oracle provides to Fusion customers. It helps extract Fusion Cloud data mainly for BI and ETL (Extract, Transform, and Load) purposes.
However, BICC does not provide direct access to the base tables. Instead, it exposes a different set of tables called Public View Objects (PVOs). The PVOs are flat table views that can be tapped into for getting the Fusion Cloud data out of Oracle. The most commonly faced challenges with the BICC PVOs are:
The most common challenges with BICC PVOs include:
- Users must explore and understand the PVOs on their own. This is unlike raw tables, which they are more familiar with.
- PVOs do not expose all Fusion Cloud fields. Missing fields often prevent users from completing BI or ETL projects using BICC.
- Users must set up a UCM Server or a database of their choice as a staging environment.
- Using BICC also requires custom coding skills. This makes it less attractive for users who want to build and manage pipelines quickly.
BI Publisher Limitations
BI Publisher reports are part of a complex reporting setup. They are mainly used by super users because SQL query skills are required. BI Publisher allows users to fetch any data fields from Fusion Cloud.
This includes fields that are not available in BICC PVOs. However, users still face several limitations.
Some of these challenges include row and memory limits. There are also difficulties when setting up incremental and automated refreshes.
Additionally, if users make any changes after setup, the report often breaks. They are then forced to start the process from scratch. In short, BI Publisher limitations prevent users from fully automating Fusion Cloud and Data Lakehouse integration.
Rest API Limitations
Oracle provides Rest APIs for Fusion Cloud integration, but they are mainly focused on daily operations to talk to external applications. The Rest APIs do not work well for reporting or ETL use cases, mainly due to their limitation of 500 rows that can be fetched at a maximum per request at a time.
The massiveness of Fusion Cloud applications, and enterprises’ storage of millions of rows of data in them, make it impossible to use these Rest APIs for data warehousing or data lake purposes.
Accumulating complementary Tech Stack
Given the limitations and challenges discussed above, companies often try to adopt a complementary tech stack from Oracle that quickly integrates with Fusion applications. Most of these complementary platforms cater to any of three purposes: Data Integration, BI and Analytics, and Data Warehouse.
For example, enterprises use Oracle Data Integrator (ODI) with an Autonomous Data Warehouse (ADW) to extract Fusion cloud data and store it in a Warehouse.
Another option is to use Oracle Analytics Cloud (OAC) for BI and reporting purposes. This would overcome the OTBI’s data visualization limitations and blend the Fusion data with other data sources, making the OAC’s semantic model on the presentation layer an effective Data Warehouse.
Another alternative that enterprises frequently encounter is Fusion Data Intelligence (FDI), formerly known as Fusion Analytics Warehouse (FAW), which is comprised of an OAC instance and ADW bundled together.
The enterprises incur additional costs to purchase these complementary platforms. Companies that have already adopted Microsoft or any non-Oracle platform for the majority of their tech stack find the accumulation of such complementary platforms challenging and difficult to manage.
Third-party Tool Limitations
The last resort that enterprises rely on to solve the problem is third-party tools. A couple of third-party tools cater to this need, but while these tools overcome some of the challenges discussed above, they fall short of expectations on a few others.
Let’s take a look at some of the top third-party tools and their limitations below:
SQLConnect
SQLConnect is an Oracle Cloud querying tool that can directly query and fetch data from Fusion Cloud’s base tables. The downside of this tool is that users have to write a query and manually run it every time they want to get data out of Fusion Cloud, and support is limited only to emails.
CData Connect Cloud
The CData Connect Cloud product connects only to the Rest API views of Fusion Cloud. Further, it is prone to row limits (10 million rows/month on standard plan), connection limits (5 on standard plan), and security considerations such as saving your Fusion Cloud credentials on CData’s servers to get the connection to work.
CData Connectors for Oracle Financials and HCM—The CData for Oracle Financials and HCM connectors are on-prem connectors and not prone to row or connection limits. But then, like CData Connect Cloud, they connect only to the Rest API views and not to the base tables.
Fivetran – With Fivetran, users can connect to the BI Publisher reports and get customized base table views from Oracle Fusion Cloud. But the major downside of using Fivetran is sticking to Fivetran’s row limits, technically referred to as Monthly Active Rows (MAR).
Manual Excel Exports
Some enterprises, after finding that the third-party tools have their own set of limitations, go back to square one and try to use Manual Excel exports, only to later find out that this option is not viable for consistently moving the Fusion Cloud data into a centralized data lakehouse or a warehouse.
The Smartest Way Out: Use BI Connector with Fabric
The problems in integrating Oracle Fusion Cloud with a Data Lakehouse or Warehouse generally create enterprise-wide chaos, with each team taking a different approach to solving the problem.
The IT teams usually prefer the BICC, while the Database/BI developer teams prefer a solution like OTBI/BI Publisher or ODI/ADW or OAC/FDI/FAW or third-party solutions. Each team is prone to getting stuck midway, causing the project to progress slowly with huge, unprecedented delays (that can take a couple of years just to arrive at the starting point) or even get stalled at a certain point.
But hang on. The good news is that using the BI Connector with Microsoft Fabric is the only “from chaos to clarity” solution that helps enterprises overcome all the bottlenecks discussed above, not within years, quarters, or months, but within minutes!
What’s BI Connector?
The BI Connector is a Microsoft-certified connector for Oracle BI and Analytics solutions, and it blends perfectly with Oracle Fusion Cloud’s reporting ecosystem, where the base tables can also be accessed. The BI Connector is also used with Power BI for quickly and easily visualizing Oracle Fusion Cloud data.
When it comes to integrating Oracle Fusion with your Data Lakehouse/Warehouse, the BI Connector is the enterprises’ go-to tool to pull the necessary data out of Oracle. The Microsoft Fabric can then be used to push the data into your desired Data lakehouse or warehouse. Our solution, BI Connector, is a complete on-premise solution and it does not view or save your Oracle credentials or data to facilitate the connection.
BI Connector – Fabric Architecture
The BI Connector Fabric architecture is simple. All you need to do is set up a Power BI Gateway Server and install the BI Connector application on it. In the BI Connector app, you just need to create a connection to your Oracle Fusion Cloud instance and set up a data source.
Then, you can easily use this BI Connector connection on the Gateway Server in Microsoft Fabric, and create pipelines to push the data to your desired data lakehouse or warehouse. Based on your needs, you can also use incremental loads.
The below architecture depicts how the data flows from Oracle Fusion to your desired data lakehouse via BI Connector and Fabric:
As a quick side note, you can also use the BI Connector to connect Power BI to Oracle Fusion Cloud for data visualization. In this architecture, the Microsoft Fabric is not required.
Now, let’s take a quick look at the three smart approaches that enterprises take to moving Fusion Cloud data to their Data Lakehouse using BI Connector with Fabric.
Smart Approach 1: Migrate Fusion Base Tables to Data Lakehouse or Warehouse
The smartest way to use the BI Connector for Fusion Cloud data integration with Data Lakehouse is to use the connector with Microsoft Fabric and run a data pipeline with a “For Each” activity to call the list of base tables that you want to fetch. With this method, enterprises can connect to any of the 40,000+ Fusion tables and pull the data out of Oracle Fusion, both as a full pull and as incremental loads.
A BI Connector user successfully migrated 200+ Fusion base tables from Oracle Fusion to their central data warehouse within a few hours. The entire setup, including configuring incremental loads twice a day for all tables, was completed in minutes. Each table was setup with a hash key with a last updated column, which was used as a reference for subsequent incremental loads.
The query for all the base tables that you want to pull can be parameterized and run as a simple script on the Fabric pipeline. This way, you don’t even have to setup each base table as an individual pipeline.
Smart Approach 2: Migrate Fusion Data From BI Publisher Data Models
The next smartest way is to create a view of the data you want from the base tables as a BI Publisher Data Model. The Data Models must be created first in the Oracle Fusion BI Publisher module and then connected via BI Connector. This approach requires a detailed understanding of SQL queries and comes in handy for Oracle Fusion reporting users.
The reporting users can join data from multiple base tables into a single view and then pull the data to Fabric by creating a dataflow using the BI Connector connector, and finally create a pipeline to push the dataflow to the preferred data lakehouse or warehouse.
This approach also works with both out-of-the-box Data Models that Oracle provides in Oracle Fusion.
Smart Approach 3: Migrate Fusion Data From OTBI Analysis Reports
This approach is intended for users who already have a good understanding of the Subject Area schemas instead of the base tables. They can quickly create an OTBI Analysis report even without writing an SQL Query, and connect to that Analysis view via a Fabric dataflow using the BI Connector connector.
The process of pushing the Fusion data to the data lakehouse is the same—just create a pipeline in Fabric, map the dataflow as the source and the data lakehouse as the destination, and map the columns as required. This approach also works with the out-of-the-box OTBI reports that Oracle provides in Oracle Fusion.
The BI Connector Advantages
In this section, let’s take a look at the advantages companies get by using BI Connector with Fabric for integrating Oracle Fusion Coud to Data Lakehouse.
Connect to the Fusion Cloud’s base tables
Oracle Fusion Cloud’s base tables are invaluable for any enterprise’s Data Lake strategy. With BI Connector in their arsenal, enterprises can easily access these base tables in minutes. In addition, the BI Connector also caters to the needs of users who are already familiar with the BI Publisher Data Models and OTBI Analysis reports.
No row or connection limits
Unlike many other third-party alternatives, BI Connector does not apply row or connection limits to data pulled from Oracle Fusion Cloud or pushed into the data lakehouse via Fabric. You don’t have to worry about the connection limits when using an Oracle Fusion Dev or Test instances with the BI Connector.
Save time and money
BI Connector is available at a fraction of the Oracle Fusion cost, and it helps enterprises save a ton of money and time. With automated incremental data loads, they radically shift their focus from data engineering to data analysis and dashboarding and uncovering hidden insights and patterns.
Microsoft-certified connector
The BI Connector passed many stringent tests by the Microsoft team and got listed as a standard connector in the Fabric and Power BI ecosystems.
Overcomes Oracle’s timeout and data size constraints
Oracle Fusion Cloud instances have stringent API timeout and memory limits. The BI Connector automatically modifies the API requests to stay within Oracle’s timeout and memory limits while fetching all the required rows without compromising performance.
Does not save your Oracle credentials or data
The connector does not save Oracle credentials or data on its servers to facilitate the integration of Oracle Fusion Cloud with your Enterprise Data Warehouse or Lakehouse. The BI Connector is entirely an on-premise solution.
Email and call support to all users
The BI Connector team has a tailored user experience for each customer to achieve their use case successfully. The team onboards the users on the connector during their trial and also after they purchase the subscription. The BI Connector support is provided via a robust ticketing system. While most problems are resolved via emails, the BI Connector team would also give call support whenever required.
Conclusion
Many organizations had a huge problem with Oracle Fusion Cloud with Data Lakehouse or Warehouse integration. BI Connector solves the problem in just minutes. It’s time to move from confusion to clarity! Check out the BI Connector trial now!
