May 8th, 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, be it in 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 for data warehousing, or data lake strategy purposes still remains a huge roadblock for these organizations. In this blog post, we’ll see the list of top use cases for Data Lakehouse Integration, the bottlenecks faced, the tools available, and the most efficient way to achieve seamless dataflow between Oracle Fusion Cloud and your Data Lakehouse/Warehouse.
Oracle Fusion Cloud + Data Lakehouse Integration: Top Use Cases
When it comes to data, all enterprises are grappling with a common challenge—creating and maintaining a unified repository of all the organization’s data for various purposes, including but not limited to operations, analytics, compliance, and machine learning.
With the growing amount of data generated each day across formats ranging from texts to audio to images to videos to files to documents to bar codes to QR codes, it’s no surprise that companies still face this challenge despite technological advancements.
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, Snowflake, etc. These companies are already bringing their data from different sources, such as Salesforce, ServiceNow, ADP, etc., into their Data Warehouse platforms. However, when they start using Oracle Fusion Cloud, they face the challenge of bringing the Fusion data to their Data Warehouse.
In addition, in recent years, the concept of a Data Lake or Lakehouse has rapidly caught up due to its flexibility to store unstructured data as well. The data lake strategy helps companies maintain a single source of truth without worrying about the data format.
One specific scenario that’s common is when enterprises already using an Oracle e-Business Suite aka EBS (the on-premise version of Fusion apps), switch to Fusion Cloud, they look for solutions that could help them consolidate the data both from On-prem EBS and Fusion cloud applications on a central data warehouse. In this scenario, the EBS side of the puzzle is already solved as it comes bundled with an Oracle on-prem database that’s directly accessible, and hence the enterprises can easily move the EBS data to a preferred data lakehouse or warehouse. The enterprises only search for ways to solve the other part of the puzzle—moving the data from Fusion Cloud to the warehouse.
Further, maintaining an enterprise-level schema on a central data warehouse that pulls data from all applications helps companies rely on a single data source for BI reporting purposes and eliminates redundant data modeling efforts on their reporting and 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, which is driving users to bring the Fusion data to a user-friendly data visualization tool such as Power BI.
While visualizing the Oracle Fusion data is a challenge, the users’ need to blend it with other data sources is another major force driving this use case.
AI and ML Projects
One of the fastest-growing use cases in recent years for the Oracle Fusion Cloud integration to a Data Lake/Warehouse is getting AI-driven insights out of Fusion data. The main driver for this use case is the hunger for Predictive Analytics among enterprises, especially for purposes such as Financial Planning, Demand and Supply Management, Talent Acquisition, Inventory Maintenance, and Fraud Detection.
Historical Data Capture is one of the crucial aspects of AI and ML projects, and capturing Oracle data from Fusion Cloud applications is at the forefront for these organizations. This allows them to predict the future and make proactive decisions to achieve business goals.
Oracle Fusion Cloud + Data Lakehouse Integration: Bottlenecks Faced
In this section, we’ll take a quick look at the list of bottlenecks that companies are facing when integrating the Oracle Fusion Cloud with Data Lakehouse.
Fusion Database Inaccessible
When enterprises purchase Oracle Fusion Cloud ERP, SCM, HCM, or CX applications, Oracle automatically spins up a database behind the scenes on the Oracle Cloud to power those applications. Fusion users have a good knowledge of these raw database tables (most users refer to them as the “base tables”) and can explore and find the fields they need for BI or reporting purposes much faster.
However, unlike the On-prem Oracle applications such as the Oracle EBS (that comes bundled with an Oracle database that’s accessible directly), the cloud applications’ databases are not directly accessible. The only way the user can get access to these Database tables right now are by creating Data Models (by writing SQL queries) in the BI Publisher module.
Oracle Transactional Business Intelligence (OTBI) Limitations
Oracle Fusion’s native reporting module, OTBI is a tool where users can create a view of the data they want from Subject Areas. The “Subject Areas” is a terminology given to a different set of out of the box schemas that Oracle creates and exposes in OTBI for the Fusion Cloud users, and these schemas are entirely different from the base tables.
A section of the users who presume the Subject Areas as base tables, manage to figure out the data they need and create an Analysis report (or a view of the data they need) out of it. 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. So only a few users rely on OTBI just as a tool to create a 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 for extracting Fusion Cloud data mainly for BI and Extract, Transform, and Load (ETL) purposes.
The BICC still does not provision direct access to the base tables but exposes a different set of tables that are termed 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:
- Users are required to explore and understand the PVOs themselves, unlike the raw tables that they are quite familiar with
- PVOs do not expose all the fields from Fusion Cloud. Users frequently deal with friction in the form of missing fields that pretty much stop them from pursuing their BI or ETL projects using BICC
- The users must set up a UCM Server or a database of their preference as a staging environment
- Using BICC also requires a certain level of custom coding skills, making it unattractive for users looking to quickly build and manage a data pipeline
BI Publisher Limitations
The BI Publisher reports are a complex reporting setup that’s utilized mainly by super users, as it requires SQL query writing skills. Though BI Publisher helps users fetch any data fields from Fusion Cloud (including the ones that are unavailable in BICC PVOs), it leaves users struggling with a different set of limitations, such as row and memory limits and complexities in setting up incremental and automated refreshes.
Further, if any changes are made in the BI Publisher report after completing the cumbersome setup, the setup is prone to break, forcing the users to start from scratch again.
In essence, the BI Publisher’s limitations leave users with no options for effectively automating the Fusion Cloud and Data Lakehouse integration use cases.
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. The BI Connector is a complete on-premise solution and it does not view or save your Oracle credentials or data to facilitate the connection.
The 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 through zoom web-conferencing whenever required.
Conclusion
Many organizations had a huge problem with Oracle Fusion Cloud with Data Lakehouse or Warehouse integration. With the BI Connector, the problem is solved in minutes. It’s time to move from confusion to clarity! Check out the BI Connector trial now!
