Now that we have three different approaches to extract Oracle ERP Cloud data from OTBI, there comes the question – what’s the best practice, or which one should you recommend? Before answering the question, let’s take a look at the feature list of each approach so we can understand what they are best at.
Feature List
| Features | Logical SQL (SA) | Physical SQL (PVO) | Custom SQL (BIP) |
| Uses the built-in Oracle BI Connector in Data Sync for data mappings | x | x | |
| Leverages ERP Cloud Public View Objects (PVOs) for execution | x | x | |
| Complies with ERP Cloud PVO data security | x | x | |
| Supports data incremental loads | x1 | x | x2 |
| Uses analyses’ advanced feature to obtain logical SQLs | x | ||
| Allows access to what is not available in Standard SAs | x | ||
| Allows future switch to Logical SQLs if desired | x | ||
| Leverages BI Publisher Web Service for execution | x | ||
| Allows direct access to ERP Cloud database for what is not available in neither Standard SAs nor PVOs | x | ||
| ——— | |||
| 1 Last modify date required for incremental load is not always available in all subject area folders | |||
| 2 Last ETL run date needs passed in as a report parameter | |||
General Recommendation
There are two questions that almost any data mapping developer will need to answer.
- Is all data anticipated available from the given data source?
- Is incremental load critical and supported?
This is also true when comes to which approach should be recommended for extracting ERP Cloud data from OTBI. Based on answers to these two questions and features each approach presents, the general approach would be recommended in the given order:
- Use Logical SQLs approach for any data mappings that require data already available in OTBI standard SAs or custom SAs, and incremental is possible whenever Last Update Date is available in the OTBI SAs.
- Use Physical SQLs approach for any full blown data mappings that require data not available in OTBI standard SAs but available in ERP Cloud PVOs, and incremental load is critical and can be supported using available Last Update Date in ERP Cloud PVOs.
- Use Custom SQLs approach for any data mappings that require data not available from either OTBI SAs or the ERP Cloud PVOs, and ERP Cloud PVO data security is not a concern.
Best Practice in Reality
Ideally, the Logical SQLs approach should be sufficient and all that is needed for extracting the ERP Cloud date from OTBI. But reality is always bringing up a different story.
- ERP Cloud only provides standard SAs in OTBI and does not allow any custom SAs or adding any missing columns to standard SAs. For example, the Supplier folder in Payables Invoices – Transactions Real Time SA does not have supplier last update date column.
- SAs in OTBI are organized in report centric, not in data centric, and consist of the partial underlying physical data. For example, when creating data mapping for Customer, it’s quickly discovered that complete customer data will have to be pulled from three different SAs.
- Implicit fact join in SAs could produce incomplete many to many dimension data
In reality, the best practice would be using the Physical SQLs approach. Reasons are simple.
- Physical SQLs has all the features from Logical SQLs
- Physical SQLs has access to what is not available in Logical SQLs
- Physical SQLs has ability to switch to Logical SQLs in the future if desired or when custom SAs is available in OTBI
- Physical SQLs complies with Fusion PVO data security that Custom SQLs bypasses
The Physical SQLs approach is safe to use and future proof.
Previous << 3 Ways to Integrate … | Next >> Using Logical SQLs …
