Oracle ADF BI Integration

This blog post will guide through the process of embedding OBIEE analytics (reports/dashboards) on an ADF application. Also hoping that this resource will act as a reference material for future.

There are multiple ways in which we can achieve BI integration with ADF. The illustrated version on this post is one that I found very sophisticated.

The reasons for OBIEE integration could be manifold. The default DVT features available within ADF can be used to build reports however not as sophisticated as BI does. In such cases, it would be a great time-saver plus a great way to show analytics to the user on a jspx page.

In a more interesting use case, I have used this feature within a BPM process user task - where the reviewer/approver can see the BI analytics on the UI along with process parameters before taking an action.

Products/Versions:

JDeveloper/ADF: 11.1.1.6
OBIEE: 11.1.1.5

Step 1: Download the following JDev extensions

1	bi-adf_bundle	Provides the ability to browse the BI Catalog through the JDeveloper resource catalog and add BI objects to an ADF page.
2	bi-adf-taskflow-extension_bundle	Provides the ability to browse the BI Catalog through the WebCenter resource catalog and add BI objects to a webCenter page.
3	bi-soap-cn_bundle	Provides the ability to create a BI Soap Connection.
4	bi-vo_bundle	Provides the ability to create a View Object that can query Oracle BI using logical SQL.
5	oracle.webcenter.customization_bundle	Provides design time capabilities for the WebCenter Customization Framework.
6	oracle.webcenter.framework_bundle	Provides design time capabilities for the WebCenter Framework and Services.

Links for download:

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/131167.xml
http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/156082.xml

I know it is a quite a list - but can't get away from this :)

Step 2: Install these extensions to your JDeveloper

Help -> Check for Updates... -> Choose "Install From Local File" option, select the extensions one-by-one and let JDeveloper install them. JDeveloper will prompt for restarts for every extension install.

Step 3: After installing all the BI extensions, under the "Application Resources" panel of the ADF application (where you want to integrate the OBIEE reports), right click on the "Connections" -> "New Connection" -> "BI Presentation Services..."

Step 4: Provide the OBIEE analytics environment information in the wizard and proceed to complete connection creation. If you have not enable 'impersonation' in BI, change this option to 'false' in the following screen. Ensure that the connection is successfully established to the BI server.

Step 5: Now, this connection should appear automatically in the "IDE Connections" panel under the "Resource Pallete". This can be used to browse through the BI analytics for available dashboards/reports.

Now that the BI presentation services is available, all that remains is integrating the available BI reports into the ADF page

Step 6: First step towards ensuring that the ADF project is BI technology enabled. To do this, right click on the ADF project -> Project Properties

Under "Technology Scope" option ensure that the "Business Intelligence ADF View Components" is selected.

Step 7: We are almost there! Browse through the BI presentation services catalog from under the "Resource Pallete", drag & drop the required reports on to the ADF page.

Common Impediments & Resolutions:

I1: Unable to drag & drop the reports to ADF page

Resolution: Initially I was not able to drag and drop the BI reports on to the ADF page successfully. I had to manually include the following element to my ADF page source to force ADF to recognize the BI reports;

<adfbi:content id="content1" value="#{bindings.biExecBinding1}"/>

After doing this the BI technology binding was enabled on my ADF project. Then I removed the element which I added manually.

I2: At runtime, OBIEE reports were not displayed. The following error was reported on the ADF page;

Unable to connect to the BI Presentation Server. Please ensure that it is running, configured properly, and that the connection details within this application are correct.Please check the log file for more details.

The server logs reported the following error;

Cannot lookup the connection, BI using fallbacks[[
oracle.bi.presentation.soap.connection.BISoapException: No credentials found for this connection - please check that your connection credentials were deployed properly.
at oracle.bi.presentation.soap.connection.impl.BaseBISoapConnection.setReference(BaseBISoapConnection.java:216)
at oracle.bi.presentation.soap.connection.impl.RTBISoapConnection.(RTBISoapConnection.java:58)
at oracle.bi.presentation.soap.connection.BISoapConnectionFactory.getObjectInstance(BISoapConnectionFactory.java:715)
at oracle.adf.share.jndi.ReferenceStoreHelper.getObjectForReference(ReferenceStoreHelper.java:295)

................................................................................................

................................................................................................

................................................................................................

................................................................................................

at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179)

at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1490)

at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)

at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

Resolution: To resolve this error, ensure that the "Credentials" option is unchecked under the "Security Deployment Options" of the "Application Properties".

This config ensured that the credentials are not overwritten and are picked up from the cwallet.sso file under the EAR package. The issue was resolved and the BI reports were displayed on the ADF page as expected.

Write back if you have any thoughts/suggestions/clarifications :)

Refresh ADF table after a popup message/confirmation

In this blog post, I am demonstrating a way to refresh the contents of an ADF table after a certain action (any CRUD operation) especially when a confirmation popup or message has to be displayed.

Just creating an invokeAction on the binding to refresh the table works if there are no popups or message dialogs on button action. However, if you have a situation to refresh the table AND display the popup, you will just have to do an additional config. There could be other ways to solve this, but I found the following approach easy to work with.

The solution is to disable the 'cacheResults' property of the iterator to which the ADF table is bounded to and ensure that an "invokeAction" executable is created on the binding to refresh it (say prepareModel stage or renderModel stage for example). To do this,

• Go to the page definition file and edit the source view
• Find the iterator to which the ADF table is bounded with
• Add the additional property cacheResults and set its value to false

<iterator Binds="AdminVO1" RangeSize="25" DataControl="AppModuleDataControl"
              id="AdminVO1Iterator" CacheResults="false"/>

Let us find out why this additional config is required? Why does the invokeAction which is set to re-execute on page render does not work when a popup/message is displayed?

By default, whenever an ADF table is created on a datacontrol it cache the results in memory in order to save on performance. Let us see what ADF documentation has to offer in this regard; (Highlighted portions that is of importance to this case)

When a data control modifies a collection, the data control must instantiate a new instance of the collection in order for the ADF Model layer to understand that it has been modified. In other words, although some action in the client may change the collection, that change will not be reflected in the UI unless a new instance of the collection is created. However, for performance reasons, accessor and method iterators cache their results set (by default, the cacheResults attribute on the iterator is set to true). This setting means that the iterator is refreshed and a new instance of the collection is created only when the page is first rendered. The iterator is not refreshed when the page is revisited, for example, if the page is refreshed using partial page rendering, or if the user navigates back to the page.

Since the popup/message dialog is displayed on action and the page refresh is interrupted midway, the refresh is not actually happening on just invokeAction and the table displays the cached results. However, if the iterator is set to not cache results, in a way we are forcing it to execute again for fetching the results when the page is rendered after the popup/message dialog display.

ADF 11g WebService Data Control JBO-25080 and JBO-25058

I recently encountered a strange error while using a WebService Data Control on ADF. Since, I could not find any documentation or reference on this issue easily available on the web I thought of posting it here.

At runtime, the following error was logged in my console;

oracle.jbo.NoDefException: JBO-25080: Definition name: CancelRequest does not match the file in which it is loaded from: com.oracle.Requests.CancelRequest
at oracle.jbo.mom.DefinitionManager.loadLazyDefinitionObject(DefinitionManager.java:999)
at oracle.jbo.mom.DefinitionManager.loadParent(DefinitionManager.java:1228)
at oracle.jbo.mom.DefinitionManager.loadLazyDefinitionObject(DefinitionManager.java:933)
at oracle.jbo.mom.DefinitionManager.findDefinitionObject(DefinitionManager.java:482)
at oracle.jbo.mom.DefinitionManager.findDefinitionObjectDontCheckName(DefinitionManager.java:433)
at oracle.adf.model.bean.DCBeanDataControl.findStructureDef(DCBeanDataControl.java:1884)
at oracle.adf.model.bean.DCBeanDataControl.getAttributeDefs(DCBeanDataControl.java:1936)

...........................................................

...........................................................

Supplementing to the above error the following error was also logged;

oracle.security.jazn.JAZNRuntimeException: JBO-25058: Definition RequestId of type Attribute not found in RequestId

Even on repeated analysis (including the case sensitivity), everything seemed fine and the error message did not make sense.

After lots of googling around, I stumbled upon an Oracle forum where it noted a critical ADF bug.

"The package name CANNOT be the same as the class name". This will sound like a very ridiculous bug but that's the way it is at least for now (I am seeing this error in JDev 11.1.1.6). Since my class name and the package name were both 'CancelRequest', ADF data control went weird and complained with the JBO errors JBO-25080 & JBO-25058.

This can happen with EJB data controls as well!

I had to resolve the error the hard way. Had to go back and change my Web Service to reflect a different name (different name for the root node and operation). Operation name will be the package name and the parameter root element name will be thei class name when the WebService data control is generated.

Although you can do very little in a production scenario to handle this bug, you can probably take a different approach beforehand like using a WS Proxy or a POJO data control instead of a WebService data control if you see that the package name and class names are identical.

ADF 11g "Cannot convert -2 of type class java.lang.String to class oracle.jbo.domain.DBSequence"

Have you ever encountered the above error? The reason for this could be manifold. Go through the following list and hopefully should resolve it if everything is 'as expected'.

1. Check whether you have any validators on the ADF component

2. Check for any 'Converters' that might run

3. Remove the mandatory, maximumLenght properties of the ADF component and check if this resolves the issue

4. Finally, go to the source view of the Entity Object attribute which is modified to DBSequence and check if the columnType, Type, SQLType properties are fine which could cause the casting error. A valid attribute can be as shown below;

<Attribute
    Name="RequestId"
    ColumnName="EQUEST_ID"
    SQLType="NUMERIC"
    Type="oracle.jbo.domain.DBSequence"
    ColumnType="NUMBER"
    TableName="REQUESTS"
    PrimaryKey="true"
    IsUpdateable="false"
    Domain="oracle.jbo.domain.DBSequence"
    RetrievedOnInsert="true">
    <DesignTime>
        <Attr Name="_DisplaySize" Value="39"/>
    </DesignTime>
</Attribute>
 
On a side note, more often than not, if the primary key is generated by a DBSequence using the trigger approach, it often shows a negative integer value in the element on the UI. Oncommit, the value gets replaced with the DB sequence. In many cases this might look ugly to show a negative number to the user. To overcome this, check for the "Default Value" property for the attribute in the entity object and remove it.
 
Usually, the DefaultValue property will have a value as "@0" which causes the VO to render a temporary negative integer value on the UI.

ADF DB Sequence - Using DB Trigger approach

In this post I am going to show another more sophisticated way to generate a unique number (generated by a database sequence) and display it on the ADF UI if needed. The advantage of this approach over the previous technique (using the View Object create() method) is that the DB sequence is not wasted - The sequence number gets generated only on commit.

Let us take an use-case and see how this can be achieved through a step-by-step illustration;

1. For this use-case assume the following database table; where REQUEST_ID is the primary key column for which we would insert value dynamically from a DB sequence through ADF

2. Now, create a Fusion Web Application (ADF) in JDeveloper - Provide a name for the ADF app and accept the defaults in the create wizard

3. Now, right click on the model project and choose 'New'. Choose the 'ADF Business Components' under the 'Business Tier' and select 'Business Components from Tables' option

 4. Choose the 'Requests' table as the entity object on the first screen and select the RequestsView entity as the 'Updatable View Object'. Accept defaults in the following screens to complete creation of the model layer business components

5. This will automatically create the data control for the Requests view object.

6. Let us now create a DB sequence which will provide values to be inserted into the REQUEST_ID column and a DB trigger on the REQUEST_ID column under the REQUESTS table. The idea of db trigger is to ensure that the sequence id gets generated whenever there is an insert request on the table. This way the sequence numbers are not lost even if the transaction is rolled back before commit.

If you are not familiar with DB sequence or DB trigger creation, don't worry. JDeveloper can help you to create these declaratively in no-time.

7. Go to the Database Navigator view -> Right click on the Sequences and create a sequence as shown below

8. Similarly, right click on the REQUESTS table and create a DB trigger as shown below. Enter the trigger name and choose the sequence name (created in step 7) and the column name (REQUEST_ID)

 

9. Now, go to the ADF model project -&gt; open the Requests entity object. Under the 'Attributes' section, edit the 'RequestId' attribute as shown below;

• Change the type to DBSequence (oracle.jbo.domain.DBSequence) - If this is not showing up by default, you can browse & select this class
• Updatable property - Never
• Refrest After - Insert

10. Time to create the UI. Right click on the ViewController project and create a JSF page.

11. Let us now drag and drop the RequestsView1 data control from the 'Data Controls' panel on to the JSF page that we created as an ADF table component

12. Now, drag and drop the 'CreateInsert' operation on to the page as an ADF button

13. Similarly drag and drop the 'Commit' and 'Rollback' operations as ADF buttons as shown below

PS: Note that 'CreateInsert' operation is available under the 'RequestsView1' data control where as 'Commit' and 'Rollback' operations which are common across all VOs are available at the root level (AppModuleDataControl)

14. Now, let us run this page and observe the results. Click on the 'CreateInsert' button to create a new row. Enter values for RequestType and Description columns. Notice that the RequestId column is read only as modelled. This column will get a value once the commit button is hit. Behind the scenes, when commit button is pressed, a record is inserted into the REQUESTS table which will invoke the trigger to get a unique number from the DB sequence. After insert the ADF UI is refreshed partially to reflect the primary key value.

Hope this helps.

There are some tips & solutions here. Take a look.

BPM 11g - Validating Boolean DO in User Task Skip Condition

This may sound like a small thing but honestly speaking I had to try out multiple methods to get this working through XPath expression builder. Hence I thought of making note of this on my blog so it may help a needful soul :)

<Employee>
   <EmployeeName>John Smith</EmployeeName>
   <IsMarried>true</IsMarried>
   <IsContract>true</IsContract>
</Employee>

Use-Case: All employees who are 'Contract' should be reviewed by 'Group A' and 'Permanent' employees must go through an additional level of review by 'Group B'.

Solution: The most logical way to build/represent this use-case in BPM 11g would be to create a user task with two sequential approval/review stages where the first stage participants would be 'Group A' and the sequential stage would be attended by 'Group B'. Now, by leveraging the 'Skip Condition' capability we can build a condition on 'Stage 2' to validate whether the boolean data object (in this case 'IsContract' element) is false. This means that if the <IsContract> element is true it skips the second stage of review else it goes through the second level of review.

Let's say the boolean data objects are modelled as 'Check Boxes' in ADF - where it returns 'true' when checked or 'false' when not checked by the user

Now, the problem is how to evaluate the boolean data object in Skip condition. Here is one way to evaluate boolean data objects;

xp20:matches(string(/task:task/task:payload/Employee/IsContract), '\s*(?i:true|1)\s*')='true'

What we are doing here is that converting the boolean data type into String and matching the result against the string 'true' or 'false'. Apparently we have to do this because, the direct boolean validation [/task:task/task:payload/Employee/IsContract = true] doesn't work in Skip condition.

Hope this helps.

ADF 11g Table Row Selection

By default, when the 'Row Selection' property of the ADF af:table component is set to 'Single', the first row rendered in the table at run-time is 'Selected'. This is all fine and works as expected. The issue occurs in a scenario where the end-user wants to choose exactly the same row that ADF renders as 'Selected' especially when there is a back end logic to fetch and process the selected row values. This is because the 'selection listener' on the table is invoked only when a row state changes from 'unselected' to 'selected' - hence the behaviour.

In order to overcome this situation and ensure that 'NO' rows are 'rendered' selected by default, clear the 'SelectedRowKeys' property under the 'Advanced' section. This will ensure that at runtime ADF renders the table with no rows selected.

On the other hand, if you override the 'Selection Listener' with a custom listener (to process selected row values) you might want to use the following code snippet in your backing bean to make the current row selected;

        ADFUtil.invokeEL("#{bindings.CI_CA_HDR_PNG.collectionModel.makeCurrent}",
                                 new Class[] { SelectionEvent.class },
                                 new Object[] { selectionEvent });

where #{bindings.CI_CA_HDR_PNG.collectionModel.makeCurrent} is nothing but the default 'Selection Listener' value.

ADF 11g JBO-25002: Definition "VO" of type View Definition is not found Error

Generally this is an error encountered at run-time when you try to 'recreate' a VO (view object) in an ADF application. The reason is that, even though the old VO is deleted from the ADF model project, the traces are still left out under the APP Module configuration.

This usually happens when the view objects are associated to the model project App module during VO creation.

To resolve this error, open the App module XML file and remove the reference.

Redeploy the application and you are good to go.

Oracle WebCenter 11g Frame Busting

While trying to add a webpage portlet in Oracle WebCenter 11g either as a portlet or as a simple webpage hosted in the server, most of the times we encounter the following error;

"WARNING: Unable to load content in a frame. Frame content will load at the top level."

This is an obvious issue of iframe busting where the ADF layout of the webpage is unable to fit itself within the portlet container and hence is complaining. To overcome this issue, we need to add few lines of code to the web.xml file of the ADF project and redeploy the webpage.

Under the <web-app> root element of the web.xml, add the following <context-param> ;

<context-param>
    <param-name>oracle.adf.view.rich.security.FRAME_BUSTING</param-name>
    <param-value>never</param-value>
  </context-param>

And there we go, WebCenter portal doesn't complain anymore on loading the webpage.

Portletize ADF Faces jspx page

In this post, let us see how to portletize an ADF 11g application. This post will demonstrate how to expose a ADF faces page as a JSR 168 standard portlet which can run within Oracle WebCenter portal.

Pre-Requisites:

Download JDev WebCenter Extension from the following link.

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/156082.xml

Go to Help -> Check for Updates -> Choose 'Install From Local File' option and select the downloaded webcenter framework bundle zip file for install

From your ADF application, expand the ViewController project, right click on the jspx ADF faces page which you want to portletize and choose 'Create Portlet Entry...'. In the resulting popup window, enter portlet name, display name, portlet title, short title, description and click OK. This action will generate a portlet deployment descriptor automatically for the page. You can portletize 1 or more pages of your ADF application under a single portlet deployment descriptor.

While deploying the ADF application, JDeveloper will prompt for confirmation whether the JSR 168 portlet producer has to be deployed. Press OK to confirm. We now have to identify the WSRP WSDL URL which will be used to register the portlet in WebCenter. Pick up the ADF application URL from the deployment log and enter the following in a browser window;

<ADF_Application_URL>/info

Click on the WSRP v2 link and copy the WSDL URL from the resulting screen.

Let us now register the portlet producer with WebCenter spaces. Follow the steps below to achieve this;

1. Login to EM console as weblogic 2. Expand WebCenter -> WebCenter Spaces -> webcenter(11.1.1.4.0) (WC_Spaces) 3. Choose 'Register Producer' from the WebCenter dropdown option 4. Under the 'Add New Portlet Producer' screen, choose producer type as 'WSRP Producer', enter a 'connection name' and provide the WSDL URL which we copied above earlier 5. Test the portlet producer and click OK This will ensure that the registered portlet is now available as a portlet in WebCenter portal which can be used as required.

ADF 11g "No Credential Mapper" Error on WebLogic

While migrating the ADF 11g application with model project on to various environments, the following error is encountered;

"java.security.PrivilegedActionException: weblogic.common.ResourceException: java.security.PrivilegedActionException: weblogic.common.ResourceException: No credential mapper entry found for password indirection user......"

This error occurs because, JDeveloper generates an application-level data source with password indirection. JDeveloper creates a weblogic-jdbc.xml file for each database connection stored in the application resources. In order to get rid of the above error, we should ensure that the jdbc settings are not synchronized during ear generation.


Go to 'Application Properties' -> Deployment -> Uncheck the 'Auto Generate and Synchronize weblogic-jdbc.xml Descriptors During Deployment' option.

After the above setting, generate the ear file for the ADF application which can now be deployed on different environments.

ADF 11g model project generic datasource

While creating a ADF model project in an ADF application, we define the connect string (hard coded) to connect to the database. However, while migrating the ADF application on to different environments, the connect string values might change and has to be referred dynamically from the data source created in the application server. To ensure that the ADF application points to the data source, we can define the configuration settings in the model project 'AppModule'.

Step 1: Right Click on the 'AppModule' and go to 'Configurations...'

Step 2: Under the 'Manage Configurations' dialog, edit the AppModuleLocal and AppModuleShared business components

Step 3: Under the 'Connection Type' panel, choose 'JDBC DataSource' and enter the data source name that is created on the application server

Step 4: Save and deploy the ADF application.

Now, we have ensured that the ADF application model refers to the application server data source. This will keep the ADF application generic as we have externalized the database connection details.

ADF generate primary key using DBSequence and display to user

In this post, I am going to demonstrate a way of generating and displaying a Oracle DB sequence and show that to user in a Oracle ADF page.

Step 1: Create a DB sequence in the database. For example let us consider order_sequence as the DB sequence

Step 2: In the ADF application, under the model project create an entity object for the table which has the primary key (for example, order_id) for which the DB sequence created above will be used for data insertion

Step 3: Open the Entity Object, go to Java tab and click on the 'Edit' button to auto-generate the entity object java class
              a. Select 'Generate Entity Object Class' check box
              b. Under the 'Include' grouping select 'Create Method' in additions to the default selected 'Accessors' option

Step 4: Now, click on the hyperlink which shows the auto-generated entity object java class and add the following java code under the create() method that is generated

super.create(attributeList);
SequenceImpl s = new SequenceImpl("ORDER_SEQUENCE", getDBTransaction());
super.create(attributeList);
Object obj = s.getSequenceNumber();
this.setOrderId((Number)obj);

Now, drag and drop the view object from the ADF data controls as a form with input text. Everytime, the form is invoked, a new sequence number will be generated from database and will be displayed in the UI.

I have illustrated another more sophisticated way of DB sequence generation here which has couple of advantages over the aforesaid method.

ADF Runtime DB Connection Error

While creating an ADF Fusion Web Application with a database datacontrol, the following error is encountered while running the application.

oracle.jbo.DMLException: JBO-26061: Error while opening JDBC connection.
at oracle.jbo.server.ConnectionPool.createConnection(ConnectionPool.java:253)
at oracle.jbo.server.ConnectionPool.instantiateResource(ConnectionPool.java:168)
at oracle.jbo.pool.ResourcePool.createResource(ResourcePool.java:546)
at oracle.jbo.pool.ResourcePool.useResource(ResourcePool.java:327)
at oracle.jbo.server.ConnectionPool.getConnectionInternal(ConnectionPool.java:104)
Truncated. see log file for complete stacktrace
java.sql.SQLException: ORA-01005: null password given; logon denied

This is because, the ADF model project fails to register the password with the app server which cause this SQL exception.

In order to overcome this error, edit the EXTRA_JAVA_PROPERTIES and append -Djps.app.credential.overwrite.allowed=true property under the setSOADomainEnv.cmd (In Windows) or setSOADomainEnv.sh (In Linux) file as follows. This file will be located under <<Middleware_Home>>\user_projects\domains\<Your_Domain_Name>\bin folder.

set EXTRA_JAVA_PROPERTIES=%EXTRA_JAVA_PROPERTIES% -Djps.app.credential.overwrite.allowed=true -da:org.apache.xmlbeans...

Restart the Oracle Weblogic server.