Tasks for Setting Up Oracle Endeca with Oracle BI Applications

Perform these detailed tasks for setting up Oracle Endeca with Oracle BI Applications.

Note:

You must perform the tasks in this section in the sequence described in Setting Up Endeca Integration with Oracle BI Applications.

Topics:

Setup Step: Install Oracle Endeca Server and Endeca Studio

Install all available patches. Optionally, you can take some steps to optimize the Endeca Server.

Recommended Data Size

For Endeca Server hardware configured for six or less threads, it is recommended that you limit the data size as follows:

  1. Limit data to 300 attributes and 1,000,000 rows.

  2. The less attributes included, the more rows can be loaded in the same amount of time, up to 300 million attributes per hour.

  3. The more processing available, the higher the number of rows that can be loaded in a given time.

Creating Endeca Data Domain Profile to Optimize CPU Core Count

Endeca Server's Data Domain Profile has its numComputeThreads property set to 2, representing two CPU Cores. Create a new Data Domain Profile to set the numComputeThreads value appropriately for the CPU cores available on the server when creating new Data Domains. For information about setting this property and creating domains using the endeca-cmd utility, refer to Endeca Server Command Reference.

After the new Data Domain Profile is created, set the new profile name to the ODI parameter ENDECA_DATADOMAIN_PROFILE when running the Endeca Load Plan or scenarios. For more information about running the Endeca Load Plan and scenarion, refer to Setup Step: Load Data.

Disabling, Enabling, or Deleting Data Domains

During data ingest, data domains reach their highest memory usage and do not release memory. Domains tend to page memory as needed to make room for other memory requests made to the operating system. If the memory usage nears the maximum amount available, disabling and then enabling each data domain can reduce their claim on memory.

The below endeca-cmd commands can be run on the server to disable an existing domain and then enable it again. The location of endeca-cmd command line utility is $MWHOME\EndecaServer7.6.1\endeca-cmd:

  • To disable a data domain: endeca-cmd disable-dd <data-domain>

  • To enable a data domain: endeca-cmd enable-dd <data-domain>

A data domain can be left disabled to preserve the existing data and not required to be online for a period of time. If a data domain is no longer needed, it can also be deleted to free resources. No backup is performed, so when a data domain is deleted it cannot be restored. A shell or batch file script can be used to disable, enable, and delete multiple data domains.

Install Endeca 3.1 Studio

Install Endeca 3.1 Studio. For information about requirements and installation of the Studio, refer to the Endeca Information Discovery Studio Installation Guide. Install available patches.

Setup Step: Set Up ODI Connectivity for Oracle BI and Endeca

Create the source and target server definitions in Oracle Data Integrator (ODI) to support Endeca ETL in the BI Applications data warehouse, as well as a definition for required Endeca view XML files.

Creating an Oracle BI Enterprise Edition Source

Endeca when integrated with Oracle BI Applications sources data from the Oracle Business Analytics Warehouse using an Endeca load plan, which denormalizes the OBAW data and loads it into the Endeca data domain, before using BI Server metadata such as data type, column name, attribute group name, and so on to load the Endeca schema. To support this integration, you create an ODI Data Server definition in the ODI repository metadata to support connections to the Oracle BI Server.

  1. In ODI Designer's Topology tab, expand the Oracle BI Technology in the Physical Architecture.
  2. In the Oracle BI Technology, create a new BI Applications data server.
  3. In the Definition tab of the new server, enter a name and, in the Connection details, provide User and Password credentials for an Oracle BI EE Server administrative user.
  4. In the JDBC tab, enter oracle.bi.jdbc.AnaJdbcDriver as the JDBC Driver.
  5. Enter jdbc:oraclebi://IP address or the Oracle BI EE Server hostname:9703/ as the JDBC URL.

    Note:

    Ensure that there are no leading or trailing spaces in the JDBC URL.

  6. In the BI Applications data server, create a new Physical Schema.

    In its Definition tab, specify Core for the Catalog (Catalog) and for Catalog (Work Catalog).

  7. In ODI Designer's Topology tab, expand the Logical Architecture and assign the OBI_BIAPPS11G logical schema to the physical schema you created.

Creating the Endeca Server Target

To support Endeca Server integration, you create a target ODI Data Server definition in the ODI Physical Architecture for the Endeca Server.

  1. In ODI Designer's Topology tab, expand the Endeca Server Technology in the Physical Architecture.
  2. In the Endeca Server Technology, create a new Endeca data server.
  3. In the Definition tab of the new server, enter a name.

    In the Connection details, leave the User and Password fields empty.

  4. In the JDBC tab, leave the JDBC Driver empty.
  5. Enter http://IP address or the Endeca Server hostname:7001/endeca-server as the JDBC URL.

    Note:

    Ensure that there are no leading or trailing spaces in the JDBC URL.

  6. In the Endeca data server, create a new Physical Schema.

    In its Definition tab, you can leave the Endeca Datastore schema and Endeca Datastore work schema as undefined.

  7. In ODI Designer's Topology tab, expand the Logical Architecture and assign the OEID_BIAPPS11G logical schema to the physical schema you created.

Creating the Data Server for Endeca Files

To support Endeca Server integration, you create a target ODI Data Server definition in the ODI Physical Architecture for Endeca files used by the Endeca web service to load view XML, including Sample Applications.

  1. In ODI Designer's Topology tab, expand the File Technology in the Physical Architecture.
  2. In the File Technology, create a new file data server.
  3. In the Definition tab of the new server, enter a name.

    In the Connection details, leave the User and Password fields empty.

  4. In the JDBC tab, enter com.sunopsis.jdbc.driver.file.FileDriver as the JDBC Driver.
  5. Enter jdbc:snps:dbfile?OPT=TRUE as the JDBC URL.

    Note:

    Ensure that there are no leading or trailing spaces in the JDBC URL.

  6. In the file data server, create a new Physical Schema.

    Use the directory containing the view XML files (for example, C:\Temp) for Directory (Schema) and Directory (Work Schema). This directory should be relative to the server where the ODI Agent is installed and can access the view XML files.

    Note:

    The Sample Apps View XMLs can be found in the BIAPPS installation under MW_ Home\Oracle_BI1\biapps\admin\provisioning\endeca\OracleBIApps_Endeca.zip.

  7. In ODI Designer's Topology tab, expand the Logical Architecture and assign the DW_OEID_SRCFILES logical schema to the physical schema you created.

Exporting Views Using Endeca Web Service Procedure

The Endeca Web Service Procedure supports exporting views. Beyond sample application view XML, you can export and import views for other subject areas.Endeca Views facilitate additional data manipulation in Endeca Server using Endeca Query Language (EQL), SQL-like views which are typically created in Studio but are stored in the server and need to be exported and then imported. Eight Subject Areas, or sample applications, have the Endeca Web Service Procedure included in the ODI package to load Endeca views.

  1. Create a new ODI package and add the Endeca Web Service Procedure.

    Give the package a name, for example Export Views.

  2. Set the following Procedure options.

    • ENDECA_WS_RELATIVE_PATH: /ws/sconfig/<Data Domain Name>

    • REQUEST_FILE_NAME: Export_Views.xml

    • RESPONSE_FILE_NAME: Name of the response message file views export. The naming convention is <Data Domain Name>_Views.xml.

  3. Place or save the Export_Views.xml file into the directory entered in the BIAPPS_OEID_FILE physical schema.

    This is also the directory where the procedure outputs the response file (RESPONSE_FILE_NAME).

  4. Run the ODI Package. If successful, the views are exported into the response file.

Importing Views Using Endeca Web Service Procedure

Exported views XML requires formatting before it can be imported or re-imported.

  1. Open the XML in a text editor and search for validatedSemanticEntity and replace it with semanticEntity.
  2. Search for listEntitiesResponse and replace it with putEntities.
  3. Use a new or existing ODI package to load the Endeca Data Domain.

    In the package, add the Endeca Web Service Procedure as the final step.

  4. Set the following Procedure options.

    • ENDECA_WS_RELATIVE_PATH: /ws/sconfig/Data Domain Name

    • REQUEST_FILE_NAME: Name of the response message file from the views export. The naming convention is Data Domain Name_Views.xml.

  5. Place or save the Export_Views.xml file into the directory entered in the BIAPPS_OEID_FILE physical schema.

    This is also the directory where the procedure outputs the response file (RESPONSE_FILE_NAME).

  6. Run the ODI Package. If successful, the views are imported into the target data domain.

Verifying Connection to the Data Warehouse

Verify that the Oracle Business Analytics Warehouse data server has correct connection information.

  1. In ODI Designer Topology tab, expand the Technology in the Physical Architecture corresponding to the data warehouse.
  2. Select the BIAPPS_DW data server.
  3. In the Definition tab, enter the connection user name and password for the OBAW database.
  4. In the JDBC tab, enter the correct JDBC Driver information for the database.

Configuring the ODI Agent

ODI Agents exist as either a standalone agent or a Java EE agent. The Endeca IKM jar files, which must be included with the ODI Agent, are available in the BI Applications installation in Middleware Home\Oracle_BI1\biapps\admin\provisioning\endeca\OracleBIApps_Endeca.zip (Import/Lib directory).

If your ODI agent is a standalone agent, copy the JAR files into the ODI drivers directory located in install path\Oracle\Middleware\Oracle_ODI1\oracledi\agent\drivers (or the equivalent path on UNIX). Restart the ODI Agent afterwards.

If your ODI agent is a Java application running on the WebLogic Server, do the following:

  1. Create a jlib directory under ORACLE_HOME/biapps/odi/bia_odiagent/.
  2. Copy all the required Endeca IKM jars in the odi agent classpath, in ORACLE_HOME/biapps/odi/bia_odiagent/jlib.
  3. Restart the ODI Server.

For more information about the IKM and jar files, refer to the Endeca SQL to Endeca Server Installation and Usage Guide.

Disabling Schemas in the Load Target Schema Interface

Endeca Server schema metadata can be modified using Endeca Studio rather than editing ODI flexfields after the schema is created during the initial Endeca ETL. To prevent overwriting a customized schema, you set the APPLY_SCHEMA option in the Load Target Schema Interface to FALSE.

  1. In ODI Designer, under Projects, open the Package of the subject area and select the Diagram tab.
  2. Right-click the TLP_OEID_Subject Area NameLoad_Tgt_Schema step and select Edit Linked Object.

    The Load Target Schema Interface opens in a new tab.

  3. In the Flow tab, click the Target (BIAPPS_OEID).

    Change the APPLY_SCHEMA option in the Properties Inspector options area to false.

  4. Save and close the interface, then the package.
  5. Generate the scenario.

    In Projects, right-click the Package and select Generate Scenario, and click OK to accept the defaults.

  6. Note the version of the new scenario.

You can now run the scenario directly using the noted version number, selecting the Context, Logical Agent, and Log Level. Alternately, you can run the scenario from the Endeca load plan. For each of the scenario steps, the load plan sets the version -1, which uses the latest generated scenario. Scenarios are run directly in ODI Designer.

Setup Step: Load Data

You can load data into the Endeca domain either by running ODI scenarios directly or by running the Endeca load plan. Source and target data stores delivered with the BI Applications installation include all Oracle BI Applications repository (RPD) logical columns. These may be customized to add or remove mappings to meet business requirements.

Setting and Declaring Variables

When running scenarios directly, you can set session variables at runtime. The following ODI variables can be overridden at runtime.

  • ENDECA_DATADOMAIN_PROFILE: This variable is the name of the Data Domain Profile if one was created. For information about creating a Data Domain Profile to maximize performance, refer to Creating Endeca Data Domain Profile to Optimize CPU Core Count.

  • ENDECA_OBIEE_SQL_PREFIX: This variable is used to override the default preferred currency, which is GLOBAL1.

  • ETL_PREDICATE_EXTRACT: This variable is used to enter filters.

The following ODI variables require modifications to the ODI package.

  • ENDECA_DATADOMAIN

  • ENDECA_DATALOAD_LOG_FILE_PATH

To change the ENDECA_DATADOMAIN and/or ENDECA_DATALOAD_LOG_FILE_PATH values:
  1. In the Designer under Projects, open the package of the subject area and select the Diagram tab.
  2. Select the ENDECA_DATADOMAIN or ENDECA_DATALOAD_LOG_FILE_PATH variable..

    In the Properties pane, in the General tab, enter the desired value

  3. Save and close the Package.
  4. Save and close the interface, then the package.
  5. Generate the scenario.

    In Projects, right-click the Package and select Generate Scenario, and click OK to accept the defaults. Select Startup Parameters Use All when prompted, then click OK.

Run Scenarios Directly

Run the ODI scenarios directly.

  1. In ODI Designer, open the scenario to run.
  2. Click Run.
  3. Select the Context, Logical Agent, and Log Level.
  4. Use the default or modify the session variables as needed. For example, BIAPPS.ETL_PREDICATE_EXTRACT is a customizable filter variable.
  5. Click OK.

Run the Endeca Load Plan

A predefined load plan is provided to run the Endeca packages. It contains steps to evaluate if the IS_ENDECA_DEPLOYED Configuration Manager parameter is assigned to each Subject Area, for example, derived Fact Groups created by development teams. To select which scenarios are run, set the IS_ENDECA_DEPLOYED variable to Yes, then run the load plan.

Complete the system setup (register sources, enable offerings, and so on) before performing any domain/parameter configuration, as described in Performing Post-Installation System Setup Tasks.
The frequency with which you run the load plan for data refresh is dependent on your use. Depending on the cost of a typical load, nightly execution may be appropriate, but if you do not require real-time data, you may run it less frequently. In the case that you are performing only historical analysis, the first load may be the only required.
  1. Log in to Configuration Manager.
  2. Select Data Load Parameters Administration, then Manage Data Load Parameters.
  3. Under the Search panel, select Source Instance, Offering, the Parameter IS_ENDECA_DEPLOYED, then click Search.
  4. Under the Data Load Parameter panel, select the Is Endeca Deployed parameter.
  5. Under the Group Specific Parameter Values panel, select the Endeca Fact Group (prefixed with OEID) and click Edit.
  6. Change Parameter Value to Yes, then Save and Close.

Setting Filters and Currency Setting

Within the predefined Endeca load plan, variables such as ENDECA_OBIEE_SQL_PREFIX and ETL_PREDICATE_EXTRACT may be overridden and saved in the load plan.

Filter and currency modifications can be done directly in the load plan under Scenario Variables by selecting the scenario step, and checking and providing a new value.

Setup Step: Create a New Endeca Application

Create a new Endeca application.

  1. Log in to Endeca Studio as an Administrator.
  2. Click the gear icon and, in the Control Panel, select Endeca Servers.
  3. Click New Connection and enter the required parameters.
  4. Validate the connection and, optionally, test it.
  5. Click Back to Home to return to the home page.
  6. Click New Application.
  7. Enter an Application name and Application description.
  8. Select a pre-built Endeca Server under Select a Data Source.
  9. Select a managed data connection.
  10. Click Done.
A default template is created.