OTBI Reporting in Fusion Applications

Oracle Transactional Business Intelligence (OTBI) is fully integrated with Fusion Applications and does not require any additional system configurations. OTBI can be used as soon as the offerings are configured in Fusion Applications and data is available for reporting. It helps in real-time adhoc reporting of the enterprise. Data access and interpretations are easier than before.

OTBI real-time analytics embedded into the Fusion Applications allows users to easily understand the data, filter, sort or drill down, extract data in different formats such as tabular formats or graphical views.

Key Features & Benefits

·         Build rich, visual and interactive reports and publish to multiple users.

·         Create reports using real time transactional data

·         Easy for business users to analyze, access, use and understand the transactional data.

·         Integrates well with the OBIEE features

·         Self-service & Ad-hoc analysis

·         Enables business in making better and real-time operational analysis.

·         Built-in extensibility to enable business users to adapt the application based on the personal or orgaanizational needs.

    

       Security & Roles

       OTBI inherits the user roles and security profiles from Fusion Applications. So access to the OTBI subject areas is determined by the Fusion user role. Data that is seen in the OTBI reports are filtered automatically based on the security profile. OTBI supports multi-language translation. The reporting user interface and metadata can switch to your chosen local language. The same report can be deployed in multiple languages across different countries.

OTBI is built using OBIEE which is made of 3 layers – the transactional database layer, then the Physical layer and the Presentation layer. In Fusion Applications, no customizations are possible in the Physical layer unlike in the on-premise implementation of OBIEE where the BI Administrator tool caters to the customizations in the layers.

All the OTBI Subject Areas are suffixed with “Real Time” in the name. Each subject area has one fact folder and a number of dimension folders. All the OTBI subject areas are secured by Fusion Applications security as it is integrated with the Fusion Applications. Each dimension folder within the subject area is linked with the Fact folder.

Analyses are secured based on the folders in which they are stored. If the Business Intelligence  reports are not secured using the report privileges, then they are secured at the folder level by default. Permissions can be set against folders and reports for Application Roles, Catalog Groups or Users.

Oracle Transactional Business Intelligence (OTBI) reports can be created using the following data sources.

·         Data Model

·         Spreadsheet

·         Subject Area

Create and Edit Analyses Using Wizard

The function of a wizard is to guide in creating or editing analyses. Oracle Business Intelligence Answers can be used to create dashboards and to delete analyses.

    

     Create Analyses

 1.       Open Reports and Analytics pane in the work area

 2.       Click on Create and select Analysis

 3.       Select the subject area that has the columns required for the report.

 4.       Additional subject areas can also be added if required.

 5.       Select the columns to be included in the report and click on Next

 6.       Enter the title for the analysis if required

 7.       Select the type of layout for the analysis – table of graphical and click on Next

 8.       Additional options for sorting, filtering and formatting are also available if required.

 9.       Click on Finish to save the Analysis and enter a Name.

10.   Click on Submit.

           Edit Analyses

 1.       Open Reports and Analytics pane in the work area

 2.       Select the Analysis in the pane and click Edit

 3.       Perform the editing based on the options discussed in the previous section.

 4.       To update an existing Analysis, select the same name in the same folder.

 5.       Click on Submit

BIP Reporting in Oracle Fusion Applications

BI Publisher reports in Fusion Applications are similar to E-Business Suite in having the data model and layouts separated.

What are the two options for BI Publisher Reports?

1.  Create a new custom BI report using data model and layout. 
2.  Seeded BI Publisher reports could be customized to have a custom layout

Data model has the business logic built into it. It can contain different ways of retrieving data for reporting purposes. Report contains the layout for the data model. There are different types of layouts that can be created in Fusion Applications.

Custom BIP report can be created in Fusion Applications using the navigation New à Data Model in the ‘Reports and Analytics’ page as shown below.

Under the Data Model, there are 6 Properties that are available to be used in building the logic for the report.

1.       Data Sets

2.       Event Triggers

3.       Flexfields

4.       List of Values

5.       Parameters

6.       Bursting

Data Sets are the code/queries used for extracting the data for the report. They can also be from existing reports or files with data or objects that can be imported.

Most commonly used data set is the ‘SQL Query’ option where a query can be written to fetch the data as per the report requirement. The source can be Financials and SCM (FSCM), HCM Cloud, CRM Cloud or OBIEE.

The type of SQL can be Standard, Procedure call or Non-standard SQL. Once the query is formed, Click on OK to save it. Also use the Save option to save the data model under ‘My Folders’ which is accessible only by the user. Then use the Data option to view the data of the query. The options for the number of records to be viewed are shown below.

Event Triggers

Events triggers are the triggers where business logic can be called before or after execution of the report data model. It can be beforeReportTrigger or afterReportTrigger.

List of Values

List of values are the value sets equivalent of EBS in Fusion Applications. For parameters that require a defined set of values to be displayed for selection, a ’List of Values’ is created. It can be created from a SQL Query and then attached to a parameter.

Parameters

Parameters can be defined for the reports. They can be of free text where users can enter any value or they can be of date type that will enable users to choose date from a calendar. Parameters can be restricted to a certain set of values by attaching a List of values defined in the previous section.

Parameters can be of various types as shown below. To attach a list of values, the type should be chosen as ‘Menu’. The particular list of values that has to be attached to the parameter can then be selected from the drop down menu.

Report Layout

Create a new Report in Fusion Applications to define a layout for the existing data model. The application requires a sample set of data to be generated for the data model before creating a layout. So it prompts the user to run the data model to generate sample data and save it.

Layouts can be of different types including Excel, RTF, Etext, XSL Stylesheet files that can be uploaded for a specific language. Fusion Applications also have the option of generating the layout based on the columns in the data model. This is the interactive layout where the look & feel of the layout can be modified easily using the editor without having the actually create a layout file as in the case of EBS Applications.

Choosing a simple Blank template would take us to the Interactive layout page where the data model fields would appear to be mapped to the layout. Look & feel can be enhanced using the Layout Grid, Chart, Pivot Table options along with the standard Page break, totals, Page totals etc.

Remote Connectivity Made Easy – App to App Interface in Oracle EBS

With the growing demands of data in the enterprises, there are multiple applications being used for various purposes to achieve end user requirements. When there are several applications being used, there arises the need for connecting all of them irrespective of their platform, language or server location etc.

Web Services can be SOAP/ REST. SOAP uses XML for all messages, REST can use even smaller message formats that makes communication faster. In this chapter, let us discuss about the REST Webservice that is recommended by Oracle for providing interoperability between remote systems/ applications.

REST is Representational State Transfer. It is an architectural style that specifies uniform interface to enable services to connect different systems over the Web. It is a client/ server architecture and is designed to use HTTP Protocol. Data and Functionality are considered as resources. So client and servers exchanges these resources using the Uniform Resource Identifiers (URI).

A Web Service that uses REST is called REST or RESTful Web Services. It is a collection of open protocols and standards for exchanging data. RESTful services are fast, simple and lightweight. It supports XML and JSON formats which is smaller in size. It is describes in WADL (Web Application Description Language) and no expensive tools are required to interact with the Web Service. It can be learned quickly by developers and no extensive processing is required. Having said all this, the implementation of REST service is definitely simpler as compared to SOAP. But REST is not a standard and its implementation varies to suit different application needs

Most businesses using Oracle EBS have other applications that have to interact with the ERP system. Data transfer to and from EBS system is required for efficient interfacing of data and also to have the different applications in sync with each other.

To enable EBS system to effectively communicate with each other, Oracle has provided the ISG (Integrated SOA Gateway) from 12.1 version for consuming SOAP based Web Services. From EBS 12.2.3, ISG has been enhanced as an infrastructure to provide and consume REST based services.

REST API uses the HTTP protocol that has the following operations/ methods:

·         DELETE – deletes a resource

·         PUT – creates a new resource

·         GET – retrieves the current state of the resource

·         POST – transfers a new data onto a resource

EBS uses PL/SQL API for POST operations and JAVA API for GET operations. Applications and their features exchange data and information through defined APIs. EBS business functionality/ data can be exposed to the remote system irrespective of the platform or the language of the originating application. This helps to query data as well as write data to and from the remote data sources

The REST API breaks down a transaction to create a series of small modules. Each module addresses a particular underlying part of the transaction.

REST Web Service provide a common data access layer that can be accessed from any platform and can be called from your Web browser. It is highly scalable, supports caching and is light weight.

It being stateless reducecs the overhead and complexity of the server. These web services are recommended by Oracle to develop custom mobile apps for Oracle EBS applications.