Skip Headers
Oracle® Fusion Middleware Developer's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)
E10545-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
 
Next
Next
 

2 Using the Oracle BI EE Logical SQL View Object

This chapter explains how you use the Oracle BI EE Logical SQL view object to embed business intelligence data into an ADF application's components.

This chapter includes the following sections:

2.1 Introduction to the Oracle BI EE 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 the 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 EE Presentation Services 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".

2.2 Oracle Business Intelligence Logical SQL View Object Use Cases

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.

2.2.1 Displaying Data from Logical SQL in an ADF-Rich User Interface Component

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:

  1. 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".

  2. Create an application module with a standard JDBC connection. For more information, see "Initializing the ADF Project".

  3. Create an Oracle Business Intelligence view object, pointing to the JDBC connection and using the following logical SQL. Note that Region, District, and Dollars will be added automatically as the attributes of the View Object.

    "Select Markets.Region, Markets.District, Sales Measures.Dollars from Paint"

  4. 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.

  5. Drag and drop the view object collection from the data control to the appropriate place on a JSPX page. When the pop-up menu appears, select an appropriate ADF Rich UI component to use for displaying the view object.

  6. Select a read-only Rich table for the display.

2.2.2 Displaying Data from Logical SQL and Passing in a Runtime Parameter

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 run-time parameter.

To display data from logical SQL and passing in a runtime parameter:

  1. 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".

  2. Create an application module with a standard JDBC connection. For more information, see "Initializing the ADF Project".

  3. Create an Oracle Business Intelligence view object, pointing to the JDBC connection and using the following logical SQL. Note that Region, District, and Dollars will be added automatically as the attributes of the View Object.

    "Select Markets.Region, Markets.District, Sales Measures.Dollars from Paint"

  4. Add a bind variable mapped to the Dollars attribute.

  5. 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 will automatically create a BIVO_AppModuleDataControl for you. Your View Object will be listed as a collection with all its attributes under this data control. The DataControl will also list the "ExecuteWithParam" operation that will take the Dollars parameter.

  6. Drag and drop the view object collection from the data control to the appropriate place on a JSPX page. When the pop-up menu appears, select an appropriate ADF Rich UI component to use for displaying the view object.

  7. Select a read-only Rich Table for the display.

  8. Create an appropriate input component to prompt the user for the threshold Revenue value.

  9. 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.

2.2.3 Displaying Data from the Online Transaction Processing Schema and Logical SQL Together in an ADF-Rich Component

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 together in an ADF-rich component:

  1. 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".

  2. Create an application module with a standard JDBC connection. For more information, see "Initializing the ADF Project".

  3. 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.

  4. 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 will be automatically added as the attributes of this view object. In this use case, assume that the view is named BI_VO.

  5. 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.

  6. 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".

  7. Edit the Java class for the OLTP_VO (OLTP_VOViewImpl). There will be 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 will look similar to the following:

    getViewObject().getCurrentRow().getAttribute("Lifetime_Value")

  8. Confirm that OLTP_VO is a part of the ApplicationModule. The OLTP_VO will have a collection of all of the attributes (Customer, Accounts Payable Days Outstanding, Lifetime Value", Customer Rank by YTD Revenue).

  9. Drag and drop the view object collection from the data control to the appropriate place on a JSPX page. When the pop-up menu appears, select the ADF Rich UI component to use for displaying the view object.

  10. Select a read-only Rich Table for the display.

2.2.4 Creating a Master-Detail Relationship Between an Oracle Business Intelligence View Object and an Online Transaction Processing View Object

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:

  1. Create an OLTP View Object.

  2. Create a Oracle Business Intelligence view object.

  3. Create a View Link between the OLTP View object and the BI View Object.

  4. Create an appropriate ADF View Object for the source and the destination data controls corresponding to the source and destination view objects.

2.3 Creating an Oracle Business Intelligence Logical SQL View Object

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.

2.3.1 How to Create an Oracle Business Intelligence Logical SQL View Object

Use the following subprocedures to prepare for and create an Oracle BI Enterprise Edition Logical SQL View Object that you can use to bind Oracle BI EE data to ADF components.

2.3.1.1 Installing the Required Business Intelligence JDeveloper 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:

  1. From the JDeveloper Help menu, select Check for Updates.

  2. Follow the steps in the wizard to locate, download, and install the Oracle BI EE extensions.

2.3.1.2 Creating a BI JDBC Connection

You must create a BI JDBC connection to the Oracle BI EE Server. It is through this connection that the view object will pass the logical SQL statement to the Oracle BI EE Server, and through which the Oracle BI EE Server will pass 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.

To create a BI JDBC connection:

  1. Determine if you want to create the BI JDBC connection for use in all ADF application or for the current ADF application, only, and then navigate to the Create Database Connection dialog.

    To create the connection in an ADF application, open an ADF project, go to the Application Navigator, then to the Application Resources panel, right-click the Connections node, select New Connection, and select Database. The Create Database Connection dialog is displayed. Confirm that Application Resources is selected in the Create Connection In field.

    To create the connection that is available for all ADF applications, go to the Resource Palette, click the New icon, select New Connection, and select Database. The Create Database Connection dialog is displayed. Confirm that IDE Connections is selected in the Create Connection In field.

  2. 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.

  3. 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.

2.3.1.3 Initializing the ADF Project

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 BI 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:

  1. Open an ADF project, click File, and then select New. The New Gallery dialog is displayed.

  2. Select the All Technologies tab and in the Categories area, select Business Tier and then select Business Intelligence.

  3. 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.

  4. Specify the database connection and SQL flavor your components will 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.

  5. Click OK. The Create View Object wizard is displayed.

2.3.1.4 Creating the Business Intelligence View Object

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 the view object: alternate keys, client interface specifications, client row interface specifications, view accessors, and user interface hints.

  1. 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 a business intelligence view object. For more information, see "Initializing the ADF Project".

  2. 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.

  3. 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.

  4. Select the BI JDBC connection that will run the logical SQL and retrieve the business intelligence data. The JDBC connection that was initialized for the project will default 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 new BI JDBC connection for the project.

  5. Click Next. The Logical SQL screen displays.

  6. 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 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 will have two attributes called "region" and "dollars."


    Note:

    The Insert, Update, and Delete logical SQL statements are not supported by the business intelligence view object. For more information about and a list of the logical SQL statements that you can use with a business intelligence view object, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

  7. Click Next. The Bind Variables screen displays.

  8. 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.

  9. Click Next. The Java screen displays.

  10. Generate any necessary Java files to extend or customize the functionality provided by the 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.

  11. Add an instance of the 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 will not be automatically generated.

  12. Click Next. The Summary screen displays.

  13. 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.

2.3.1.5 Modifying the Oracle Business Intelligence Logical SQL View Object's Behavior

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 will determine 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.

  1. 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.

  2. Click the Connection node to change the view object's BI JDBC connection information, or add or switch to another BI JDBC connection.

  3. Click the Logical SQL node to modify the view object's logical SQL and to add named bind variables to the SQL query.

  4. 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.

  5. 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.

  6. 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.