Oracle BI Publisher Blog

Yesterday I spoke about having XML and SQL datasources retrieved in the same data template. Today we'll cover the ability to pull data from multiple database sources in your DTs. We call this 'Distributed Queries'.
Say we have my customers sitting in an Oracle database and for whatever reason their invoices reside in an MS SQLServer database. Those of you using the Enterprise release will hopefully have seen that we can just set up two data connections, write our queries against each and then have the server concatenate the result. But we end up with:

<CUSTOMERS>
 <CUSTOMER1>
 <CUSTOMER1>
<INVOICES>
 <INVOICE1>
 <INVOICE2>
 <INVOICE3>
 <INVOICE4>

Its possible to write a layout template to merge the two datasets into a master-detail report buts its a lot of work linking invoices with customers.

Ideally we need to generate a merged dataset across the two data sources so that I get something like:

<CUSTOMER1>
 <INVOICE1>
 <INVOICE2>
 <INVOICE3>
<CUSTOMER1>
 <INVOICE1>
 <INVOICE2>

The data template can help us out here, we still need to define the two data connections (non-Publisher Enterprise users - hang on Im coming to you) and write your queries as before but your are going to combine them into a data template to get the desired result.
An example is worth a 1000 words here:

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="data" description="DistQ" dataSourceRef="ODB"
version="1.0"> <! - Note the dataSourceRef attribute definition - >
<parameters>
<parameter name="DeptID" dataType="number" defaultValue="10" />
</parameters>
<dataQuery>
<sqlStatement name="Q1"><! - first query does not have a dataSourceRef so the default will be used - >
<![CDATA[
SELECT DepartmentID,
Name FROM Department where DepartmentID=:DeptID
]]>
</sqlStatement>
<sqlStatement name="Q2" dataSourceRef="MSDB"><! - second query uses MSDB to reference the MS database - >
<![CDATA[
SELECT DepartmentID,EmployeeID,Title,BirthDate FROM
HumanResources.employee where DepartmentID=:DepartmentID
]]><! - Notice this query is using the :DepartmentID bind variable
from Q1 - >
</sqlStatement>
</dataQuery>
<dataStructure><! - No change in the data structure just referencing Q1 and Q2- >
<group name="G_DEPT" source="Q1">
<element name="DEPT_NUMBER" value="DepartmentID" />
<element name="Name" value="Name" />
<group name="G_EMP" source="Q2">
<element name="EMPLOYEE_NUMBER" value="EmployeeID" />
<element name="Title" value="Title" />
<element name="BirthDate" value="BirthDate" />
</group>
</group>
</dataStructure>
</dataTemplate>

Not too tough right? Simple queries in the example I know but they hopefully demonstrate the point.
For the non-Enterprise users out there - you can use this too but you will have to get into the APIs to execute the data template. That means a java concurrent program for EBS users. You'll need to define the data connections in your code and reference them in the data template and then make the call to the data engine to process the data template. There are details on this in the documentation. Here's the snippet of the code you'll need:

...
//Initialize variables
 Connection orcl=null;
 Connection sqlServer=null;
try {
 //Initialization -instantiate the DataProcessor class
 DataProcessor dp = new DataProcessor();
 //Set Data Template to be executed
 dp.setDataTemplate("/home/DistrubutedQuery.xml");
 //Get jdbc connections to the distrubuted databases we wish to
 //execute the template against. Assuming you have functions
 //to retrieve these
  orcl = getOracleConnection();
  sqlServer = getSQLServerConnection();
 //Use a hashtable to assign the connection to the dataSourceRef names
 Hashtable connections = new Hashtable();
  connections.put("ODB",orcl);
  connections.put("MSDB",sqlServer);
 //Set the jdbc connections
  dp.setDistributedConnections(connections);
 //Specify the output directory and file for the data file  
  dp.setOutput("/home/DistrubutedQuery_data.xml");
 //Process the data template
  dp.processData();
 }
 catch (Exception e)
{
 }