Introduction

In one of the previous examples we have seen how to use the Bursting Feature in Oracle HCM Cloud. But at times there is also a need to have the same feature for OTBI Analysis. One may say that one can use Agents and choose to send selective data to recipients but the same till date only allows sending data to the EmailAddress registered with the UserAccount (as of date it picks the WorkEmail value). While that’s fine and works in most cases some kind of information might be required to be send to employee’s personal email. Typical examples being:

1. Experience Letter To Ex-Employees

2. New Joining Intimation Letter to Potential New Hires

3. Reliving Letter to Ex-Employees

4. Salary Certificate to Ex-Employees

5. Tax Certificate

…. And much more

As such, in this example we would try to demonstrate how to use bursting feature for a simple OTBI Analysis.

At a broad level we would be performing the following steps:

1. Creating an OTBI Analysis

2. Fetch the OTBI Analysis Logical SQL from Advanced Tab

3. Use ‘Oracle BI EE’ as data source in Data Model and create a new Data Model

4. Add Bursting Definition

5. Run the Newly Developed BI Report

So let’s get started.

Creating An OTBI Analysis

For this example, we would create a very simple OTBI Analysis using “Workforce Management – Person Real Time” Subject Area.

Details of the Database Column used are below:

Once we have a look at the Results Section we will see results as below:

Fetching OTBI Analysis SQL From Advanced Tab

We can go to the Advanced tab and get the Issued SQL from there

We would need to make some changes to the above SQL before we make a Data Model out of this. Changes are:

1. Removing the SET VARIABLE PREFERRED CURRENCY line

2. Remove the 0_s_0 field

3. Remove the ORDER BY Clause

4. Remove the FETCH FIRST clause

Creating Data Set with Oracle BI EE as Data Source

We would use the modified sql as mentioned below and create a data set:

The UI Page would appear as below:

Bursting Definition

We would need to add a bursting definition for the same.

In order to know more details about bursting definition please refer Oracle Documentation available at below link:

https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm

As for this example the Bursting SQL used is mentioned below:

And the screenshot would look as below:

Report Execution

We would now have to run the Report and check results.

Ashish Harbhajanka

Add comment