Recently there was question on internal mailing list about the possibilities having SQL queries from different databases in one data template. Yes, it is pretty much possible. Generating report based on the data across multiple DB instances is very common requirement.
In BI Publisher, this functionality termed as Distributed Queries. This allows selecting and merging data across different data sources pointing to different DB types (Oracle, SqlServer …), HTTP, Web Services.
Here is the sample Data Template to extract the data from two different data sources and define a master-detail relationship. The first Data Source (default data source) is based on Oracle and other one specific to Q2 is based on SQL Server.
<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="data" description="Template description
dataSourceRef="oracleDS" version="1.0">
<parameters>
<parameter name="P_DeptNo" dataType="number"
defaultValue="10"/>
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT deptno,dname FROM Dept
where deptno = :P_DeptNo]]>
</sqlStatement>
<sqlStatement name="Q2" dataSourceRef="sqlServerDS" >
<![CDATA[SELECT DepartmentID,EmployeeID,Title,
BirthDate FROM HumanResources.employee
where DepartmentID=:deptno ]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_DEPT" source="Q1" >
<element name="DEPT_NUMBER" value="deptno " />
<element name="Name" value="dname"/>
<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>.
|
Sample java code to execute Distributed query using DataProcessor API.
public static void DistributedQueryTest()
{
Connection orclConnection=null;
Connection sqlConnection=null;
Hashtable dataSourcRef= new Hashtable();
try {
// initialize oracle jdbc connection//
orclConnection = getOracleConnection();
// initialize sql server jdbc connection//
sqlConnection = getSQLServerConnection();
dataSourcRef.put("oracleDS",orclConnection);
dataSourcRef.put("sqlServerDS",sqlConnection);
DataProcessor dp = new DataProcessor();
dp.setDataTemplate("d:\\sample\\DistrubutedlinkQuery.xml");
dp.setDistributedConnections(dataSourcRef);
dp.setOutput("d:\\out\\DistrubutedData.xml");
dp.processData();
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
orclConnection.close();
sqlConnection.close();
} catch (Exception e)
{
e.printStackTrace();
} } }
|
