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
Here 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:
Step 4: Create a report based on the DM as below.
Step 5: Set the Report Properties:
Select Enable Bursting Name: Shipment burst
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
Add the below query which will create 9 reports that uses Parameters as setup below.
Step 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.
Step 8: Observe the reports are available in the SFTP environment (Snowflake)
If 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.
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.
