How to create a direct database query with OTBI?

Within Oracle Fusion OTBI go to New > Analysis and click on Create Direct Database Query:


Within the Editor the connection Pool information needs to be entered:


The following connection pools can be entered:

  • HCM – “HCM_OLTP”.”Connection Pool”
  • CRM – “CRM_OLTP”.”Connection Pool”
  • FIN and PROC – “FSCM_OLTP”.”Connection Pool”

After that, a query may be entered for direct database execution on the Oracle Fusion Cloud (bundled) Database (identified by the Connection pool supplied before):


For a listing of all Physical Tables please refer to (only available for HCM): 

This contains a Excel mapping with the Physical OTBI Tables:



For a full support note please refer to: How To Create A Report with Direct Database Query in OTBI/OBIEE Analysis, Who Can Access Direct Database Query (Doc ID 2141412.1)

For a similar approach using BI Publisher please check:  Creating a BI Publisher Report using SQL Query in Oracle Fusion HCM Cloud

To check the general TechNote for OTBI and Fusion HCM, please refer to: Oracle Fusion HCM: Resources for Oracle Transactional Business Intelligence (Doc ID 2023155.1)


BI Publisher and BICS?

Within the on-Premise World BI Publisher (BIP) was bundled with Oracle BI EE (OBIEE). However, the respective Oracle BI Cloud Service (BICS) does not come with a bundled Version of BI Publisher as Cloud Service.

A user may only choose one of the following options to create new items:


However, Oracle Transactional Business Intelligence (OTBI) does come with a bundled Version of BI Publisher. Once the User logs into OTBI, the following options are available to create new Analytical items:


For further reference check Oracle Support note: Is BI Publisher (BIP) Available as part of BICS (Doc ID 2104841.1)


Link to information about business questions and answers for Oracle Transactional Business Intelligence (OTBI) Embedded Analytics Cloud, HCM Subject areas:

And for OTBI-E subject areas. The advantage of this is, that it is indeed very similar to Oracle BI Applications (OBIA) and can easily be applied for the same:

provide access to OTBI and BI Administration Role

Fusion Application Cloud users can access the Reporting and Analytics (OTBI) module via the Fusion Sitemap listed under “Tools”:


This opens the embedded Analytics Catalog which contains all items for each domain such as Human Capital Management:


To grant access to Reporting and Analytics, the Cloud Admin User or another User having received the “IT Security Manager” Role can provide access to OTBI:


In this example a new Employee called “OTBIUser” should receive access to OTBI. The User should receive the following Roles by the Cloud Administrator:


To grant the User “OTBIUser” access requires to follow the TechNote from Cloud R12 and onwards as the standard BI Roles (such as BI Author or BI Consumer) can’t be assigned to Users directly any more. Instead, it is required to create a custom Role based on the standard (OOTB) Role and assign those custom role to the User to act e.g. as BI Administrator.


After the User has received the role, an Administrator must run the “Retrieve Latest LDAP Changes” ESS Job to reflect these changes. This process may actually take up to 8 hrs. for the changes to take effect.