Setting Up Data Lineage

When you set up data lineage, prebuilt data lineage warehouse tables in the Oracle Business Analytics Warehouse are populated by an ETL package which loads lineage metadata from five sources.

  • Oracle BI Applications Configuration Manager (Configuration Manager)

  • Oracle Data Integrator (ODI) Work Repository

  • Oracle BI Presentation Catalog

  • Oracle BI metadata repository

  • Oracle Fusion OLTP tables

One-time preliminary setup steps include preparing metadata from and access to these sources for load of data lineage information into the prebuilt data lineage warehouse tables. Metadata and data refresh can then be performed on an ongoing basis.

To install and set up data lineage dashboards, you must complete the following tasks, in order. Each high-level task breaks down into a list of detailed steps.

  1. Configure the environment to support data lineage, as described in Setup Step: Configure ODI Topology and Load Plan.
  2. Configure the WebLogic Server heap size, as described in Setup Step: Configure the WebLogic Server Heap Size.
  3. Optionally, disable the Fusion step if you are using other Oracle BI Applications sources, as described in Setup Step: Disable Fusion Step (Optional).
  4. Create data lineage warehouse tables, as described in Setup Step: Create the Data Lineage Warehouse Tables.
  5. Extract metadata using Windows Oracle BI Enterprise Edition (Oracle BI EE) clients, as described in Extracting Oracle Business Intelligence Metadata Using Catalog Manager and Administration Tool.
  6. Execute the data lineage load plan in ODI to load the data lineage warehouse tables, as described in Executing and Monitoring the Data Lineage Load Plan. This step loads the data lineage warehouse tables.

Setup Step: Configure ODI Topology and Load Plan

The ODI Work Repository comes preconfigured with required topology and environment settings to support data lineage data extraction from the dashboards' five sources.

During initial setup, you configure prebuilt data lineage sources in the ODI topology so that ODI, Configuration Manager, and Oracle BI EE data can be sourced during data lineage ETL. You then configure a prebuilt data lineage home directory variable which provides access to extracted metadata files and configure an adaptor list variable.

The data lineage ETL uses the following connections to extract metadata from its sources and load it into the data warehouse:

  • BIAPPS_ODIREP: Used to extract ODI Metadata from ODI schema where SNP_*** tables are located.

  • BIAPPS_BIACOMP: Used to extract configuration metadata from the Configuration Manager schema where C_*** tables are located.

  • BIAPPS_DW: Used to load data lineage tables located in the Oracle Business Analytics Warehouse.

  • BIAPPS_DW_FILE: Used to extract Oracle BI EE metadata from files. This connection should point to the BIA_11 location (<source file home>/biapps/etl/data_files/src_files/BIA_11)

To configure the data lineage source and target connections:

  1. In ODI Studio, navigate to the Physical Architecture view of the Topology Navigator's tree view, and search for the prebuilt BIAPPS_BIACOMP Data Server.

    The BIAPPS_BIACOMP connection to the Configuration Manager database may already have been configured during Oracle BI Applications installation and configuration.

    Description of GUID-744E4D0A-1570-4486-A67D-E5642250C0B1-default.gif follows
    Description of the illustration GUID-744E4D0A-1570-4486-A67D-E5642250C0B1-default.gif
  2. In the Definition tab of the editor, verify or enter the correct connection details for the server, including the instance name and a correctly privileged ODI user and password.
  3. In the JDBC tab of the editor, verify the JDBC Driver and verify or specify a valid JDBC URL for the connection.
  4. Click Test Connection to verify that the connection details you have entered are correct.
  5. In the Search pane, double-click the physical schema to open it in the editor.
  6. Configure or confirm the Schema and Work Schema.
  7. Repeat the above steps to configure the other connections.

    • Configure the BIAPPS_ODIREP connection with ODI Repository database and schema details.

    • Configure the BIAPPS_DW connection with Oracle Business Analytics Warehouse database and schema details. This connection may already have been configured while installing and configuring Oracle BI Applications product.

Configuring Data Lineage File Connection

To configure the data lineage file connection:

  1. Search for the prebuilt BIAPPS_DW_FILE and double-click it to open it in the editor.
  2. Configure Schema and Work Schema to point to the BIA_11 location, source file home/biapps/etl/data_files/src_files/BIA_11.
    Description of GUID-B04648FC-ECBB-4A37-A4C6-9A9B71E15D8B-default.gif follows
    Description of the illustration GUID-B04648FC-ECBB-4A37-A4C6-9A9B71E15D8B-default.gif

    Open Logical File Connection DL_BIAPPS11G_SRCFILES. It can be located in Topology - Logical Architecture under Technologies -> File.

  3. Navigate to the Logical Architecture view of the Topology Navigator's tree view, and locate the Logical File Connection DL_BIAPPS11G_SRCFILES under Technologies, then File.
  4. Double-click the connection and, in the editor, associate DL_BIAPPS11G_SRCFILES to the BIAPPS_DW_FILE - Physical Schema.

Configuring the Load Plan Variable

Configure the load plan variable in the Data Lineage Extract and Load loadplan.

  • DL_HOME — Location of data lineage source files. Configure to source file home/biapps/etl/data_files/src_files/BIA_11, the same as the BIAPPS_DW_FILE configured in a previous step.

    Note:

    If you are running DL on Windows, you should also use forward slash "/" as separator instead of "\" in the file patch. In the Windows actual path, c:\biapps\etl\data_files\src_files\BIA_11, DL_HOME should be set with a forward slash. The correct path is c:/biapps/etl/data_files/src_files/BIA_11.

  • ADAPTOR_LIST — List of required Source Adaptor Names.

    Based on your source system, one or more values can be selected from the following list:

    'SDE_ORA11510_Adaptor','SDE_ORAR1212_Adaptor','SDE_PSFT_90_Adaptor','SDE_OP_V1_Adaptor','SDE_ORAR1211_Adaptor','SDE_PSFT_91_Adaptor','SDE_SBL_822_Adaptor','SDE_SBL_811_Adaptor','SDE_ORAR12_Adaptor', 'SDE_ORAR1213_Adaptor','SDE_JDEE1_91_Adaptor','SDE_JDEE1_90_Adaptor','SDE_Universal_Adaptor','SDE_FUSION_V1_Adaptor'

    The Adaptor Name has to be enclosed in single quotes. If you want to enter multiple adaptors, use commas as separators between multiple adaptor names.

  • ADAPTOR_LIST — This is the same as ADAPTOR_LIST. If you want to configure multiple adaptors and ADAPTOR_LIST value has reached limits, use ADAPTOR_LIST1. Otherwise, copy the ADAPTOR_LIST value to ADAPTOR_LIST1.

    Note:

    Do not leave ADAPTOR_LIST1 value empty. If the variable is empty, it will cause ETL failure.

  1. In ODI Designer tree view, navigate to the Load Plans and Scenarios view and open the Data Lineage Extract and Load load plan under Predefined Load Plans, then Data Lineage.
  2. In the Steps tab, click the root step, DATA_LINEAGE.
  3. Enable the Overwrite option and type a value for DL_HOME, ADAPTOR_LIST and ADAPTOR_LIST1.

    Enclose Adaptor Names in single quotes and, if you want to enter multiple adaptors, use commas as separators between multiple adaptor names.

    Description of GUID-18405A27-919F-4D34-A38E-7AC3EC352005-default.gif follows
    Description of the illustration GUID-18405A27-919F-4D34-A38E-7AC3EC352005-default.gif

Setup Step: Configure the Oracle WebLogic Server Heap Size

Configure the Oracle WebLogic Server heap size.

Setting the Oracle WebLogic Server Heap Size in Windows

Set the heap size in Windows.

  1. Open the DOMAIN_HOME/bin/setDomainEnv.cmd file for editing.
  2. Locate the if NOT "%USER_MEM_ARGS%"=="" ( line, and add the following code before it: 
    if "%SERVER_NAME%"=="odi_server1" (
    set USER_MEM_ARGS=-Xms512m -Xmx3072m -XX:PermSize=256m -XX:MaxPermSize=512m
)

Setting the Oracle WebLogic Server Heap Size in Linux

Set the heap size in Linux.

  1. Open the DOMAIN_HOME/bin/setDomainEnv.sh file for editing.
  2. Locate the if [ "${USER_MEM_ARGS}" != "" ] ; then line, and add the following code before it: 
    if [ "${SERVER_NAME}" = "odi_server1" ] ; then
      USER_MEM_ARGS="-Xms512m -Xmx12288m -XX:PermSize=256m -XX:MaxPermSize=512m"
      export USER_MEM_ARG
fi

Setup Step: Disable Fusion Step (Optional)

If you are not using a Fusion source for the data warehouse, you must disable Fusion steps in the Data Lineage ETL. This step is applicable only for non-Fusion sources. If you use Fusion as a source, you can skip this step.

  1. In ODI Designer tree view, navigate to the Load Plans and Scenarios view and open the Data Lineage Extract and Load load plan under Predefined Load Plans, then Data Lineage.
  2. In the Steps tab, in the Steps Hierarchy, expand DATA LINEAGE, then Dimension_ETL.
  3. Deselect the Enabled check box for the Fusion step (step 28) to disable the step.
  4. Expand DATA LINEAGE, then Fact_ETL in the hierarchy and disable the Fusion step (step 52).
  5. Expand DATA_LINEAGE, Dimension_ETL, then OBIEE, and enable the SDE_DL_OBIEE_BMM_HIERARCHY step (step 3).

Setup Step: Create the Data Lineage Warehouse Tables

Use the Generate DataLineage DDL package to create the data lineage tables in the warehouse. These tables are created by default during data warehouse creation, but can be created at any time using this package.

  1. In the ODI Studio Designer Navigator, expand BI Apps Project, Components, DW, Oracle, Generate DW DDL, Generate DataLineageDDL, Scenarios, then ORACLE_GENERATE_DATALINEAGE_DDL.
  2. Select the ORACLE_GENERATE_DATALINEAGE_DDL and click the Execute (green arrow) button.
  3. In the Execution dialog box, choose Agent and click OK.
  4. In the Variable Values dialog box, change values for the third and fourth variables. Set BIAPPS.UTIL_GENDDL_RUN_DDL to 'Y' and set BIAPPS_UTIL_GENDDL_SCRIPT_LOCATION to a valid local folder location.

    Set BIAPPS.UTIL_GENDDL_RUN_DDL to Y and set BIAPPS_UTIL_GENDDL_SCRIPT_LOCATION to a valid local folder location.

    Description of GUID-9AB1F849-BD79-40B7-A7B5-C403BAA0CFD3-default.gif follows
    Description of the illustration GUID-9AB1F849-BD79-40B7-A7B5-C403BAA0CFD3-default.gif
  5. Click OK in the Variable Values dialog box and monitor the progress in the Operator.