BIP - Multiple File Bursting with Ora_Hash Function

Business Need/Requirement: To extract Huge Data into multiple chunks i.e., multiple file extraction instead of having one single extraction filed with all records.

This could be achieve, by performing below core activities:-

1.   Using OraHash Function, we can split records into balanced data sets

2.   Using Bursting, we can have each defined balanced sets in separate files

3.   Using Cutom ESS Job, to run the BIP program, pass the chunk size and to execute BIP Program to move files into a SFTP Location

Please refer to below example, which illustrates the process to follow, along with screenshots

·         First we create a data model and reports using BIP Reports (Refer to BIP Report Creation Blog)

·         Then we add the busting query to the Report

·         We should then create an Input parameter for the report say, ‘File_count’ which is used by Ora_hash and BIP bursting Logic to split records and to create multiple files.

1.   As we know, ORA_HASH function (Part of Data model) will split rows into balanced data sets. Number of data sets depends on value passed to BIP report Input parameter.

Example: ORA_HASH(column, File_count) -- > ORA_HASH(Trx_number,2) à this will split the records into three sets (0,1,2) as shown in below screen shot.

2.      We have split the records into three Data Sets and now we want to create three Files for each data set. Inside BIP Bursting query, we are using the alias name HASH defined for Ora_Hash function part of Data Model query. Since Ora_hash created three Data Sets, BIP Bursting will also create three Files and shall have each data set in one file each.

BIP Query where we also need to provide SFTP details where we want to place the file, once BIP program is exceuted.

SELECT distinct 1 as "KEY"

 , 'XXVR_CUST_ACCOUNT' Template  à Data Template Name

 ,  'en-US' LOCALE                    à English

 , 'TEXT' OUTPUT_FORMAT à Output Format

 ,  'true' SAVE_OUTPUT        àSaving the output or not

 , 'FTP' DEL_CHANNEL            à Delivery Channel type

 , 'XXVR_CUST_ACCOUNT' OUTPUT_NAME à Output name (Give any name)

 , 'SFTP Server' PARAMETER1 à SFTP Server Name

 , 'em257008' PARAMETER2                à Username

 , 'Integrat!0n'  PARAMETER3 à Password

 , '/E_1/ftp_inbox/upload/INT001' PARAMETER4      à File Path in the server

 ,  'XXVR_CUST_ACCOUNTS_'||TO_CHAR(SYSDATE, 'DDMMYYHH24MISS')||'.csv'  PARAMETER5 à Output File Name

 , 'TRUE' PARAMETER6          à Use Secure FTP (TRUE or FALSE)

Note: - We should also enable the bursting from the properties tab as shown below.

Custom ESS JOB execution for BIP Report –

We can create a custom ESS Job to run the BIP program. Create a parameter prompt in custom job, which we will use in Ora_Hash and Bursting Query Logic

From the Navigation Tab, click on Schedule Process à click on Schedule new Process à select the custom ESS JOB and pass the paramter value (In our examle we used File Count value is 2)

As shown above, the Job status is Succeded.

Login into the SFTP server via Wincp or any other tool and we can see that 3 Files are placed in SFTP location via BIP bursting with almost equal split of records in each file.

Ambarish Goel

Add comment