Skip Headers
Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)

Part Number E10540-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Working with ADF Data Sources

This chapter describes how to set up Oracle ADF Business Components for use with Oracle Business Intelligence, and how to import metadata from ADF data sources.

Connecting to ADF data sources 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 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?

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:

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 is populated with the join condition defined in the view link.

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:

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

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:

  1. Start the WebLogic Configuration Wizard. For example, on Windows, run MW_HOME\wlserver_10.3\common\bin\config.cmd.

  2. Select Create a new WebLogic domain and click Next.

  3. On the Select Domain Source screen, ensure that Basic WebLogic Server Domain, Oracle JRF, and Oracle Application Core (Webapp) are selected.

  4. Follow the remaining steps in the wizard, providing values appropriate for your environment.

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

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

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

  3. Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.

  4. In the Change Center, click Lock & Edit.

  5. On the Home Page, in the left pane, click Deployments.

  6. In the right pane, click Install.

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

  9. Select Install this deployment as a library and click Next.

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

  11. Click Next.

  12. You can optionally update settings about the deployment. Typically, the default values are adequate. Click Help for more information.

  13. Click Next, then click Finish to complete the installation.

  14. 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:

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

  1. Start JDeveloper. For example, on Windows, run MW_HOME\jdeveloper\jdev\bin\jdev.exe.

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

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

  4. Create a Business Component Archive deployment profile, 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.

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

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

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

  8. Expand the Web Project and open web.xml. Then, go to the source view of the file.

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

    <context-param>
      <description>This holds the Principals (CSV) that a valid end user should 
    have (at least one) in order to query the ADF layer from BI.</description>
      <param-name>oracle.bi.integration.approle.whitelist</param-name>
      <param-value>Application_Roles_List</param-value>
    </context-param>
    
    <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

    For application_roles_list, provide a list of application roles in CSV form. For example:

    <param-value>FBI_TRANSACTION_ANALYSIS_GENERIC_DUTY, OBIA_ANALYSIS_GENERIC_DUTY, 
    OBIA_EXTRACT_TRANSFORM_LOAD_DUTY, FUSION_APPS_BI_APPID</param-value>
    

    If you provide a list of application roles, a user's application role is checked before access is allowed to the application. Note that this run-time check requires the following grant to be present in the domain_name/config/fmwconfig/system-jazn-data.xml file for the WebLogic domain:

    <grant>
      <grantee>
        <codesource>
          <url>file:${domain.home}/servers/${weblogic.Name}/tmp/
          _WL_user/oracle.bi.integration.adf/-</url>
        </codesource>
      </grantee>
      <permissions>
        <permission>
          <class>oracle.security.jps.JpsPermission</class>
          <name>IdentityAssertion</name>
          <actions>execute</actions>
        </permission>
        <permission>
          <class>oracle.security.jps.JpsPermission</class>
          <name>AppSecurityContext.setApplicationID.obi</name>
        </permission>
      </permissions>
    </grant>
    

    If you do not want application roles to be checked by the OBIEEBroker servlet, use DISABLE_WHITELIST_ROLE_CHECK as the value for the <context-param> in web.xml. For example:

    <param-value>DISABLE_BI_WHITELIST_ROLE_CHECK</param-value>
    
  10. 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.

  11. Under Application Resources, select Descriptors > META-INF > weblogic-application.xml.

  12. On the left, select the Libraries tab.

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

  15. 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:

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

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

  3. Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.

  4. On the Home Page, select JDBC, then select Data Sources.

  5. Click New.

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

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

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

  3. Save and close the file.

  4. Restart Oracle WebLogic Server.

Importing Metadata from ADF Data Sources

This section describes different import scenarios for ADF data sources. You must complete the steps in "Setting Up ADF Data Sources" before you can import metadata from ADF sources.

This section contains the following topics:

Performing an Initial Import from ADF Data Sources

This section describes how to use the Import Metadata Wizard to perform an initial import from ADF data sources.

To import metadata from an ADF data source:

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

    Note:

    If you have already defined an existing ADF 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 6-1 shows the Import Metadata Wizard.

    Figure 6-1 Import Metadata Wizard: ADF Data Source

    Description of Figure 6-1 follows
    Description of "Figure 6-1 Import Metadata Wizard: ADF Data Source"

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

  3. Select the objects you want to import in the Data source view and move them to the Repository View, using the > (Import selected) or >> (Import all) buttons. You can also move objects from the Repository View back to the Data source view, 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.

    If this import is creating a new connection to the data source, when you move the items from the Data source view to the Repository View 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 of the Connection Pool dialog 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.

  4. Click Finish to close the wizard, or click Next to continue to the Map to Logical Model screen. See "Automatically Mapping Flex Object Changes to the Logical Model" for more information.

  5. To validate that your import was successful, expand the database object for the ADF 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.

Using Incremental Import to Propagate Flex Object Changes

If you make changes to flexfields in your ADF applications, then you can use the Import Metadata Wizard in the Administration Tool to incrementally import the changes to the Physical layer of the Oracle BI repository.

The Import Metadata Wizard includes a synchronization feature for ADF data sources that enables you to import only the changes made to objects. The synchronization feature detects the changed objects, including new joined dimensions (KFF) and new attributes (DFF), so that they can be added automatically, without you having to search for them. The synchronization feature detects the following:

  • Changes in columns

  • Additions or deletions of tables and columns

  • Additions of keys and foreign keys

  • Newly joined tables

    Note that new tables that are joined to any existing table are only imported when you select the option Automatically include any missing joined objects on the Select Metadata Objects screen.

After import, the ADF data is modeled as shown in Table 6-1.

Table 6-1 How the ADF Metadata is Modeled in the Oracle BI Repository

ADF Metadata Imported BI Metadata

Root Application Module

Database

View Objects

Physical Tables

View Object Attribute

Physical Column

View Object Key

Physical Key

View Links

Physical Joins


Note that as data is imported incrementally, modifications to properties of attributes are detected and propagated. For example, if an attribute changes its data type, that change is propagated to the physical layer objects.

To incrementally import metadata for ADF data sources:

  1. In the Administration Tool, in the Physical layer, right-click the connection pool for your ADF OLTP source and select Import Metadata.

    The Import Metadata Wizard is displayed, starting at Step 3, Select Metadata Objects.

    Figure 6-2 shows the Select Metadata Objects screen of the Import Metadata Wizard.

    Figure 6-2 Select Metadata Objects Screen: ADF Data Source

    Description of Figure 6-2 follows
    Description of "Figure 6-2 Select Metadata Objects Screen: ADF Data Source"

  2. Click Synchronize to locate and automatically select all recent changes for import. The Synchronize button is located in the upper right corner of the screen, above the Repository View.

  3. Review the selected metadata to locate the new attributes.

  4. Click Finish to close the wizard, or click Next to continue to the Map to Logical Model screen. See "Automatically Mapping Flex Object Changes to the Logical Model" for more information.

Automatically Mapping Flex Object Changes to the Logical Model

After importing changes to flexfields in your ADF application, you can use the Map to Logical Model screen of the Import Metadata Wizard in the Administration Tool to automatically propagate the changes to the Business Model and Mapping layer.

If needed, you can override the default mapping behavior during this step by renaming logical tables, splitting a VO into multiple tables, combining multiple VOs into a single logical table, and so on.

See also "Customizing the Mapping Behavior" for information about using XML files to automate the mapping behavior displayed in this screen.

To automatically map flex object changes to the logical model:

  1. In the Administration Tool, in the Physical layer, right-click the connection pool for your ADF OLTP source and select Import Metadata.

  2. Complete the Select Metadata Objects screen and click Next. See "Using Incremental Import to Propagate Flex Object Changes" for more information about this screen.

  3. In the Map to Logical Model screen, the Table Mapping and Column Mapping grids display the results of a default drag-and-drop. You can keep the default behavior, or customize the behavior for your needs. For example, you might want to rename tables and columns in the Business Model and Mapping layer, map to an existing logical table, or map a logical column to multiple source columns.

    Note that the Column Mapping grid shows alias columns as well as regular columns, so that you can handle customized mappings that include alias columns. The Table Mapping grid enables a single physical table to map to multiple logical tables, and the reverse.

    The Table Mapping grid includes a VO Type column. Options include Normal, ETL Only, and Query Only. ETL Only VOs exist only to extend the ETL mappings, and are not used for queries. Logical table sources that reference imported VOs of this type are marked as disabled in the Business Model and Mapping layer. Query Only VOs are only used for queries, and are not passed to the BI Extender for extension into the data warehouse.

    The Table Mapping grid also includes a Hierarchy column. Select this option for objects that are hierarchies.

    Select Create Logical Joins if the imported tables are being mapped to a new business model that will be created during the Map to Logical Model step. In other words, select this option when the imported logical joins do not already exist. Do not select this option for business models that already have the required logical joins in place. Doing so will create erroneous multiple logical joins.

    Figure 6-3 shows the Map to Logical Model screen.

    Figure 6-3 Map to Logical Model Screen of Import Metadata Wizard

    Description of Figure 6-3 follows
    Description of "Figure 6-3 Map to Logical Model Screen of Import Metadata Wizard"

  4. Click Finish to close the wizard, or click Next to continue to the Publish to Warehouse screen. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition) in the Oracle Fusion Applications Technology Library for information about the Publish to Warehouse screen.

Customizing the Mapping Behavior

You can create a set of XML files that specify custom mapping requirements for the mappings displayed in the Map to Logical Model screen. The Administration Tool reads the XML files and then automatically maps the KFF segments according to the specified logic. Each XML file has a top-level element with an appName attribute that specifies the application to which the file applies.

You must create your XML files according to the logic in the XML schema files app_segment_rule.xsd and mapping_rules.xsd. You can find these files in:

ORACLE_HOME\bifoundation\javahost\lib\obisintegration\biextender

All XML files in this directory with the prefix mapping_rules and app_segment_rules are parsed by the Administration Tool for ADF data sources.

You can use the existing app_segment_rules_*.xml and mapping_rules_*.xml in this directory as examples.

See Appendix G, "XML Schema Files for ADF Mapping Customizations" for more information about the app_segment_rule.xsd and mapping_rules.xsd XML schema files.

Manually Mapping Flex Object Changes to the Logical Model

You can choose to skip the logical mapping step in the Import Metadata Wizard, and instead drag and drop the physical objects to the Business Model and Mapping layer. The Administration Tool supports incremental drag-and-drop for ADF data sources, which enables physical database and schema objects to be dragged and dropped into an existing business model, resulting in updates made only for the incremental changes.

Note that the behavior in previous releases was to create new logical objects for every physical object that was dragged and dropped. The new logic includes data source-specific default rules that can enable, for example, logical dimensions and hierarchies to be automatically created.

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:

  1. Open your repository in the Administration Tool.

  2. Select Manage, then select Variables.

  3. Select Action > New > Repository > Variable.

  4. For Name, enter ADF_PARAM_LIST. Do not enter the name of the custom property as the name of the variable.

  5. Ensure that the Type is Static.

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

  7. Click OK.

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

or

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.

Propagating Labels and Tooltips from ADF Data Sources

You can propagate user interface hints, such as labels and tooltips, from ADF data sources to display when users work with analyses. When translated labels and tooltips (based on user locale) are maintained within an ADF data source, you can query the data source to access this translated data. You use the Administration Tool to configure presentation columns to use when creating analyses.

This section contains the following topics:

What are Labels and Tooltips?

A label is the text that is used in prompts or table headers that precedes the value of a data item. A tooltip is the text that is displayed when a user hovers the mouse pointer over the item. Each attribute of a view object (VO) has an associated label and tooltip. A view object is the Oracle Application Development Framework component that enables a developer to work easily with SQL query results. The propagation of UI hints enables a presentation column in the Administration Tool to use a label and tooltip as its Custom display name and Description respectively.

Figure 6-4 shows the Label Text and Tooltip Text options in the Edit Attribute dialog in Oracle JDeveloper.

Figure 6-4 Edit Attribute Dialog in JDeveloper for Label and Tooltip Options

This graphicshows the label and tooltip options in JDev.
Description of "Figure 6-4 Edit Attribute Dialog in JDeveloper for Label and Tooltip Options"

About the Session Variable Naming Scheme for UI Hints

Session variable names are generated by the Oracle BI Enterprise Edition broker servlet in Oracle WebLogic Server in the following format:

ADF_UI Hint Type_Database Name_VO's Name_Attribute's Name

Where:

UI Hint Type is either LABEL or TOOLTIP, depending on the UI hint type that the session variable represents.

Database Name is the value of the "database" attribute of the ADFQuery element in the XML query. Special characters such single quotes ('), double quotes ("), and spaces are replaced by the underscore character.

VO's Name is the name of the View Object to which the attribute belongs. Oracle ADF prohibits special characters and spaces in the name.

Attribute's Name is the name of the attribute that the session variable represents. Oracle ADF prohibits special characters and spaces in the name.

Every character in the session variable name is uppercase. For example, the XML query in Example 6-3 generates four session variables with the following names:

ADF_LABEL_MY_ORCLADF_EMPLOYEESVIEW_FIRSTNAME

ADF_TOOLTIP_MY_ORCLADF_EMPLOYEESVIEW_FIRSTNAME

ADF_LABEL_MY_ORCLADF_EMPLOYEESVIEW_LASTNAME

ADF_TOOLTIP_MY_ORCLADF_EMPLOYEESVIEW_LASTNAME

About Determining the Physical Column for a Presentation Column

As required by the naming scheme for session variables, each presentation column must map to a physical column. When you select Externalize Display Names > Generate ADF Label or Externalize Descriptions > Generate ADF Tooltip for a presentation layer object, then the physical column is located using the following rules:

  1. Examine the presentation column and determine its logical column. If the logical column is derived from an existing logical column, then the physical column cannot be found.

  2. If the default aggregation rule for the logical column is not None or Sum, then the physical column cannot be found. It does not make sense semantically to use the ADF UI hints for aggregation rules other than Sum.

  3. A logical column can be mapped to physical columns by multiple logical table sources. Only logical table sources that are not disabled are searched.

  4. Do not search logical table sources that map the logical column using non-trivial expressions (that is, anything more than a physical column name). If no logical table sources are searched, then the physical column cannot be found.

  5. From the remaining ordered list of logical table sources, examine the physical column that is mapped by the first logical table source. The physical column must be mapped to a VO attribute. In other words the physical column must be part of a physical database of type OracleADF11g.

    • If this condition is satisfied, then the physical column for obtaining UI hints is found.

    • If this condition is not satisfied, then continue to examine the physical column that is mapped by the next logical table source until the physical column that is mapped to a VO attribute is found.

      If all logical table source are searched without satisfying the condition, then the physical column cannot be found.

If the physical column for obtaining UI hints is found using these rules, then the custom display name or description is populated with a session variable that has a name based on a predetermined naming scheme. See "About the Session Variable Naming Scheme for UI Hints" for more information.

If the physical column for obtaining UI hints is not found using these rules, then the Generate ADF Label and Generate ADF Tooltip options are shown as disabled in the right-click menu.

As an alternative to using the physical column found using these rules, you can use XML code in an initialization block to initialize your own session variables with ADF UI hints. You must then enter these session variable names in the Custom display name and Custom description fields manually. See "Using XML Code in Initialization Blocks to Query UI Hints" for more information.

Initializing Session Variables Automatically for Propagating UI Hints

If the Externalize Display Names > Generate ADF Label and Externalize Descriptions > Generate ADF Tooltip options were used to successfully generate the session variable names for UI hints from Oracle ADF, then the session variables are created and initialized when Oracle BI Presentation Services queries them during the session. The variables are not created and initialized during the session logon stage for performance reasons. Instead, the variables are created and initialized when they are needed by a specific query within a session, using the Allow deferred execution feature.

When Presentation Services queries the custom display names and custom descriptions through ODBC, the Oracle BI Server checks if the associated session variables have been created. If they have not been created, then the Oracle BI Server dynamically generates the appropriate XML query (as described in "Using XML Code in Initialization Blocks to Query UI Hints") to query the UI hints from the Oracle ADF data source. The Oracle BI Server uses the UI hints to create and initialize the session variables. As an optimization, the Oracle BI Server queries UI hints per VO; that is, if the Oracle BI Server needs the UI hints of a VO's attributes, then the UI hints for all the attributes under the VO are queried and propagated through session variables.

Example of Using UI Hints From an Oracle ADF Data Source When Creating Analyses

The following example shows how you can use UI hints from an Oracle ADF data source when creating analyses.

The following prerequisites must be met:

  • UI hints must have been configured in the Oracle ADF data source.

  • A working repository must have been configured for the Oracle ADF data source in the Administration Tool.

To use UI hints from an Oracle ADF data source when creating analyses:

  1. Suppose that the repository contains a presentation column named "LastName." On the General tab of the Presentation Column dialog, the Custom display name and Custom description fields are not selected.

    Right-click the column in the Presentation layer and select first Externalize Display Names > Generate ADF Label, then Externalize Descriptions > Generate ADF Tooltip to generate the strings that populate the Custom display name and Custom description fields.

    You can also use these options from the right-click menu of a presentation table to generate the strings for all the columns in that table.

  2. View the UI hints:

    1. Sign in to Oracle Business Intelligence.

    2. Create a new analysis using the subject area for which you obtained UI hints.

    3. In the Subject Areas pane, expand the Employee folder to see the UI hints that have been propagated from the Oracle ADF data source.

      The LastName column displays as "Last Name" (the label value from the Oracle ADF data source). When you hover the mouse pointer over the column, the tip displays as "This is the employee's last name" (the tooltip value from the Oracle ADF data source).

    For information about creating analyses, see Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

Using XML Code in Initialization Blocks to Query UI Hints

As an alternative to using the automated system described in the previous section, you can use specialized XML code in place of SQL statements in initialization blocks to query the data source for UI hints, within a single repository and subject area. You use the ADFQuery element, which has three attributes that are named mode, database, and locale. The element requires zero or more child elements. The syntax of the element is as follows:

<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
<ADFQuery mode="{Mode}" database="{Database Name}"
locale="VALUEOF(NQ_SESSION.WEBLANGUAGE)">
     <ViewObject><![CDATA[{VO Name}]]></ViewObject>
     <Attribute>
     <ViewObject><![CDATA[{Attribute VO Name}]]></ViewObject>
     <Name><![CDATA[{Attribute Name}]]></Name>
     </Attribute>
</ADFQuery>

where

{Mode} specifies what you want to query:

  • label for querying attributes' label

  • tooltip for querying attributes' tooltip

  • ui_hints for querying attributes' label and tooltip

{Database Name}

Use the name of the physical database object in the Administration Tool, which contains the physical columns that correspond to the attributes in the Oracle ADF data source.

{VO Name}

Use the name of the View Object to obtain the UI hints of all attributes in it.

{Attribute VO Name}

Use the name of the View Object that contains the attribute.

{Attribute Name}

Use the name of the attribute that belongs to the associated View Object to obtain the UI hints of this attribute.

Example 6-1 Querying Labels for All View Objects

No child elements must be included in the ADFQuery element, if the UI hints of all attributes in all View Objects are queried. For example, to query the labels of all attributes in all View Objects under the My_orclADF physical database object, use the following XML code:

<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
<ADFQuery mode="label" database="My_orclADF"
locale="VALUEOF(NQ_SESSION.WEBLANGUAGE)">
</ADFQuery>

Example 6-2 Querying Tooltips for Specific View Objects

The ADFQuery element can contain zero or more child elements named ViewObject if UI hints of all attributes in specific View Objects are queried. Each ViewObject element has a text content that contains the View Object's name. The ViewObject element is used to specify the View Objects from which the UI hints of all attributes are queried. For example, to query the tooltips of all attributes in the View Object that is named EmployeesView and CustomersView under the My_orclADF physical database object, use the following XML code:

<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
<ADFQuery mode="tooltip" database="My_orclADF"
locale="VALUEOF(NQ_SESSION.WEBLANGUAGE)">
   <ViewObject><![CDATA[EmployeesView]]></ViewObject>
   <ViewObject><![CDATA[CustomersView]]></ViewObject>
</ADFQuery>

Example 6-3 Querying UI Hints for Specific Attributes

The ADFQuery element can contain zero or more child elements named Attribute. Each Attribute element has two required child elements named ViewObject and Name. The Attribute element is used to specify the attributes from which the UI hints are queried. The ViewObject child element has a text content that contains the View Object's name. This element specifies the View Object that the attribute belongs to. The Name child element has a text content which contains the attribute's name. For example, to query the labels and tooltips of the attributes named Firstname and Lastname in the EmployeesView View Object under the My_orclADF physical database object, use the following XML code:

<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
<ADFQuery mode="ui_hints" database="My_orclADF"
locale="VALUEOF(NQ_SESSION.WEBLANGUAGE)">
   <Attribute>
      <ViewObject><![CDATA[EmployeesView]]></ViewObject>
      <Name><![CDATA[Firstname]]></Name>
   </Attribute>
   <Attribute>
      <ViewObject><![CDATA[EmployeesView]]></ViewObject>
      <Name><![CDATA[Lastname]]></Name>
   </Attribute>
</ADFQuery>

After configuring the initialization blocks, you must manually enter the session variable names in the Custom display name and Custom description text fields for the appropriate presentation column.

Follow the procedure in the example in "Example of Using UI Hints From an Oracle ADF Data Source When Creating Analyses", but replace the first step with the following ones:

  1. Create session initialization blocks in the Administration Tool.

    1. In the Session Variable Initialization Block Data Source dialog, enter the Initialization string.

      In this example, the initialization block queries both the label and tooltip of all attributes in a View Object named EmployeesView. Figure 6-5 shows the setup of a session variable initialization block with an appropriate Oracle ADF UI hint query. "My_orclADF"."Connection Pool" is a connection pool for an Oracle ADF data source.

      Figure 6-5 Setting Up a Session Variable Initialization Block Data Source with an Oracle ADF UI Hints Query

      Description of Figure 6-5 follows
      Description of "Figure 6-5 Setting Up a Session Variable Initialization Block Data Source with an Oracle ADF UI Hints Query"

    2. In the Session Variable Initialization Block dialog, select Row-wise initialization as the Variable Target.

    3. Click Test to test the query against the Oracle ADF data source.

      In the results window, the first column contains the session variable names that are generated using the naming scheme. The second column contains the label and tooltip values from the Oracle ADF data source.

      See "About the Session Variable Naming Scheme for UI Hints" for a description of the naming scheme.

  2. Configure a custom display name and a description in presentation columns.

    To find the presentation tables that can use the UI hints from the EmployeesView View Object, this example uses the Query Repository feature in the Administration Tool.

    1. Right-click a physical table (for example, EmployeesView), then select Query Related Objects > Presentation > Presentation Table from the menu.

      The Query Related Objects dialog displays all the related presentation tables.

      This example sets up a custom display name and custom description for columns in the Employee presentation table.

      Figure 6-6 Using the Query Related Objects Feature to Find the Related Presentation Tables

      Description of Figure 6-6 follows
      Description of "Figure 6-6 Using the Query Related Objects Feature to Find the Related Presentation Tables"

    2. Select the required presentation table and click Go To.

      This displays the selected presentation table.

    3. Expand the presentation table to view the presentation columns.

    4. Double-click the LastName presentation column to display the Presentation Column dialog.

    5. Select Custom display name and enter a value such as the following one:

      VALUEOF(NQ_SESSION.ADF_LABEL_MY_ORCLADF_EMPLOYEESVIEW_LASTNAME)
      
    6. Select Custom description and enter a value such as the following one:

      VALUEOF(NQ_SESSION.ADF_TOOLTIP_MY_ORCLADF_EMPLOYEESVIEW_LASTNAME)
      
    7. Click OK.

    8. Save the changes in the repository and restart the Oracle BI Server.