| Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition) 11g Release 1 (11.1.1) Part Number E20836-06 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition) 11g Release 1 (11.1.1) Part Number E20836-06 |
|
|
PDF · Mobi · ePub |
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:
Oracle Application Development Framework (Oracle ADF) is an object-relational framework that can be used to create J2EE business services and expose underlying database objects. This framework provides an abstraction layer that enables application developers to build applications quickly and efficiently.
When you use Oracle ADF to build service-oriented Java EE applications, you implement your core business logic as one or more business services. These back-end services provide clients with a way to query, insert, update, and delete business data as required, while enforcing appropriate business rules. ADF Business Components are prebuilt application objects that provide a ready-to-use implementation of Java EE design patterns and best practices.
The ADF model is represented through the ADF Business Component constructs called Entity Objects and View Objects, usually constructed and defined during design time:
Entity Objects: ADF framework components that represent a row in a database table and simplify modifying its data. Importantly, it enables you to encapsulate domain business logic for those rows to ensure your business policies and rules are consistently validated.
View Objects: ADF framework components that encapsulate a SQL query and simplify working with its results. In addition to read-only view objects, there are entity-based view objects that support updatable rows. The view object queries just the data needed for the client-facing task at hand, then cooperates with one or more entity objects in your business domain layer to automatically validate and save changes made to its view rows. Like the read-only view object, an entity-based view object encapsulates a SQL query, can be linked into master/detail hierarchies using view links, and can be used in the data model of your application modules.
Applications built using ADF obtain their data by querying the defined View Objects using the ADF APIs.
The ADF model also includes an application module, which is the transactional component that UI clients use to work with application data. It defines an updatable data model along with top-level procedures and functions (called service methods) related to a logical unit of work related to an end-user task.
The application module serves as a container for multiple View Objects and Entity Objects, and also contains configuration related to the JDBC data source.
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.
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 view objects in different ApplicationModules are created upon import from ADF if custom properties are defined on the ApplicationModule. Note the following:
The property name format is BI_VIEW_LINK_property_name
The property value format is source_view_object_instance_name, ViewLink_definition_name, destination_view_object_instance_name
Be sure to use the fully qualified view object instance names for the source and destination view objects, as well as the fully qualified package name for the ViewLink definition.
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 view object 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 fewer 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.
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:
Deploying OBIEEBroker as a Shared Library in Oracle WebLogic Server
Deploying the Application EAR File to Oracle WebLogic Server from JDeveloper
Setting the Logging Level for the Deployed Application in Oracle WebLogic Server
Create a WebLogic Domain for your ADF Business Components that supports WebLogic Server, Oracle Application Core (Webapp), and Oracle JRF.
To create a WebLogic domain that supports the required components:
Start the WebLogic Configuration Wizard. For example, on Windows, run MW_HOME\wlserver_10.3\common\bin\config.cmd.
Select Create a new WebLogic domain and click Next.
On the Select Domain Source screen, ensure that Basic WebLogic Server Domain, Oracle JRF, and Oracle Application Core (Webapp) are selected.
Follow the remaining steps in the wizard, providing values appropriate for your environment.
Click Create on the Configuration Summary screen to create the domain.
You can start and stop the Oracle WebLogic Server for this domain using command-line scripts in the domain directory. For example, on Windows, use the following:
MW_HOME\user_projects\domains\domain_name\bin\startWebLogic.cmd
MW_HOME\user_projects\domains\domain_name\bin\stopWebLogic.cmd
The OBIEEBroker shared library is installed as part of your Oracle Business Intelligence installation. You need to deploy the OBIEEBroker library as a shared library in Oracle WebLogic Server by installing it (making its physical file or directory known to Oracle WebLogic Server) and starting it. After the library has been installed and started, other deployed modules can reference the library.
To deploy OBIEEBroker as a shared library in Oracle WebLogic Server:
Ensure that Oracle WebLogic Server is running. If it is not running, start it. For example, on Windows, run MW_HOME\user_projects\domains\your_domain\bin\startWebLogic.cmd.
Open the WebLogic Server Administration Console. For example, if your Oracle WebLogic Server is running locally on port 7001, go to http://localhost:7001/console.
Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.
In the Change Center, click Lock & Edit.
On the Home Page, in the left pane, click Deployments.
In the right pane, click Install.
Using the Install Application Assistant, locate the OBIEEBroker EAR file. You can find this file at:
ORACLE_HOME\bifoundation\javahost\lib\obisintegration\adf\
oracle.bi.integration.adf.ear
Click Next.
Select Install this deployment as a library and click Next.
Select the servers and/or clusters to which you want to deploy the OBIEEBroker library. Make sure to select all servers and clusters to which modules or applications that reference the library are deployed.
Click Next.
You can optionally update settings about the deployment. Typically, the default values are adequate. Click Help for more information.
Click Next, then click Finish to complete the installation.
In the Change Center, click Activate Changes.
Follow the steps in this section to deploy the application EAR file to Oracle WebLogic Server from JDeveloper. Before beginning this procedure, ensure that the following conditions are true:
You have an ADF Model project that contains ApplicationModules and view objects that will be exposed to Oracle Business Intelligence.
You have deployed OBIEEBroker as a shared library in Oracle WebLogic Server. See "Deploying OBIEEBroker as a Shared Library in Oracle WebLogic Server" for more information.
Oracle WebLogic Server is running.
To deploy the application EAR file to Oracle WebLogic Server from JDeveloper:
Start JDeveloper. For example, on Windows, run MW_HOME\jdeveloper\jdev\bin\jdev.exe.
Select File, then select Open to open the project that contains your ADF Business Components in JDeveloper. If prompted, allow JDeveloper to migrate the project to the latest version.
Create a new Application Module configuration, as follows:
In the Model project, double-click the application module, then click the Configurations tab for that application module.
Create a new configuration with the following characteristics:
Select JDBC DataSource for Connection Type.
Keep the default DataSource Name (for example, java:comp/env/jdbc/ApplicationDBDS).
When you set up the JDBC data source in Oracle WebLogic Server in a later step, you use part of this DataSource Name as the JNDI name required by Oracle WebLogic Server. The JNDI name is the DataSource Name without the java:comp/env context prefix (for example, jdbc/ApplicationDBDS).
Create a Business Component Archive deployment profile, as follows:
In the Projects window, right-click the Model project and choose New.
Select Deployment Profiles under General in the left pane, then choose Business Components Archive in the right pane and click OK.
Provide a name for the deployment profile (for example, MyApplication_Archive) and click OK.
On the Deployment page, click OK.
In the Projects window, right-click the Model project and select Deploy > your_deployment_profile_name > Deploy, or use the deployment wizard by selecting Deploy to File.
After the project has been deployed, two jar files are created in the deploy directory for the Model project (for example, MyApplication_Archive_Common.jar and MyApplication_Archive_MiddleTier.jar).
Create a new Web Project for the application, as follows:
Right-click the global application and select New Project.
Select Projects from the left pane, then select Web Project from the right pane.
Provide a project name (for example, OBIEEBroker).
Click Next until you reach the Web Project Profile page.
Modify the Java EE Context Root to a name that better represents your application (for example, MyApplication).
This value determines the URL that you use to connect to the application from Oracle Business Intelligence (for example, http://localhost:7001/MyApplication/obieebroker).
Edit the Profile Dependencies of the WAR deployment, as follows:
Right-click the Web Project you just created (for example, OBIEEBroker) and select Project Properties.
From the left pane, select Deployment. Then, open the WAR File deployment profile on the right pane.
Select Profile Dependencies from the left pane. Then, on the right pane, select the Common and MiddleTier deployment profiles of your Model project.
Following this step ensures that the Business Component Archives for the Model project are included in the WAR file.
Expand the Web Project and open web.xml. Then, go to the source view of the file.
In the web.xml source, replace the content within the <web-app> element with the following:
<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>
Create an EAR deployment profile for the application, as follows:
Right-click the global application and select Application Properties.
From the left pane, select Deployment, then click New on the right pane to create a new deployment profile.
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.
From the left pane, select Application Assembly. Then, on the right pane, select the webapp deployment profile of your Web Project.
Following this step ensures that the WAR file from your Web Project is included in the EAR file.
Under Application Resources, select Descriptors > META-INF > weblogic-application.xml.
On the left, select the Libraries tab.
Create two new Shared Library References, as follows:
Create the first Shared Library Reference with the following characteristics:
Library Name: oracle.bi.integration.adf
Implementation Version: 11.1.1.2.0
Create the second Shared Library Reference with the following characteristics:
Library Name: oracle.applcore.model
Implementation Version: 11.1.1.0.0
These two Shared Library References create the following entries in the weblogic-application.xml file for the application:
<library-ref> <library-name>oracle.bi.integration.adf</library-name> <implementation-version>11.1.1.2.0</implementation-version> </library-ref> <library-ref> <library-name>oracle.applcore.model</library-name> <implementation-version>11.1.1.0.0</implementation-version> </library-ref>
Deploy the EAR file to Oracle WebLogic Server by right-clicking the global application, then selecting Deploy > EAR_deployment_profile_name. From the dialog that appears, select Deploy to Application Server and then follow the instructions in the wizard.
To verify that the application has been deployed, log in to the WebLogic Server Administration Console and click Deployments under Your Deployed Resources. Verify that your application appears in the list (for example, obieebroker_app_name).
You must set up a JDBC data source in Oracle WebLogic Server for your application.
To set up a JDBC data source in Oracle WebLogic Server:
Ensure that Oracle WebLogic Server is running. If it is not running, start it. For example, on Windows, run MW_HOME\user_projects\domains\your_domain\bin\startWebLogic.cmd.
Open the WebLogic Server Administration Console. For example, if your Oracle WebLogic Server is running locally on port 7001, go to http://localhost:7001/console.
Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.
On the Home Page, select JDBC, then select Data Sources.
Click New.
Provide information for your data source. For Name and JNDI Name, provide the DataSource Name you specified in the Application Module configuration for the application, without the java:comp/env context prefix (for example, jdbc/ApplicationDBDS). In addition, make sure to select the target on which you want to deploy the data source before exiting the wizard.
Click Finish when you are done providing JDBC data source settings.
The log file for the server to which your application is deployed (server_name-diagnostic.log) records information about your deployed application. You can find this file in the server-specific directory within your domain. For example, on Windows, the log file for the AdminServer is located in:
MW_HOME\user_projects\domains\your_domain\servers\AdminServer\logs
To set the logging level for your deployed application:
Open the Oracle WebLogic Server file logging.xml for editing. You can find this file in:
MW_HOME\user_projects\domains\your_domain\config\fmwconfig\servers\server_name
Within the <loggers> element, add the following child elements:
<logger name="oracle.bi.integration.adf" level="LOG_LEVEL"/> <logger name="oracle.bi.integration.adf.v11g.obieebroker" level="LOG_LEVEL"/>
Log levels include SEVERE, WARNING, INFO, CONFIG, FINE, FINER, and FINEST. Refer to the Oracle WebLogic Server documentation for information about logger levels.
Save and close the file.
Restart Oracle WebLogic Server.
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:
Automatically Mapping Flex Object Changes to the Logical Model
Automatically Mapping Flex Object Changes Using the biserverextender Utility
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:
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
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.
For Oracle Fusion Applications, you must connect as the FUSION_APPS_BI_APPID user.
When you have finished providing information in the Select Data Source screen, click Next. The Select Metadata Objects screen appears.
Select the objects you want to import in the 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.
Note:
When importing flexfields from Oracle Fusion Applications sources, you must always import both the name_ and name_c attributes for each segment. The name_ attribute contains the value. The name_c attribute contains the code of the value set that the value comes from. Both attributes are mapped to the corresponding dimension view object. You typically use the name_ attribute in reports.
For DFF segments, you can also optionally import:
DESC_name_ attribute: contains a description of the value
TRAN_name_ attribute: contains translated values, when available
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.
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.
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.
Note:
See also the following resources in Oracle Fusion Applications Developer's Guide on designating flexfields as business intelligence-enabled:
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:
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
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.
Review the selected metadata to locate the new attributes.
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.
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 and Presentation layer.
If needed, you can override the default mapping behavior during this step by renaming logical tables, splitting a view object into multiple tables, combining multiple view objects 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:
In the Administration Tool, in the Physical layer, right-click the connection pool for your ADF OLTP source and select Import Metadata.
Complete the Select Metadata Objects screen and click Next. See "Using Incremental Import to Propagate Flex Object Changes" for more information about this screen.
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 view objects exist only to extend the ETL mappings, and are not used for queries. Logical table sources that reference imported view objects of this type are marked as disabled in the Business Model and Mapping layer. Query Only view objects 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
Click Finish to close the wizard, or click Next to continue to the Publish to Warehouse screen. See "Publishing Changes to the Data Warehouse and Propagating Changes to the Repository" for more information.
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 and DFF 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.
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 and Presentation 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 current logic includes data source-specific default rules that can enable, for example, logical dimensions and hierarchies to be automatically created.
You can use the biserverextender utility to import flex object changes from your ADF sources and map them to the Business Model and Mapping layer and Presentation layer. Because this feature does not require the Administration Tool, it is especially useful when you want to map flex object changes on Linux and UNIX systems where the Administration Tool is not available.
To use the biserverextender utility, you must first create an XML parameter file that contains the connection pool for an existing ADF data source. The biserverextender utility retrieves the existing ADF connection pool name from the parameter file, synchronizes the ADF data source, updates the deployed objects in the source, and then maps physical metadata to the Business Model and Mapping and Presentation layers based on the default rule files in the following directory:
ORACLE_HOME/bifoundation/javahost/lib/obisintegration/biextender
See "Customizing the Mapping Behavior" for more information about the rule files.
Syntax
biserverextender -R base_repository_name [-P repository_password] -O output_repository_name -I input_XML_file [-S]
Where:
-R base_repository_name is the name and path of the repository into which you want to import and map flex object changes.
-P repository_password is the Oracle BI repository password for the base repository.
Note that the repository_password argument is optional. If you do not provide the password argument, you are prompted to enter the password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide password arguments either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release. For scripting purposes, you can pass the password through standard input.
-O output_repository_name is the name and path of the repository generated by the utility.
-I input_XML_file is the name and path of an input XML parameter file that contains the fully-qualified name of a connection pool for an ADF data source.
-S is optional. If -S is not specified, only the changes from the ADF source's DFF and KFF objects are synchronized to the Oracle BI Repository. If -S is specified, it reimports all DFF and KFF objects from the ADF source based on the ADF source's database properties, and resynchronizes the Oracle BI Repository.
-S also incorporates the following changes in the app_segment_rules.xml rules file:
New mapping rules segments
New alias table creation
New "ADF VO To Be Exposed" subject area or presentation table
Example
biserverextender -R /scratch/my_repos.rpd -O /scratch/my_repos_modelled.rpd
-I /scratch/ADFSource.xml -S
Give password: password
Sample XML Parameter File
<BIExtenderParameters> <ConnectionDetails> <ConnectionPool> <ConnectionPoolName>"oracle.apps.fscm.model.analytics.applicationModule.Fscm TopModelAM_FscmTopModelAMLocal"."Connection Pool"</ConnectionPoolName> </ConnectionPool> </ConnectionDetails> </BIExtenderParameters>
You can configure and enable the BI Extender functionality to propagate changes made on Flex objects to your data warehouse.
This section contains the following topics:
About Propagating Changes to Flex Objects to the Data Warehouse
Publishing Changes to the Data Warehouse and Propagating Changes to the Repository
Setting Up XSL Transform Files to Customize XML Output to the Oracle BI Extender
You can use the Administration Tool to propagate changes in your ADF applications to Informatica, DAC, and the Physical and Business Model and Mapping layers of the Oracle BI Repository. In this scenario, the BI Extender is the driver that coordinates the information exchange between the ADF objects and the other targets.
The BI Extender feature supports changes made to flexfields in ADF data sources. Flexfields are columns within tables that can be reused based on a user-specified context. There are two types of flexfields:
Key (KFF). These objects are modeled as dimension view objects. KFF segments are imported as new dimensions joined to an existing fact table.
Descriptive (DFF). These objects are modeled as view object attributes. DFF segments are imported as new attributes (on both facts and dimensions) on existing tables.
The BI Extender uses the JavaHost service to propagate flexfield changes. Because of this, JavaHost must be running for this feature to work, and the NQSConfig.INI file on the Administration Tool computer must be configured for the correct JavaHost location.
Figure 6-4 BI Extender Orchestration of Flexfield Object Changes
In Figure 6-4, numbers indicate the steps in the flexfield change propagation process. These numbers represent the following steps:
The Import Metadata Wizard sends XML containing the flexfield object changes to the BI Extender.
XSL transform files are applied to the base XML.
The BI Extender retrieves information about mappings from the Informatica repository.
The BI Extender uses the mapping information to propagate the flexfield object changes to the Informatica Server.
The BI Extender propagates the changes to DAC.
The BI Extender propagates the changes to the database object for the data warehouse in the Physical layer of the Oracle BI repository.
The BI Extender maps the changes to the logical model.
In a separate step, the DAC user goes to the DAC client and synchronizes the changes in DAC with the actual data warehouse.
The BI Extender supports a variety of use cases for propagating changes made to flexfields. The primary use cases include:
New attribute added on a dimension (Dimension DFF - DescriptiveFlexExtensionStandard)
New attribute added on a fact (Fact DFF - DescriptiveFlexExtensionStandard)
New Dimension added, joined to an existing Fact (Dimension KFF - KeyFlexCreationStandard for dimension, KeyFlexExtensionStandard for the fact foreign key)
New Dimension added, joined to an existing Dimension (Dimension on Dimension KFF - KeyFlexCreationStandard on the new dimension, KeyFlexExtensionStandard for the foreign key)
Note:
For an example of an Oracle BI Applications KFF use case, see "Configuring GL Segment and GL Account Dimensions" in Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications.
In addition to these standard use cases, some more complex, advanced use cases are supported. The following sections describe these advanced use cases.
ETL Only View Object A view object that exists only to extend the ETL mappings, and that will not be used for queries. Imported view objects of this type are marked as disabled in the Business Model and Mapping layer. View objects of this type are marked as ETL Only in the Map to Logical Model screen of the Import Metadata Wizard.
Query Only View Object A view object that is only to be used for queries. View objects of this type are not passed to the BI Extender. View objects of this type are marked as Query Only in the Map to Logical Model screen of the Import Metadata Wizard.
View Objects that Map to Existing Dimensions This occurs when the imported view object is mapped to an existing logical dimension table that already maps to the data warehouse. In this case, the BI Extender uses the existing columns to perform the extensions. A different transformation template (from the specified XSL transform file, discussed in "Setting Up XSL Transform Files to Customize XML Output to the Oracle BI Extender") is applied for this use case, as shown in Table 6-2.
Table 6-2 Normal and Mapped to Existing Transformation Template Names
| Normal Template | "Mapped to Existing" Template |
|---|---|
|
KeyFlexCreationStandard |
KeyFlexCreationExtract |
|
DescriptiveFlexCreationStandard |
DescriptiveFlexCreationExtract |
|
KeyFlexHierarchyCreationStandard |
KeyFlexHierarchyCreationExtract |
|
DescriptiveFlexExtensionStandard |
DescriptiveFlexExtensionExtract |
|
KeyFlexExtensionStandard |
KeyFlexExtensionExtract |
Hierarchy View Object A special type of view object that is treated differently by the BI Extender. You can specify a Hierarchy view object in the updatehierarchy.xsl file, where you specify the HIERARCHY_NAME and DATASOURCE_NUM_ID (required attributes for Hierarchy view objects). This tells the Administration Tool which view objects are Hierarchy view objects. In the Map to Logical Model screen of the Import Metadata Wizard, the Hierarchy check box appears as selected for each Hierarchy view object.
Striping (W_GL_SEGMENT_D) Some view objects come with predefined filters. These filter definitions are automatically propagated to the appropriate logical table source content filter.
The BI Extender configures the logical table source filters for GL accounts by putting appropriate segment labels in the filters. There is a right-click option for GL-SegmentX dimension tables to pull in the synchronized custom AM properties into the logical table source filter.
To enable the BI Extender feature, you must configure a connection to Informatica, DAC, and the data warehouse in the biextension.properties file. You also need to provide configuration information in the config.xml file for JavaHost, opmn.xml, and optionally in the ContainerMapping.xml file. The following sections describe the steps needed for this configuration:
This section explains how to configure the biextension.properties file.
To configure connections to Informatica, the Informatica repository, and DAC in biextension.properties:
Open the biextension.properties file for editing. You can find this file at:
MW_HOME/Oracle_BI1/bifoundation/javahost/lib/obisintegration/biextender
Set the parameters in biextension.properties as needed for your deployment. The infa.* parameters define the connection to Informatica, the db.connection.* parameters define the connection to the Informatica repository, and the dac.* parameters define the connection to DAC and the data warehouse. The following table describes the parameters in biextension.properties:
| Parameter | Description |
|---|---|
|
infa.connection.sdk.repository |
The name of the Informatica repository. |
|
infa.connection.sdk.domainname |
The Informatica domain name. |
|
infa.connection.sdk.installpath |
The Informatica install path. |
|
infa.connection.sdk.ssl_enabled |
Whether the Informatica connection is configured for SSL. Set this parameter to |
|
db.connection.type |
Set this property to |
|
db.connection.repository |
The name of the Informatica repository. |
|
db.connection.dbname |
The name of the Informatica repository database. |
|
db.connection.host |
The Informatica repository database host. |
|
db.connection.port |
The Informatica repository database port. |
|
db.connection.dsn |
The DSN for the Informatica repository database. |
|
db.connection.driver |
The Informatica repository database driver (for example, oracle.jdbc.driver.OracleDriver). |
|
db.connection.url |
The Informatica repository database driver URL (for example, jdbc:oracle:thin:@localhost:1521:XE) |
|
db.connection.ssl_enabled |
Whether the connection to the Informatica repository database has been configured for SSL. Set this parameter to |
|
dac.connection.sdk.container |
The DAC container name. Note: In DAC, you cannot modify objects in the predefined source system containers either through the DAC Client or directly through SQL statements to the DAC repository. You must make a copy of a predefined container in order to make any changes to it. See "About Source System Containers" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console for more information. |
|
dac.connection.sdk.primarysource |
The DAC primary source. |
|
dac.connection.sdk.primarytarget |
The DAC primary target. |
|
dac.connection.sdk.driver |
The DAC repository database driver (for example, oracle.jdbc.driver.OracleDriver). |
|
dac.connection.sdk.url |
The DAC repository database driver URL (for example, jdbc:oracle:thin:@localhost:1521:XE). |
|
dac.connection.sdk.dbtype |
The DAC repository database type (for example, Oracle). |
Note that for Oracle Database, the driver parameters can use either a service name or SID. For example:
jdbc:oracle:thin:@host:port/service name
jdbc:oracle:thin:@host:port:SID
Save and close the file.
You must obtain the Informatica mapfwk.jar file for the BI Extender to work. This file is included in the Fusion Applications Install Repository, which is one of the Oracle Fusion Applications 11g parts available in the Oracle Fusion Applications Media Pack on the Oracle Software Delivery Cloud.
After downloading and unzipping the Oracle Fusion Applications 11g parts, the mapfwk.jar file is located in:
Fusion_Applications_Install_Repository\installers\PowerCenter
Copy the mapfwk.jar file from the given location to the computer running the Java Host.
This section explains how to configure the JavaHost loaders.xml file.
To configure the IntegrationServiceCall loader in loaders.xml:
Open the JavaHost loaders.xml file for editing. You can find this file at:
ORACLE_HOME\bifoundation\javahost\config
Find the Loader section for IntegrationServiceCall. Then, edit the section to include the location of mapfwk.jar, commons-logging-1.0.4.jar, and DAWSystem.jar, as shown in the following example. The commons-logging-1.0.4.jar and DAWSystem.jar files are available under the DAC install directory.
<Loader>
<Name>IntegrationServiceCall</Name>
<Class>oracle.bi.integration.javahost.ServiceCallLoader</Class>
<ConfigNodePath>ServiceCall</ConfigNodePath>
<ClassPath>
{%ORACLE_BIJH_ROOTDIR%}/lib/obisintegration/javahostservice.jar;
{%ORACLE_BIJH_ROOTDIR%}/lib/obisintegration/aw/11g/ojdbc5.jar;
C:\INFA\mapfwk.jar;
C:\DAC\lib\commons-logging-1.0.4.jar;
C:\DAC\DAWSystem.jar
</ClassPath>
</Loader>
Important: Be sure to replace the path names in each highlighted line with the appropriate path names for your deployment.
If the JavaHost is running on a Linux system, you must also download log4j-1.2.16.jar from http://www.apache.org and specify the classpath to this jar file in loaders.xml.
Save and close the file.
This section explains how to configure opmn.xml for the BI Extender.
Open the opmn.xml file for editing. You can find this file at:
ORACLE_INSTANCE\config\OPMN\opmn
Locate the tag <ias-component id="coreapplication_obijh1"> and add a new variable called INFA_DOMAINS_FILE under the <environment> subtag. For example:
</ias-component><ias-component id="coreapplication_obijh1"> <environment> <variable id="ORACLE_BI_OPMNMANAGED" value="true"/> <variable id="ORACLE_BI_APPLICATION" value="coreapplication"/> ... <variable id="INFA_DOMAINS_FILE" value="C:\\Informatica\\ PowerCenter\\domains.infa"/> </environment>
Important: Be sure to replace the path name in the highlighted line with the appropriate path name for your deployment.
If the JavaHost is running on a Linux system, follow these additional steps:
Add the variable INFA_HOME to the <environment> subtag of the <ias-component id="coreapplication_obijh1"> tag in opmn.xml. For example:
<variable id="INFA_HOME" value="/u01/APPLTOP/informatica/powercenter"/>
Add the INFA_HOME/server/bin directory to the LD_LIBRARY_PATH and PATH variables in opmn.xml. For example:
<variable id="LD_LIBRARY_PATH" value="$ORACLE_HOME/common/ODBC/Merant/5.3/ lib$:$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_HOME/bifoundation/web/ bin$:$ORACLE_HOME/bifoundation/odbc/lib$:$ORACLE_INSTANCE$:$ORACLE_HOME/lib $:/u01/APPLTOP/informatica/powercenter/server/bin$:$PATH" append="true"/> <variable id="PATH" value="$ORACLE_HOME/common/ODBC/Merant/5.3/lib $:$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_HOME/bifoundation/web/bin $:$ORACLE_HOME/bifoundation/odbc/lib$:$ORACLE_INSTANCE$:$ORACLE_HOME/lib $:/u01/APPLTOP/informatica/powercenter/server/bin$:$PATH" append="true"/>
For all platforms, save and close the file.
For all platforms, use the following commands to restart OPMN and the system components:
opmnctl stopall opmnctl startall
If you have customized your default Oracle BI Applications Informatica ETL mappings, you might need to update your Informatica repository name in ContainerMapping.xml.
To configure ContainerMapping.xml:
Make a copy of the Sample.ContainerMapping.xml file and rename the copied file ContainerMapping.xml. You can find Sample.ContainerMapping.xml in:
ORACLE_HOME/bifoundation/javahost/lib/obisintegration/biextender/EM/RS/
infa/EC/obj
Open the ContainerMapping.xml file for editing. Note the following about values in the default file:
"Informatica861Versioned" is the default Informatica repository name. Replace this name with the name of your Informatica repository.
"SDE_Fusion_Adaptor," "SILOS," and "PLP" are folder names in the repository. SDE_Fusion_Adaptor is an example; change all occurrences to the name of the SDE folder in the repository (for example, SDE_FUSION_V1_Adaptor).
"OLTP" and "OLAP" are the database names for the source tables registered in Informatica. These values do not have to match the actual database name. Rather, they are used as a way to folder source tables in Informatica.
Make any needed changes to the default values, such as the Informatica repository name and the SDE folder name.
Save and close the file.
If you are using a client installation of the Administration Tool, such as when you are running the JavaHost on a Linux system, you must also update the NQSConfig.INI file on the Administration Tool computer to point to the location of a running JavaHost. To do this, follow these steps:
Close the Administration Tool, if it is open.
On the same computer as the Administration Tool, open the local NQSConfig.INI file in a text editor. You can find this file at:
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
Locate the JAVAHOST_HOSTNAME_OR_IP_ADDRESSES parameter, near the bottom of the file. Update this parameter to point to a running JavaHost, using a fully-qualified host name or IP address and port number. For example:
JAVAHOST_HOSTNAME_OR_IP_ADDRESSES = "myhost.example.com:9810"
Note that in a full (non-client) Oracle Business Intelligence installation, you cannot manually edit this setting because it is managed by Oracle Enterprise Manager Fusion Middleware Control.
Save and close the file.
These steps are only required for client installations of the Administration Tool.
Optionally, you can change the location of the BI Extender files. To do this, move the files from the default location to a new location, then create an operating system environment variable called ORACLE_BI_ETL_EXTENDER and set its value to the value of the new path. The default path for the BI Extender files is:
ORACLE_HOME/bifoundation/javahost/lib/obisintegration/biextender
Because the BI Extender makes direct changes to a variety of sensitive targets, it is strongly recommended to run the entire BI Extender process in a secured environment behind a firewall.
To run the BI Extender in a secured environment:
Set up the Informatica server for SSL. Refer to the Informatica documentation for more information.
Set up the Informatica repository for SSL, including setting up the trusted certificate and exporting the certificate into the "export" file. Refer to the Informatica documentation for more information.
Open the biextension.properties file for editing. You can find this file at:
MW_HOME/Oracle_BI1/bifoundation/javahost/lib/obisintegration/biextender
Set the following two properties to true:
infa.connection.sdk.ssl_enabled = true db.connection.ssl_enabled = true
Save and close the file.
Add the database server root certificate to the JavaHost JVM using the keytool utility to enable JavaHost to authenticate itself with the database server. Use a command similar to the following:
C:\Program Files\Java\jdk1.6.0_12\bin\keytool -import -keystore "C:\Program Files\Java\jdk1.6.0_12\jre\lib\security\cacerts" -trustcacerts -file C:\oracle\product\10.2.0\client_1\admin\orcl\wallet3\export -alias ssl_certificate
In this example, note that:
C:\Program Files\Java\jdk1.6.0_12\jre\lib\security\cacerts is the path to the JavaHost JVM's certificate file
keytool is the program used to add the certificate
C:\oracle\product\10.2.0\client_1\admin\orcl\wallet3\export is the exported root certificate of the database server
ssl_certificate is an alias given to this certificate so that it can be identified later
When prompted, type "yes" after "Trust this certificate?"
This section explains how to use the BI Extender to propagate the flex object changes to the Informatica server, DAC, the Physical layer, and the logical model. It also explains how to use DAC to make the required schema changes in the data warehouse itself. You must complete the preconfiguration steps in "Performing Preconfiguration Tasks for the BI Extender" before performing the steps in this section.
This section contains the following topics:
Running the BI Extender to Update Informatica, DAC, and the RPD
Using DAC to Propagate Flex Object Changes to the Data Warehouse Schema
This section explains how to run the BI Extender to update Informatica, DAC, and the Oracle BI repository for flex object changes. This section assumes that you have already added a new attribute in your ADF application.
To run the BI Extender to update Informatica, DAC, and the RPD:
Ensure that the JavaHost process is running.
Open your repository in the Administration Tool.
In the Physical layer, right-click the connection pool for your ADF OLTP source and select Import Metadata.
Complete the steps in the Select Metadata Objects and Map to Logical Model screens. See "Using Incremental Import to Propagate Flex Object Changes" and "Automatically Mapping Flex Object Changes to the Logical Model" for more information.
On the Publish to Warehouse screen, perform the following steps:
Select the warehouse database.
Enter a user name and password for the Informatica server.
Enter a user name and password for the Informatica repository database. This user does not need to have write permission.
Enter a user name and password for DAC.
Click Connect.
Figure 6-5 shows the Publish to Warehouse screen.
Figure 6-5 Publish to Warehouse Screen of Import Metadata Wizard
Click Finish.
See "About Propagating Changes to Flex Objects to the Data Warehouse" for a description of what happens when you click Finish in the Publish to Warehouse screen.
After the BI Extender finishes, you need to propagate the necessary schema changes to the data warehouse. This section describes one recommended method of accomplishing this task.
Note:
In DAC, you cannot modify objects in the predefined source system containers either through the DAC Client or directly through SQL statements to the DAC repository. You must make a copy of a predefined container in order to make any changes to it.
See "About Source System Containers" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console for more information.
To propagate the schema changes to the data warehouse:
In DAC, filter the tables in DAC view to show only those tables that were modified or created by the BI Extender. To do this:
Go to the Tools menu and choose UI Preferences.
Select Always show Last Updated.
Select the Tables tab. Right-click and select Flat Views, then select Table Columns.
A dialog opens that shows a Table Columns list in Query mode. Scroll to the right and then double-click the Last Update field.
Set the date and time when you started the BI Extender process, and then select After, as shown in Figure 6-6.
Click OK, then click Go. A list of tables and columns that were created and modified after the specified date is shown.
Run the DAC functionality to upgrade these tables in the data warehouse using the option Generate DW Table Scripts for Oracle. Make sure to select All records in the list. See "Managing Data Warehouse Schemas" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console for more information.
The biextension.properties file specifies the XSL files to be used to transform and customize the default XML generated by the Administration Tool and sent to the BI Extender. By default, the following XSL files are applied:
biextension.xsl: Contains default transformations for the Extender. Contains templates for both normal and "mapped to existing" cases.
updatehierarchy.xsl: Specifies rules for how Hierarchy view objects should be handled.
LastUpdateDate.xsl: Identifies which input columns need to be filtered on the Informatica parameter LastUpdateDate.
All XSL files are located in the same directory as biextension.properties.
In rare cases, you might need to make additional customizations the default XML that is generated by the Administration Tool and sent to the BI Extender. To do this, you can create other XSL files to be applied in addition to the three default XSL files. Note the following:
Additional XSL files need to conform to the XML schema defined in the biextension.xsd file, located in the same directory as the biextension.properties file.
It is a best practice to define changes in an additional XSL file rather than updating one of the default files.
The replaceName.xsl file provides examples on how to do XSL transforms for name changes.
To specify additional XSL files:
Open the biextension.properties file for editing. You can find this file at:
ORACLE_HOME/bifoundation/javahost/lib/obisintegration/biextender
Add additional XSL files to the xsl_transforms line, as follows:
xsl_transforms = updatehierarchy.xsl,LastUpdateDate.xsl,biextension.xsl, replaceNames.xsl
Save and close the file.
Sample base XML output generated by the Administration Tool might look like the following:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Document>
<extension mode="KeyFlexCreationStandard" type="Dimension">
<table name="W_COST_CENTER_D">
<columns>
<column datatype="Varchar2(50)" name="COST_CENTER_NAME" type="Attribute">
<source column="COST_CENTER_NAME" table="SnowflakesalesApp.
ADF_COST_CENTER_VO"/>
</column>
<column datatype="Varchar2(10)" name="COST_CENTER_LOCATION"
type="Attribute">
<source column="COST_CENTER_LOCATION" table="SnowflakesalesApp.
ADF_COST_CENTER_VO"/>
</column>
<column datatype="Varchar2(5)" name="COST_CENTER_ID" type="Key">
<source column="COST_CENTER_ID" table="SnowflakesalesApp.
ADF_COST_CENTER_VO"/>
</column>
</columns>
</table>
</extension>
</Document>
This section explains how to implement ADF data security in the Oracle BI Server. To implement ADF data security, you must have an application with secured View Objects and user setup.
See also "Configuring SSL in Oracle WebLogic Server" for related information.
Perform the steps in the following sections:
When you set up Oracle Business Intelligence to use ApplSession, the following occurs:
Pillar-specific AOL sessions are created for data queries. A new AOL session is created for every pillar within a BI session when a data query is fired against the pillar. This AOL session is reused for all subsequent data queries against the same pillar. There are, at most, as many AOL sessions as there are ADF pillars (databases) defined in the Oracle BI repository.
For the examples used in this section, there are three or less AOL sessions ("AOL_SESSION_ID_HCM", "AOL_SESSION_ID_CRM", "AOL_SESSION_ID_FSCM") created within a BI Session for data queries.
AOL context variable values are propagated to the newly-created pillar-specific AOL sessions. Presentation Services propagates the AOL context variables when it logs in to the Oracle BI Server.
The SQL Bypass query reattaches to the previously created AOL session for that ADF Database.
This section contains the following topics:
Setting Up Database Objects and Connection Pools for ApplSession Integration
Setting Up Initialization Blocks for ApplSession Integration
About the Client Login Process in an ApplSession Integrated Environment
To set up the Oracle BI repository to enable ApplSession integration, you must first configure the appropriate database object and connection pools in the Physical layer.
To set up database objects and connection pools for ApplSession integration:
Open your repository in the Administration Tool.
Create database objects and connection pools in the physical layer for each pillar, as follows:
Create a database object and connection pool for SQL bypass during data queries (for example, Pillar1_bypass).
Use the Import Metadata Wizard to create a database object and connection pool that correspond to the application EAR file deployed in WLS (for example, Pillar1_http). This database object contains the physical table and column mappings to view objects and attributes. Be sure to specify the SQL bypass database you want to use during metadata import.
Figure 6-7 shows the pillar-specific database objects.
Figure 6-7 Pillar-Specific Database Objects
Open the connection pool for each SQL bypass database and click the Connection Scripts tab. To ensure that SQL bypass queries are issued within the pillar-specific AOL session (for example, "AOL_SESSION_ID_Pillar1_http,") you must provide the appropriate pre-query and post-query scripts, as follows:
Expand Execute before query and click New.
Enter a pre-query script similar to the following:
BEGIN
fnd_session_mgmt.attach_session('VALUEOF(NQ_SESSION.AOL_SESSION_ID_Pillar1_http)');
END;
Click OK.
Expand Execute after query and click New.
Enter the following post-query script:
BEGIN fnd_session_mgmt.detach_session; END;
Click OK.
Click OK in the Connection Pool dialog.
Figure 6-8 shows the Connection Scripts tab of the Connection Pool dialog.
Figure 6-8 Pre-query and Post-query Scripts for the SQL Bypass Connection Pool
Initialization blocks are used to initialize BI session variables. They contain SQL/XML queries that are set up to return columns of values which are then mapped to BI session variables. Initialization blocks typically execute during BI session creation.
In the Initialization Block dialog, you can choose to defer the execution of an initialization block by selecting Allowed deferred execution. A deferred initialization block runs when its target variable is used for the first time within a BI session.
To set up initialization blocks for ApplSession integration:
In the Administration Tool, select Manage, then select Variables.
Select Action > New > Session > Initialization Block.
Provide a name for the initialization block (for example, create_AOL_SESSION_ID_Pillar1). This initialization block creates a new AOL session when a data query is issued against the data source (for example, Pillar1_http).
Click Edit Data Source.
Select Default initialization string, and provide an XML initialization string similar to the following:
<?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
<ADFQuery mode="create_applsession">
<ContextAttribute>
<Name><![CDATA[ADDTL_CUSTOM_LEVEL]]></Name>
<Value><![CDATA[VALUEOF(NQ_SESSION.AOL_ADDTL_CUSTOM_LEVEL)]]></Value>
</ContextAttribute>
<ContextAttribute>
<Name><![CDATA[CLIENT_ENCODING]]></Name>
<Value><![CDATA[VALUEOF(NQ_SESSION.AOL_CLIENT_ENCODING)]]></Value>
</ContextAttribute>
<ContextAttribute>
<Name><![CDATA[CURRENCY]]></Name>
<Value><![CDATA[VALUEOF(NQ_SESSION.AOL_CURRENCY)]]></Value>
</ContextAttribute>
</ADFQuery>
This initialization string causes the OBIEE broker servlet to create a Java AOL session.
The <ContextAttribute> elements represent the name-value pairs of context variables that will be propagated to the newly created AOL session. If no <ContextAttribute> elements are specified in the XML initialization string, default values of the user are used to create the AOL session.
The values provided in the <Value> elements are mapped to the session variables initialized by Presentation Services.
The following list shows a full mapping between AOL context attributes and session variables initialized by Presentation Services:
ACCESSIBILITY_MODE - AOL_ACCESSIBILITY_MODE ACTION - AOL_ACTION ADDTL_CUSTOM_LEVEL - AOL_ADDTL_CUSTOM_LEVEL ANIMATION_ENABLED - AOL_ANIMATION_ENABLED APPLICATION_LANGUAGE - AOL_APPLICATION_LANGUAGE CLIENT_ENCODING - AOL_CLIENT_ENCODING COLOR_CONTRAST - AOL_COLOR_CONTRAST CURRENCY - AOL_CURRENCY DATE_FORMAT - AOL_DATE_FORMAT DECIMAL_SEPARATOR - AOL_DECIMAL_SEPARATOR EMBEDDED_HELP_ENABLED - AOL_EMBEDDED_HELP_ENABLED FONT_SIZE - AOL_FONT_SIZE GROUPING_SEPARATOR - AOL_GROUPING_SEPARATOR HISTORY_OVERRIDE_USER_NAME - AOL_HISTORY_OVERRIDE_USER_NAME INDUSTRY - AOL_INDUSTRY INDUSTRY_IN_TERRITORY - AOL_INDUSTRY_IN_TERRITORY LANGUAGE - AOL_LANGUAGE MODULE - AOL_MODULE NLS_SORT - AOL_NLS_SORT NUMBER_FORMAT - AOL_NUMBER_FORMAT PRODUCT - AOL_PRODUCT PRODUCT_FAMILY - AOL_PRODUCT_FAMILY TERRITORY - AOL_TERRITORY TIME_FORMAT - AOL_TIME_FORMAT TIMEZONE - AOL_TIMEZONE TRACE_LEVEL - AOL_TRACE_LEVEL
Application-specific, nonstandard variables that do not appear in the preceding list can be propagated in the same manner.
Click Browse next to Connection Pool and select the connection pool for the data source (for example, Pillar1_http_cp), then click Select.
Click OK in the Session Variable Initialization Block Data Source dialog.
Click Edit Data Target, then click New.
Create a variable to be initialized by this initialization block. The name must be in the following format:
AOL_SESSION_ID_physical_layer_database_object_for_pillar
For example:
AOL_SESSION_ID_Pillar1_http
Click OK in the Session Variable Initialization Variable Target dialog.
Select Allowed deferred execution. Selecting this option means that this initialization block only executes when a data query is issued against Pillar1.
Click OK in the Session Variable Initialization Block dialog.
Repeat the steps in this procedure for each pillar, if you have more than one.
Figure 6-9 shows an example pillar-specific session variable initialization block.
Figure 6-9 Session Variable Initialization Block Dialog for Pillar-Specific Initialization Block
The following steps provide an example of the client login process when you have integrated Oracle Business Intelligence with ApplSession:
A client logs in to the Oracle BI Server with the BI session variables that represent AOL context.
The BI Session is established.
The client issues a Logical SQL statement that navigates to the database object (for example, Pillar1_http).
The initialization block runs and initializes the session variable (for example, create_AOL_SESSION_ID_Pillar1 initializes AOL_SESSION_ID_Pillar1_http).
The session variable (for example, AOL_SESSION_ID_Pillar1_http) is attached within the OBIEE broker servlet before querying Composite view object SQL from the database object (for example, Pillar1_http).
The Oracle BI Server replans the query with Composite view object SQL.
The session variable (for example, AOL_SESSION_ID_Pillar1_http) is attached using the pre-query script in the connection pool before querying data directly from the SQL bypass database object (for example, Pillar1_bypass).
Oracle Business Intelligence provides a custom identity assertion provider that must be installed in Oracle WebLogic Server. This custom asserter is used to extract a token from the HTTP request from Oracle Business Intelligence. The super user name and password provided in the repository connection pool object, as well as the session user name provided in the BI user session, are extracted from the token.
Authentication is performed in the custom asserter using the super user name and password. After the super user's credentials are authenticated, the custom asserter performs identity assertion using the session's user name. One-way SSL is required to secure the communication channel between Oracle Business Intelligence and Oracle WebLogic Server.
Note that the custom identity assertion provider only allows a user with the user name FUSION_APPS_BI_APPID to perform identity assertion. In other words, the user specified in the connection pool (the ADF HTTP connection pool, not the SQL bypass connection pool) must be FUSION_APPS_BI_APPID.
This section describes the configuration tasks you need to perform in the application before deploying to Oracle WebLogic Server for Oracle Business Intelligence consumption.
Ensure that you have completed the tasks described in "Setting Up ADF Data Sources" before you perform the steps in the following sections.
This section contains the following topics:
Setting Up Security Constraints and Security-Related Servlet Filters in web.xml
Configuring Role-to-Principal Mapping in weblogic-application.xml
Configuring the Custom Identity Assertion Provider in Oracle WebLogic Server
You must set up the CLIENT-CERT authentication method in web.xml to trigger the custom identity assertion provider during log-on. In addition, you need to set up the JPS servlet filter in web.xml for data security to work properly.
To set up the CLIENT-CERT authentication method and servlet filters in web.xml:
In JDeveloper, expand the Web Project for OBIEEBroker and open web.xml.
Go to the source view of the file.
To set up the CLIENT-CERT authentication method, include the following elements under the <web-app> element:
<security-constraint>
<web-resource-collection>
<web-resource-name>restricted</web-resource-name>
<url-pattern>/obieebroker</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>EndUsers</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>CLIENT-CERT</auth-method>
<realm-name>myrealm</realm-name>
</login-config>
<security-role>
<role-name>EndUsers</role-name>
</security-role>
Note that the value for <role-name> can be anything. This value will eventually be mapped to principals that exist in the security realm; see "Configuring Role-to-Principal Mapping in weblogic-application.xml" for more information.
To set up the JPS servlet filter, include the following <filter> elements before the <filter> element for ServletADFFilter:
<filter> <filter-name>JpsFilter</filter-name> <filter-class>oracle.security.jps.ee.http.JpsFilter</filter-class> </filter>
Then, include the following <filter-mapping> elements before the <filter-mapping> element for ServletADFFilter:
<filter-mapping> <filter-name>JpsFilter</filter-name> <servlet-name>OBIEEBroker</servlet-name> <dispatcher>FORWARD</dispatcher> <dispatcher>REQUEST</dispatcher> <dispatcher>INCLUDE</dispatcher> </filter-mapping>
You must map the value you provided for <role-name> in web.xml to principals that exist in the security realm. This mapping is defined in weblogic-application.xml.
To configure role-to-principal mapping in weblogic-application.xml:
In JDeveloper, under Application Resources, open Descriptors > META-INF > weblogic-application.xml.
Include the following <security> element under the <weblogic-application> element:
<security>
<realm-name>myrealm</realm-name>
<security-role-assignment>
<role-name>your_role_name</role-name>
<principal-name>your_principal_name</principal-name>
</security-role-assignment>
</security>
Note the following:
Replace your_role_name with the actual value you provided for <role-name> in web.xml.
Replace your_principal_name with the name of either a BI session end user (for example, joeUser), or an Oracle WebLogic Server group (for example, testUsers).
Redeploy the application.
This section explains how to configure the custom identity assertion provider.
To configure the custom identity assertion provider in Oracle WebLogic Server:
Locate the custom identity assertion provider jar file, located in your Oracle Business Intelligence installation directory at:
ORACLE_HOME/bifoundation/javahost/lib/obisintegration/adf/
biadfidentityasserter.jar
Copy the jar file to the following Oracle WebLogic Server location:
MW_HOME/wlserver_10.3/server/lib/mbeantypes
Restart the Oracle WebLogic Server.
Open the WebLogic Server Administration Console. For example, if your Oracle WebLogic Server is running locally on port 7001, go to http://localhost:7001/console.
Log in to the WebLogic Server Administration Console with the credentials you created when you set up your WebLogic domain.
In the Change Center, click Lock & Edit.
In the left pane, select Security Realms, and then click the security realm that you want to configure.
Click the Providers tab, then click New.
Enter a name (for example, BI-ADF IdentityAsserter) and select BIADFIdentityAsserter for Type.
Click OK.
In the Change Center, click Activate Changes.
Restart the Oracle WebLogic Server.
This section explains how to configure one-way and two-way SSL in Oracle WebLogic Server. Topics include:
One-way SSL is required to properly secure the communication between Oracle Business Intelligence and Oracle WebLogic Server.
To configure one-way SSL in Oracle WebLogic Server:
From the WebLogic Server Administration Console home page, click Servers under the Environment heading.
In the Servers table, the name of the server you want to manage. Then, on the General subtab of the Configuration tab, select SSL Listen Port Enabled.
Use the Administration Tool to update the appropriate connection pool object in the Physical layer so that the URL uses https:// instead of http://. Also, update the port number to use the SSL port (7002 by default).
Optionally, you can set up two-way SSL to secure the communication between Oracle Business Intelligence and the Oracle WebLogic Server.
To set up and test two-way SSL:
Create client certificates in the Oracle BI Server, if they do not already exist. See "Creating Certificates and Keys in Oracle Business Intelligence" in Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for more information.
Modify the ADF Oracle WebLogic Server to accept SSL connections and to perform mutual SSL. To do this, perform the following steps in the Oracle WebLogic Server Administration Console:
Log in to the Administration Console and click Servers under the Environment heading, then click the server name (for example, AdminServer).
In the Change Center, click Lock & Edit to enable configuration changes.
In the General tab, select SSL Listen Port Enabled and record the SSL Listen Port number. Then, click Save.
Select the SSL tab, then select Advanced. For Two Way Client Cert Behavior, select Client Certs Requested and Enforced. Then, click Save.
Select the Keystores tab and record the Trust Keystore that is being used. For example, if the Demo Trust keystore is used, record its location and file name.
Click Activate Changes.
Ensure that the Certificate Authority (CA) for the Oracle BI Server client certificate is trusted by the ADF Oracle WebLogic Server. To do this, follow these steps:
On the Oracle BI Server computer, find the CA file for the client certificate. If the file was generated using the instructions referenced in Step 1, the file will be "cacert.pem" in:
ORACLE_HOME/user_projects/domains/bifoundation_domain/config/fmwconfig
/biinstances/coreapplication/ssl
Copy this file to a known location.
On the ADF Oracle WebLogic Server computer, open a command window and go to the location of the trust keystore. You recorded this value in Step 2. For example:
/scratch/user_name/view_storage/user_name_fmw/fmwtools/mw_home/wlserver_10.3/server/lib
Copy the client CA file (for example, cacert.pem), stored in the previous step, to this location.
Use the JDK keytool utility to import the client CA into the trust keystore for the ADF server, making it a trusted CA. Use the following command:
keytool -import -file client_CA_file -keystore keystore_file -keystorepass keystore_password
For example:
/scratch/my_name/view_storage/my_name_fmw/jdk6/bin/keytool -import -file ~/Downloads/SSL/cacert.pem -keystore DemoTrust.jks -keystorepass DemoTrustKeyStorePassPhrase
Restart the ADF Oracle WebLogic Server.
Update the Physical layer of the Oracle BI repository, as follows:
In the Administration Tool, in the Physical layer, open the first ADF connection pool object and select the Miscellaneous tab.
Update the URL field to use the https protocol and the SSL port noted in Step 2, and then click OK.
Repeat the previous two steps for each additional ADF connection pool object.
Save the repository and restart the Oracle BI Server.
Configure the Oracle BI Server ODBC DSN to use SSL. For example, on Windows:
Open the ODBC Data Source Administrator and select the System DSN tab.
Double-click the DSN for the Oracle BI Server. The DSN should start with "coreapplication_OH."
Select Use SSL.
Click Next, click Next again, and then click Finish.
Perform queries against ADF using your Oracle BI Server client of choice (such as nqcmd). The Oracle BI Server should now be communicating with the ADF Oracle WebLogic Server using mutual SSL / client certs.
Some ADF applications have custom properties defined on the ApplicationModule, such as EFFECTIVE_DATE or TREE_VERSION. You can include these custom properties in your application queries, and the Oracle BI Server will pass them to the ADF application. To enable this feature, you must register the custom properties as a static repository variable using the Administration Tool.
You cannot use this feature to pass any custom property to your ADF application. Only certain custom properties, like EFFECTIVE_DATE and TREE_VERSION, are supported.
To register custom properties:
Open your repository in the Administration Tool.
Select Manage, then select Variables.
Select Action > New > Repository > Variable.
For Name, enter ADF_PARAM_LIST. Do not enter the name of the custom property as the name of the variable.
Ensure that the Type is Static.
For Default Initializer, enter the name or names of the custom properties as a character string. If you have multiple custom properties, include them as a comma-delimited list. For example:
'PARAM_EFFECTIVE_DATE'
'PARAM_EFFECTIVE_DATE, ApplicationIdBind, KeyFlexfieldCodeBind'
Click OK.
Save and close the repository.
After you register the custom properties as a repository variable, you can include these variables in queries. For example:
set variable PARAM_EFFECTIVE_DATE=2001-01-01 : SELECT c1 FROM t1;
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.
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:
About Determining the Physical Column for a Presentation Column
Initializing Session Variables Automatically for Propagating UI Hints
Example of Using UI Hints From an Oracle ADF Data Source When Creating Analyses
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 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-10 shows the Label Text and Tooltip Text options in the Edit Attribute dialog in Oracle JDeveloper.
Figure 6-10 Edit Attribute Dialog in JDeveloper for Label and Tooltip Options
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_View Object 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.
View Object 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
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:
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.
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.
A logical column can be mapped to physical columns by multiple logical table sources. Only logical table sources that are not disabled are searched.
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.
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 view object 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 view object 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.
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 for each view object; that is, if the Oracle BI Server needs the UI hints of a view object's attributes, then the UI hints for all the attributes under the view object are queried and propagated through session variables.
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:
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.
View the UI hints:
Sign in to Oracle Business Intelligence.
Create a new analysis using the subject area for which you obtained UI hints.
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.
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[view_object_name]]></ViewObject> <Attribute> <ViewObject><![CDATA[attribute_view_object_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 specifies 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.
view_object_name specifies the name of the view object to obtain the UI hints of all attributes in it.
attribute_view_object_name specifies the name of the view object that contains the attribute.
attribute_name specifies 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:
Create session initialization blocks in the Administration Tool.
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-11 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-11 Setting Up a Session Variable Initialization Block Data Source with an Oracle ADF UI Hints Query
In the Session Variable Initialization Block dialog, select Row-wise initialization as the Variable Target.
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.
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.
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-12 Using the Query Related Objects Feature to Find the Related Presentation Tables
Select the required presentation table and click Go To.
This displays the selected presentation table.
Expand the presentation table to view the presentation columns.
Double-click the LastName presentation column to display the Presentation Column dialog.
Select Custom display name and enter a value such as the following one:
VALUEOF(NQ_SESSION.ADF_LABEL_MY_ORCLADF_EMPLOYEESVIEW_LASTNAME)
Select Custom description and enter a value such as the following one:
VALUEOF(NQ_SESSION.ADF_TOOLTIP_MY_ORCLADF_EMPLOYEESVIEW_LASTNAME)
Click OK.
Save the changes in the repository and restart the Oracle BI Server.
|
 Copyright © 2010, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|