by John Chung and Harish Gaur
Part of the Oracle Fusion Middleware Patterns article series.
Published June 2010
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.
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).
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.
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.
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.
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:
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
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.
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:
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.
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.
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.
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 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 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.|