Automated Parallel Processing technique for BI Report
Automated Parallel Processing technique for BI Report

 445 total views

In this article we will look into another interesting topic of executing the BI Report in parallel to avoid time out issues or to improve performance.

We have the Process Configuration Group that we can use for HCM Extracts to have them run on multi-threads to have better performance. However we do not have such provision for BI Reports which does the magic.

With this approach, we will be able to run the BI Reports in parallel but the caveat here is each parallel instance will have it’s own result and we cannot consolidate the output into a single file.

This approach uses a BI Report, Payroll Flow Pattern and Fast Formula to achieve parallel processing.

Let’s get into the details of how this has been done..

Create BI Report

First part is the creation of BI Report. I have created an Assignment Salary report which fetches the assignment and salary details of all employees. Now if we want to run this in parallel, we need to split the employees by count using rownum function in the Data Model SQL.

Original Data Model SQL before applying any parallel processing Logic

select distinct papf.person_number,
paam.assignment_number,
to_char(paam.effective_start_date,'MM/DD/YYYY') effective_start_date,
to_char(paam.last_update_date,'MM/DD/YYYY HH24:MI:SS') asg_last_update_date,
paam.last_update_date paam_last_update_date,
paam.last_updated_by asg_last_updated_by,
cs.last_updated_by sal_last_updated_by,
to_char(cs.last_update_date,'MM/DD/YYYY HH24:MI:SS') sal_last_update_date,
cs.last_update_date cs_last_update_date,
PJ.NAME JOB,
PD.NAME Department,
HLAF.LOCATION_NAME LOCATION,
HAP.NAME POSITION,
PLE.NAME Legal_Employer,
paam.employment_category,
paam.FULL_PART_TIME,
paam.hourly_salaried_code,
cs.SALARY_AMOUNT
FROM per_all_assignments_m paam, per_all_people_f papf, cmp_salary cs, PER_LEGAL_EMPLOYERS PLE, HR_ALL_POSITIONS HAP, HR_LOCATIONS_ALL_VL HLAF, PER_JOBS PJ, PER_DEPARTMENTS PD
WHERE 1 = 1
AND PJ.JOB_ID(+) = PAAM.JOB_ID
AND PAAM.ORGANIZATION_ID = PD.ORGANIZATION_ID(+)
AND PAAM.LOCATION_ID = HLAF.LOCATION_ID(+)
AND paam.assignment_type = 'E'
AND paam.primary_flag = 'Y'
AND papf.person_id = paam.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND cs.assignment_id(+) = paam.assignment_id
AND sysdate BETWEEN PLE.effective_start_date(+) AND PLE.effective_end_date(+)
AND SYSDATE BETWEEN HAP.EFFECTIVE_START_DATE(+) AND HAP.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN PJ.EFFECTIVE_START_DATE(+) AND PJ.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN PD.EFFECTIVE_START_DATE(+) AND PD.EFFECTIVE_END_DATE(+)
AND PLE.ORGANIZATION_ID(+) = PAAM.LEGAL_ENTITY_ID
AND HAP.POSITION_ID(+) = PAAM.POSITION_ID
AND sysdate BETWEEN paam.effective_start_date AND paam.effective_end_date
AND sysdate BETWEEN cs.date_from(+) AND nvl(cs.date_to(+),sysdate)
order by papf.person_number

Now, we will need to modify this query to add two parameters.. one is the total number of parallel threads that we want to run, and the other is the actual thread we are trying to run.


Ex: if we want to run 5 parallel threads, then the Parallel Thread Count will be 5 and the Thread number will be from 1 to 5 based on the thread we run..

Apart from these, if you have any additional parameters for the BI Report, you can also add those. But these are the two mandatory parameters for the parallel functionality to work.

Modified SQL Query handling the parallel thread instance:

WITH PARENT_QUERY as (
select main.*, rownum SEQUENCE_NBR from (
select distinct papf.person_number,
paam.assignment_number,
to_char(paam.effective_start_date,'MM/DD/YYYY') effective_start_date,
to_char(paam.last_update_date,'MM/DD/YYYY HH24:MI:SS') asg_last_update_date,
paam.last_update_date paam_last_update_date,
paam.last_updated_by asg_last_updated_by,
cs.last_updated_by sal_last_updated_by,
to_char(cs.last_update_date,'MM/DD/YYYY HH24:MI:SS') sal_last_update_date,
cs.last_update_date cs_last_update_date,
PJ.NAME JOB,
PD.NAME Department,
HLAF.LOCATION_NAME LOCATION,
HAP.NAME POSITION,
PLE.NAME Legal_Employer,
paam.employment_category,
paam.FULL_PART_TIME,
paam.hourly_salaried_code,
cs.SALARY_AMOUNT
FROM per_all_assignments_m paam, per_all_people_f papf, cmp_salary cs, PER_LEGAL_EMPLOYERS PLE, HR_ALL_POSITIONS HAP, HR_LOCATIONS_ALL_VL HLAF, PER_JOBS PJ, PER_DEPARTMENTS PD
WHERE 1 = 1
AND PJ.JOB_ID(+) = PAAM.JOB_ID
AND PAAM.ORGANIZATION_ID = PD.ORGANIZATION_ID(+)
AND PAAM.LOCATION_ID = HLAF.LOCATION_ID(+)
AND paam.assignment_type = 'E'
AND paam.primary_flag = 'Y'
AND papf.person_id = paam.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND cs.assignment_id(+) = paam.assignment_id
AND sysdate BETWEEN PLE.effective_start_date(+) AND PLE.effective_end_date(+)
AND SYSDATE BETWEEN HAP.EFFECTIVE_START_DATE(+) AND HAP.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN PJ.EFFECTIVE_START_DATE(+) AND PJ.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN PD.EFFECTIVE_START_DATE(+) AND PD.EFFECTIVE_END_DATE(+)
AND PLE.ORGANIZATION_ID(+) = PAAM.LEGAL_ENTITY_ID
AND HAP.POSITION_ID(+) = PAAM.POSITION_ID
AND sysdate BETWEEN paam.effective_start_date AND paam.effective_end_date
AND sysdate BETWEEN cs.date_from(+) AND nvl(cs.date_to(+),sysdate)
order by papf.person_number) main),
GET_THREAD_COUNTS AS
(SELECT ROWNUM Thread_Number,
TOT_COUNT.SEQ,
((ROWNUM - 1) *round(TOT_COUNT.SEQ/:Input_parallel_Threads) + 1) Start_Seq,
(ROWNUM * round(TOT_COUNT.SEQ/:Input_parallel_Threads)) End_Seq
FROM DUAL, (select max(SEQUENCE_NBR) SEQ from PARENT_QUERY) TOT_COUNT
CONNECT BY LEVEL <= :Input_parallel_Threads)
select PQ., GTC. from PARENT_QUERY PQ, GET_THREAD_COUNTS GTC
where (:Input_parallel_Threads = :Input_Thread_Number and :Input_parallel_Threads = 1 and GTC.Thread_Number = 1)
OR (:Input_Thread_Number = GTC.Thread_Number AND PQ.SEQUENCE_NBR between GTC.Start_Seq and GTC.End_Seq)

If you see the above query will assign a unique sequence number for each employee and then break the total employees by total number of threads and get the starting and ending person numbers for the current thread which is running. This is one of the key piece in achieving the parallel processing.

So, Now the report is ready and we can manually run/schedule this report by passing the parameters of Max. Threads and Current thread.

However we do not want to manually submit/schedule the report. So, let’s look at automating this process using payroll flows.

Create Payroll Flow Pattern

For this automation we will create a payroll flow with the task “Submit Another Task“. Submit Another Task is used to submit a task instance multiple times. It takes the Task Name as the input and uses the “Task Repeat” Formula to set the parameters for the Task which gets invoked multiple times. We have an option to run the Task in serial mode/parallel mode. Parallel Mode provides best performance, however if you are running some bulk reports then serial mode is suitable.

The main use of this “Submit Another Task” is for executing the payroll processes for Multiple PSUs which has been documented by Oracle at Submit Another Task Documentation

I have applied the same approach for “Run BI Publisher Report” task and was successful in achieving the same. Similarly this logic can be applied to any task/process but it cannot be implemented for Flow Patterns with multiple tasks in it. The Task Name LOV pulls only Flow Patterns with single tasks.

First step is to create a Payroll Flow Pattern with “Submit Another Task” and add Mandatory and Required parameters for “Submit Another Task” to the Flow Pattern and the flow will look like below.

Create Task Repeat Fast Formula

Now, We will have to create Fast Formula of type “Task Repeat“. The sample mentioned on Oracle Documentation is incorrect and will not work. Instead of REPEATFLOW, we have to use REPEATFLAG. I got this by posting my question on Cloud Customer Connect and got help from Avishek Nandi who was kind enough to help me and attend to my call and clarify the questions that I had.

We need to pass the Report path and Report Name as well in the FF as the “Run BI Publisher” task needs these parameters to run.

Sample Fast Formula Code to invoke Five parallel threads for a report:

/* FORMULA NAME: BI Parallel
FORMULA TYPE: Flow Repeat
Developer: Sricharan Monigari
DESCRIPTION: Formula to iterate the for parallel processing of BI Report */ 
/* Inputs */
INPUTS ARE REPEAT_COUNTER, BASE_TASK_NAME (text)
REPEATFLAG = 'N'
START_DATE = '2020-09-01'
EFFECTIVE_DATE = '2020-09-01'
REPORT_PATH = '/Custom/Human Capital Management/Interfaces/Assignment_Salary_Info_Rpt.xdo'
REPORT_NAME = 'Assignment_Salary_Info_Rpt'
/* FORMULA BODY */
IF REPEAT_COUNTER= 1 THEN
(FIRST_ARGUMENT = 5
SECOND_ARGUMENT = 1
REPEATFLAG = 'Y')
IF REPEAT_COUNTER= 2 THEN
(SECOND_ARGUMENT = 2
FIRST_ARGUMENT = 5
REPEATFLAG = 'Y')
IF REPEAT_COUNTER= 3 THEN
(SECOND_ARGUMENT = 3
FIRST_ARGUMENT = 5
REPEATFLAG = 'Y')
IF REPEAT_COUNTER= 4 THEN
(SECOND_ARGUMENT = 4
FIRST_ARGUMENT = 5
REPEATFLAG = 'Y')
IF REPEAT_COUNTER= 5 THEN
(SECOND_ARGUMENT = 5
FIRST_ARGUMENT = 5
REPEATFLAG = 'Y')
IF REPEAT_COUNTER= 6 THEN
(REPEATFLAG = 'N'
RETURN REPEATFLAG)
/Results/
RETURN START_DATE, EFFECTIVE_DATE, REPORT_PATH, REPORT_NAME, FIRST_ARGUMENT, SECOND_ARGUMENT, REPEAT_COUNTER, REPEATFLAG
/* End Formula Text */

Here, we are using the REPEAT_COUNTER to set values of input variables. The first parameter on the BI Report will be mapped to FIRST_ARGUMENT and the second parameter will be mapped to SECOND_ARGUMENT and so on. If you have any additional input parameters for BI Report then you need to add them to the above payroll flow and then use the below syntax in the FF to get those flow parameter values and pass it in the RETURN statemement.

Ex: We have an Effective Date as input parameter to the flow, then we need to use the syntax in FF

EFFECTIVE_DATE = GET_FLOW_PARAM_VALUE('EFFECTIVE_DATE')
..
..
..
RETURN REPEAT_COUNTER, REPEATFLAG, EFFECTIVE_DATE,...

In this way, we can pass custom parameters as well to BI Report and use the parallel processing technique..

Once, the FF is ready, we can create it on the Define Fast Formula screen with an appropriate name under the type “Task Repeat” with 1/1/1951 date.

Once the FF and Payroll Flow are ready, we are good to test the flow and check the results.

Submit the Payroll Flow Pattern

Navigate to My Client Groups > Payroll > Submit a Flow (Responsive UI)
Navigate to Payroll > Checklist > Submit a Process or Report (Classic UI)

Search for the Payroll Flow Name and input the parameters like below:

We need to select the Task Name as “Run BI Publisher Report” as that’s the child process name that we want to run multiple times.

Execute in Parallel – Yes/No. No means serial processing
Max parallel Threads – This is not the parameter that we created for BI Report. This is used to stop the process if it gets into an infinite loop due to some issue.
Max. Repeat Counter – This is the max number of times the loop will repeat in the FF.
Task Name – This is the “Run BI Publisher Report” in our case as that’s the process we want to run multiple times.
Task Repeat Formula – Custom Formula that we created above to pass input parameters for the Task.

Once this flow is submitted, it will initiate the “Run BI Publisher Report” five times

Click on Submit Another Task to check details of the run.

We can see five instances of Run BI Publisher Report has been initiated in parallel. There is one issue that we are unable to check the output here, so either we have to go to BI Report > More > History or use bursting to email/ftp to get the output.

I haven’t used the bursting option, so let me navigate to the BI Report path

We can click on the Report Job Name and check the input parameters and the output file.

2nd thread output:

4th thread output:

Download Link for Catalog File:

Assignment Salary Report Catalog

With this we come to the end of the article.

Hope this has given you a fair idea of how to implement Parallel processing for BI Reports. This logic can be implemented for any other process too by passing the parameters dynamically using the Task Repeat Formula.

If you have any questions, feel free to reach out to me on Comments, LinkedIn, Telegram Group.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Raghavendra

    Awesome, intresting. So far I have not come across with the requirements where client will agree with multiple files. I would definitely want to try this approach.

  2. Gopi

    Again great article Bro. keep it up.. Thanks

  3. Sricharan

    @Raghavendra – Yes.. Most of the clients won’t accept multiple files. However if the BI Report can’t be tuned any further then we can propose this solution.
    @Gopi – Thank you for the compliments.

Leave a Reply