Oracle Analytics Cloud and Server

Products Banner

BI Publisher Bursting by Multiple Datasets

Received Response
1464
Views
5
Comments

Content

Hi All, 

We have a very complex report requirement encompassing a total of 3 different datasets (this can be compressed to 2 as one is a 'Summary' dataset and we can summarize within the two detailed sets). Now, we have developed an XML Excel template that has 3 tabs; one for Summary and two for Detailed. Ideally, we would like to have one Data Model and burst each dataset onto its retrospective tab. Currently, we are only able to burst 1 dataset at a time, therefore we have to execute a script on our own server to merge each excel sheet into one excel document. 

Can anyone offer any guidance or advice as to how we can burst multiple datasets from one data model onto one excel template? 

Thanks

Aaron

Answers

  • Hi Aaron

    Are you using Bursting query in data model to burst data, or are you using the Excel Template splitting technique to split data into multiple sheets? From description it seems like you are using Excel template splitting technique. If you use the Bursting query, you do not require multiple tabs in your excel template. From your requirement you seem to be creating one tab output for one data set and another tab output for second data set. I think this can be achieved by the splitting technique in Excel Template. If you create concatenated data set, you should be able to split each portion and separate into a new tab. 

  • Hey Pradeep,

    This is where it gets complex; we are using the Excel Template Splitting Technique and it is working well. When we manually run the report we get the output expected (data from each dataset on separate tabs). However, we then need to automate this and burst the output by each individual project to our FTP server. Not sure if it is possible to burst multiple datasets, I have seen some examples from the early 2010s/pre-2010 regarding data templates and multiple queries, but I am not getting the option to do this on Fusion BI Publisher. 

  • Hey, did you get the issue resolved, I also had same issue, if want can share the workaround.

     

    Regards,

    Abinash

     

  • Hi Abinash, 

    Yes we solved this; we had 1 Summary Dataset and 2 Detailed Datasets (1 for Payables and 1 for Receivables). I joined the datasets up using the data modeller to create a link between the key IDs (in our case, project ID). The detailed datasets were joined onto the summary dataset. This formed an overall hierarchical dataset where the parent was the Summary dataset and the children were the Detailed datasets. 

    We had a complex Excel BIP template that took in the generated XML from each respective Datasets and we used the Project ID to Split by and Deliver By for bursting. The Bursting query included logic to only return projects that had either AP or AR data, as to not produce a load of blank reports. This was delivered by email, so the email address was specified in the bursting query (we didn't do a dynamic email but this would also be possible); so when we ran the report the BIP engine generated all of the data, split it out by project then generated the files based on the parent-child hierarchies for each project. Whilst we used email, you can also burst to the UCM server and run an integration to get it to whatever place you need it to get too. 

    Thanks

    Aaron

  • Thank you Aaron,

    Even for my requirement I checked and saw its a current ER for Oracle.

    Then I tried the workaround having one extra data set which will be linked with other data sets on the KEY column. In this case I have to add a dummy Key column for each data sets.

    This worked for me.