Oracle BI EE 11g and Oracle ADF - Part 2 - Real Time reporting using View Objects

In the last part i showed how BI EE 11g and Oracle ADF are integrated through the same Security Framework. In this part, we shall look into another reporting innovation (by use of the common ADF Framework) i.e. real time reporting using BI EE by directly reversing metadata from a transactional application. I call it an innovation as i haven't seen similar such capability in any other reporting tool(i could be wrong). So far, most major BI tools in the market have been doing reporting in 2 ways(including BI EE)

1. Reporting from a set of database(relational, multi-dimensional, flat-files etc) tables
2. Reporting from a set of pre-defined data services

The problem with these approaches (even in BI EE) is that one has to spend time doing the data modeling. Data Modeling from a BI Perspective is good as it helps in visualizing the data from an end-user standpoint. But in some cases, for example, when reporting out of transactional databases we might already have some pre-defined reporting screens available (real time reporting as part of the transactional system). E-Business Suite, Peoplesoft etc all come with pre-defined reporting or updatable forms/screens. Each of these screens will be firing some sort of SQL to get the data and display it to the end user(which might span multiple tables). Screenshot of an expense reporting application screen in EBS is given below

This itself contains 2 SQL queries(minimum) for displaying the data. To fire the same report in another reporting tool, requires the following tasks

1. Identify the source tables
2. Model them in the reporting tool
3. Develop and then match the results with the EBS screen (testing)

In effect, this requires a person who knows EBS source systems, a BI Developer and then atleast 2 to 3 days of time in modeling, reporting and testing. All of this is needed for a single report, what if the requirement is to mimic the reporting available in 50 or 100 such EBS screens. In such cases, the reporting can grow quite complex and requires careful balancing of resources who know EBS and resources who know BI EE (or any other reporting tool for that matter).

BI EE - ADF View Objects integration alleviates the above said problem by completely bypassing the need to do initial source table identification (and sometimes modeling). Its advantage is it can understand the transactional system and convert the ADF metadata into BI EE specific metadata. So, all a BI EE developer has to know is what application module View object provides the data for a specific screen. That way rather than importing the metadata from the database, BI EE will reverse the source tables/columns from the transactional system and then import them accordingly. This is not only applicable for EBS, PSFT etc but also applicable for any application built out of the ADF framework.

The fundamental goal of Oracle Fusion Applications is to make everything interoperable thereby reducing implementation/go live times. You can sort of start seeing how BI EE will eventually merge into the Fusion Stack with this capability. This feature will also provide the ability for BI EE to do true real-time reporting. The diagram below explains how this integration works

To demonstrate how this works, we will be creating an application(Order Entry for example) which will contain 2 View Objects. One for getting the Orders and the other for getting the Order Items as shown below

To create these AM's/View Objects follow the instructions in my previous post. In an ADF application, generally all business logic, business rules etc get applied in a View Object/Application Module. So, during the BI EE import/reporting all of these business rules/business logic will be honored as well. To set this up there are 3 steps we need to follow

1. Have a Weblogic application server with a domain that will contain the JRF, Oracle Appl Core binaries - While creating a domain, weblogic will not show the option of Application Core libraries. If that is the case, get the Appl Core binary from Oracle BI EE 11g instance and deploy it to your weblogic instance. This is key to get the integration working.

2. BI EE and the application can be in completely different boxes. To enable the communication, a BI EE broker needs to be deployed in the application weblogic instance.

3. A SQL Bypass database can be specified for improving performance. Another good feature wherein one can persist aggregation to a physical database rather than performing in the BI Server memory. Now, only if we could have this feature for cross database joins.

In my example, i will be deploying the application in the same weblogic instance as BI EE. To check whether the Application Core libraries exist, lets go to the weblogic console and check for applcore library.

As mentioned before, if this does not exist in your weblogic domain, copy the applcore library files from BI EE instance and then deploy them in your weblogic instance. In the same way, a BI EE broker needs to be deployed in your application weblogic instance. This broker is a war file that is present under {Middleware_Home}\Oracle_BI1\bifoundation\javahost\lib\obisintegration\adf folder of the BI EE 11g instance. Copy the ear file oracle.bi.integration.adf.ear under this folder and then deploy it in the application weblogic instance.

Now the next step is to prepare our application for deployment. To do that we need to make sure that the database connection that we are using as part of the application (OE schema) exists as a JNDI in weblogic(details of why we need to do this is given in the last blog post). Also, we need to enable the configuration of the application module to use the JNDI instead of going through the Credential Framework.

So, we go to the configuration of the Application Module and then enable a new configuration to use the JNDI as shown below

Ensure that this configuration is the default configuration of the application.

After enabling this, create a New Business Components Archive in the Model Project (click on New from the Project) and give it a name

You should now see 2 jars that will be deployed as part of the Archive.

Deploy the model using this deployment profile (use the default options of deploying to a file)

Right click on the application and create a new Web Project as shown below (adapt the names as necessary for your application)

Go to the project properties of the OBIEEBroker application and then ensure that the jar files that we created before are included as dependent libraries for this application war file (default profile webapp).

Expand the web.xml of this project and add the following entries

Now create a new EAR deployment profile for the whole application. Ensure that the webapp.war that was created for the webproject is included in the EAR.

In the application resources task pane, open up the weblogic-application.xml and add the following entries to the xml file.

This is needed for referencing the broker and applcore libraries. Login to the weblogic console and then add a JDBC data source with the same name & JNDI as the one we specified in the Application module (without the java:comp/env in the name).

Deploy this to the BI EE 11g cluster as shown below

Deploy the application now directly to the BI EE 11g instance from JDeveloper.

After this the URL for the broker should be accessible from a browser (http://172.16.62.131:9704/BIEEOrders/obieebroker in my case). Now the application is ready for import into BI EE. Lets now open up BI Administrator and choose the OracleADF_HTTP as the database type. In the database source, enter the same JNDI name as we entered in weblogic. Enter Model Name.Application Module name as the Application Module definition (model.orders). Enter the name that we gave for the Application Module config(ordersAM). Also enter a valid weblogic username and password

We should now be seeing both the View Objects that we created before. Lets now import both of them.

If we now look in the connection pool, there is an option to set the SQL Bypass Database which we can use for pushing aggregation/sorting etc to a physical database rather than doing them in the BI Server layer.

In the next part, i will cover how to use the SQL Bypass Database and see how that influences the queries etc.