5 Working with ADF Business Component Data Sources

Oracle Business Intelligence supports connecting to ADF Business Components as data sources. This enables Oracle Business Intelligence users to query data from any application that is built using the ADF Framework. For example, because Oracle CRM applications are developed using the ADF Framework, Oracle Business Intelligence users can report on CRM data using an ADF Business Component data source that implements the required ADF Application Programming Interface (API).

By using the ADF components as a data source to the Oracle BI Server, users can quickly integrate operational reporting with any application that is built on top of the ADF Framework.

This chapter contains the following topics:

What Are ADF Business Components?
What Happens During Import?
About Specifying a SQL Bypass Database
Setting Up ADF Business Component Data Sources
Importing Metadata from ADF Business Component Data Sources
Enabling the Ability to Pass Custom Parameters to the ADF Application

What Are ADF Business Components?

Oracle Application Development Framework (Oracle ADF) is an object-relational framework that can be used to create J2EE business services and expose underlying database objects. This framework provides an abstraction layer that enables application developers to build applications quickly and efficiently.

When you use Oracle ADF to build service-oriented Java EE applications, you implement your core business logic as one or more business services. These back-end services provide clients with a way to query, insert, update, and delete business data as required, while enforcing appropriate business rules. ADF Business Components are prebuilt application objects that provide a ready-to-use implementation of Java EE design patterns and best practices.

The ADF model is represented through the ADF Business Component constructs called Entity Objects and View Objects, usually constructed and defined during design time:

Entity Objects: ADF framework components that represent a row in a database table and simplify modifying its data. Importantly, it enables you to encapsulate domain business logic for those rows to ensure your business policies and rules are consistently validated.
View Objects: ADF framework components that encapsulate a SQL query and simplify working with its results. In addition to read-only view objects, there are entity-based view objects that support updatable rows. The view object queries just the data needed for the client-facing task at hand, then cooperates with one or more entity objects in your business domain layer to automatically validate and save changes made to its view rows. Like the read-only view object, an entity-based view object encapsulates a SQL query, can be linked into master/detail hierarchies using view links, and can be used in the data model of your application modules.

Applications built using ADF obtain their data by querying the defined View Objects using the ADF APIs.

The ADF model also includes an application module, which is the transactional component that UI clients use to work with application data. It defines an updatable data model along with top-level procedures and functions (called service methods) related to a logical unit of work related to an end-user task.

The application module serves as a container for multiple View Objects and Entity Objects, and also contains configuration related to the JDBC data source.

About Operational Reporting with ADF Business Components

You can use Oracle Business Intelligence integration with ADF Business Components to generate reports on data within your applications. For example, you can generate reports based on expense report data entered into an Expense Application.

To do this, you would first import the Expense Application metadata into the Oracle BI repository using the Administration Tool, then map the data from the Physical layer to the Business Model and Mapping layer and Presentation layer. After you restart the Oracle BI Server and reload the metadata into Oracle BI Presentation Services, you can log in to Oracle BI Answers and drag and drop the columns to generate a report on the Expense Application data. For example, you can select columns to view a report of your expenses grouped by category.

What Happens During Import?

On import, the required physical tables and complex joins are automatically created. The instances (ViewObject and ViewLink) are imported into Oracle Business Intelligence. During query execution, the definitions retrieved from these instances are used to create the CompositeVO in ADF.These complex joins are 'dummy joins' and are not executed in Oracle Business Intelligence. Instead, they denote ViewLink instances that connect pairs of View Objects in the ADF model. The physical table and complex join names correspond to the fully qualified ViewObject and ViewLink instance names, respectively. This convention allows arbitrary nesting of ApplicationModules in the ADF model.

Note that the External Expression field in the Complex Join dialog for ADF data sources shows an arbitrary expression that has no meaning. This field is reserved for a future release.

The name of the automatically generated joins follow a naming convention similar to ViewObjectName1_ViewObjectName2 (for example, AppModuleAM.AP_VO1_AppModuleAM_BU_VO1). The ViewLink instance name appears in the ViewLink Name field of the Complex Join dialog.

The complex joins are only created automatically if a ViewLink instance is available. They are not created for ViewLink definitions. Joins using ViewLink definitions must be created manually. To do this, specify the ViewLink definition name in the ViewLink Name field of the Complex Join dialog.

Alternatively, Oracle Business Intelligence joins between VOs in different ApplicationModules are created upon import from ADF if custom properties are defined on the ApplicationModule. Note the following:

The property name format is BI_VIEW_LINK_property_name
The property value format is source_VO_instance_name, ViewLink_definition_name, destination_VO_instance_name

Be sure to use the fully qualified VO instance names for the source and destination VOs, as well as the fully qualified package name for the ViewLink definition.

About Specifying a SQL Bypass Database

The Oracle BI Server can automatically create composite View Objects at run time, so that an ad-hoc BI query can reference multiple View Objects in the ADF layer. For improved performance, a SQL bypass query is generated that incorporates the projection columns, filters, and joins required by the BI query.

The SQL Bypass feature directly queries the database so that aggregations and other transformations are pushed down where possible, reducing the amount of data streamed and worked on in Oracle Business Intelligence. When using a SQL Bypass database, the Oracle BI Server gets the VO query from the ADF Business Component data source and then wraps it with the aggregations in the Logical SQL query. The query, including the aggregations, is then executed in the database. Because the database computes the aggregation and less rows are streamed back to Oracle Business Intelligence, using a SQL Bypass database can result in significant performance gains.

Multiple View Objects are modeled as separate BI physical tables and are connected with dummy complex joins. These joins only represent the ViewLinks in the ADF model and are not executed by the Oracle BI Server.

You can specify the name of the SQL Bypass database in the connection pool for the ADF Business Component data source. The SQL Bypass database must be a physical database in the Physical layer of the repository. The database object for the SQL Bypass database must have a valid connection pool, with connection information that points to the same database that is being used by the JDBC Data source defined in the Oracle WebLogic Server that runs the ADF application.

The SQL Bypass database does not need to have any tables under it. After a valid database name is supplied, the SQL Bypass feature is enabled for all queries against that ADF database.

Setting Up ADF Business Component Data Sources

This section explains how to configure your ADF Business Components for use with Oracle Business Intelligence.

See "System Requirements and Certification" for information about supported versions.

This section contains the following topics:

Creating a WebLogic Domain
Deploying OBIEEBroker as a Shared Library in Oracle WebLogic Server
Deploying the Application EAR File to Oracle WebLogic Server from JDeveloper
Setting Up a JDBC Data Source in the WebLogic Server
Setting the Logging Level for the Deployed Application in Oracle WebLogic Server

Creating a WebLogic Domain

Create a WebLogic Domain for your ADF Business Components that supports WebLogic Server, Oracle Application Core (Webapp), and Oracle JRF.

To create a WebLogic domain that supports the required components:

Start the WebLogic Configuration Wizard. For example, on Windows, run MW_HOME\wlserver_10.3\common\bin\config.cmd.
Select Create a new WebLogic domain and click Next.
On the Select Domain Source screen, ensure that Basic WebLogic Server Domain, Oracle JRF, and Oracle Application Core (Webapp) are selected.
Follow the remaining steps in the wizard, providing values appropriate for your environment.
Click Create on the Configuration Summary screen to create the domain.

You can start and stop the Oracle WebLogic Server for this domain using command-line scripts in the domain directory. For example, on Windows, use the following:

MW_HOME\user_projects\domains\domain_name\bin\startWebLogic.cmd
MW_HOME\user_projects\domains\domain_name\bin\stopWebLogic.cmd

Deploying OBIEEBroker as a Shared Library in Oracle WebLogic Server

The OBIEEBroker shared library is installed as part of your Oracle Business Intelligence installation. You need to deploy the OBIEEBroker library as a shared library in Oracle WebLogic Server by installing it (making its physical file or directory known to Oracle WebLogic Server) and starting it. After the library has been installed and started, other deployed modules can reference the library.

To deploy OBIEEBroker as a shared library in Oracle WebLogic Server:

Ensure that Oracle WebLogic Server is running. If it is not running, start it. For example, on Windows, run MW_HOME\user_projects\domains\your_domain\bin\startWebLogic.cmd.
Open the WebLogic Server Administration Console. For example, if your Oracle WebLogic Server is running locally on port 7001, go to http://localhost:7001/console.
Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.
In the Change Center, click Lock & Edit.
On the Home Page, in the left pane, click Deployments.
In the right pane, click Install.
Using the Install Application Assistant, locate the OBIEEBroker EAR file. You can find this file at:
```
ORACLE_HOME\bifoundation\javahost\lib\obisintegration\adf\
oracle.bi.integration.adf.ear
```
Click Next.
Select Install this deployment as a library and click Next.
Select the servers and/or clusters to which you want to deploy the OBIEEBroker library. Make sure to select all servers and clusters to which modules or applications that reference the library are deployed.
Click Next.
You can optionally update settings about the deployment. Typically, the default values are adequate. Click Help for more information.
Click Next, then click Finish to complete the installation.
In the Change Center, click Activate Changes.

Deploying the Application EAR File to Oracle WebLogic Server from JDeveloper

Follow the steps in this section to deploy the application EAR file to Oracle WebLogic Server from JDeveloper. Before beginning this procedure, ensure that the following conditions are true:

You have an ADF Model project that contains AMs and VOs that will be exposed to Oracle Business Intelligence.
You have deployed OBIEEBroker as a shared library in Oracle WebLogic Server. See "Deploying OBIEEBroker as a Shared Library in Oracle WebLogic Server" for more information.
Oracle WebLogic Server is running.

To deploy the application EAR file to Oracle WebLogic Server from JDeveloper:

Start JDeveloper. For example, on Windows, run MW_HOME\jdeveloper\jdev\bin\jdev.exe.
Select File, then select Open to open the project that contains your ADF Business Components in JDeveloper. If prompted, allow JDeveloper to migrate the project to the latest version.
Create a new Application Module configuration, as follows:
1. In the Model project, double click the application module, then click the Configurations tab for that application module.
2. Create a new configuration with the following characteristics:
  - Select JDBC DataSource for Connection Type.
  - Keep the default DataSource Name (for example, java:comp/env/jdbc/ApplicationDBDS).
  When you set up the JDBC data source in Oracle WebLogic Server in a later step, you use part of this DataSource Name as the JNDI name required by Oracle WebLogic Server. The JNDI name is the DataSource Name without the java:comp/env context prefix (for example, jdbc/ApplicationDBDS).
Create a Business Component Archive deployment provide, as follows:
1. In the Projects window, right-click the Model project and choose New.
2. Select Deployment Profiles under General in the left pane, then choose Business Components Archive in the right pane and click OK.
3. Provide a name for the deployment profile (for example, MyApplication_Archive) and click OK.
4. On the Deployment page, click OK.
In the Projects window, right-click the Model project and select Deploy > your_deployment_profile_name > Deploy, or use the deployment wizard by selecting Deploy to File.

After the project has been deployed, two jar files are created in the deploy directory for the Model project (for example, MyApplication_Archive_Common.jar and MyApplication_Archive_MiddleTier.jar).
Create a new Web Project for the application, as follows:
1. Right-click the global application and select New Project.
2. Select Projects from the left pane, then select Web Project from the right pane.
3. Provide a project name (for example, OBIEEBroker).
4. Click Next until you reach the Web Project Profile page.
5. Modify the Java EE Context Root to a name that better represents your application (for example, MyApplication).
  
  This value determines the URL that you use to connect to the application from Oracle Business Intelligence (for example, http://localhost:7001/MyApplication/obieebroker).
Edit the Profile Dependencies of the WAR deployment, as follows:
1. Right-click the Web Project you just created (for example, OBIEEBroker) and select Project Properties.
2. From the left pane, select Deployment. Then, open the WAR File deployment profile on the right pane.
3. Select Profile Dependencies from the left pane. Then, on the right pane, select the Common and MiddleTier deployment profiles of your Model project.
  
  Following this step ensures that the Business Component Archives for the Model project are included in the WAR file.
Expand the Web Project and open web.xml. Then, go to the source view of the file.

In the web.xml source, replace the content within the <web-app> element with the following:

<filter>
  <filter-name>ServletADFFilter</filter-name>
  <filter-class>oracle.adf.share.http.ServletADFFilter</filter-class>
</filter>
<filter-mapping>
  <filter-name>ServletADFFilter</filter-name>
  <servlet-name>OBIEEBroker</servlet-name>
  <dispatcher>FORWARD</dispatcher>
  <dispatcher>REQUEST</dispatcher>
</filter-mapping>
<servlet>
  <servlet-name>OBIEEBroker</servlet-name>
  <servlet-class>oracle.bi.integration.adf.v11g.obieebroker.OBIEEBroker
  </servlet-class>
</servlet>
<servlet-mapping>
  <servlet-name>OBIEEBroker</servlet-name>
  <url-pattern>/obieebroker</url-pattern>
</servlet-mapping>

Following this step ensures that the OBIEEBroker servlet will be used to access your application from Oracle Business Intelligence.

Create an EAR deployment profile for the application, as follows:
1. Right-click the global application and select Application Properties.
2. From the left pane, select Deployment, then click New on the right pane to create a new deployment profile.
3. For Archive Type, select EAR File. Then, provide a name for the deployment profile (for example, MyApplication).
  
  The deployment profile name is used as the name displayed in the list of deployments in Oracle WebLogic Server.
4. From the left pane, select Application Assembly. Then, on the right pane, select the webapp deployment profile of your Web Project.
  
  Following this step ensures that the WAR file from your Web Project is included in the EAR file.
Under Application Resources, select Descriptors > META-INF > weblogic-application.xml.
On the left, select the Libraries tab.
Create two new Shared Library References, as follows:
- Create the first Shared Library Reference with the following characteristics:
  - Library Name: oracle.bi.integration.adf
  - Implementation Version: 11.1.1.2.0
- Create the second Shared Library Reference with the following characteristics:
  - Library Name: oracle.applcore.model
  - Implementation Version: 11.1.1.0.0
These two Shared Library References create the following entries in the weblogic-application.xml file for the application:
```
<library-ref>
  <library-name>oracle.bi.integration.adf</library-name>
  <implementation-version>11.1.1.2.0</implementation-version>
</library-ref>
<library-ref>
  <library-name>oracle.applcore.model</library-name>
  <implementation-version>11.1.1.0.0</implementation-version>
</library-ref>
```
Deploy the EAR file to Oracle WebLogic Server by right-clicking the global application, then selecting Deploy > EAR_deployment_profile_name. From the dialog that appears, select Deploy to Application Server and then follow the instructions in the wizard.
To verify that the application has been deployed, log in to the WebLogic Server Administration Console and click Deployments under Your Deployed Resources. Verify that your application appears in the list (for example, obieebroker_app_name).

Setting Up a JDBC Data Source in the WebLogic Server

You must set up a JDBC data source in Oracle WebLogic Server for your application.

To set up a JDBC data source in Oracle WebLogic Server:

Ensure that Oracle WebLogic Server is running. If it is not running, start it. For example, on Windows, run MW_HOME\user_projects\domains\your_domain\bin\startWebLogic.cmd.
Open the WebLogic Server Administration Console. For example, if your Oracle WebLogic Server is running locally on port 7001, go to http://localhost:7001/console.
Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.
On the Home Page, select JDBC, then select Data Sources.
Click New.
Provide information for your data source. For Name and JNDI Name, provide the DataSource Name you specified in the Application Module configuration for the application, without the java:comp/env context prefix (for example, jdbc/ApplicationDBDS). In addition, make sure to select the target on which you want to deploy the data source before exiting the wizard.
Click Finish when you are done providing JDBC data source settings.

Setting the Logging Level for the Deployed Application in Oracle WebLogic Server

The log file for the server to which your application is deployed (server_name-diagnostic.log) records information about your deployed application. You can find this file in the server-specific directory within your domain. For example, on Windows, the log file for the AdminServer is located in:

MW_HOME\user_projects\domains\your_domain\servers\AdminServer\logs

To set the logging level for your deployed application:

Open the Oracle WebLogic Server file logging.xml for editing. You can find this file in:
```
MW_HOME\user_projects\domains\your_domain\config\fmwconfig\servers\server_name
```
Within the <loggers> element, add the following child elements:
```
<logger name="oracle.bi.integration.adf" level="LOG_LEVEL"/>
<logger name="oracle.bi.integration.adf.v11g.obieebroker" level="LOG_LEVEL"/>
```
Log levels include SEVERE, WARNING, INFO, CONFIG, FINE, FINER, and FINEST. Refer to the Oracle WebLogic Server documentation for information about logger levels.
Save and close the file.
Restart Oracle WebLogic Server.

Importing Metadata from ADF Business Component Data Sources

You must complete the steps in "Setting Up ADF Business Component Data Sources" before you can import metadata from ADF Business Component data sources.

To import metadata from an ADF Business Component data source:

In the Administration Tool, select File, then select Import Metadata. The Import Metadata Wizard appears.

Note:
If you have already defined an existing ADF Business Component data source and connection pool, you can right-click the connection pool in the Physical layer and select Import Metadata. The Import Metadata Wizard appears with the information on the Select Data Source screen pre-filled.

Figure 5-1 shows the Import Metadata Wizard.

Figure 5-1 Import Metadata Wizard: ADF Business Component Data Source

Description of "Figure 5-1 Import Metadata Wizard: ADF Business Component Data Source"
In the Select Data Source screen, select OracleADF_HTTP for Connection Type. Then, provide the following values:
- Select New Connection, or select Existing Connection if you already have a connection pool for this data source. Click Browse to locate and select an existing connection pool. If you select Existing Connection, you do not provide information for Data Source, AppModule Definition, AppModule Config, or URL, and the User Name and Password fields are prefilled.
- Keep the Data Source field blank to use the default JDBC data source configured in the application module. You only need to provide data source information (a JDBC data source name, such as jdbc/nWindORA05) if you want to use a different data source than the one set up in the application module.
- For AppModule Definition, provide the fully qualified Java package name of the Root Application Module to which you want to connect, such as oracle.apps.fii.receivables.model.RootAppModule, or snowflakesales.SnowflakeSalesApp.
- For AppModule Config, provide the name of the configuration you want to use in your connection, such as RootAppModuleShared or SnowflakeSalesAppLocal. See step 3 of "Deploying the Application EAR File to Oracle WebLogic Server from JDeveloper" for more information.
- For URL, provide the URL to the Oracle Business Intelligence broker servlet, in the format:
```
http://host:port/APP_DEPLOYMENT_NAME/obieebroker
```
  For example:
```
http://localhost:7001/MyApp/obieebroker
```
  The URL is case-sensitive.
- For User Name and Password, provide a valid user name and password for the Oracle ADF application. The user name and password must be set up and authenticated in the Oracle WebLogic Server security realm.
When you have finished providing information in the Select Data Source screen, click Next. The Select Metadata Objects screen appears.
Select the objects you want to import in the Available list and move them to the Selected list, using the > (Import selected) or >> (Import all) buttons. You can also move objects from the Selected list back to the Available list, using the < (Remove selected) and << (Remove all) buttons.

To search for a particular item, enter a keyword in the Find box and then click Find Down or Find Up.

Select Show complete structure to view all objects, including those that have already been imported. Deselecting this option shows only the objects that are available for import. When this option is selected, objects that have already been imported appear grayed out.

When you move the items from the Available list to the Selected list, the Connection Pool dialog opens, showing the values that you provided in the Select Data Source screen of the Import Metadata Wizard. Optionally, click the Miscellaneous tab and provide the name of a SQL Bypass database in the SQL Bypass Database field. Then, click OK. If you do not want to specify a SQL Bypass database, click Cancel.

See "About Specifying a SQL Bypass Database" for more information.
Click Finish.
To validate that your import was successful, expand the database object for the ADF Business Component data source in the Physical layer. Then, right-click a physical table and click View Data. If the appropriate data is displayed, the import completed successfully.

Enabling the Ability to Pass Custom Parameters to the ADF Application

Some ADF applications have custom properties defined on the ApplicationModule, such as EFFECTIVE_DATE or TREE_VERSION. You can include these custom properties in your application queries, and the Oracle BI Server will pass them to the ADF application. To enable this feature, you must register the custom properties as a static repository variable using the Administration Tool.

You cannot use this feature to pass any custom property to your ADF application. Only certain custom properties, like EFFECTIVE_DATE and TREE_VERSION, are supported.

To register custom properties:

Open your repository in the Administration Tool.
Select Manage, then select Variables.
Select Action > New > Repository > Variable.
For Name, enter ADF_PARAM_LIST. Do not enter the name of the custom property as the name of the variable.
Ensure that the Type is Static.
For Default Initializer, enter the name or names of the custom properties as a character string. If you have multiple custom properties, include them as a comma-delimited list. For example:

'PARAM_EFFECTIVE_DATE'

'PARAM_EFFECTIVE_DATE, ApplicationIdBind, KeyFlexfieldCodeBind'
Click OK.
Save and close the repository.

After you register the custom properties as a repository variable, you can include these variables in queries. For example:

set variable PARAM_EFFECTIVE_DATE=2001-01-01 : SELECT c1 FROM t1;

set variable ApplicationIdBind = '0', KeyFlexfieldCodeBind = 'KFF1' :
select_physical ApplicationID, KeyFlexfieldCode, DataSecurityObjectName,
SegmentLabelCode from adfdb..."AppModule.KFFHierFilterVO1";

Note that when you are including a custom property of type PARAM_EFFECTIVE_DATE, the date format for the property value must be in the format yyyy-mm-dd.