How to backup and migrate OTBI objects in Fusion Cloud

Following  approaches one can use to promote OTBI objects from one instance to another. 

1) Manual :  Old school method. Manually follow same instruction as development. This is beneficial if change is simple only in layout or in one data set and you are unsure about other changes in development instance. 

2) Archieve/Unarchieve : 

On catalog screen we can see Archive and Unarchive options. 

Select the object and click archive for export OR

Click on More and select archive option. 

*.Catalog file will be created. 

Login to target instance and use unarchive option and select catalog file.

3) Upload/Download :

On catalog screen another option available is upload/download.

if download option is not available, open XMLPSERVER

<POD_URL>/xmlpserver

 Example :  https://san-test.fa.ap1.oraclecloud.com/xmlpserver

This will open BI Publisher Enterprise ( Old BI Publisher feature - Only BIP Data Model and Report are available). 

*.xdoz (Report)  & *.xdmz (Data Model) file will be downloaded. 

Logical and Physical Query in OTBI

OTBI reporting is based on pre-configured subject areas.  Oracle provide multiple SAs for respective area which can be used to create an OTBI analysis by drag drop and calculations.

Based on selected SA and columns system generates a logical query, which can be viewed from Advanced  => SQL.


Physical query is SQL used to fetch logical query from Oracle DB. Physical query can be view in nqquery.log [Doc ID 1469168.1 - How to generate nqquery.log]

Steps to view physical query 
1) Get the logical query
2) Go to Administraton >> SQL Issues >> Paste following line followed by logical query
SET VARIABLE DISABLE_CACHE_HIT=1,DISABLE_PLAN_CACHE_HIT=1;
<<logicalQuery>>
3) Click on view log