Until today, Oracle BI Publisher was only available as a (SaaS) bundle with Oracle Fusion Applications Cloud and OTBI, see also a previous Article. However, with Oracle Analytics Cloud, Oracle BI Publisher is now also available as a “first degree” Platform Cloud Service (PaaS) giving more flexibility especially in terms of Data Sources. This allows to create highly formatted Reports/Documents such as Invoices, Purchase Orders, Dunning Letters without the context of Oracle Fusion Applications Cloud.
See the full announcement here.
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:
To Analyse the Days of Absence for Workers, the Absence information is extracted and loaded into a dedicated Star (W_ABSENCE_EVENT_F) of the BI Applications data model. A simple Report on the days of Absence and absence occurrences looks like the following from the Report Builder (Answers):
This can also be restricted to a individual worker and a specific time period by applying additional filters in the Report Builder:
This Report then return the following filtered results:
This is based on the Fact for Absence:
The Absence Days are calculated based on the DAYS_DURATION column for the selected period from 2017/07/06 until 2017/07/17. Thus, each duration will be counted for the selected period regardless of the start or end date of a an Absence event (First column). If the worker was not scheduled to work of a certain days such as 7.7. and 8.7. then these days will not be counted as days of Absence. The Absence occurrences are calculated on the ABS_OCCRNC_DAY column.
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): 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)