Oracle BI Publisher Blog

I have been working with data templates for the last few daze ... we are doing some work on getting some performance numbers - at last. I have been building some more complex data templates than those I have built previously on the blog. The engine is extremely powerful and can do alot of the heavy lifting for you when it comes to grouping hierarchy and summary amounts.

Here's my latest.

<dataTemplate name="EMPLOYEES" defaultPackage="" dataSourceRef="demo-hr">
 <properties>
  <property name="include_parameters" value="true"/>
  <property name="include_null_Element" value="true"/>
  <property name="include_rowsettag" value="false"/>
  <property name="scalable_mode" value="off"/>
  <property name="db_fetch_size" value="300"/>
 </properties>
 <parameters/>
 <lexicals/>
 <dataQuery>
  <sqlStatement name="LOCATIONS" dataSourceRef="">
   <![CDATA[select  COUNTRIES.COUNTRY_NAME as COUNTRY,
  REGIONS.REGION_NAME as REGION,
  LOCATIONS.STREET_ADDRESS as STREET_ADDRESS,
  LOCATIONS.POSTAL_CODE as POSTAL_CODE,
  LOCATIONS.CITY as CITY,
  LOCATIONS.STATE_PROVINCE as STATE_PROVINCE,
         LOCATIONS.LOCATION_ID as LOCATION_ID1
 from  HR.LOCATIONS LOCATIONS,
  HR.COUNTRIES COUNTRIES,
  HR.REGIONS REGIONS 
 where   COUNTRIES.REGION_ID=REGIONS.REGION_ID
 and  COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID]]>
  </sqlStatement>
  <sqlStatement name="DEPTS" dataSourceRef="">
   <![CDATA[select  DEPARTMENTS.DEPARTMENT_ID as DEPARTMENT_ID1,
  DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME
from  HR.DEPARTMENTS DEPARTMENTS 
where   DEPARTMENTS.LOCATION_ID = :LOCATION_ID1]]>
  </sqlStatement>
  <sqlStatement name="EMPLOYEES" dataSourceRef="">
   <![CDATA[select     EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID,
     EMPLOYEES.FIRST_NAME||' '||EMPLOYEES.LAST_NAME as EMP_NAME,
     EMPLOYEES.EMAIL as EMAIL,
     EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
     EMPLOYEES.HIRE_DATE as HIRE_DATE,
     EMPLOYEES.SALARY as SALARY,
     EMPLOYEES.DEPARTMENT_ID as DEPT_ID,
     JOBS.JOB_TITLE as JOB_TITLE
 from     HR.JOBS JOBS,
     HR.EMPLOYEES EMPLOYEES
 where   EMPLOYEES.JOB_ID=JOBS.JOB_ID
 and department_id = :DEPARTMENT_ID1 ]]>
  </sqlStatement>
 </dataQuery>
 <dataStructure>
  <group name="G_REGION" source="LOCATIONS" groupFilter="">
   <element name="LOC_REGION" value="REGION" function=""/>
   <group name="G_LOC" source="LOCATIONS" groupFilter="">
    <element name="LOC_ADDR" value="STREET_ADDRESS" function=""/>
    <element name="LOC_CITY" value="CITY" function=""/>
    <element name="LOC_CODE" value="POSTAL_CODE" function=""/>
    <element name="LOC_STATE" value="STATE" function=""/>
    <element name="LOC_COUNTRY" value="COUNTRY" function=""/>
    <group name="G_DEPT" source="DEPTS" groupFilter="">
     <element name="DEPT_NUM" value="DEPARTMENT_ID1" function=""/>
     <element name="DEPT_NAME" value="DEPARTMENT_NAME" function=""/>
     <element name="DEPT_SALARY" value="G_EMP.SALARY" function="sum()"/>
     <element name="DEPT_COUNT" value="G_EMP.EMPLOYEE_ID" function="count()"/>
     <group name="G_JOB" source="EMPLOYEES" groupFilter="">
      <element name="JOB_TITLE" value="JOB_TITLE" function=""/>
      <group name="G_EMP" source="EMPLOYEES" groupFilter="">
       <element name="EMPNO" value="EMPLOYEE_ID" function=""/>
       <element name="EMP_NAME" value="EMP_NAME" function=""/>
       <element name="HIRE_DATE" value="HIRE_DATE" function=""/>
       <element name="PHONE_NUMBER" value="PHONE_NUMBER" function=""/>
       <element name="EMAIL" value="EMAIL" function=""/>
       <element name="SALARY" value="SALARY" function=""/>
      </group>
     </group>
    </group>
   </group>
  </group>
 </dataStructure>
</dataTemplate> 

Maybe not such a real data case but it shows off some of what you can do with the data engine. You can see I have 3 queries using bind variables LOCATIONS -> DEPARTMENTS -> EMPLOYEES. Now, rather than just then render the three levels in the grouping hierarchy I have created 5 levels:

G_REGION
    |
    |_ G_LOCATION
            |
            |_ G_DEPT
                     |      
                     |_ G_JOB
                              |
                              |_G_EMP

I have introduced the G_REGION and G_JOB levels into the data. Even thou we only have 3 levels in the queries the engine will calculate the hierarchies to be applied to the data.

The one drawback with the data templates is the fact that we do not have a builder for creating the templates. Good news on the way, I have been testing a new data template builder. This allows you to build your queries,

set appropriate engine flags

then bind them together into the grouping hierarchy

and finally set properties for the individual columns.

Its a nice tool - needs a little tweaking but its very close - of course usual disclaimers apply and it may look completely different by the time it hits the shelves. Look for it in a release coming soon ... performance numbers will come sooner.