At Conneqtion Group, we have started a new series of technical blogs. In the first blog, we have tried to cover data syncing from Oracle SaaS to third-party systems using Bursting.
But, first let’s try to understand what Bursting is.
What is Bursting?
If bursting is allowed for your subscription, then it is possible to exceed the subscription capacity upto two times the number of purchased resources. Though, the charge is normally higher and you take the Pay-As-You-Go model for the additional resources. It is also known as bursting up. Bursting is helpful in instances where there is a need to temporarily increase the capacity during the release of a new product.
Problem
Most of the times data from Oracle Fusion has to be synced with third party systems or reports needs to be delivered to users via email. So data has to be extracted from Oracle Fusion in required file formats and has to be delivered via different delivery channels like SFTP, Email, Printer, Fax etc.
Solution
Extracting the data from Fusion can be done by BIP report. To deliver the output files to different channels we need to use Bursting feature. A bursting definition is a component of the data model. After defining the data sets for the data model, we can set up one or more bursting definitions based on requirement. While adding the bursting definition, below details are required
- Split By: The Split By element is an element from the data that will govern how the data needed to be split
- Deliver By: The Deliver By element is the element from the data that will govern how formatting and delivery options are applied.
- Delivery Query: The Delivery Query is a SQL query that defines BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details. SQL query parameters changes based on delivery channels.
For more details, you can check out the to Oracle documentation
Approach
Now let’s see the process and steps involved in adding bursting definition and testing.
Prerequisites:
- Oracle Fusion Instance
- SFTP
Let us take a example of sending purchase order details to third party system using SFTP. Below are the following steps involved.
Step 1: Login in to the SaaS instance and navigate to Tools -> Reports and Analytics and click on Browse catalog as shown in the below screenshots
Step 2: Click on Create and click Data Model as shown below
Step 3: Once data model is opened, create a data set as shown below with the SQL query and click OK. This SQL code contains a column ‘001’ as key which we will be using for Spilt By and Delivery By in bursting definition.
Step 4: Now let’s register the SFTP details in BI Administration so that we can use it in Bursting definition. Click on user profile icon and then click on Administration and then on the next page click on Manage Publisher as shown in below screen shots.
Step 5: On the next page in Delivery section click on FTP to configure FTP as shown in the below screenshot. 
Step 6: Provide all the necessary details like Server Name, Host, Username, Password and provide all other necessary details and then click on Test Connection and Apply as shown in the below screenshot. We will use the Server Name SecureSFTP provided here in bursting definition.
After testing the connection and click and on apply, we will be able to see the SFTP details like below.
Step 7: Now let’s store the SFTP server name and SFTP path in SaaS lookup and fetch dynamically from code instead of hard coding them in code. Navigate to Setup and Maintenance from SaaS front end UI and then click on search on next page as shown in below screenshots.
Step 8: Search “Manage Common Lookups” in search box and select it
Step 9: Create a lookup type as shown below with necessary details
Step 10: After creating the lookup, add the lookup code as shown below. Here Lookup code will be unique for each record, meaning we have provided as SFTP name which we have registered in earlier step and in Tag we have provided SFTP folder path where file needs to be delivered.
Step 11: Now we have added successfully added SFTP and then created a lookup which stores SFTP Server name , SFTP Path. Lets proceed now to add the bursting definition in Data Model using these details.
Step 12: In Data Model, Click on Bursting and then Data Source to ApplicationDB_FSCM. In Bursting Query Select the KEY that was added in data set SQL code and in SQL query provide the code as shown in below screenshot.
Step 13: Now let’s create a E-text report template.
Step 14: Now let’s proceed to create the report this data model using the E-Text template. Click on Create and click on Report as shown in the below screenshot.
Step 15: Link the data model and upload the E-text template as shown below.
Step 16: Now let’s enable the bursting property in report, click on properties and then enable the Enable Bursting and select the bursting definition added in data model and save the report
Step 17: By this step we have successfully preformed all necessary steps and lets proceed to test the report.
TESTING
1. Navigate to report folder and click on More and then click on Schedule and on next page click on Submit as shown below in the screenshot.
2. Provide the report job name and click ok as shown below .
3. Navigate to home page and click on Report Job History to view the results
4. Now let’s see the file in SFTP, as shown below the file has been placed in the specified path in SFTP as mentioned in lookup.
5. Output file contains the data as shown below. With this step we have successfully tested bursting
Advantages
Using bursting feature we can deliver the data to different deliver channels like SFTP, Email , UCM, Fax etc in required file format as per the scheduled frequency. SFTP details can be modified without changing code and can be fetched dynamically in code.
If you need assistance to sync data from Oracle SaaS to third-party systems using bursting, then get in touch with us at Conneqtion Group today. You can also share your feedback or business requirements with us at [email protected]
Karan works as the Delivery Head at Conneqtion Group, a Oracle iPaaS and Process Automation company. He has an extensive experience with various Banking and financial services, FMCG, Supply chain management & public sector clients. He has also led/been part of teams in multitude of consulting engagements. He was part of Evosys and Oracle’s consulting team previously and worked for clients in NA, EMEA & APAC region.
