Using Physical SQL (PVOs) to Extract ERP Cloud Data from OTBI

Physical SQLs are essentially the same as Logical SQLs. Behind the scenes, Logical SQLs are converted back to physical SQLs by BI server using the OTBI Fusion public view objects (PVOs) defined in the RPD physical layer. Physical SQLs leverage the same Data Sync built-in Oracle BI Connector used by logical SQLs for extracting ERP Cloud data from OTBI. The process to create physical SQL data mappings is the same as that to create logical SQL ones.

1. Create an OTBI connection

Open the Data Sync UI, click Connections button on the top, and then click on the New icon button to create a new Data Sync connection using Oracle BI Connector.

Edit URL field.  Enter the web service URL for OTBI analytics

https://<otbi-cloud-url>/analytics-ws

If the web service URL does not work, try enter the normal URL for OTBI analytics

https://<otbi-cloud-url>/analytics

The former (web service URL with suffix -ws) uses less wrappers than later (the normal URL with analytics only), therefore it’s more efficient and should be always used if possible.

2. Create OTBI physical SQLs

OTBI physical SQLs are manually created with the help of a copy of RPD and the Oracle Business Intelligence Developer Client Tools. Both can be downloaded from Oracle website for the 11g release. RPD is downloaded as part of the Oracle Business Intelligence Applications.

Find PVOs in physical layer

Open RPD in Oracle Business Intelligence Developer Client Tools, use either Query Repository … or Query Related Objects to find PVOs in the RPD physical layer.  For example, to find Supplier dimension

Using Query Repository …

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Using Query Related Objects

In addition, the OTBI data lineage cross reference spreadsheet provided by Oracle can also be used to find the OTBI Fusion PVOs.

After finding the PVOs, physical SQLs can now be created by using this format.

select_physical * from "<otbi-fusion-database-name>"."".""."<otbi-fusion-public-view-object-name>"

Where

<otbi-fusion-database-name> is the Fusion database name defined in the RPD physical layer

<otbi-fusion-public-view-object-name> is the Fusion PVO name defined in the RPD physical layer

Test physical SQLs

Log in to OTBI analytics, go to Administration, open Issue SQL, enter the physical SQL, and click Issue SQL button.

select_physical 
    *
from 
    "oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."".""."FscmTopModelAM.PrcPozPublicViewAM.SupplierPVO"
FETCH FIRST 100 ROWS ONLY

The query will return the first 100 rows of the Fusion PVO. The sample data can be copied and pasted from the browser to an excel spread sheet to work out the final physical SQL.

select_physical 
 VendorId as Supplier_Id , PartyPartyName as Supplier_Name ,
 PartyPartyNumber as Supplier_Number , PartyDunsNumberC as Duns_Number ,
 SupplierVendorTypeLookupCode as Supplier_Vendor_Type ,
 PartyPartyType as Supplier_Type ,
 SupplierLastUpdateDate as Last_Upate_Date 
from 
 "oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal"."".""."FscmTopModelAM.PrcPozPublicViewAM.SupplierPVO"
FETCH FIRST 100 ROWS ONLY

3. Create Data Sync mappings

Go back to Data Sync UI.  Choose Project, select Pluggable Source Data tab, then click Manual Entry to create a new data mapping.

Select SQL.  Paste the physical SQL created from step 2 above for both Initial SQL and Incremental SQL.  Remove last FETCH FIRST 100 ROWS ONLY line from the query.

Go to Target Tables/DataSets tab, and fix all mapping columns set to FLOAT data type to use NUMBER data type with correct data length and precision.

Go to select Pluggable Source Data tab and the new created data mapping, and in Pluggable Attributes tab update the Incremental SQL to include the ‘%LAST_REPLICATION_DATETIME%’ timestamp filter.  The physical SQL data mapping now is ready for incremental loads.

So which to use, logical SQLs or physical SQLs?  Functional wise, both physical SQLs and logical SQLs work the same way, as they are created using the same built-in Oracle BI Connector process.  The difference is the SQL starting point: logical SQLs start from the SA; physical SQLs start from the RPD physical layer that the SA is mapped to.  Therefore, physical SQLs are more flexible and capable than logical SQLs in terms of data visibility and incremental load ability.

Previous << Using Logical SQL …   |   Next >> Using Custom SQL …