Introduction
This post looks at how the BI Publisher server embedded within the Fusion Applications technology stack is being leveraged by integration developers. With the predominant implementation being based on Oracle Cloud services, the co-existence of the Fusion Applications with an existing on-premises application portfolio requires the movement of specific sets of data - something BI Publisher can support very effectively. Indeed most of the existing integration functionality leverages the flexible BI Publisher delivery options and its formatting and transformation engine, converting raw data into a consumable common standard. The HCM Extract is good example, allowing data to be exported in a variety of formats including xml, xls, csv, and pdf, and to be delivered via email, sFTP or Fax.
In this post we'll look at how the standard integration features can be supplemented to meet additional needs using BI Publishers configuration options and its declarative development features.
Data Models
Whilst BI Publisher is often subtitled as 'pixel-perfect-reporting' in reference for its use in printing, its flexible report creation options can support many different use-cases. Generally speaking all BI Publisher output is based on a Report Layout for selecting and positioning the fields, labels and graphics, each of which sits over a Data Model. The combination of these two allow you to get the data you want, in the format you need.
Applications that use BI Publisher reports in their functionality (such as HCM and ERP) provide predefined Data Models based on existing application View Objects which can be reused. These can be found in the BI catalog folders, such as shown below, and allow you to quickly access commonly used data.
In addition for applications that also include many BI Analytics (e.g. Sales) you can use the Subject Areas defined in the Oracle BI Answers repository. As shown below, this again gives you access to business objects and transaction data, in a ready-to-use format. For Sales Cloud these subject areas also include your custom fields and objects.
To simplify your development, it is strongly recommended to use the existing data models or subject areas where they exist. These can be used as required for your own report layouts, selecting the fields you need, however all seeded data models should not be customized directly, but copied then edited.
Of course occasionally you may need to create brand new data models. One must bear in mind however, that whilst BI Publisher is taken from a standard Fusion Middleware 11g installation, not every feature is configured for use in the Fusion Applications environment, especially in the Oracle Cloud. One example is the Data Model creation menu (shown below), which lists many alternative data sources for use, however it is only the SQL Query and Oracle BI Analysis options that are supported at this time. In addition a Fusion Applications
environment should not be configured with new data sources using the BI Publisher server
administration console.
Your new data model will often need some filtering, limiting the data returned. This is done through either variables for run-time data passed implicitly (not covered here), or more commonly for BI Publisher as prompts explicitly setup as parameters before the report runs. The parameters are automatically used in the invocation processes, whether through the UI, through an Enterprise Scheduling Service (ESS) request job, or as part of the request payload in a web service call.
Creating a parameter in a SQL Query based data model is done by entering your query condition with a bind parameter using the colon notation, such as :param1 as shown in the screenshots below. This will automatically take you to the Parameters tab where you can setup details like the Display Label. In addition to standard parameter data types like String, Integer, and Boolean you'll often see BI Publisher reports with lists of values, which is set using a parameter type of 'menu' - shown below. Of course, list of values as parameters is obviously more suited when exposed in a UI than as part of an integration solution.
The Data tab shows the results, using the parameter value specified in the list of values here.
In addition to parameters, in the data model properties dialog you can specify the timeout and the maximum number of lines returned - very useful to eliminate performance issues.
Building Queries
Of course building queries against a packaged application database is not always easy, especially in a cloud deployment where you have no direct access. The solution is to seek-out examples and documentation for your specific use-case.
Here are some invaluable resources depending on which kind of data model you are using. Again the recommendation is reuse wherever possible:
For data models based on the Subject Areas from BI Analysis, then review the detailed Oracle Technology Network documentation such as this provided for Oracle Sales Cloud. and this for Oracle HCM Cloud.
For data models used by existing reports you can find out more using the documentation in Oracle Enterprise Repository (see below), in the resulting spreadsheets for each family you will find both report layout descriptions, their location in the BI Catalog, sample report output PDF's, and most importantly the names and locations of all the data models.
For completely new data models, you can find out about the database tables by
looking at Oracle Enterprise Repository. Search under your product and
type as "Data Model Diagram" to see both technical and logical
representations. This has a good level of description, including field
descriptions, data types, and even foreign keys to look-up tables.
Security
All reports and (BI catalog sub-folders) can be configured to be accessible to either specific users or application job roles. This is setup in the Permissions page shown below, available under the More drop down for each report.
In addition individual report Properties can be set so they are always Read Only or even Hidden from view entirely, useful for reports that are intended for integration and not UI display.
Obviously data security must be applied whenever using custom SQL Queries for your new data model, and this is a good reason that reusing existing data models is recommended as it's already built in. There are documents on My Oracle Support that give details on the Secured List Views that can be used for this purpose, such as this for HCM.
Delivery
Out of the box the BI Publisher environment within Fusion Applications allows delivery of output to the screen, saved to the local filesystem, or sent through email. With the assignment of the 'BI Administrator' job role to your user (see note 1572045.1) you can extend the delivery options available. As shown in the screenshot below, the Administration pages allow the configuration of several other options.
For integration projects FTP is a popular choice, where reports are scheduled or invoked and the result is put in an accessible folder for pick-up by a remote program. In cloud deployments the FTP folders are configured to use only HTTPS (sFTP) and have their own separate user credentials. As an example, the HCM Extract supports many delivery options, leveraging the underlying options from BI Publisher. Read more on this in the documentation and in note 1526505.
Scheduling Reports
Once your report is complete, you need to decide how it will be run. There a several options:
You can define it as runnable through Enterprise Scheduling Service (read more here). Then you can create your own repeating job schedule as you see fit.
You can do similar in BI Publisher, creating a report job based on a schedule. As shown in the screenshot below there are many flexible options here.
Using Web Services
An alternative to scheduling the report to run at a specified frequency, you may want to get your data ad-hoc, such as upon a certain business event or when a user takes a specific action in a remote system. The solution to this is to make the request using a web service call. This can either return the report data synchronously in the response payload, or invoke the scheduler to deliver the report to a specific destination such as FTP folder or email, to be collected by a secondary process.
The Enterprise Scheduling Service provides a web service interface through which you can simply submit a job for immediate execution. This is named the ESSWebservice and has several useful operations including submitRequest, cancelRequest, submitRecurringRequest, getRequestState, and getCompletionStatus. The WDSL can be found at the following URL:
If you wanted to get the report data immediately, the BI Publisher native web services are available for use. It is recommended to use the ExternalReportWSSService for this, as it is simple to use and includes complete security. This can be discovered at:
In this case the response payload includes the report data in BASE64 encoded format. Obviously output size is a consideration here, as hundreds of lines of data may not perform well as a synchronous response. Please review this video demo of this being used, taken from our YouTube channel.
Resources
In addition to the links above, the following content can be useful in finding out more about BI Publisher features that may support integration tasks:
The BI Publisher blog, forum and community
BI Publisher Documentation - Creating Data Sets from BI Publisher , the Administrator's Guide, the Data Modeling Guide, the Report Designer's Guide and the Developer's Guide.
The Developers Guide for ESS - Web Services
The My Oracle Support Information Center for Fusion Applications BI & Reporting - BI Publisher
The Integration posts on this blog and the BI playlist on our YouTube Channel