3 Modes for Moving Data to the BI Applications DW from a Source Application Database

In BI Applications 11.1.1.7.1 the adaptors for the following product lines use the LKM BIAPPS SQL to Oracle (Multi Transport) to move the data from the source Application database to the target BI Applications DW database:

•  E-Business Suite
•  Siebel
•  PeopleSoft
•  JDE

A key feature of this LKM developed specifically for BI Applications is that the data from the source system may be transported in 3 different ways and using a parameters set in Configuration Manager the mode can be selected to suit how the system has been setup, thereby optimizing ETL performance.  This blog post details those 3 modes, and how to configure BI Applications to use the mode that best suits the deployment.
The transport modes are:

• JDBC mode - the data is moved via JDBC from the source up to the ODI Agent and from the ODI Agent down to the Target.  The ODI Agent transfers the data into a dynamically created temporary target table.  This is the default mode.
• Database Link mode - the data is moved over a predefined database link.  A view is created on the source Oracle database, and a synonym is used instead of a temporary target table to reference the data across the database link
• SDS mode - the data is referenced directly from an SDS replicated table.  The SDS table is continuously and asynchrounously replicated using Oracle GoldenGate. A view is used instead of a temporary target table and the view references the source data in the local SDS schema.

The default mode is selected as it works in all deployments, however the other modes if they've been setup and enabled allow significant improvements in ETL performance.  The following details each mode.

JDBC Mode

This is the default mode based on the default settings for the underlying variables. In this mode data is transferred from the source via JDBC. This is the slowest mode amongst the three. Use this mode when you do not have SDS and when your source is non-Oracle. The Array Fetch Size against the Physical Data Server being used to run the interface can impact the performance of this mode. Refer to ODI documentation on more about that parameter.

JDBC Mode is used when KM option OBI_SRC_VIA_DBLINK (default variable ETL_SRC_VIA_DBLINK) is not enabled (N) and variable IS_SDS_DEPLOYED is not enabled (N)

Database Link Mode

In this mode data is transferred from the source via a DB Link. This mode is usually faster than the JDBC mode assuming the network between the source and the warehouse remains constant. This KM is controlled via the option OBI_SRC_VIA_DBLINK is defaulted to variable ETL_SRC_VIA_DBLINK. When the ETL Parameter ETL_SRC_VIA_DBLINK is set to Yes, and SDS is not enabled (see next section), then transport is done via db link.  Database Link Mode can be used only when the source database is Oracle and a compatible DB Link can be created from the Oracle Warehouse to the source. The LKM does not create a DB Link. It instead relies on a db link already existing with the naming convention followed below:

<DATASERVER_NAME>.WORLD@DSN_<DSN_ID>

So if the Data Server name for EBS is EBS11510 and the DSN for EBS11510 is 310, then the db link name should be:

EBS11510.WORLD@DSN_310

This DB Link should exist in the warehouse and be accessible using the warehouse credentials (user specified against the warehouse connection in ODI). For details of what a Data Server is and how it's defined please take a look at this prior blog post: Registering BI Applications Data Sources

Database Link Mode is used when option OBI_SRC_VIA_DBLINK (default variable ETL_SRC_VIA_DBLINK) is enabled (Y) and variable IS_SDS_DEPLOYED is not enabled (N)
The ETL Parameter ETL_SRC_VIA_DBLINK is set in Configuration Manager in the Manage Data Load Parameter screen and can be set for the source Instance.

SDS Mode

When the IS_SDS_DEPLOYED variable is set to Y, the LKM uses the SDS as the source. A view is created pointing to the SDS schema and that view is used to insert data into the target. SDS resides on the same database as the warehouse. This is the fastest mode as no data is moved over the network.  Use this mode only when you have a SDS deployed and populated.  Refer to the Bi Applications Administrator's Guide and the Section on Administering Oracle GoldenGate and SDS for more details on how to setup the SDS and populate the SDS using Oracle GoldenGate.

SDS Mode is used when the variable IS_SDS_DEPLOYED is enabled (Y)
The Parameter IS_SDS_DEPLOYED is set in Configuration Manager in the Manage Data Load Parameter screen. and can be set either for the Instance or for each individual Dimension or Fact Group for more granular control.  Note if IS_SDS_DEPLOYED is not enabled for a Fact Group or Dimension whilst others for that same Instance are enabled, then the LKM will fall back to the other mode, i.e if ETL_SRV_VIA_DBLINK is enabled it will used the Database Link Mode, otherwise it will used the JDBC Mode.