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 design.
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 blog post:
Do you know what is a Composite View Object?
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.