Connect Microsoft Fabric to Oracle Fusion Cloud Applications [4 Simple Steps]
Introduction
In recent years, Oracle has emerged as a front-runner in cloud-based database products and in creating enterprise-scale software applications. Enterprises worldwide across several industries run seamless business operations with Oracle Fusion Cloud applications.
The Oracle Fusion Cloud applications are mainly comprised of a suite of applications in the following streams:
- Enterprise Resource Planning (ERP)
- Human Capital Management (HCM)
- Supply Chain Management (SCM)
- Customer Experience (CX)
In essence, a large number of enterprises’ mission-critical data now reside securely on these Oracle Cloud applications. These enterprises have a pressing need to frequently move the Fusion data into platforms such as Microsoft Fabric and Power BI for numerous use cases, including the following, to point out a few:
- Monthly Trial Balance and General Ledger (GL) reporting for Oracle Financials Cloud users
- Fusion ERP users analyzing the Projects, Accounts Payable (AP) and Accounts Receivable (AR) data
- Fusion HCM users analyzing payroll, hiring, and attritions data, and Employee Reporting
- Fusion SCM users analyzing Inventory, Procurement data for simulations and scenario planning
- Fusion CX users analyzing Sales, Customer data
In addition to the use cases mentioned above, these enterprises are looking to tap into Microsoft Fabric’s powerful Artificial Intelligence (AI) and Machine Learning (ML) capabilities. The use case is tremendous, as companies, by leveraging Fabric, can now predict the future, make strategic business decisions proactively, and make the most of the opportunities ahead while mitigating risks. In fact, Fabric has the potential to take a business’s preparedness for the opportunities and risks in the future to a whole new level!
However, companies using Oracle Cloud applications face a major challenge: pulling the data out of Oracle at scale to create a single source of truth for performing data analysis and generating AI-powered insights.
In this article, we’ll explore four simple steps for pulling data from Oracle into Microsoft Fabric and Power BI.
The 4 steps are listed below:
- Create an Oracle BI Publisher Data Model
- Download and Install BI Connector on the Gateway Server
- Create a DSN to Oracle Fusion Cloud in BI Connector
- Connect from Microsoft Fabric and Power BI
Now, let’s dive into the details of each of these steps!
- Create an Oracle BI Publisher Data Model
The first step is to create a view of the data you want to fetch into Power BI or Fabric. This is made possible through the two reporting modules, OTBI and BI Publisher, which are available as a part of your Oracle Fusion Cloud applications.
Out of these two modules, the BI Publisher helps users retrieve data directly from the Oracle database. First, you need to log in to the BI Publisher and create a Data Model, which is just an SQL query routed directly to your Fusion Cloud account’s database.
You can create datasets (using the SQL query option) and parameters as required for the Data Model. Use parameters to cut down the query execution time as much as possible to facilitate faster data retrieval within 300 seconds (which is Oracle’s timeout limit for Data Models). Please note that this timeout limit applies to the time taken to execute the query, no matter the number of rows it retrieves.
Pro tip
Keep your Data Model query well-optimized to get it executed within the 300-second timeout limit. Remember that a well-optimized query can fetch millions of records in a few minutes, while an inefficient query could take several minutes just to fetch a single record.
- Download and Install BI Connector on the Gateway Server
The next step is to set up the BI Connector on the Power BI on-premise Gateway Server. The BI Connector connects Microsoft Fabric and Power BI to Oracle Fusion BI Publisher Data Models, which were created in Step 1.
The BI Connector, in technical terms, is an ODBC Driver that works on the 64-bit console. It’s an on-premise solution, and it does not view or save your Oracle data to facilitate the connection from Fabric or Power BI to Oracle Fusion Cloud.
You will need Admin access to the Windows Server on which the Gateway is hosted. Are you looking for the link to download the BI Connector? You may want to start your BI Connector journey here!
Pro tip
If you’re using a Gateway cluster, you need to install and configure the BI Connector on all the Gateways in the cluster. To avoid repetition of steps and save time, its recommended to use a cluster with just a single Gateway.
- Create a DSN to Oracle Fusion Cloud in BI Connector
The next step is to launch the BI Connector application on the Gateway Server, and activate your trial license key.
Then you can navigate to the Data Sources tab, and click on the New Data Source button. At this point, please follow the steps below to create the Data Source:
- Give the Data Source a convenient name for you to identify easily.
Ex: Fusion ERP - Select the Data Source type as Data Model
- Enter your Oracle URL and credentials in the appropriate fields
- Enter the path of the folder (on which the Data Model you created in Step 1 is located) on the Folder Path box, and click on the Verify & Add button.
An example path is:
/shared/Custom/General Ledger/Data Models
You can take this folder path for your Data Model by navigating to that Data Model in Oracle, and clicking on More and then Properties. In the dialog box that opens, you can see this path on the Location field. - Finally, click on Test and Save
Once the data source is created successfully, click on the Tables tab.
- Click on the Add Table button
- Give your table a name that’s easy for you to identify
Ex: Monthly Trial Balance - Select the Data Source you created moments ago from the dropdown on the Data Source box
- Click Next
- Open the folder after the load, navigate to the Data Model of your interest, and select the specific dataset
- Click on Next: Configure Parameters, and enter the appropriate Parameter values if your Data Model was set up with Parameters
- Click on Preview if you’d like to preview the data, and finally, click on Save
At this point, you have successfully created a connection to your Data Model in the BI Connector.
Pro tip
When pulling a large number of records from Oracle, use the “Number of rows per fetch” setting on the BI Connector table to perform a data chunking.
- Connect from Microsoft Fabric and Power BI
The last and final step is to bring the Oracle data to the Microsoft Fabric.
To achieve this, please start by creating a Dataflow and selecting the BI Connector option. Enter the name of the Data Source that you created in step 3, enter the Oracle credentials, and save the connection. Please note that if a connection to the same Data Source already exists on Fabric, you can go directly through the subsequent steps of selecting the table (which you created in BI Connector in step 3) and finally publishing the dataflow.
After the first refresh, you can also configure a scheduled refresh to facilitate the automated pull of Oracle data into Fabric based on your requirements.
You can then use this dataflow in a warehouse, a lakehouse, or a datamart to analyze the data and also use it for your AI and ML projects for predictive modeling and insights.
In addition, you can also create a dashboard in Power BI Desktop and publish and automate the report in Power BI Service for the day-to-day reporting needs of your users. With BI Connector, you can easily pull your Oracle data to Power BI and Fabric in minutes!
Pro tip
When creating a Dataflow, you must refresh it manually the first time you pull the data into Fabric. After that, you can create an automated schedule of refreshes.
The mystery of bringing your Oracle Fusion Cloud data to Microsoft Fabric is now solved with these simple 4 steps. You can find more details on using the BI Connector with Fabric and Power BI on the support portal as well!