| Developer: Business Intelligence
Integrating Oracle Business Intelligence Enterprise Edition Plus with SOA
By Mark Rittman and Joel Crisp
A look at the integration possibilities between Oracle SOA Suite and Oracle Business Intelligence Enterprise Edition Plus.
Published January 2008
As you are most likely aware by now, Service-Oriented Architecture (SOA) is a design approach that allows developers to construct business applications and business processes out of loosely coupled, independent services provided by distributed applications. By basing your application infrastructure on SOA-enabled products such as Oracle Fusion Middleware, Oracle JDeveloper, and Oracle Database 11g, you'll be able to develop applications that reuse existing functionality within your organization, adapt easily to changing business priorities, and present users with compelling, Web 2.0–style interfaces built using a framework of events, services, business rules, and application logic.
Business intelligence (BI), too, offers many benefits to organizations that look to gain better insights into their operations and financial performance. Interactive dashboards and graphical reports quickly convey important information about the performance of your organization, while sensors and alerts rapidly bring problems, and opportunities, to your attention. The result is a competitive advantage for your organization. Moreover, the business metadata layer provided with tools such as Oracle Business Intelligence Enterprise Edition Plus creates a semantic model over your organization's entire data set, helping you make joined-up decisions that take into account the many factors that make up your business.
But what if you could bring the two worlds of SOA and BI together, so that your business processes could take advantage of the semantic business model provided by your BI tools and include information in business rules from across the whole organization? What if your business processes could establish the estimated future value of a contract based on a financial model built by one of your financial analysts? And what if your applications could take advantage of the graphing and dashboard capabilities of Oracle's next-generation Interactive Dashboard tools, so that you could embed graphs, tables, and other BI presentations in your Java applications?
In this article, we will look at the integration possibilities between Oracle SOA Suite and Oracle Business Intelligence Enterprise Edition Plus, two elements of the Oracle Fusion Middleware family of products. In particular we will look at a new example Web services API for Oracle Business Intelligence Enterprise Edition Plus, now available on Oracle Technology Network (OTN), that promises to make the integration of BI technology into business processes and applications an even simpler prospect.
A Service-Oriented World
As we know, the main attraction of SOA to application architects is the opportunity it presents to leverage the existing application functionality within an organization to quickly create new business processes. SOA makes it easy to integrate separate business processes through the adoption of Web services as the primary means of integration.
A typical SOA consists of a number of elements:
All of these elements are provided by Oracle SOA Suite, a part of Oracle Fusion Middleware, which comes with tools such as Oracle BPEL Process Manager, Oracle Business Rules, Oracle Enterprise Service Bus, and Oracle Web Services Manager.
Using Oracle SOA Suite together with Oracle Application Server and other elements of Oracle Fusion Middleware, you can develop standards-based, loosely coupled business applications and processes that integrate easily with applications both inside and outside your organization.
Adding the Power of Analytics
Along with Oracle SOA Suite, another key part of Oracle Fusion Middleware is Oracle Business Intelligence Suite Enterprise Edition Plus. Oracle Business Intelligence Enterprise Edition Plus is Oracle's latest generation of BI and analytical tools and provides an interactive dashboard, reports and graphing, alerts, pixel-perfect publications, and a metadata layer over all of your data that makes creating a report as simple as selecting business terms from a data dictionary.
Best of all, from an application developer's perspective, Oracle Business Intelligence Enterprise Edition Plus is fully Web services-enabled and comes with a SOAP API that makes it possible to connect to the BI metadata layer, run reports, retrieve data using business terms, perform analytical queries, and execute BI workflow tasks called "iBots." Given that a typical BI metadata layer spans multiple subject areas within your organization, with integrated, cleansed data; contains calculations and key performance indicators (KPIs); and is backed by a database tuned for retrieving data quickly, this presents an interesting opportunity for developers who wish to add analytical capabilities to their business processes and applications.
For example, you may wish to reference data from across multiple business areas in a business process, bringing data together from sales, marketing, human resources, and inventory when orchestrating a business process. You may wish to reference profitability or customer service KPIs when defining a business rule, or establish whether your business would benefit in the long-term from refunding a customer charge to a long-standing customer. All of these require information commonly held in your corporate BI application, and through Oracle Business Intelligence Enterprise Edition Plus' Web services interface, it becomes easy to incorporate feeds from such systems into your SOA-based applications.
The other opportunity that this integration brings addresses a traditional weakness of standalone BI applications. Most employees within an organization have day-to-day jobs to do that require working with line-of-business applications such as HR, inventory management, salesforce automation, or inventory management applications, and have neither the time nor the training to make use of separate reporting and analysis tools. When they do get the opportunity to use a query tool to analyze their business, they face a "context switch" between the query tool and their line-of-business tool that, at best, slows them down and, at worst, creates a disconnect between the insight they have made and the action they need to carry out to take advantage of it.
If, however, you could build BI insights directly into their line-of-business applications, either through the application having access to integrated data and KPIs; through integration with BI alerts and workflows; or simply by being able to embed, within the application, dashboards and graphical reports generated by the BI tool, the possibility exists to embed BI directly into an application and have it infuse every business process within the organization.
Introducing Oracle Business Intelligence Enterprise Edition Plus
Oracle Business Intelligence Enterprise Edition Plus is Oracle's next-generation set of BI tools that provides an interactive dashboard, reports and high-fidelity publishing, alerts, integration with a wide range of relational and online analytical processing (OLAP) datasources, and a metadata layer that provides a business data dictionary over all of your organization's data. Part of Oracle Fusion Middleware and downloadable now from OTN, Oracle Business Intelligence Enterprise Edition Plus provides a complete platform for delivering reports, alerts, graphs, and role-based dashboards for all categories of users across your organization.
Oracle Business Intelligence Enterprise Edition Plus is a suite of products based around four server products and a Microsoft Windows–based administration tool:
Working alongside these server applications is Oracle BI Administration, a Microsoft Windows–based tool for managing the metadata layer, security, caching, and other Oracle BI Server features.
Web Services Integration
One of the most interesting features of Oracle Business Intelligence Enterprise Edition is the SOAP interface provided by Oracle BI Presentation Server. This interface presents a number of Web services methods that can be invoked from business processes and applications using tools such as Oracle JDeveloper 10g.
The SOAP interface in Oracle Business Intelligence Enterprise Edition Plus 10.1.3.3 provides the following services:
Although these "fine-grained" Web services expose a great deal of Oracle Business Intelligence Enterprise Edition Plus functionality to the calling application, working with them in this "raw" state can sometimes be time-consuming because you have to manage authentication, persisting connection details across multiple steps, retrieving data, and manipulating results at a fairly low level. To make this process simpler, the development team behind this Web services functionality has made some example code available on OTN called the Easy BI Bridge.
The EasyBI Bridge
To make working with the Oracle Business Intelligence Enterprise Edition Plus SOAP interface even simpler, the Easy BI Bridge is an example "coarse-grained" Java Web service that exposes a deliberately limited subset of the full Oracle Business Intelligence Enterprise Edition Plus Web services functionality.
The Easy BI service exposes just four easy-to-understand methods instead of the many low-level methods exposed by the native Oracle Business Intelligence Enterprise Edition Plus SOAP interface. In addition to presenting a simplified set of services, it also manages the required logon/logoff process between the Oracle BI Presentation Server and the calling application, again reducing the work required to gain access to this functionality. It should however be noted that although this code does make consuming these services much easier, it is example code only and the simplicity comes at the cost of hiding much functionality and reducing performance.
Installing the Easy BI Bridge
The Easy BI Bridge, and the examples in this article, require the following versions of Oracle software, all of which are downloadable now from OTN:
In addition, the examples in this article use the following version of Oracle Database:
Download the source code for the Easy BI Bridge here. Expand this zip file into a directory such as "d:\workspace" using the option to preserve full pathnames. (Note: This walk-through assumes that you are using Microsoft Windows; however, the Easy BI Bridge service should work under other operating systems as well.)
Inside the Easy BI Bridge
The Oracle JDeveloper project contains three key components:
In addition to these components, there are some support files:
The Java package namespace is oracle.bi.soa, which also contains the namespace oracle.bi.soa.proxy for the generated proxies to the OBI: EE Web services.
The BusinessIntelligence Web service exposes the following methods:
For simplicity these methods all take the same RequestSpecification structure, which contains a reference to a report path in the Oracle BI Presentation Server catalog as well as a set of optional parameters for the report.
To configure the Easy BI Bridge for your environment, start Oracle JDeveloper and select File/Open... Browse to the directory in which you have unzipped the code, and open the file BISOABridge.jws.
Oracle JDeveloper will open the project as shown in the screenshot below. Within the project, locate the EasyBIBridge.java source file and double-click it to open it.
Within this file, locate the public class EasyBIBridge and amend the values for biServer, administrator, and adminPW to reflect the values for your installation of Oracle Business Intelligence Enterprise Edition Plus. In this example code, the Web service will always connect using the Oracle BI Server administrator account. Again, note that this is example code and should be used only in a trusted environment for demonstration purposes only.
Save the changes you have made, and then right-click the WebServices.deploy file and select Deploy to EAR file. Oracle JDeveloper will then recompile and deploy the Easy BI Service to an EAR file, which can then be deployed to your local installation of Oracle Application Server 10.1.3.1.0
To install the data and Oracle BI Server repository referenced in the article, follow the instructions provided in the download to install the data, and use the provided OTN_SOA_BI.rpd repository file to provide a ready-built business model on which you can build the required requests.
Adding BI Functionality to the Order Bookings Demonstration
Now that you have successfully installed the Easy BI Bridge, you can use the features it provides to add BI functionality to your business applications and processes. To demonstrate how you might go about doing this, you will take an existing business process provided as part of the Oracle SOA Suite Order Bookings demonstration and amend it to take advantage of the functionality provided by Oracle Business Intelligence Enterprise Edition Plus. By doing this, you will see how an existing process can use the analytic capabilities of Oracle Fusion Middleware, and you will see how easy it is to use the Easy BI Bridge along with the Web services and BPEL functionality provided by Oracle JDeveloper 10g.
The Order Bookings demonstration, an overview of which is shown in the diagram below, shows the capabilities of Oracle SOA Suite 10.1.3.1.0 and starts with an order being accepted via a Web interface. This order is then transmitted, via Enterprise Service Bus, to a BPEL process, which then registers the order and then retrieves customer information, via a DBAdapter, from a database. Once the customer details are retrieved, a call to a credit-checking service is made to verify the customer's status, with the process then continuing to fulfill the order or not, based on the status of the customer and the value of the order.
If you have not done so already, download and install the Order Bookings SOA Suite demonstration, configure it for your particular environment, and go through the initial process of placing an order and then following the workflow process using Oracle BPEL Console. The examples in this article assume that you have a basic understanding of the Order Bookings demonstration and the BPEL functionality in Oracle JDeveloper and that you have previously built requests using Oracle Answers, the report-authoringcomponent provided with Oracle Business Intelligence Enterprise Edition Plus 10.1.3.2 or 10.1.3.3.
In the two examples used in this article, you will first replace the existing call to a database to retrieve the customer's details with a request to Oracle Business Intelligence Enterprise Edition Plus to return a report, on the same customer, but this time run against an integrated, corporate data warehouse containing KPIs and customer analytics. Second, you will replace the existing call to an external credit-checking agency with another call to Oracle Business Intelligence Enterprise Edition Plus, this time to evaluate a condition that determines whether the customer is valid or fraudulent. While these are trivial examples, they will demonstrate the process of retrieving data from the Oracle Business Intelligence common enterprise information model, via requests created using Oracle BI Answers, and will point you in the right direction for including analytical capabilities in your own applications and processes.
After installing Oracle SOA Suite 10.1.3.1.0 and then installing and configuring the Order Bookings demonstration, using the Applications tab locate the SOAOrderBooking entry and then the SOAOrderBooking.bpel file within it. Double-click the file to open the main Order Bookings BPEL process.
The BPEL process starts with a call from the client and initially inserts details of the order into a database. It then uses a number of services, shown in the left- and right-hand side "swim lanes" in the BPEL process, to initially retrieve details of the customer from a database and then verify the customer's credit worthiness using an external credit-checking service.
Depending on the results of the credit-checking service, the order is then either rejected or passed on to a business rule that either processes the order immediately or refers it to a human operator to review, depending on the status of the customer and the value of the order.
In this exercise, you are going to replace the call to a database via a DBAdapter with a call to the executeRequest Easy BI service, and the call to the CreditValidatingService with a call to the evaluateCondition Easy BI service.
Executing an Oracle BI Answers Request
Your first task is to open the Order Bookings BPEL process and make the Easy BI Bridge available to use by registering it as a partner service. To do this, open the Component Palette in Oracle JDeveloper, use the drop-down list to select the Services view, and drag-and-drop a partner service onto the right-hand swim lane in your BPEL process. Using the Create Partner Link dialog box, name the partner service BusinessIntelligence and press the Browse WSDL Files from Local File System... button to select the file
from the Easy BI Bridge sample code you installed earlier.
Accept all the prompts and then select BusinessIntelligence_Role as the partner role. Click OK to save the Partner Link definition. You have now registered the Easy BI Bridge and made its services available to your BPEL process.
Now that the partner link has been added, go back to the Component Palette and this time use the drop-down list to select Process Activities. This list of activities represents actions that your business process can take, and you should now select the Invoke activity, which instructs the business process to make use of a particular Web service.
Drag-and-drop the Invoke activity onto your business process, just after the existing GetCustInfo Invoke activity. Later on, you will remove the GetCustInfo Invoke activity, together with the Assign activity before it that initializes its variables, once you confirm that the calls to Oracle Business Intelligence Enterprise Edition are working as expected.
Once the new Invoke activity is added, connect it to the BusinessIntelligence partner link and then select the ExecuteRequest service, as shown in the screenshot below:
Give the Invoke activity the name GetCustInfoFromBI, and use the Magic Wand buttons provided by the Edit Invoke dialog box to create global input and output variables using the default names. Finally, click OK to save the activity. You will use these input and output variables in a moment to pass values to, and from, the Answers request.
Now that the new activity has been added, your business process will look like this:
The next step is to pass to the activity the name of the Oracle BI Answers request to execute, and the name and value of the parameter that the request requires. Before you do this, though, you first need to create a report in Oracle BI Answers for the activity to execute. This report will take a customer ID as a parameter and pass back details of the customer in a Microsoft XML Result set format, which you will then transform so that it matches the variables used in the rest of the business process.
Using Oracle BI Answers, create a request that returns a simple list of columns, as shown in the screenshot below.
Next, create a filter on the CUSTID column that uses a Presentation Server variable with a default value of 10. Finally, save the report to the Shared area of the Web Catalog into a new directory called EasyBI.
Now, return to the Order Bookings BPEL process and use the Process Activities list to add a new Assign activity just before the Invoke activity you just added. This activity will assign the variables required by the Invoke activity and will set the request name, parameter name, and parameter value. Once the Assign activity is added, double-click it to open the editor, call the activity AssignRequestForBI, and use the Copy From operation to assign the following variables:
Once all variable values are copied, the Edit Invoke activity dialog box will look like this:
Click OK to save the Assign activity details.
Next, add a second Assign activity to the BPEL project, just after the GetCustInfoFromBI Invoke activity, and call it DecodeCustInfo, as shown in the screenshot below.
Double-click on the DecodeCustInfo Assign activity to bring up the activity dialog box.
Now, click on the Copy Operation tab, and on the left-hand side of the dialog select Expression as the type, and then use the Expression Editor button to first add the following expression:
Place the cursor between the two brackets, and then use the variable selector to select the executeRequestResponseElement part of the Oracle BI Answers request output, like this:
Click OK to save the expression and return to the Copy Operation dialog box.
On the To side of the Copy Operation right-click in the variable, right-click the Process Variables node, and select Create Variable. Name the variable DecodedResults and select Element as the variable type.
The variable you are now going to create is of a custom type whose schema is defined in the file rowsets.xsd, provided as part of the Easy BI Bridge project. This schema contains a number of element types, such as
<xsd:element name="rowset1" type="rowset1t"/> <xsd:complexType name="rowset1t"> <xsd:sequence> <xsd:element name="Row"> <xsd:complexType> <xsd:sequence> <xsd:element name="Column0" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType>
To import the schema into the Order Bookings project, select the Flashlight icon by the Element field in the variable browser to open the Type Chooser, as shown in the screenshot below:
When the Type Chooser dialog box is displayed, click the icon that resembles an organization chart with a green arrow to import the new schema.
Now browse to the rowsets.xsd file in the Easy BI project at /bisoabridge/public_html/rowsets.xsd and click OK to import it. A new node will appear in the Type Explorer tree named Imported Schemas. Expand this tree to see all the elements.
The XML Schema that you are using has a number of elements corresponding to the number of columns in the request output. As the report you are using returns seven columns, select the rowset7 element when the Type Chooser dialog box is displayed.
Click OK and then OK, and select the BusinessIntelligence partner link to save the XSD Schema file. Now, set the To part of the Copy Operation dialog to the rowset7 element provided by this new variable. Once you have completed this step, the dialog should look like the screenshot below:
Click OK to save the Assign activity details. Now that you have taken the output from Oracle BI Answers and copied it into a "generic" variable that uses the correct XML Schema, your final step is to copy it from this variable into the existing process variable that is used by the rest of the BPEL process in handling the customer order.
Rather than creating another Assign activity that copies across each variable element in turn, you will instead use a Transform activity to transform all elements in one step, using an XSL transformation map.
To do this, use the Component Palette to add a Transform activity after the last Assign activity you added, and name this activity TransformDecodedtoCVariable.
Your BPEL process should now look like the screenshot below:
Now you will provide details on how the elements in the DecodedResults XML rowset are transformed into the correct process variable elements. To do this, double-click the Transform activity to display the Transform dialog box, and within the Source and Target section, select DecodedResults as the source and CustomerServiceResponse as the target.
Then, press the Create Mapping button to map the two variables together.
Noting the definition of each column in the request that you created beforehand, map each column in the request result set to the target elements in the transformation. For example, Column 0 in the Answers request contains the CUSTID, and therefore it is mapped to the tns:custid element in the target area.
Refer back to the original Answers request to find the meaning of the remaining rowset elements and map these to the appropriate elements in the transformation target.
Once you have completed the transformation mapping, save what you have done and save the resulting .xsl file.
Deploying the Updated BPEL Process
You are now ready to deploy and then test your amended BPEL process. For the time being, the original step to retrieve customer details from a database still remains in the business process, but you will remove this once you confirm that your activities are working as expected. Note that the following steps assume that you have previously installed and deployed the complete Order Bookings demonstration prior to this exercise and that you have checked that the Order Bookings BPEL process installed and deployed correctly by submitting an order through the demonstration Web interface. If you have not completed these steps, do so prior to proceeding any further.
In Oracle JDeveloper, locate the System Navigator pane and the SOAOrderBooking.jpr folder. Within this folder, locate the build.xml file, right-click it, and select Run Ant. Using the Properties tab, ensure that the properties file you selected when installing the Order Bookings demonstration is still selected, and then click OK to build and then install the updated Order Bookings BPEL process.
Check the Apache ANT log to confirm that the build and deploy was successful (you should see a BUILD SUCCESSFUL entry at the end of the log), and then start the Order Bookings Web client together with Oracle BPEL Console to check that your amended business process works.
Using the Web client, log in as email@example.com and place an order for a Treo 650 Phone/PDA, which at US$650 will pass through the Order Bookings business process without requiring manual approval. Once the order has been submitted, open the Oracle BPEL Console and locate the last instance of the amended SOA Order Bookings BPEL process. If all has been set up correctly, you should be able to see the updated business process with your call to the Answers report to retrieve data on the customer.
If you click any of the new activities added to the BPEL process, you can check the data that each activity is working with. For example, click the DecodeCustInfo Assign activity to see the results of the Answers request loaded into the variable that you created earlier in the example.
Once you have successfully completed this first stage, you can move on to the second stage and use another method presented by the Easy BI Bridge, this time to evaluate a condition.
Evaluating a BI Condition
Directly after the original BPEL process retrieved the customer's data, a credit-checking service was called to verify the validity of the customer's credit card. You will now replace this with an Answers request that returns rows if a customer is judged to be trustworthy and returns nothing if the customer is fraudulent. Using this approach, you will be able to swap out the existing validation service call within the Order Bookings BPEL process but use the same switch logic later on to reject or accept the order.
Moving back to Oracle JDeveloper, first of all delete the AssignRequest and GetCustInfo activities, which you have now replaced with calls to BI functionality. Your BPEL process should now look like the screenshot below:
Now turn to Oracle BI Answers and create a report that returns a row for a given customer if the customer is valid. In the example data set provided as part of the article download, the Customer Attributes logical table contains a column called Fraud Alert, which you will use to determine the validity of a customer. In real life, your Answers report could use data from across the organization, together with metrics and analytics built up by BI analysts, to determine the status of a customer, but for this example you will use a simple column that returns either a Y or an N value. Create a filter on this value as shown in the screenshot below, again use a Presentation Server variable to create a Customer ID parameter, and save the request in the same Web Catalog directory as the other request used in the business process.
Turning back to Oracle JDeveloper, you use the Component Palette to add a new Invoke activity just after the InvokeCreditService activity, link it to the BusinessIntelligence partner service, and call the activity InvokeCreditServiceFromBI. Select the evaluateCondition service and use the Magic Wand buttons to create two global variables using their default names.
Using the same procedure as before, add an Assign activity before this new Invoke activity, to assign the ID of the customer, the name of the presentation variable, and the name of the report to the InvokeCreditServiceFromBI input variable.
The following screenshot shows how the amended BPEL process should look. Again, leave the existing Assign and Invoke activities in place until you have checked that the new logic works properly.
If you look at the activity immediately following the request invocation, you will see that it is a switch activity that, if you click the Edit link next to the <case> entry, checks the return value from the existing call to the credit-validation service.
If you click the Edit link at the top right-hand side of the condition expression, you can amend the condition to instead use the return value from the evaluateCondition Invoke activity, again checking to see whether it is false, indicated by the fact that no rows have been returned by the request.
Now save and redeploy the business process, submit an order as a customer who will not pass the validation test, and check using the BPEL Control application that the amended process has incorporated your changes correctly. For a customer who fails the evaluated condition (in other words, is fraudulent), the Answers Web service will return a "false" response and the process will fail, as shown in the BPEL Console output below:
Clicking the new InvokeCreditServiceFromBI Invoke activity shows that the condition was evaluated correctly and returned a response of "false".
You have now completed the exercise. You can go back to Oracle JDeveloper and remove the original reference to the credit-checking service, and add further calls to the Oracle BI Answers request to retrieve sets of data or individual data elements, evaluate other conditions, or even use the invokeIbot service to distribute a report or check an alert.
In this article, you have seen some of the possibilities presented by the Web Services SOAP Interface provided by Oracle Business Intelligence Enterprise Edition Plus. Using the Easy BI Bridge sample code to accelerate application development, you have amended an existing business process to retrieve customer data from a data warehouse, and used data in the data warehouse to evaluate a condition based on the results of an Oracle BI Answers request.
Using standards-based interfaces in this way enables your applications and business processes to take advantage of the data integration, analytics, and metadata capabilities of Oracle's BI tools, all in a way that is transparent to the calling application and with familiar tools such as Oracle JDeveloper. For further information on the Easy BI Bridge example code and the SOAP interface that Oracle Business Intelligence Enterprise Edition Plus provides, consult the documentation provided as part of the Easy BI Bridge download and the product documentation available on OTN.
Mark Rittman is an Oracle ACE Director and cofounder of Rittman Mead Consulting, an Oracle Partner providing specialist BI and data warehousing solutions based in the U.K. He chairs the U.K. Oracle User Group Business Intelligence & Reporting Tools SIG, co-chairs the ODTUG BI&DW SIG, and runs a blog at www.rittmanmead.com/blog.
Joel Crisp is a principal engineer for Oracle working on the Oracle Business Intelligence Suite of products. A Chartered IT Professional with the British Computer Society (BCS), he has previously worked for Trilogy in the U.S. and as a Java architect for Sun Microsystems Professional Services.