Here are the blogs that I have posted previously as part of my blog series on Oracle DataSync (BICS).
In this blog, I am going to talk about three different ways to integrate Oracle ERP Cloud data with Data Sync (BICS) through OTBI.
1. Using Logical SQL
Data Sync comes with a built-in Oracle BI Content connector which can be used to extract ERP Cloud data from OTBI. The connector supports 3 data extraction modes.
- SQL – used to extract data from logical SQL queries
- Report – used to extract data from BI analyses reports
- Subject Area.Table – used for extracting data from Subject Area folders
The SQL mode is more versatile and useful, should be recommended. The logical SQL supports incremental load for performance, and once created, does not have any reliance on any saved analyses reports. The Report mode does not support incremental load, and has a limitation that can not exceed the 65,000 max data download size. The Subject Area.Table mode is more useful if the Subject Area folders closely mimic the data structures in BICS.
2. Using Physical SQL
Essentially this is the same approach as Using Logical SQL in SQL mode. It uses physical SQL to extract ERP Cloud data from OTBI. The logical SQLs are constructed with the help of OTBI analyses report’s advance tab. The physical SQLs are constructed by using the RPD physical tables.
Such approach is very useful when certain critical data elements are not available or possible from logical SQL, Report, and Subject Area.Table. For example, in the Payables Invoices – Transactions Real Time Subject Area, the Supplier folder does not have a last modified date column. In order for the Supplier dimension to run incrementally, such date column is necessary. Incremental load should always play a crucial role in BICS, as it significantly improves the data load performance by reducing data load size and time.
3. Using Custom SQL
This approach does not use the built-in Data Sync Oracle BI Content connector, instead, it leverages the BI Publisher Web Service features to extract ERP Cloud data from OTBI. By creating a custom data model and custom SQL in OTBI, BI Publisher reports can be created to export data as flat files directly from fusion database tables through Web Service calls. The exported flat files can then be picked up by Data Sync’s file trigger automatically for upload to BICS.
This is is a very powerful approach, and can be very useful for loading data that are not available from OTBI using either logical or physical SQLs.
Here is the diagram to summarize this blog:
In my next coming up blogs, I am going to show you the details on:
- Using Logical SQL to Extract ERP Cloud Data from OTBI
- Using Physical SQL (PVOs) to Extract ERP Cloud Data from OTBI
- Using Custom SQL (BIP) to Extract ERP Cloud Data from OTBI
Previous << Best Practices to ETL … | Next >> Best Practices in Reality …
