Working with Bursting in Oracle Fusion

Requirement 
How to define delivery details for an BIP report at run time ? 

Solution
Similar to e-Business BIP reports are delivered to use bursting concept for run time delivery selection. 

Bursting SQL need to be defined at Data Model. 

Following are the options to run data model and bursting scope - 

• Data Model - Data model can be executed directly from data model by using view button.
  Bursting does not execute in this.
  
  
• Report (Online View) - Report can be run using Open option in Oracle analytics. Bursting  defined at associated Data model does not execute in this. 
  
  
• Report submitted as Job - Report can be run using (More > Schedule) option in Oracle analytics. Bursting  defined at associated Data model may/may not execute in this. In order to execute Report ned to be updated by selecting properties option and enable bursting need to be checked if bursting is defined at linked data model. 

 

• ESS Job - If ESS job is setup in Oracle fusion and linked with BIP report. Any bursting defined at associated data model will be executed when report run using ESS job. 

How to use Fast Formula in HCM Extract

Requirement 
Can we use Fast Formula in HCM Extrat to derive any Database calculation ?

Solution 
Oralce provides rule type attribute in HCM extract.  We can use this type to use fast formula driven attribute. 

In order to access Fast formula in Extract attributes, use type = "Extract Rule" 

If using GET_VALUE_SET method to cal value set ( only TABLE type value set are supported). Please ensure you pass value to ID field as well.  

How to schedule a report with timestamp in filename

 Requirement 
Schedule a report to deliver output file with timestamp in filename so it can be differentiated 

Solution
Dynamic filename can be achieved using 2 ways - 

1) User delivery option manually in schedule

In this method in delivery tab, select the delivery method and use below syntex in filename - BIPReport_%y%m%d%H24%M%S.txt

2) Define bursting and use SQL method of appending timestamp in filename parameter -  

'BIPReport_'||to_char(SYSDATE, 'YYYYMMDDH24MISS'||'.txt' PARAMETER5

How to default Date value in parameter

Requirement

Default dynamic date parameter values 

Solution 

Use following predefined function to default date para,eters -

 {$SYSDATE()$}                            – current date

 {$FIRST_DAY_OF_MONTH()$} – first day of the current month

 {$LAST_DAY_OF_MONTH()$}  – last day of the current month

 {$FIRST_DAY_OF_YEAR)$}      – first day of the current year

 {$LAST_DAY_OF_YEAR)$}       – last day of the current year

*Dates are system date of the server on which BI Publisher is running.

Example 

How to add new attribute in Seeded Data Model - Simple Reports

Requirement 
Enhance a simple seeded report by adding additional attributes is very common. 

Solution1
We can enhance the seeded data set by modifying SQL statement using new attributes. 

Example
DataSet SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
FROM
ap_invoices_all 

Need to display Business Unit Name. 

Modified Data Set SQL 

SELECT
invoice_num
, invoice_id
, invoice_date
,fbu.bu_name
FROM
ap_invoices_all apa
,fun_all_business_units_v fbu
WHERE
apa.org_id = fbu.bu_id

OR 

SELECT
invoice_num
, invoice_id
, invoice_date
,(SELECT bu_name from fun_all_business_units_v where bu_id = apa.org_id) bu_name
FROM
ap_invoices_all apa

Solution2
We can add a new data set for requested attributes and common attribute from existing data set. Then link the new data set using common attribute. 

DataSet1 SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
,org_id
FROM
ap_invoices_all 

Need to display Business Unit Name. 

DataSet2 SQL 

SELECT
bu_id
,bu_name 
FROM
fun_all_business_units_v 

Linke DataSet 1 with DataSet2 using attribute Org_id (DS1) and Bu_id (DS2).

 

Profile Utilities:Diagnostics

 Utilities:Diagnostics to enable Access to  custom code under HELP > Diagnostics

JAR Files

Create JAR File 

jar cvf  <jar file name> <class name>....

Example 

jar cvf  TEST.jar A.class B.class

Execute Jar file 

java -cp ./<jar file name> <class name> <properties file>

Example 

java -cp ./SKPDebugREST.jar DebugREST DebugREST.properties

CURL

Open Command Prompt 

SKP>curl -help 

This shows curl utility is installed 

This shows utility is missing and need to install. 

Download and install - Win64OpenSSL-1_1_1i.exe

Oracle Fusion URLs Tips

Oracle Fusion Login URL 

https://<POD_NAME>.login.<DOMAIN>.oraclecloud.com

https://san-test.login.ap1.oraclecloud.com/

Oracle Fusion Application URL

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com

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

Oracle Fusion UCM URL 

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/cs

https://san-test.fa.ap1.oraclecloud.com/cs

Oracle Transnational BI Publisher  URL 

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/analytics

https://san-test.fa.ap1.oraclecloud.com/analytics

Oracle Fusion BI Publisher Enterprise URL 

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/xmlpserver

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

Oracle Business Intelligence Cloud Connector Console  

https://<POD_NAME>.<APPLICATION_NAME>.<DOMAIN>.oraclecloud.com/biacm

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

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.