This chapter describes how to create and add the Oracle Business Intelligence Logical SQL View Object to bind Oracle BI EE data into an ADF application's components. It describes how to create the required BI JDBC data source and JDBC connection, configure the BI JDBC data source for impersonation, and extend the Oracle Business Intelligence SQL view object.
This chapter includes the following sections:
Section 4.1, "Introduction to the Oracle BI EE Logical SQL View Object"
Section 4.2, "Oracle Business Intelligence Logical SQL View Object Use Cases"
Section 4.3, "Creating an Oracle Business Intelligence Logical SQL View Object"
Section 4.5, "Extending the Oracle Business Intelligence Logical SQL View Object"
The Business Intelligence Logical SQL View Object allows you to access the Oracle Business Intelligence Server to create ADF applications that bind native ADF components to Oracle BI EE data.
The Oracle Business Intelligence view object exposes the items in the select clause as attributes. After the Business Intelligence Logical View Object's setup is complete, you can use the Oracle Business Intelligence Logical SQL View Object wherever the other view objects can be used in an ADF application. For example, you could create a view link, which allows you to join the attributes of the Oracle Business Intelligence view object to attributes of another view object that returns data from the operational schemas. You can easily show the data from the Oracle Business Intelligence view object, or joined data created using view links, in any ADF rich/faces component by using a data control that JDeveloper automatically generates.
Including business intelligence objects such as analyses, dashboards, and Scorecard components in ADF pages allows you to access the Oracle BI Presentation Catalog, browse the catalog, and choose Oracle BI EE objects to include in ADF pages. For more information about this method of adding business intelligence data to ADF pages, see Chapter 1, "Embedding Business Intelligence Objects in ADF Applications".
The following use cases are designed to help you understand how you can use the Oracle Business Intelligence Logical SQL View Object to create robust ADF pages. Each use case consists of a scenario and general instructions.
This use case describes how to display data returned by a logical SQL statement in an ADF face or rich user interface component. This use case assumes that you are interested in displaying data for Revenue by Region and District in a read-only rich table format.
Tip:
This use case describes one way of displaying data in a rich table. Normally, it is easier to embed an analysis into an ADF page.
To display data from logical SQL in an ADF-rich user interface component:
Create a JDBC connection to an Oracle BI EE Server that contains the data you want to display in the ADF page. For more information, see "Creating a BI JDBC Connection".
Create an application module with a standard JDBC connection. For more information, see "Initializing the ADF Project".
Create an Oracle Business Intelligence view object, pointing to the JDBC connection and using the following logical SQL. Note that Region, District, and Dollars are added automatically as the attributes of the view object.
"Select Markets.Region, Markets.District, Sales Measures.Dollars from Paint"
Navigate to the Application Model page of the Create View Object wizard and add an instance of the view object that you created to an application module. In this use case, assume that the application module is named "BIVO_AppModule."
JDeveloper automatically creates a BIVO_AppModuleDataControl. Under this data control your view object is listed as a collection with all its attributes.
Drag and drop the view object collection from the data control to the appropriate place on a JSPX page. When the shortcut menu appears, select an appropriate ADF Rich UI component to use for displaying the view object.
Select a read-only Rich table for the display.
This use case is an extension of the "Displaying Data from Logical SQL in an ADF-Rich User Interface Component" use case. In this use case, however, you want to display data for Revenue by Region and District from logical SQL, but only when Revenue is greater than a number inputted by the user. You can accomplish this by passing in a runtime parameter.
To display data from logical SQL and passing in a runtime parameter:
Create a JDBC connection to an Oracle BI EE Server that contains the data you want to display in the ADF page. For more information, see "Creating a BI JDBC Connection".
Create an application module with a standard JDBC connection. For more information, see "Initializing the ADF Project".
Create an Oracle Business Intelligence view object, pointing to the JDBC connection and using the following logical SQL. Note that Region, District, and Dollars are added automatically as the attributes of the view object.
"Select Markets.Region, Markets.District, Sales Measures.Dollars from Paint"
Add a bind variable mapped to the Dollars attribute.
Navigate to the Application Model page of the Create View Object wizard and add an instance of the view object that you created to an application module. In this use case, assume that the application module is named "BIVO_AppModule."
JDeveloper automatically creates a BIVO_AppModuleDataControl for you. Your view object is listed as a collection with all its attributes under this data control. The DataControl also lists the "ExecuteWithParam" operation that takes the Dollars parameter.
Drag and drop the view object collection from the data control to the appropriate place on a JSPX page. When the shortcut menu appears, select an appropriate ADF Rich UI component to use for displaying the view object.
Select a read-only Rich Table for the display.
Create an appropriate input component to prompt the user for the threshold Revenue value.
Invoke the ExecuteWithParam operation to pass in the threshold revenue value when the user submits the form and refresh the Rich Table that you created earlier.
This use case describes how to display an ADF Rich Table that contains the following columns:
Customer
Accounts Payable Days Outstanding
Lifetime Value
Customer Rank by YTD Revenue
The "Accounts Payable Days Outstanding" value comes from an online transaction processing (OLTP) system and "Lifetime Value" and "Customer Rank" come from the Oracle Business Intelligence Server. "Customer" is common to both systems.
To display data from the online transaction processing schema and logical SQL in an ADF-rich component:
Create a JDBC connection to an Oracle BI EE Server that contains the data you want to display in the ADF page. For more information, see "Creating a BI JDBC Connection".
Create an application module with a standard JDBC connection. For more information, see "Initializing the ADF Project".
Create a regular view object within the OLTP system and uses the appropriate SQL/Entity Objects to expose the Customer and Accounts Payable Days Outstanding attributes. In this use case, assume that the view object is named OLTP_VO.
Using the JDBC connection to the Oracle BI EE Server that you created in Step 1 of this procedure, create an Oracle Business Intelligence view object, using the following logical SQL:
"Select Customer, Lifetime_Value, Rank(YTD Revenue) from CustomerSubjectArea"
Customer, Lifetime Value and Customer Rank by YTD Revenue is automatically added as the attributes of this view object. In this use case, assume that the view is named BI_VO.
Create a view link with the Customer attribute of the OLTP_VO as the source attribute, and the Customer attribute of the BI_VO as the destination attribute.
Navigate to the Java step of the OLTP_VO and generate the Java classes for the view object. Also add attributes to the OLTP_VO for "Lifetime_Value" and "RankByRevenue".
Edit the Java class for the OLTP_VO (OLTP_VOViewImpl). There is an accessor method for the each attribute added in the previous step. Edit the implementation of the accessors to return the corresponding values from the Oracle Business Intelligence view object. The business intelligence view object is available to the OLTP_VO view object through the getViewObject method. For example, the code for the Lifetime_Value accessor looks similar to the following:
getViewObject().getCurrentRow().getAttribute("Lifetime_Value")
Confirm that OLTP_VO is a part of the ApplicationModule. The OLTP_VO has a collection of all of the attributes (Customer, Accounts Payable Days Outstanding, Lifetime Value", Customer Rank by YTD Revenue).
Drag and drop the view object collection from the data control to the appropriate place on a JSPX page. When the shortcut menu appears, select the ADF Rich UI component to use for displaying the view object.
Select a read-only Rich Table for the display.
In this use case, you create the master-detail relationship in the same way that you create a master-detail relationship between two OLTP view objects. The instructions provided in the following procedure are high-level instructions. You can find complete information about creating a master-detail relationship in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
To create a master-detail relationship between an Oracle Business Intelligence view object and an online transaction processing view object:
Create an OLTP View Object.
Create a Oracle Business Intelligence view object.
Create a View Link between the OLTP View object and the Oracle Business Intelligence view object.
Create an appropriate ADF view object for the source and the destination data controls corresponding to the source and destination view objects.
Before you can create the Oracle BI Enterprise Edition Logical SQL View Object, you must perform two preparatory steps: creating a BI JDBC connection and initializing the ADF project with a JDBC connection.
After creating and saving the Oracle BI Enterprise Edition Logical SQL View Object, you can modify the view object behavior so that it interacts in a specific way with the ADF application's other view objects.
Use the following procedures to prepare for and create an Oracle Business Intelligence Logical SQL View Object that you can use to bind Oracle BI EE data to ADF components.
Oracle JDeveloper offers the following Oracle BI EE extensions:
Business Intelligence ADF Task Flow
Business Intelligence ADF View Regions
Business Intelligence Composer
Business Intelligence Logical SQL View Objects
Business Intelligence Soap Connection
Use this procedure if you are not sure whether you have the required Oracle JDeveloper 11g extensions and Oracle BI EE extensions.
To check for installed extensions:
In JDeveloper, click the Help menu and click About. The About Oracle JDeveloper 11g dialog displays where you can confirm the release number.
Click the Extensions tab to view a list of the installed extensions. To sort the list, click either the Name or Identifier column heading.
Note that all Oracle BI EE extensions contain the "bi-" prefix. If no extensions begin with the "bi-" prefix, then you do not have the required extensions.
Use JDeveloper's Check for Updates wizard to find and install the required Oracle BI EE extensions.
To install the Oracle BI EE extensions:
From the JDeveloper help menu, select Check for Updates.
Follow the steps in the wizard to locate, download, and install the Oracle BI EE extensions.
For a list of the available Oracle BI EE extensions, see "Checking for Installed JDeveloper Extensions".
You must create a BI JDBC connection to the Oracle BI Server. It is through this connection that the view object passes the logical SQL statement to the Oracle BI Server, and through which the Oracle BI Server passes data back to the view object in the ADF application.
You can create the BI JDBC connection to be available for any ADF application, or in the context of a specific ADF application.
For information about how to create a BI JDBC data source, see "Creating a BI JDBC Data Source".
To create a BI JDBC connection:
Determine if you want to create the BI JDBC connection for use in all ADF application or for the current ADF application. Use the following steps to create the connection in an ADF application.
Open an ADF project and navigate to the Application Navigator.
Go to the Application Resources panel.
Right-click the Connections node and select New Connection.
Select Database. The Create Database Connection dialog is displayed.
Confirm that Application Resources is selected in the Create Connection In field.
Use the following steps to create a connection that is available for all ADF applications.
Go to the Resource Palette and click the New icon.
Select New Connection.
Select Database. The Create Database Connection dialog is displayed.
Confirm that IDE Connections is selected in the Create Connection In field.
In the Connection Type field, select BI (JDBC) and enter the appropriate connection information. For more information, press F1 or click Help from within the Create Database Connection dialog.
Click Test Connection.
If the test succeeds, a "Success!" message displays in the status text area.
If the test does not succeed, an error displays. Change the information that you entered in the dialog as needed to correct the error, or check the error content to determine other possible sources of the error.
The new connection name appears under the appropriate node in the Application Navigator or the Resource Palette.
If the Oracle Business Intelligence view object that you are creating is the first business component added to the ADF application, you must initialize the project with a standard JDBC connection. You cannot initialize the project with a BI JDBC connection. The BI JDBC connection is specified later when you create the Oracle Business Intelligence view object.
Note:
You do not need to initialize a Business Components project. Business Components projects are initialized at creation time.
To initialize the ADF project:
Open an ADF project, click File, and then select New. The New Gallery dialog is displayed.
Select the All Technologies tab and in the Categories area, select Business Tier and then select Business Intelligence.
Confirm that View Object (Business Intelligence Logical SQL View Object) is highlighted in the Items area and click OK. The Initialize Business Components Project dialog is displayed.
Specify the database connection and SQL flavor your components use when creating an initial business components project, or when setting project properties. For more information, press F1 or click Help from within the Create Database Connection dialog.
Click OK. The Create View Object wizard is displayed.
Use the following procedure to create and save the Oracle Business Intelligence Logical SQL view object using the Create View Object wizard. For more information about a specific dialog within the Create View Object wizard, press F1 or click Help from within the dialog upon which you are working.
After you have created and saved the view object, you can then access the saved view object and add the following items to it:
alternate keys
client interface specifications
client row interface specifications
view accessors
user interface hints
To create the view object:
Open an ADF project, click File, and then select New. The New Gallery dialog is displayed.
Note:
If you have not yet initialized the ADF project, JDeveloper requires you to do so before you can create an Oracle Business Intelligence view object. For more information, see "Initializing the ADF Project".
Select the All Technologies tab and in the Categories area, select Business Tier and then select Business Intelligence, confirm that view object (Business Intelligence Logical SQL View Object) is highlighted in the Items area. Click OK.
In the Name screen, enter a name for the view object and specify the package into which you want to place the view object. You can use the Property Set field to select an existing property set for the view object. Click Next. The Connection screen displays.
Note:
After you save the view object, you cannot modify its name.
Select the BI JDBC connection that runs the logical SQL and retrieve the business intelligence data. The JDBC connection that was initialized for the project defaults to this connection, but you must choose a BI JDBC connection from the list of available connections or click the Add button to create a BI JDBC connection for the project.
Click Next. The Logical SQL screen displays.
Type the logical SQL to execute against the Oracle BI EE Server. You can use most of the logical SQL select statements that are supported by the Oracle BI EE Presentation Server. The items in the select list are automatically available as attributes of the Oracle Business Intelligence view object. For example, if the logical SQL is "Select Markets.Region saw_0, Measures.Dollars saw_1 from Paint", the corresponding view object has two attributes called "region" and "dollars."
Note:
The Insert, Update, and Delete logical SQL statements are not supported by the Oracle Business Intelligence view object. For more information about and a list of the logical SQL statements that you can use with an Oracle Business Intelligence view object, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.
Click Next. The Bind Variables screen displays.
Add or create any necessary variables. Any bind variables that you create are available as parameters on the data controls that include this view object. For more information about bind variables, press F1 or click Help from within the dialog.
Click Next. The Java screen displays.
Generate any necessary Java files to extend or customize the functionality provided by the Oracle Business Intelligence view object and click Next. The Application Model screen appears.
For more information about generating custom Java classes for the view object, press F1 or click Help from within the dialog.
Add an instance of the Oracle Business Intelligence view object to a new or saved Application Module. Note that if you do not add an application module, the data control for the view object is not automatically generated.
Click Next. The Summary screen displays.
Review the summary and confirm your selections. After you have confirmed that the selections are correct, click Finish and JDeveloper creates the Oracle Business Intelligence view object.
Use the following procedure to modify the view object's behavior and define any additional behaviors, such as client interface specifications, client row interface specifications, view accessors, and user interface hints. The behaviors that you specify determines how the Oracle Business Intelligence Logical SQL View Object interacts with the application's other ADF view objects.
For more information about a specific dialog within the View Object Editor, press F1 or click Help from within the editor.
To modify the view object's behavior:
To locate and open the view object, click File and then click Open. The Open dialog is displayed, where you can browse to and select the view object. The Overview Editor is displayed.
Click the Connection node to change the view object's BI JDBC connection information, or add or switch to another BI JDBC connection.
Click the Logical SQL node to modify the view object's logical SQL and to add named bind variables to the SQL query.
Click the Java node to generate Java files for the view object and its view row set. The generated files allow the client to call methods on the object remotely, generates a service data object that wraps a view row, and allows the view object to be exposed in a web service interface.
Click the View Accessors node to define the view accessors to point from a base entity object attribute or view object attribute to a source view row set.
Click the List UI Hints node to specify a default list of values (LOV) hint when you want the LOV-enabled attributes of other view objects to inherit the list UI hints from the current view object.
A BI JDBC data source must exist before you can build a connection to it. It is this data source and connection that allow you to access the Oracle BI Server and pass the logical SQL statement to request data for the view object in the ADF application.
For more information about creating a BI JDBC connection, see "Creating an Oracle Business Intelligence Logical SQL View Object".
Use this procedure to access the Oracle WebLogic Server Administration Console to create a BI JDBC data source. Before you begin, ensure that Oracle WebLogic is started.
To create a data source in WebLogic:
Access and log in to the WebLogic Server Administration Console for the WebLogic domain in which Oracle BI EE is deployed.
In the Domain Structure tree in the left pane, expand Services and choose Data Sources.
Click New. You may need to click Lock & Edit first to enable the New button.
The Create a New JDBC Data Source wizard displays.
On the JDBC Data Source Properties page, follow these steps:
For Name, enter the database name of the Oracle Business Intelligence view object in the JDeveloper application. For example, ApplicationOBIS.
For JNDI Name, enter the same value you provided for Name. Note the following JNDI name format: jdbc/name from Name fieldDS. For example, jdbc/ApplicationOBISDS.
Set the Database Type to Other.
Click Next.
Set the Database Driver to Other.
Click Next.
On the Transaction Options page, select Supports Global Transactions and select One-Phase Commit. Click Next.
On the Connection Properties page, follow these steps:
For Database Name, enter any text string. Note that what you specify in this field is not used for Oracle BI EE.
For Host Name, enter the name of the Oracle BI server. For example, sr7619.us.mycompany.com.
For Port, enter 9703.
For Database User Name, enter a valid Oracle BI EE user name that has been assigned the oracle.bi.server.impersonateUser privilege.
For Password and Confirm Password enter and then re-enter the password corresponding to the user name that you specified. Click Next.
On the Test Database Connection page, follow these steps:
For Driver Class Name, enter oracle.bi.jdbc.AnaJdbcDriver.
For URL, enter jdbc:oraclebi://Host Name:9703/. For example, jdbc:oraclebi://sr7619.us.mycompany.com:9703/.
For Properties, enter user=user from Database User Name field (from above). For example, user=BIImpersonatorUser.
For Test Table Name, enter the following: SQL {call NQSGetSQLCatalogs()}
Click Test Configuration. If the test fails, go back and check your settings. If the test succeeds, click Next.
Select the server or cluster where you want to deploy the data source. Note that you must perform this step before completing the data source configuration.
Click Finish.
Click Activate Changes.
Use this procedure to add a connection string property to the BI JDBC data source. Peforming this step enables impersonation. You can skip this step if you do not want the connection to use the currently logged in user.
To configure the BI JDBC data source for impersonation:
Access and WebLogic Server Administration Console and locate the BI JDBC data source to configure for impersonation. The Settings page displays.
Click the Connection Pool tab. Note that you may need to click Lock & Edit before you can configure the data source.
In the Properties field, create a line and add the following: IMPERSONATE=
In the Initial Capacity field, enter 1.
Click Save.
If you want to run an Oracle Business Intelligence logical SQL view object from a standalone WebLogic Server, then you must deploy the view object to the standalone WebLogic Server.
To extend the Oracle Business Intelligence logical SQL view object, you must set up a WebLogic domain that supports BI JDBC, prepare the view object, and deploy the application.
Use the following procedure to set up a WebLogic domain that supports BI JDBC.
For information about using the Fusion Middleware Configuration Wizard to create or extend a WebLogic domain, see Oracle Fusion Middleware Creating Domains Using the Configuration Wizard.
To create a WebLogic domain:
Access the configuration wizard by clicking Start, then selecting Programs, then selecting Oracle Fusion Middleware, then selecting WebLogic Server 11gR1, then selecting Tools, and then selecting Configuration Wizard. The Fusion Middleware Configuration Wizard displays.
Use the wizard to create a WebLogic domain that supports BI JDBC. Table 4-1 lists the Fusion Middleware Configuration Wizard pages and the corresponding selections you need to make or values you need to specify to correctly set up the WebLogic domain for BI JDBC.
Table 4-1 WebLogic Domain Settings
| Wizard Page | Required Selection of Value |
|---|---|
|
Select Domain Source |
Select the following: Oracle BI JDBC - <your version number> (oracle_common) Select all Oracle JRF options, for example:
|
|
Select Optional Configuration |
Select Deployment and Services and then use Next to confirm that the required applications are selected. |
|
Target Deployments to Clusters or Servers |
Confirm that the following applications are selected:
|
|
Creating Domain |
Select Start Admin Server. Or, after you have completed the WebLogic server set up, you can start the server by running |
Use this procedure to create a connection to the target application server, create the deployment profile, and deploy the application to a standalone WebLogic server. For more information about a specific page used in the following tasks, click the Help button on that page.
Use the following task to create a connection to the target application server.
To create a connection to the target application server:
In JDeveloper, display the Application Server tab by clicking the View menu and selecting Application Server Navigator. The Application Server tab displays.
In the Application Server tab, right-click Application Servers and select New Application Server. The Create Application Server Connection Wizard - Usage Page displays.
On the Usage Page, select Standalone Server and click Next.
Complete the pages on the wizard to set up, test, and save the application server. For information about a specific page, click the page's Help button.
The application server displays in the Application Server tab.
Use the following task to create the deployment profile for the EAR file.
To create the deployment profile for the EAR file:
Access the Create Deployment Profile page by clicking the Application menu and selecting Application Properties. The Application Properties page displays.
From the left pane of the Application Properties page, select Deployment. The Deployment profiles and options display.
Click New. The Create Deployment Profile page displays.
Accept the Deployment Profile Name and click OK. The General page displays.
Accept the General page values and click OK. The Deployment profiles, including the deployment profile that you just created, and options display.
In the Deployment page, define the deployment profiles as follows:
Select the Auto Generate and Synchronize weblogic-jdbc.xml Descriptors During Deployment option.
Deselect the Application Policies and Credentials options.
Select the Users and Groups option.
From the Deployment Profiles list, select the deployment profile that you created and click Edit. The Edit EAR Deployment Profile Properties page displays.
From the left pane, select Application Assembly. The Application Assembly page displays.
In the Java EE Modules area, locate the Model.jpr module and confirm that the items listed within this module are correct. Click OK. The Application Properties page displays.
Click OK to exit the Application Properties page.