Automating Enterprise Reporting with SOA and Oracle Business Intelligence Publisher

by John Chung and Harish Gaur


Building an automated reporting platform with SOA, an enterprise reporting tool, and a portal


Part of the Oracle Fusion Middleware Patterns article series.

Published June 2010

Downloads
Oracle SOA Suite
Oracle Business Intelligence
Oracle WebCenter

Introduction

The value of an enterprise reporting solution cannot be underestimated. To author, manage, and deliver all types of highly formatted documents against practically any datasource is extremely important functionality. Enterprises need such tools to generate thousands of reports for viewing and printing by employees, customers, partners, and others.

However, enterprise reporting can get complicated very easily. Not only does it have to generate purchase orders (POs) on the fly by acquiring data from multiple datasources, but it also has to personalize the look and feel of the report based on vendor country terms and conditions. The report has to adhere to a custom calendar schedule of the vendor: Vendor A in Canada shouldn't get POs on July 1, Canada Day, and Vendor 2 in India shouldn't get reports on August 15, India's Independence Day. All reports should be generated only if internal business rules around PO limits and vendors' past performance are met. But that's not enough. Companies also need to automate the actual report compilation and generation process to ensure clear visibility into the reporting process. How otherwise can it known whether a PO has been approved by the director of operations?

This business requirement is very typical in any business setting. Workflow and personalization are intrinsic to the reporting process, requiring a combination of a reporting platform, a process orchestration framework, and a collaborative portal. SOA offers an agile process orchestration framework, which can easily complement or enhance the enterprise reporting platform. Any reporting workflow could be automated by the SOA platform. SOA would enable humans to participate in the report approval process. Business rules could help generate reports on a conditional basis. With a process- and business rules driven approach, visibility into the reporting process is much clearer. Similarly, a collaborative portal becomes a vehicle to deliver these reports in a personalized environment to the end users. Richly formatted reports could easily be delivered in a consistent manner, helping reinforce a particular brand. In this article, we will take a look at how to build an automated reporting platform with SOA, an enterprise reporting tool, and a portal. We will then walk through a real-life example. Arcturus, a leading real estate services company, built ClientConnect, a property management reporting solution using Oracle Business Intelligence (BI) Publisher, Oracle SOA Suite, and Oracle WebCenter.

Automated Reporting Platform Architecture

Bringing SOA and a portal together in an enterprise reporting platform can allow users to develop flexible reporting solutions to meet a variety of business needs. SOA handles the data logic, and the reporting platform handles the layout and translation. This enables greater flexibility with report layouts, report maintenance, and optimization of the data extraction and document generation process.

Here are the six steps in the processing sequence, beginning where the reporting process starts and ending at the point where the end user can see the personalized reports (see Figure 1).

  1. Reporting process orchestration using SOA. All process activities related to report compilation, such as data gathering, scheduling, personalization, rule validation, and human review, are modeled in a business process. This is traditionally accomplished using process orchestration engines like Oracle SOA Suite. The business process drives the entire report creation and delivery process. Any changes to how a report is created only impacts the SOA process. Using SOA to drive report generation also provides a definitive "immunity" from datasource changes. If underlying datasources change at a later point of time, the SOA process could easily incorporate this change without affecting the end report. Similarly, if the business rule driven report needs to be updated, this change would only be made in the rule sets. Another significant advantage comes from the ability to track process, for example, how long it takes a user to review the report and where bad data in the report comes from. Such visibility can help identify deficiencies in the process.
  2. Datasources for report generation. Once the report generation process is kicked off, it begins collecting data to be presented in a report to the end user. These datasources interact with the SOA process using Web services. As discussed in the first step, this service-based approach offers certain flexibility to the overall process. Let's say report data is coming from SAP Customer Relationship Management (CRM) today. If the CRM system changes to Oracle Fusion CRM, it would not impact the report at all. Only the Web service connecting the SOA process to Oracle CRM would need to be updated.
  3. Human approval and business rule validation. Business rules play a very important role in the reporting process. Rules engines like Oracle Business Rules can help align the reporting process with business goals. For example, the SOA process will check the product inventory. Using business rules, the process will validate whether this inventory level is too high. If it is, then the SOA process would automatically generate a campaign letter to all customers. Rules can also be used to control the dissemination of reports to the end user. In the example, rules can determine if a campaign blast should be limited to customers who have purchased a similar product no later than a year before.

    Similarly, human intervention can help put some controls into an otherwise automatic process. Oracle SOA Suite provides capabilities for humans to interact with the process. The Oracle BPEL Process Manager Human Workflow component manages the lifecycle of human tasks, including creation, assignment, expiration, deadlines, and notifications. In the example, sales managers could get a notification from the SOA human workflow asking them to review the campaign letter to be sent to all customers.
  4. Reporting output. The SOA process can only handle business logic. Report formatting and translation are handled through the reporting platform. The handoff between SOA and the reporting engine is accomplished through XML. Reporting output generated after data collection, processing, and validation is handed off to the reporting engine in a specific XML format.
  5. Enterprise reporting. The reporting engine takes processed XML data from the SOA process and creates the final report. It does so by applying the processed XML on its layout template (which could also be selected by the SOA process). For example, if you intend to produce the campaign report in PDF format, the layout template could be described in Extensible Style Language-Formatting Objects (XSL-FO), a markup language for XML document formatting that is most often used to generate PDFs.

    Oracle BI Publisher is suited for enterprise reporting. Built on open standards, Oracle BI Publisher handles data in XML. It can generate reports in a wide range of industry standards such as PDF, HTML, rich text format (RTF), Microsoft Excel, Microsoft PowerPoint, flash, electronic data interchange, electronic fund transfer, and comma-separated values. Oracle BI Publisher also provides a Web service interface to integrate with the SOA layer. These Oracle BI Publisher Web services can be used to generate and schedule reports, manage reports and folders, and validate user report access.
  6. Web 2.0 portal for personalized report viewing. The final report is delivered to the end user in a personalized portal platform. In addition to a consistent user experience, the portal platform provides the capability to embed key Web 2.0 features. This could also be the interface to interact with the SOA process via human workflow services. Oracle WebCenter Suite is an ideal platform to deliver a rich user experience by combining diverse content and allowing users to collaborate through social computing services. Oracle WebCenter can easily bring in human workflow data from SOA and display Oracle BI Publisher reports. This makes Oracle WebCenter a singular platform for user interaction during report creation and report viewing.



Figure 1: Automated reporting platform architecture



Now that we have seen critical components of the solution, let's see how Arcturus built ClientConnect, a property management reporting solution using Oracle BI Publisher, Oracle SOA Suite, and Oracle WebCenter.

Property Management Reporting Solution at Arcturus

Arcturus is a real estate services firm specializing in property and facilities management, asset management, leasing, and advisory services. It manages more than 600 properties (a mix of commercial, retail, and industrial). Each building exists as its own business entity, and on a monthly basis, Arcturus provides its clients (the landlords or asset managers) with a management report that includes an executive summary, financial statements, variance analysis on budget to actual, leasing (sales) information and forecasting, occupancy statistics and stacking plans, cash flow, and operational reports. Although the number of buildings amount to more than 600, the number of reports the company generates on a monthly basis is in the thousands.

However, the report generation process is highly complex (see Figure 2). Three key groups are involved in delivering the reports: accounting, leasing, and property management. Data was pulled from several datasources including Oracle's JD Edwards, content servers, and databases. On a monthly basis, Arcturus closes off the period for the property, and each group is responsible for their sections. The accountants compile the numbers, and then send them to the property managers. (Some back and forth generally happens missed accruals, discrepancy of numbers, and so forth.) Each time a change is made, the accountants will rerun the financial statement and e-mail it to the property manager. Upon approval of draft financials, the property manager manually rekeys the financial data in relevant fields within the management report template, and adds text comments and additional reports where required. The entire report is manually arranged in sections. Then it is PDFed into one document (including the JD Edwards financials, which are PDFs; thus, they are PDFed twice) and e-mailed to the client or posted on ClientConnect, the Arcturus report repository.




Figure 2: Arcturus property report generation process



This manual process led to several issues. Most important, delivery of reports required a lot of staff time due to inefficiencies in task resourcing and management. Disjointed collaboration between groups led to missed timelines and finger-pointing. There were several data inconsistencies and inaccuracies due to the manual rekeying of financial data into report template results. For example, JD Edwards income statement data was manually keyed into the executive summary. Arcturus decided to automate the entire process of report compilation and generation using Oracle Fusion Middleware.

ClientConnect Solution

Arcturus chose Oracle Fusion Middleware to orchestrate and facilitate the monthly reporting process. The main objective of this project is to automate the monthly reporting process and to deliver reports to the end users in a rich collaborative environment.

As shown in Figure 3, the solution consists of Oracle SOA Suite (Oracle BPEL Process Manager, Oracle Business Activity Monitoring, Oracle Business Rules, Oracle Human Workflow), Oracle WebCenter, and Oracle BI Publisher. As soon as data changes in the JD Edwards table, Oracle Enterprise Service Bus (ESB) picks up the changes and kicks off the BPEL process. The BPEL process orchestrates the entire reporting process. Oracle BI Publisher is the reporting engine that takes processed data from Oracle BPEL Process Manager and produces a highly formatted report. The Arcturus Portal, built on Oracle WebCenter technology, delivers the report to property managers. It also displays human task lists and business activity monitoring dashboards. Business activity monitoring dashboards (Phase 2) provide the operational visibility into the reporting process.




Figure 3: Solution using Oracle SOA Suite, Oracle WebCenter, and Oracle BI Publisher



To understand how the solution is integrated, let's walk through a use case. In this scenario, property data and canned reports are captured from JD Edwards EnterpriseOne with Oracle Enterprise Service Bus (ESB). Once captured, BPEL pushes this data through various approvers in the accounting and property management groups. Once approved, this data is passed on to Oracle BI Publisher for report generation. Oracle BI Publisher generates the property report on the fly. This report is then surfaced in Oracle WebCenter for end users.

1. The ESB captures reporting data from JD Edwards and kicks off the BPEL process

The reporting process is started once a company's month is closed for both accounts payable (AP) and accounts receivable (AR) by a user within JD Edwards through the company master.

As the month closes, important property data is pushed from JD Edwards to the ESB. Various details about the specific property are published from JD Edwards. This includes business unit number, period, year, report type, general ledger account number, names, and balance for reconciliation. In addition, critical financial information about the property is obtained from JD Edwards:

  • Income statement data
  • Rent roll
  • Lease expiration information
  • Accounts receivable information
  • Open POs (used to generate accrual tasks)

The ESB uses a database adapter to poll data from JD Edwards (see Figure 4). It reads the table that identifies whether AP/AR has been closed. As soon as records are found to be true, it will initiate the BPEL process.

In this example, F55XPARClo? is a database adapter that connects to the JD Edwards database to poll the table and check if there is a need to initiate or run a BPEL process; that is, if AP/AR is closed, it will pick up that record and initiate or run a BPEL process. The middle activity then receives the data polled from the JD Edwards table and does the transformation, passing it to the input parameters of the BPEL process.




Figure 4: ESB integrating with JD Edwards using a database adapter



Figure 5 shows that transformation (mapping) from the database adapter to the SOAP input parameters. It maps the columns selected from the database to the input parameters of the BPEL process. For example, column xamcu of the database table is passed and mapped to the other side as businessunit.




Figure 5: Property data from JD Edwards EnterpriseOne transformed into canonical format



2. BPEL process starts report compilation process

The BPEL process takes property data from the ESB and orchestrates the monthly reporting process through a series of human tasks. The process involves tasks where property data and canned JD Edwards reports are pushed to users for approval. This is accomplished through the human workflow capability of the Oracle BPEL Process Manager. Let's see what the BPEL process looks like and what role human workflows and business rules have in the process.

Figure 6 shows the parent BPEL process. Each step in the process represents a subprocess where most of the process orchestration is performed.




Figure 6: Parent BPEL process orchestrating report compilation



The BPEL Human Workflow allows users to view tasks in a Oracle BPEL work list application (UI). Users can then view the data/reports online, select/add records (general ledger amounts) to be accrued/adjusted, and add commentary for the published report. The tasks must be completed in succession, where each task must be actioned by a user in order for the process to continue and result in the compilation of the final report. The BPEL process dynamically determines who are approvers are for a specific property report (see Figure 7).




Figure 7: BPEL process dynamically determining approvers for a specific property report



When a human task is processed, the assigned user will receive an e-mail notification, which contains a direct link to the task page on the UI. Once users log in, they are directed to the specified task. In the sample task in Figure 8, the user (general manager/director) is approving the property management report as submitted by the property manager. The user can view the draft PDF copy of the property management report embedded as a link on the bottom right. The user has various options: approve as is, add internal commentary regarding changes/edits and send back to originator, or reject the task entirely if the financials are incorrect and data changes are required.




Figure 8: Task List UI for Property managers/accounting staff to review the report



Business rules play an important role during this orchestration. The process primarily involves two groups accounting (A) and property management (P) where levels of approval might be required for tasks, that is, where P1 tasks require approval by P2, or A1 tasks require approval by A2. Prior to each human task, BPEL reads the business rules to determine based on the property which user is to receive a task in the case of approvals, whether approval is required or not. This is achieved by populating the rules with JD Edwards category code numbers that represent a corresponding role: property manager (P1), director (P2), accountant (A1), and accounting manager (A2). The category code number refers to the category code number in the business unit setup within JD Edwards, which houses the address book number of the assigned user. Through this all, necessary user information is gathered so the task can be directed accordingly by business unit/property. A skip approval function is also used within the business rules via a unique variable.

The screenshots in Figures 9 through 12 illustrate how a variance threshold rule is created at the client level (#9071). This threshold checks whether the year-to-date actual versus the year-to-date budget variance is (> $1000.00 or < -$1000.00) AND (> 10% or < -10%). Both conditions must apply. The BPEL process can invoke this rule to see if threshold checks are met.




Figure 9: All threshold rules for different clients in a rule set




Figure 10: Threshold rules for client 9071




Figure 11: Actual definition of the rule




Figure 12: Centrally defined Rule variables



We just saw how the BPEL process, with the help of human workflow and business rules, drives the entire report compilation process. Throughout this process, property reports are generated for different approvers to review. Let's see how BPEL integrates with Oracle BI Publisher to generate reports.

3. BPEL invokes Oracle BI Publisher to generate reports

As previously mentioned, Oracle BI Publisher offers most of its functionality through a Web service API. Developers can use the Web service API to generate and maintain reports. Calling the Oracle BI Publisher Web service from Oracle BPEL Process Manager is very simple.

Although we could have called the Oracle BI Publisher Web service directly from BPEL, we integrated Oracle BI Publisher with BPEL through its Java API component. This was done for two reasons:

  • We enhanced the functionality of UI to view the report on the fly just by clicking a button.
  • Our strategy to only write to the database once the whole process is done. All the data/information we need to create the report is in the payload.

The Java program connects to BPEL and accesses the payload, generates the XML data needed by the template, and uses the Oracle BI Publisher API to merge them. The program accesses the FOProcessor and RTFProcessor methods to handle the necessary transformation of the XML input to its corresponding PDF output.

  • RTFProcessor converts an RTF template to XSL in preparation for input to the FOProcessor engine.
  • The FOProcessor engine merges XSL and XML to produce any of the following output formats: Microsoft Excel (HTML), PDF, RTF, or HTML.

Using Oracle JDeveloper, this program is quickly converted into a Java Web service. Once the Java Web service is deployed, the URL for the deployed Web Services Description Language (WSDL) is used to link it to BPEL. Sample code below shows how the report accepts XML data from BPEL and calls Oracle BI Publisher APIs.

Import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;
import oracle.apps.xdo.template.FOProcessor; 
import oracle.apps.xdo.template.RTFProcessor



Figure 13: How the report accepts XML data from BPEL and calls Oracle BI Publisher APIs



The sample code in xmlGetAuditDetails.jsp.xml (click link to view) shows the BPEL-generated XML for the RTF templates and the final report with populated BPEL data. This data came from the human task payload, which was submitted by the user, and the amount displayed was encoded through the UI.

The Oracle BI Publisher template in illustrated in Figure 14 receives the XML data shown in the sample above through the Oracle BI Publisher API, and transforms the output to PDF format.




Figure 14: Screenshot report template used to create the report



Figure 15 shows the generated PDF output from the combined XML data/payload submitted by the user and the Oracle BI Publisher template. The signature is also automatically attached to the template as soon as users approve the task assigned to them.




Figure 15: Screenshot of the final report



We've illustrated how the Oracle BI Publisher API is wrapped as a Java Web service and called from BPEL. This Java Web service takes XML data from BPEL and merges it with the report template to create the final report. Now, let's see how end users see this report in the Arcturus Portal.

4. Arcturus portal delivers reports to property managers

The Arcturus Portal, built using Oracle WebCenter, is a unified platform to view reports, approve tasks, and get connected with peers. Let's see two important facets of this portal: how Oracle BI Publisher reports are integrated into the portal and how human tasks are integrated into the portal.

BPEL will send the generated final output report to a structured folder on the Arcturus Portal Server. From there, users would be able to access the complete report through the Oracle WebCenter Document Library component. The Oracle WebCenter application uses an application development framework (ADF), fileDownloadActionListener, in conjunction with a backing bean to download the PDFs.




Figure 16: Configuring ADF fileDownloadActionListener for
downloading and displaying Oracle BI Publisher reports



Human task lists are also integrated with the Arcturus Portal. Users log in to the Task List Webcenter application and view their assigned Human Tasks. The Human Tasks are the front-end human interaction Web pages provided by a connection to the SOA-BPEL application. The ADF ViewController project is used to display a list of tasks and links to SOA Human Tasks along with Oracle WebCenter components and features on the Web. Screenshots show how property managers use WebCenter to review & download reports.




Figure 17: Work list UI integrated with Oracle WebCenter




Figure 18: Oracle WebCenter allowing users to download and view reports




Figure 19: Screenshot of portal displaying the final report



As you can see, this solution combining Oracle SOA Suite, Oracle WebCenter, and Oracle BI Publisher automated the entire property management reporting for Arcturus. The result was a more-transparent process enabling cohesive collaboration between the accounting and property management groups. One of the biggest advantages Arcturus sees from this solution is the ability to track tasks within process. This provides management with the ability to identify potential issues and solutions to ensure reports are delivered to the client on time and proactively mediate risk where timelines might be affected.

Data "autopopulation" resulted in a 50% reduction in time/effort (per property report depending on size of report) required by property manager to compile and transpose data into a report. The total report cycle per report was reduced by 40 percent, and Arcturus estimated ROI is less than two months. Arcturus is looking to implement a similar solution in other key areas, such as contract management, new property transition/property disposition, and employee hire/termination.

Conclusion

Enterprise reporting can greatly benefit from SOA and portal platforms to create an agile, personalized reporting platform. SOA can complement the reporting tool to manage crucial human interventions and align reports with constantly changing business goals. The portal platform provides a unified interface to deliver reports in a consistent and personalized environment. With the existence of standards like BPEL, Web services, WSRP, and XML, it is simpler than ever to integrate all these pieces in a seamless manner. Organizations can greatly benefit from reduced manual processing and improved visibility into the entire reporting process.

John Chung John Chung Arcturus Realty Corporation's VP of IT, has over 10 years' experience in the real estate industry, with diversified knowledge in technology and programming covering a broad range of languages and environments.
Harish Gaur Harish Gaur is Director of Product Management for Fusion Middleware at Oracle. In this role he works closely with strategic customers implementing Service-Oriented Architecture using Oracle SOA technology. Harish is the co-author of The BPEL Cookbook, from Packt Press.