Data Template Building & Performance

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,


DTBuilder1:


set appropriate engine flags


DTBuilder2:


then bind them together into the grouping hierarchy


DTBuilder3:


and finally set properties for the individual columns.


DTBuilder4:


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.

Comments:

Hi Tim, This looks exciting, great articles by the way... searched the archives but couldn't find any update on the new data template builder... any news for general release date?? Also is this related to the blog below ================= September 24, 2007 MSOffice Dependency? posted on September 24, 2007 9:03 AM ================= Cheers, David Hi David There is a new release imminent - can not be more specific but its v soon Tim

Posted by David Nasti on July 21, 2008 at 06:32 AM MDT #

Hi Tim, Any news concerning releasing new Data Template tool? Do you recommend any alternate tools untill your tool released? I'm developing Reports under E-Business Suite and I create Data Template using text editior which is very slow. Best regaards, Tamer Magdi

Posted by Tamer Magdi on January 27, 2009 at 09:05 PM MST #

Hi Tim, Do you have any news about this data template builder tool? Has it been released? Thanks, Joey

Posted by Joey Cronn on February 18, 2009 at 07:39 AM MST #

Hi Tim I'm relatively new to BI Publisher, and just found blog about Data Template Building. Has the Data Template Builder been released? Thanks Coenraad

Posted by Coenraad on March 03, 2009 at 01:01 AM MST #

How imminent is imminent? Its been more than 8 months now. :) Any news on when it will be released?

Posted by Rune on March 03, 2009 at 10:57 PM MST #

Hi Tim, Is there any update on the new data template builder tool? That would be really useful. Thank you, Akos

Posted by Akos Thurzo on March 11, 2009 at 08:49 AM MDT #

Hi Tim, Is this data template builder available now? I want to merge the data from multiple data models and do report bursting based on a common parameter in each of the data models. The common parameter is like the dept no used in the example above. This feature would be really helpful. Thanks, Anupam

Posted by Anupam Jha on April 27, 2009 at 06:27 PM MDT #

Hello out there ? Any more word on this imminent 15 month project - it' so tedious building the templates by hand :( Please someone ? Cheers, Brent

Posted by Brent Harlow on June 09, 2009 at 01:27 AM MDT #

Any sign of this Data Template Builder Yet?

Posted by KON on October 12, 2009 at 07:21 PM MDT #

All, Apologies for the belated reply. The Data Model Editor is planned for a future release. Unfortunately I can't say anything more specific than sometime in CY2010 ... and even this comes with the caveat that plans are subject to change. Developing Data Templates is easier if you use an XML Editor. There are many good ones out there. I've been using XMLPad and it works well for me. The team is working hard to finish the next release as soon as possible. BTW - we did demonstrate the Data Model Editor at OOW earlier this week.

Posted by mike.donohue on October 16, 2009 at 07:06 AM MDT #

Great Mike! Waiting for it desperately. I am sure it is a great value addition to BI Publisher.

Posted by Rajender Singh on October 25, 2009 at 10:41 PM MDT #

Have you tried to handle CLOB with this configuration? I mean with a Data Link. e.g your master query has a CLOB column and your child is just any other query. Now according to developer guide when you use data link it is neccessary to make groups. And if you use group the CLOB data does not show. I am receving base64 encoded string as CLOB when i run it in a single query it is perfect but when i use a data link and make groups it is not working. Can you shed some light on it?

Posted by Sarfraz R Ramay on March 07, 2010 at 05:09 AM MST #

Where do i get the data template DTD or schema from? If i want to know what are the things i can do with data template, i suppose to look for examplws. That ishould not be the case. Please point me where can i get data template schema or DTD if one exsists. I googleed long time.... got bored of it.

Posted by Muthuvel on March 22, 2010 at 04:19 AM MDT #

Wonderful piece of data that you've obtained on this web site publish. Hope I will get some far more of the stuff on your own web site. I will are available again.

Posted by never fail list building system review on August 22, 2010 at 04:31 AM MDT #

Hi Tim,

I refer to your post while creating a data template.
The problem is it is not passing values to the bind variable.

any leads

Posted by guest on March 08, 2012 at 03:29 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today