Technologies behind Oracle Transactional Business Intelligence (OTBI)
By Dylan Wan on Jul 25, 2013
Oracle Transactional Business Intelligence (OTBI) is one of the business intelligence solutions provided as part of Fusion Applications.
To build a real-time BI, the major challenge is to make sure that it
can perform and has no or minimum interfere to the core objective of the
transactional application, the online processing.
This is the reason why we need Oracle Business Intelligence
Applications (OBIA) for Fusion Applications. The idea is to keep the
minimal processing of detecting changes and capturing changes in the
transactional system and leave everything else, such as, preparing and
consolidating the data for reporting, to BI Applications.
Here are some of the technologies available to make OTBI possible:
1. SQL Trimming from ADF
ADF stands for Application Development Framework. It is the
application development framework used in developing Fusion
Applications. In general, it is a declarative metadata driven framework
to let the application developers to define the data model, define the
data access layer, define the UI rendering, put the validation logic and
processing in the middle tier.
The underlying data model, in most of cases, is still the relational
model defined in the Fusion Apps transactional database under the 3rd NF
The key enabling technologies provided from ADF to OTBI is the
“Composite VO” or “Composite View Object”. For me, it can generate the
database SQL for us based on the metadata. Unlike the database
technology using the database view, ADF engine can look further down to
the entity objects included in the view object and selectively choose
which entities are needed in a given SQL. If the view object includes
two tables (EOs), one primary EO for data at the line level, and the
other EO for getting the parent data, When the query (Composite VO) does
not include any column from the parent EO, the SQL generated by ADF
will not include the table in the join.
This is a superior technologies, comparing to the old technologies of building the business views.
If you are a Java programmer and would like to get the feeling about
what Composite View Object looks like and how it works, here is a good
2. BI Platform – ADFQuery to Composite VO
This enabling technology is provided by BI platform and available as a
Java library. It adds a layer on top of the ADF composite VO. Without
writing the Java code, it generates the codes of creating the composite
VO on the fly. It allows us to query the data from the ADF engine by
sending them a XML block called ADFQuery.
This doc shows some of the ADFQuery XML blocks.
To see better examples, you can find them in NQQuery.log files.
It is a query language like SQL. You have the section for the
column projection, the join criteria using view links, and the filter
using view criteria.
Here are other enabling technologies behind OTBI.
3. ADFQuery generation from BI Server
4. SQL By Pass Database
5. Relational to Dimensional Mapping (Physical Layer to Business Model Layer)
6. SELECT Physical in initialization block
7. ADFQuery Initialization block
8. Physical Lookup function from BI platform
9. Logical Lookup function from BI platform
10. Data Security enabled at the VO layer via Fusion AppCore
11. Applcore Tree Flattening
12. Applcore Business Intelligence Column Flatten VO (BICVO)
13. BI Flexfield VO generator
14. BI Extender via Import Wizard
15. BI View Object created based on the BI EE Logical SQL (BIJDBC)
16. Effective Date VO with as of date filter
17. ADF Application Module to BI variable interface
Regardless, the goal of these technologies is to enable the users to
get the real time data access to the Fusion Apps. There is really
little or no much we can do for providing the feature like data
snapshot, pre-built aggregate, multiple currencies, data consolidation
and conformance, cross subject area analysis, and the most important,
the query performance with complexity logic to be available in a
reasonable time without the interfere to the transactional system.