Integrated Document Solutions
for Business Applications

Sort Posts

Get Fresh Updates

Home / Blog / Current Page

BIP-Enterprise – Bursting with Data From Multiple Sources

Posted on by Brent Lowe

I received a comment from Kris regarding a post on How to Burst and Deliver Documents From BIP Enterprise that said:

Thanks for your example. I managed to get this to work but as my data is based on 2 datasets I miss part of my data on the report. Only data that is in the dataset on which is split is shown.

hmm… I believe what Kris is referring to is that he has 2 Data Sources for his data, likely 2 or more plain SQL Queries that in the end create a nice looking report by setting BIP up to “Concatenate the SQL Data Sources”. However, when he goes to burst the data, some data is missing from the output as he restricted to selecting split/delivery information from the Concatenated SQL. Meaning, if you choose to split by an element in Query 1, all of the data in Query 2 will not be available because it’s not in the same XML Element Grouping as the Split By element (and vice versa).

Multiple Data Sources Bursting Options

Believing that Data Templates allow you to merge multiple Data Sources into 1 Data Model with the appropriate grouping, I took a look at the BI Pubisher Enterprise Documentation. I was surprised to find that it appeared not to be possible (or I was looking in the wrong spot)! You can define a default data source for the entire report, but it did not look like you could define a data source PER query in the Data Template.

Not accepting defeat, I started Googling and came across Tim Dexter’s post regarding Multi-Database Data Templates.

Using this information and my original Overdue Invoice example, I setup 2 database schemas in separate databases (i.e. 2 Data Sources) and created my Customers table in one database and my Invoices table in another database. I then created a Data Template that would query from both and merge the results!

Merging 2 Data Sources with a Data Template

As you can see, I’m connecting to 2 different data sources (MEDUSA_BIPDEMO and SILVERBOLT_BIPDEMO – which are completely separate databases), querying for my data and then merging the queried data together to create the XML. The XML that I have created is not simply concatenated, it’s grouped so that I have CUSTOMERS at my top level and INVOICES underneath.

XML Data from Multiple Data Sources

Now, as far as my report and bursting is concerned, I only have 1 Data Source!

One thing of note, when you are creating your bursting query, you must have all of your delivery information in 1 database, you cannot span multiple data sources here. In general, I believe this is OK. It’s doubtful (but not impossible) that you would have contact data strewn across multiple databases, but who knows….

So hopefully this can help Kris, if I completely missed the mark, then I apologize, at the very least I learned something new!


Share This



5 Responses to BIP-Enterprise – Bursting with Data From Multiple Sources

  1. Rocky says:

    Hi Brent,

    Thanks for the article, ‘BIP-Enterprise – Bursting with Data From Multiple Sources’. But I’ve a slightly different requirement. I need to create a dashboard like structure in BIP. My BIP version is 10.1.3.4. I’ve say 3 reports on same page. I can create such structure with ‘concatenated SQLs’ OR ‘Data Template’ but as you’ve mentioned bursting was an issue. I tried this above approach but prob with this is, if I create a group inside the group, child group data is getting repeated for every parent record. Is there any work-around to achieve that? Thanks in advance for your help.

    Regards,
    Rocky

    • Brent Lowe says:

      Hi Rocky, I think I need some more detail. Can you distill the problem down to a example that I could set up internally to take a closer look? Please feel free to email me directly with the info and I’d be glad to check it out. -> brent dot lowe at strsoftware dot com

  2. nidhi says:

    Hi Brent…Thanks for the article..I have a similar requirement where i have to use a data template instead of a sql query..I have written data template xml in which I have defined data structure as you mentioned but my problem is in xml i dont get LIST_G_BILL tag in my xml outputas you’ve got LIST_CUSTOMERS,(G_bill is my group name) but in split by option i get /HVBILL/LIST_G_BILL/G_BILL/. Because of this i am getting java null pointer exception as it is unable to find LIST_G_BILL in xml..Thanks in advance for your help.
    Regards,
    Nidhi

  3. pallavi says:

    Could you please let me know where i will get data template?where to write html code?

  4. Shanthi says:

    Thanks. The article was quite useful.

Leave a Reply to pallavi Cancel reply

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