Oracle BI Publisher Blog

Another series of articles on the way ... this time concerning the relatively new data extraction engine from XML Publisher that is currently available in the Enterprise and E Business Suite flavors.

First a little history ... 

Why build another extraction tool, there are others that can generate XML, Oracle Reports, various solutions from the database folks, the list goes on. To answer why you have to understand the volumes of data we are trying to handle here and the furture of reporting for Oracle Applications. For EBS in particular we had multiple reporting solutions, the majority of which were based on Oracle Reports. The plan going forward is to remove Oracle Reports from the techstack in the fusion timeframe. Therefore we needed an extraction engine that offered all that Oracle Reports offers and was just as fast - Oracle Reports is danged fast at generating XML data. On top of that we are not talking about extracting data for a 2 page invoice, the engine needs to extract millions of rows to build tens of thousands of pages for some reports. 
We looked at what was out there, SQL XML provided fast extraction for large data sets but we needed among others, event triggers and flexfield user exits, plus users would need to learn the wrapper functions used to generate the hierarchies. After a lot of research we decided to build an engine ourselves with all the bells and whistles needed to cover the Oracle Reports replacement requirements.

Features ... features ... features

To dig in a little to the features the engine provides:

Fast, scalable extractions - its fast, faster than Oracle Reports. We worked closely with the Oracle Performance team and had to re-write it a couple of times to get their approval. It needs to be fast too, those of you that use Oracle Reports EBS know that out of the box we generate flat text character based output. With XMLP we're generating highfidelity output ... that requires more processing time so the more time we save on the extraction the more we have for formatting without slowing the report generation.

Oracle Reports Features - if its going to replace OR in EBS then it has to match OR on features:

• Multiple Queries/Joins -  enabling master/detail extractions
  
• Event Triggers - pre- and post- fetch for business rule processing. Currently plsql support but will add java
  
• Flexfield Support - gotta get that natural account description with out the select statement - this is an EBS feature.
  
• Formula/summary columns -  again similar to Oracle Report functionality allowing you to create aggregation values and pl/sql based formula columns in your extraction
  
• Data Structure - this allows you to build a hierarchy into your XML data similar to the grouping abilities in Oracle Reports
  
• Group filters - a la Oracle Reports
  
• etc

on top of that its also has:

• Rich Java API layer - call a data template from your jsp or java app
  
• Distributed Queries - this is neat. You're not tied to a single db nor even to an Oracle db. You can construct a data template that, for example hits an MSSQL instance for customer data and an Oracle db for their invoices. the engine will generate a single result set of hierarchical XML i.e. Customer1
   Invoice1
   Invoice2
  Customer2
   Invoice1
   Invoice2
  
  
• Static XML File support - query across a db and an XML file. Text and XLS support coming.
  
• Pluggable Data Templates - not here yet but theidea here is that you can build a core data template and then allow a plugin DT to be applied over the top to get more data from other sources.

Some of you will have wondered what the heck a Data Template is? this is the name we give to the extraction definition, its an XML representation of the queries, joins, data structure, etc.

Thats the brief overview, next Im going to cover some sample Data templates over the next few articles from the most basic to an all singing all dancing EBS DT, I'll also post a jsp appyou can use to test DTs and some help on how to build them.