Scroll to top
Please assign a menu to the primary menu location

How To Burst Large Data Files Using Chunk or Split By From Oracle BI Report


Roshan - November 18, 2022 - 0 comments

Reading Time: 3 minutes

In this blog, we will cover how to create a BIP report to develop and burst to load the report data into files by creating chunks. So, let us begin.

Step 1: Get the count of the Dim/Fact Table simultaneously identify the ID from the Dim/Fact table and get the count of the same.

Recommended for you: How To Run Async Or One-Way Long Running Database Processes Using OIC?

If both matches, then

SELECT COUNT(ROWNUM) CNT_ROWNUM, COUNT(SALES_ORDLN_ID) CNT_SOLN_ID FROM W_SALES_PICK_LINE_F

step1Here we observe that both the counts are matching, and it is 4,452,847 (Roughly 4.5 mil)

Step 2: Create a Chunk based on the No of Records and based on how many records can be sourced using SFTP

In the above case

No of Records:  4,452,847

No of Records for one File chunk: 500000

No of Chunks that can be created: ROUND (4452847/500000)= 9

Step 3: Create a Data Model with the above no of Chunks:

step3

Step 4: Create a report based on the DM as below.

step4

Step 5: Set the Report Properties:

Select Enable Bursting Name: Shipment burststep5

Step 6: Open the DM and create Bursting Steps:

Choose a name for Bursting Report (Shipment Burst)

Type: SQL Query

Data Source: DWH

Bursting Query:

Split By: /DATA_DS/G_1/HASH

Deliver By: /DATA_DS/G_1/HASH

step6

Add the below query which will create 9 reports that uses Parameters as setup below.

step6_2Step 7: Schedule the report using the above DM and Report.

You could either schedule the report or run instantly.  No need to do any further setup in the scheduler.

step8Step 8: Observe the reports are available in the SFTP environment (Snowflake)

step8If you have any questions regarding this blog, please get in touch with us at Conneqtion Group. Do let us know about your thoughts in the comments and suggest what topics you would like us to cover in our technical blogs.

Author avatar

Roshan

Roshan comes with an extensive experience in the Oracle Implementation domain after completing his Electronics and Communication Engineering from Bengaluru. He holds a plethora of professional certifications from Oracle after starting as an Associate Developer role and quickly made his way to a Senior Manager. He has worked with clients across the globe in ANZ, EMEA, APAC and Europe. At Conneqtion Group, Roshan has been a key part of managing the organization’s largest risk teams apart from sharing his experience and skills to develop and nurture young talent with his dynamic vision.   

Related posts

Post a Comment

Your email address will not be published. Required fields are marked *