Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Security vulnerability in Hyperion EPM | Main | To Cloud or Not to Cloud: Make the leap of faith logically! »


Oracle provides multiple ways of reporting from Oracle Cloud (Fusion) Apps, one of them is Oracle Transactional Business Intelligence (OTBI). While, OTBI is built on the OBIEE platform there are few limitations as compared to the on premise OBIEE features. This blog will give a high level understanding of the OTBI capabilities and limitations on cloud, which will especially be helpful during the requirements gathering to see if the requirements are suited for OTBI capabilities, and during the design sessions to fit the requirements with the many tools that OTBI provides.


OTBI is included as part of the Oracle Cloud Apps licenses and does not need any additional setup. This includes out of the box reports, subject areas which are built to work with the Fusion view objects (VOs). In addition, there are tools for customizations namely, OBIEE Answers, BI Publisher. Also, fusion allows to create custom subject areas using the Application Composer tool.

Thus, OTBI aims to make reporting easier for Managers and Analysts by providing tools to simply drag and drop fields from subject areas, while also providing tools for complex customizations when required.

OTBI is attractive for customers for the following reasons:

·         All setup, including the security makes use of the fusion architecture. Hence, setup time is low.

·         Provides real time reporting capabilities.

·         Report customizations are possible with the OBIEE platform at no extra costs. Translates to huge value for money.


While OTBI comes with pre built reports, it comes with tools to create custom reports for requirements which aren't met by the out of the box reports

OTBI comes with the following tools:

OBIEE Answers (also referred to as OTBI in some documentation)

Answers provides users with an ability to access the out of the box functional subject areas to create reports by simply dragging and dropping columns to create reports. In addition, users can add filters, custom formulae and use the different views which are provided. Data and Object security in OTBI uses the Fusion security model and hence no separate configuration needs to be done.

In cases where the subject areas do not have the fields which are needed to build reports, Fusion provides us a couple of customization options.


Flexfields are placeholder fields in Fusion application related to business objects. These fields are used to extend business objects and display data on application pages and can in turn can be enabled to be used in OBIEE subject areas.

Once we enable the flexfields in Fusion, we would need to associate them to the appropriate dimension or fact so that it appears in the desired subject area.

Custom Subject Areas

Fusion applications also allow us to create custom subject areas using a tool called application composer.

To create a custom subject area, we need to identify the fusion object which is the main focus of the reports and build the subject area using this object. In addition, we can add any related or child objects, whose fields are needed for reporting.

In cases where the object we need to report on is not related to any primary objects, we would need to create the relationship between the objects so that the fields are available in the custom subject area.



When reports have specific requirements like pixel perfect published reporting, splitting of reports (by person or date or delivery type) or if requirements cannot be delivered using OBIEE Answers, OTBI provides BI Publisher.

Using BI Publisher, we can write custom SQL queries to hit the fusion tables and get the required output. BI Publisher also provides the bursting feature where we can split the reports on particular fields and deliver using delivery devices like Email, FTP, Printer etc.

Data security can be configured using the security view in Fusion tables and passing session variable for user or role name which are in built in BI Publisher.



While OTBI provides a lot of features to build reports, it still has some limitations in comparison to the traditional way OBIEE is used for reporting.

       OTBI is for Oracle Cloud Apps only; we cannot report on any other sources of data.

       It performs online BI queries against the Cloud Application database directly.There is no data warehouse with advanced metrics and it lacks historical data, which limits the type of analytics that can be done.

       OTBI RPD cannot be customized. Minor customizations can be done using flexfields to expose some columns in OTBI subject areas. For major customizations we need to use BI Publisher.

       Any changes to configuration parameters can only be done by submitting an SR to Oracle with a business justification.

       The Fusion Apps database on cloud cannot be queries using tools like SQL Developer. So, the only way to query the database is by using BI Publisher. This is mainly a developer pain point.



OTBI Workflow 2.jpg

The above is suggested approach before deciding which tool would be used to deliver reporting requirements.

It is always preferable to build reports using OBIEE Answers/OTBI subject areas for 2 main reasons. Firstly, reports built out of the subject areas are interactive in nature. Users can drill up/drill down, navigate to another report based on actions which are configured on the report.

Secondly, the subject areas are accessible to Analysts and Managers based on access provided. This allows them to create reports easily by choosing columns from the appropriate subject area using the drag and drop approach. Also, the configurations are reusable for creating more reports.

However, if either we can cannot configure the subject areas for reporting requirements or if the requirements specifically require published reporting or BIP specific features like bursting, then we would go for the BI Publisher approach. These reports are static in nature and while you can filter data in the reports they are not interactive.

Also, the custom SQL's built are usually complex in nature since the data in the Fusion tables are normalized unlike the typical data warehouse. This means data would need to be pulled from multiple objects causing the code to grow while also impacting performance due to huge data which needs to be processed.

To summarize, the preferable approach is to use OTBI subject areas to build reports and BI Publisher must be used if all else fails.


Good One Marwin..Interesting and very Informative.

Thanks Marwin. perfect summary which lengthy Oracle documents wont provide.

Nicely summarized all about OTBI

Thanks for summarizing it so nicely. Best way to start knowing about OTBI.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles