Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Is Multiple Data Sets Creation supported for BI Bursting

Received Response
442
Views
7
Comments
AshishERPCloud
AshishERPCloud Rank 5 - Community Champion
in Oracle Transactional Business Intelligence

Summary

Multiple Data Sets in BI Bursting

Content

This is with reference to Oracle Fusion HCM Applications

Say we want to have a report which would hold Person Details as one Data Set (Parent) and Email Address (Child Data Set).

We then need to burst the Data to a SFTP Folder

Can we do this , if so please help with a simple example

Thanks

Ashish

 

Tagged:

Answers

  • Wade Wilson
    Wade Wilson Rank 4 - Community Specialist

    Hi Ashish,

    This appears to be doable. I dug up some sample code that one of my teammates used.

    SELECT 'key' AS "KEY"
     , '<TemplateName>' AS TEMPLATE
     , 'en-US' AS LOCALE
     , 'text' AS OUTPUT_FORMAT
     , 'FTP' AS DEL_CHANNEL
     , '<SFTPServerName>' PARAMETER1
     , '</directory/location>' PARAMETER4
     , '<file.txt>' PARAMETER5
     , 'true' PARAMETER6
FROM dual

    This link explains the parameters. Just scroll to the 'FTP and SFTP' section - http://docs.oracle.com/cd/E23943_01/bi.1111/e22258/add_burst_def.htm#BIPDM394

    Hope this helps.

  • AshishERPCloud
    AshishERPCloud Rank 5 - Community Champion

    Hi Wade,

    Thanks for replying, but am looking for multiple data-sets.

    The one you are referring to is the most commonly known and widely used calssic single data-set example which I am aware of am looking for a multi data-set example

    Thanks

    Ashish

  • Wade Wilson
    Wade Wilson Rank 4 - Community Specialist

    Hi - The same should apply for multiple data sets. You can add a second bursting in the data model. You would use a different Split By/Deliver By for each bursting. We do this to email to HR Reps vs Workers' managers and they are in separate data sets.

  • Zanith
    Zanith Rank 1 - Community Starter

    Hi Ashish,

    Do u able to achieve the requirement of busting using multiple data sets?

     

    Thanks,

    Zanith.

  • AshishERPCloud
    AshishERPCloud Rank 5 - Community Champion

    Hi Zanith,

    I am unable to achieve this yet.

    Kind Regards

    Ashish

  • Priya_Ananda
    Priya_Ananda Rank 2 - Community Beginner

    Hi , please let me know how to proceed with the Bursting logic when we have multiple data sets.

    Is there any workaround for this ?

    Thanks,

    Priya

  • RVohra
    RVohra Rank 6 - Analytics Lead

    You'll need two data sets and create a link between these two data sets

    • Data Set 1: Person Details (Parent)
    • Data Set 2: Email Addresses (Child, linked via person_id or party_id)

    Using the BI Publisher Plugin for Word or Excel:

    • Design a template with groupings (Group by Person).
    • Show person's details in the header.
    • Use a repeating group for the email addresses.

    In the Data Model, click Bursting and define the bursting configuration.

    Sample Query could be

    SELECT
    p.person_number AS KEY,
    p.person_number || '_report.pdf' AS OUTPUT_NAME,
    'PDF' AS TEMPLATE_FORMAT,
    'en-US' AS TEMPLATE_LANGUAGE,
    'Default' AS TEMPLATE_NAME,
    'sftp' AS DELIVERY_TYPE,
    NULL AS PARAMETER1, -- Not used for SFTP
    NULL AS PARAMETER2,
    NULL AS PARAMETER3,
    NULL AS PARAMETER4,
    NULL AS PARAMETER5
    FROM
    per_all_people_f p
    WHERE
    trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date;


    Note: KEY must match the data grouping key in your report (e.g., person_number). TEMPLATE_NAME should match the name of your uploaded layout. DELIVERY_TYPE = 'sftp'