OTBI direct database access disabled Oracle Fusion 19A
With the most recent available upgrade of Oracle Fusion Applications Cloud 19A, the direct database access with OTBI has been disabled by default by Oracle. The feature will be disabled within a grace period of 12-month:
This is also looged a Bug, since many customers relied on the feature:
Community discussion: R13.19A OTBI Direct Database Query Privilege Change
Fusion Application: Upgrade 19A What’s new (see chapter: OTBI DIRECT DATABASE QUERY PRIVILEGE IS DISABLED BY DEFAULT FOR
AUTHENTICATED USERS )
The reasons for this are security concerns from Oracle side and the currently only remaining alternative, is to use BI Publisher in cases of direct database access (as confirmed in the community discussion by Oracle, this method will not be restricted).
Additionally, for OAC, the direct database access feature as also been removed from the default permissions of the logged in User (Authenticated User). But, using the Manage Privileges Pages, the permission can still be assigned for Users who require the feature:
How to create a direct database access is also described in a previous post.
A strange Issue exists with OTBI in terms of Report prompts. If a User adds a prompt directly within the Report, the prompts appear correctly within BI Answers. However, if the same Report is opened from within Oracle Cloud, the prompts are not shown to the User.
Prompts added to Report:
Prompts appearing in BI Answers of OTBI when running (opening) the Report:
However, when opening the same Report from Fusion (OTBI embedded) the Report executes without showing the prompts, even though the prompt has been specified as “required”:
When clicking on the “Edit” button of the Fusion Catalogue Browser, the following warning message is being displayed:
The standard provides prompted Analysis usually of Type “Dashboard”, hence this Issue is not applying for standard content. E.g. the Analysis on Department details also provides filters as Dashboard prompts:
Oracle Fusion Cloud allows to enable text fields as multi lingual enabled attributes. As part of the batch upload process e.g. from Fusion Product Hub, these enabled attributes can be specified within different languages:
Within Oracle Fusion Cloud, these attributes are displayed in the respective Language of the logged in User. These is also true for OTBI. E.g. for US the respective item description is displayed in US:
If a user logges into OTBI with RUS language, the item description is automatically displayed with RUS description:
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”
Please note: when copying&pasting the connection pool names, there may be an issue with the double quotes and you may need to re-type the double quotes.
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): https://cloudcustomerconnect.oracle.com/posts/ac54c31fcf
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)
Link to information about business questions and answers for Oracle Transactional Business Intelligence (OTBI) Embedded Analytics Cloud, HCM Subject areas: http://www.oracle.com/technetwork/documentation/otbi-hcm-2225528.html
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:
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 (Oracle Fusion BI: How to Add the BI Administrator Role to a user in Release 12. (Doc ID 2238277.1) from Fusion Application 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.