Hakan Biroglu: Leverage the power of BI Publisher in Cloud integrations

By now we are all accustomed to running Applications in the Cloud and it has reached the next phase: extensibility and integrating of the Cloud Applications with other applications in our enterprise architecture. Oracle is investing a lot on this area with PaaS, IaaS, DBaaS, MWaaS, JaaS and everything else that ends with aaS. This will work fine if you are a Sales Cloud customer, where you have the ability to create custom objects, outbound calls, etc.

But what if you're a Cloud ERP or HCM customer? Integration here is currently limited to file based extracts, file based loaders, spreadsheet loaders and web services that have been made available to be consumed externally. In real-time integrations you may be confronted with data needs that are not (yet) available by the web services provided in Cloud Applications, but you also do not the tooling to create custom web services for these needs. Or do you?

Inspired by this blog of FA Developer Relations, I would like to demonstrate how you can create custom real-time data extracts on nearly any data that resides in Fusion, using the PublicReportService

The PublicReportService is an externally available web service that provides you the ability to create, delete, update, schedule and run BI Publisher Reports. What makes this really interesting is the combination of creating your custom datamodel in BI Publisher and the operation to run a Report on this data.

Custom Datamodel

From the Navigator you can access BI Publisher via the menu item Reports and Analytics. From here create a new Report.

The Report Creation Wizard will now be presented, ignore this for the moment and from the banner menu, select to create a custom Data Model.

This option is available if you have the role BI Administrator.

Notice that you can create a data model on a lot of different levels. Let's choose the most simple and direct one: SQL Query.

You can now construct any simple or complex sql query to retrieve your data. In above example you can see a simple sql to retrieve the employee name from HCM Cloud. You could also create multiple data models to create an hierarchical data set.

Running the query will return the data. Pay attention to the output we will be using this later on.
Did we just find an alternative for SQL Developer to query cloud data, where we actually do not have direct access to the database?!

Report

In order to run this query as a web service, we first need to create a report layout.

When you move to the View as list mode, you can set the output format. Because we would like to use this as web service, the most convenient output format would be XML.

Now save the report in an appropriate location.

Test using SoapUi

We are now able to run this report (actually query) using the PublicReportService.

The PublicReportService is accessible through

https://[bi-server]/xmlpserver/services/PublicReportService?WSDL

Create a new project in SoapUi by providing the WSDL of the PublicReportService and initiate the operation runReport

A few things about this call and output. The request message has a lot of elements, but these are the ones that we need to run this report, so I removed all unnecessary elements. You can also see that authentication is part of the payload and this differs from the "normal" call of Fusion web services.

Let's have a look at the output. Did we expect this? The actual xml response is Base64 encoded and this is fairly logical, since the output format for BI Publisher can be of many types (PDF, Excel, HTML, CSV, XML).

Decode output

When you run the report output (element reportBytes) through a Base64 decoder you will be able to see the actual xml response.

Use in Soa Suite

Now let's try bringing all of this together in the Soa Suite. Start off by creating a reference to the PublicReportService as we did using SoapUi.

Create a BPEL process and wire this to the PublicReportService.

I kept the request and response of this composite the same as the report data model parameter and output, like below XSD.

Of course you can choose any kind of request/response for your composite and handle the PublicReportService payload in BPEL.

I have constructed the following BPEL flow.

Assign values to the request elements of the PublicReportService and remove any unnecessary elements to construct the same request as you have seen in the SoapUi call. Invoke the PublicReportService. Using an embedded java activity and only a few lines of code you can decode the Base64 encoded output and parse the actual response.

Decode: