X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Special use cases for Replication to SDS via OGG

Authors: "Guna Vasireddy - Oracle","Sai Rajendra Vadakattu - Oracle ", "Ankur Jain - Oracle"

 

Replicating Table without Primary Key
Replicating Views
Replicating OLTP Packages
 

 

Replicating Table without Primary Key:

Use Case:

Customer is using Oracle Golden Gate (OGG) to replicate the Source to SDS and would like to configure BIAPPS to use the SDS instead of the Source. Note: The SDS table differs from the source table in primarily two things:

  1. BIAPPS expects CDC columns in the SDS. These are used by the BIAPPS mappings. These columns are automatically generated when you use the  BIAPPS provided tools to create the SDS tables. Refer to the BIAPPS documentation for more details.
  2. Hard Deletes in source get translated as soft deletes in the SDS. The column CDC$_DML_CODE=’D’ for such rows in the SDS.

Issue:

Some of the source tables that are used by BIAPPS, neither have a primary key in the Source nor have a key defined in the BIAPPS ODI repository. At times, the source system enforces the primary key using the application layer and does not have primary keys at the database level. In such cases, the key would have been identified and mentioned in the BIAPPS ODI repository. And the BIAPPS documentation specifies how to configure OGG to use those columns using the keycols clause. There are other source tables that don't have a primary key at all. An example is the MTL_TRANSACTION_ACCOUNTS table in EBS. This table does not have a primary/unique key or a combination of columns that would uniquely identify a row. This blog details how such tables can be replicated. 

Solution:

Follow the below steps to be able to replicate a table without Primary Keys using OGG. Essentially we ask OGG to treat all the columns in the table as the identifier using keycols clause.

  1. Make a list of all the columns present in the table. Using the list prepare the keycols clause for OGG including all the columns.
  2. Edit the replicat param file to specify the keycols clause for the tables being replicated. Restart OGG so that the new replicat file becomes effective.
  3. Do a full replication of the table initially using either dblink or other methods (like expdp) as would have been done with other tables. Subsequently use OGG to perform the incremental replication.
  4. If the table is known to have large volumes, then to improve performance, consider creating a regular index on the join columns. These are usually the columns on which the table is joined with other tables in the SDE maps. Create this after doing the initial full replication of the table.

Following is the observed behavior when using the above solution

 

Action on Source

Result in Target Db

Results as Expected

Insert
records

Record
inserted

Y

Update
record

Existing
record get updated

Y

Delete
record

Existing
record gets marked for delete CDC$_DML_CODE=’D’

Y

Have two duplicates rows in source. These match in all columns.

Perform Inserts/updates/deletes and check

Have same two duplicates rows in target.

It is randomly picking one row out of the two rows for updates/deletes

N

So as long as there are no duplicates in the tables (i.e. rows having values matching in all columns), the above approach works fine. It is unlikely that the source table will have two exact duplicate rows and so this approach should be expected to work. The above is explained by taking the example for one EBS table, MTL_TRANSACTION_ACCOUNTS, which falls under this category. The Replicat file is modified to include all the columns.

Sample Replicat file:

--------------------------------------------------
MTL_TRANSACTION_ACCOUNTS

------------------------------------------------

-- Process Inserts --

GETINSERTS

IGNOREUPDATES

IGNOREDELETES

MAP
EBS_SRC.MTL_TRANSACTION_ACCOUNTS, TARGET SRC_REPLICA.MTL_TRANSACTION_ACCOUNTS

,COLMAP(USEDEFAULTS)

,CDC$_RPL_LAST_UPDATE_DATE = @DATENOW (),

CDC$_SRC_LAST_UPDATE_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),

CDC$_DML_CODE = 'I')

,keycols(TRANSACTION_ID,REFERENCE_ACCOUNT,LAST_UPDATE_DATE,LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_VALUE, PRIMARY_QUANTITY, GL_BATCH_ID, ACCOUNTING_LINE_TYPE, BASE_TRANSACTION_VALUE, CONTRA_SET_ID, RATE_OR_AMOUNT, BASIS_TYPE, RESOURCE_ID,  COST_ELEMENT_ID, ACTIVITY_ID, CURRENCY_CODE, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_RATE, REQUEST_ID,  PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,  ENCUMBRANCE_TYPE_ID, REPETITIVE_SCHEDULE_ID, GL_SL_LINK_ID, USSGL_TRANSACTION_CODE, INV_SUB_LEDGER_ID);

-- Process Updates --

GETUPDATES

IGNOREINSERTS

IGNOREDELETES

MAP EBS_SRC.MTL_TRANSACTION_ACCOUNTS, TARGET SRC_REPLICA.MTL_TRANSACTION_ACCOUNTS , COLMAP
(USEDEFAULTS)

,CDC$_RPL_LAST_UPDATE_DATE = @DATENOW (),

CDC$_SRC_LAST_UPDATE_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),

CDC$_DML_CODE = 'U')

,keycols(TRANSACTION_ID,REFERENCE_ACCOUNT,LAST_UPDATE_DATE,LAST_UPDATED_BY, CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_DATE,TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_VALUE,PRIMARY_QUANTITY,GL_BATCH_ID, ACCOUNTING_LINE_TYPE, BASE_TRANSACTION_VALUE,CONTRA_SET_ID, RATE_OR_AMOUNT, BASIS_TYPE,RESOURCE_ID ,COST_ELEMENT_ID, ACTIVITY_ID,CURRENCY_CODE, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE,CURRENCY_CONVERSION_RATE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,ENCUMBRANCE_TYPE_ID, REPETITIVE_SCHEDULE_ID, GL_SL_LINK_ID,USSGL_TRANSACTION_CODE,INV_SUB_LEDGER_ID)

;

 

-- Process  Deletes --

IGNOREINSERTS

IGNOREUPDATES

GETDELETES

UPDATEDELETES

MAP EBS_SRC.MTL_TRANSACTION_ACCOUNTS, TARGET  SRC_REPLICA.MTL_TRANSACTION_ACCOUNTS

,COLMAP (USEDEFAULTS)

,CDC$_RPL_LAST_UPDATE_DATE = @DATENOW (),

CDC$_SRC_LAST_UPDATE_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),

CDC$_DML_CODE = 'D')

,keycols(TRANSACTION_ID,REFERENCE_ACCOUNT,LAST_UPDATE_DATE,LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_VALUE, PRIMARY_QUANTITY, GL_BATCH_ID, ACCOUNTING_LINE_TYPE, BASE_TRANSACTION_VALUE, CONTRA_SET_ID, RATE_OR_AMOUNT,BASIS_TYPE,RESOURCE_ID, COST_ELEMENT_ID, ACTIVITY_ID, CURRENCY_CODE, CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_RATE, REQUEST_ID, PROGRAM_APPLICATION_ID,  PROGRAM_ID, PROGRAM_UPDATE_DATE, ENCUMBRANCE_TYPE_ID, REPETITIVE_SCHEDULE_ID, GL_SL_LINK_ID, USSGL_TRANSACTION_CODE, INV_SUB_LEDGER_ID)

;

Suggested columns for indexes in SDS:

Looking at columns used as joins for this table, TRANSACTION_ID is a likely candidate for an index on the SDS.

Table Name

Suggested Columns for index

MTL_TRANSACTION_ACCOUNTS

TRANSACTION_ID

Note: These are regular indexes and not unique/primary keys and should help with the mappings that extract from this SDS table.

FAQs:

1) If I don't specify any keycols, Golden Gate is supposed to use all the columns as key columns. How is this different from that?

Not specifying any keycols is the same as specifying keycols on all the columns. Specifying keycols however make it clear to the person reviewing it as to how OGG will do the replication.

2) Is there any performance impact due to this?

OGG will need to store the values against all the columns inorder to find the row in the target to perform the same operation. So there definitely will be an overhead. So where primary keys are available, you should use them. Use this approach only for cases where it is not possible to identify such a key

3) I don't have a primary key index on my source and we are not allowed to modify the Source tables. However I know the columns that make up the primary key. What do I do?

If you have a primary/unique key/index defined on Source/Target, OGG will automatically use them. In the absence of such keys, OGG by default will treat all columns as keycols as specified above. However if you do know the columns that make up the primary key and do not want to define the primary key index at the database level, then you can use the keycols clause detailed above to specify only those columns. In other words keycols can be used to specify which subset of columns make up the primary key in the source/target. And that would be more efficient than specifying all the columns.

 

4) If we specify all columns as keycols, won't we end up with new rows everytime there is an update to the source

That is not how keycols work. Keycols specify the columns that will be used to find the record in the target and then the operation that was performed on the source, will be applied on that row in the target. Consider a case where source has three columns and a row with col1=a,col2=b,col3=c. In the source col2 is now updated to b2. The operation OGG will do is roughly equivalent to this SQL

update table set col2=b2 where col1=a,col2=b and col3=c

 So OGG uses the values of the columns mentioned in keycols to identify the row in the target and then applies the same operation to that row.

Replicating Views:

Use Case:

In case of golden gate as the replication technology, the mappings which use a view as source are set to run in non-SDS mode, and such mappings will try to directly connect to source OLTP. If customer has security restriction on direct connection to source OLTP, then such mappings will fail.

Solution:

Following workaround can be used for running such mappings.

Create
tables/materialized view on top of views in the OLTP schema and trigger a process to refresh these objects in OLTP before ETL starts, Replicate these MVs to target tables in the SDS using OGG.
There are few views from the source which are used in the BI ETL. List of such views are provided at the end. SDS schema should already have these views as tables.

First time replication should be full load of views to target SDS tables, Populate CDC$_SRC_LAST_UPDATE_DATE and CDC$_DML_CODE with current date and ‘I’ respectively. For incremental, A materialized view can be created on source OLTP on top of view

CREATE MATERIALIZED VIEW test_mv BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND AS select * from test_v;

 

In above sql command, REFRESH can be set to FORCE or COMPLETE. For any change in the underlying view definition, MV might need to be recreated.

A procedure/process need to refresh the MV before the BI Load Plan runs. Use Oracle DB procedure DBMS_MVIEW.REFRESH to achieve the same.

For replicate parameter file, As the OGG Replicat param files for OBIA are generated using an ODI procedure, from the generated file remove the complete entry for these views, And add a normal entry like

------------------------------------------------
-- Test_v
------------------------------------------------

MAP ggtest.test_mv; TARGET ankur_test.test_v,COLMAP (USEDEFAULTS, CDC$_SRC_LAST_UPDATE_DATE = @GETENV ('GGHEADER','COMMITTIMESTAMP'),CDC$_DML_CODE = 'I'),KEYCOLS(COL1,COL2,COL3);

If no PK is being defined at target, you should choose appropriate key columns and define them in OGG in replicat processes using KEYCOLS keyword. In ODI, source OLTP connection should be altered to point to SDS itself.

 

Note: If faced with performance issues (If data is huge in views). Users should explore alternate approach to achieve this view replication.

List of such views in E-Business Suite
PO_VENDORS
JTF_TASK_ASSIGNMENTS
GL_SETS_OF_BOOKS
ORG_ORGANIZATION_DEFINITIONS
BOM_BILL_OF_MATERIALS
MTL_ITEM_LOCATIONS_KFV
CST_ORGANIZATION_DEFINITIONS
CS_LOOKUPS
PA_EGO_LIFECYCLES_PHASES_V
GL_CODE_COMBINATIONS_KFV
PON_AUCTION_HEADERS_ALL_V
MTL_ITEM_CATALOG_GROUPS_B_KFV
AP_INVOICES_V
PER_WORKFORCE_CURRENT_X


List of such views in PeopleSoft
CM_ITEM_METH_VW
DEPENDENT_BENEF
EMPLOYMENT
PERSON_ADDRESS

 

Replicating OLTP Packages:

There might be few cases where ODI maps are using the package or function from OLTP. Since these would be not be available in the SDS, these mappings cannot be run against the SDS normally.

Solution:

These need to be handled on case to case basis. Technical notes for such cases are available on oracle support.For example, Units of Measure (UOM) is not supported for SDS based E-Business Suite source. A tech note is available for the OTBI-E, but same with few changes can be leveraged for OBIA.

http://docs.oracle.com/applications/biapps102/relnotes/GUID-A4A75F7A-8AFC-4279-9AE1-1448AEEE25F2.htm#BIARL-GUID-4E9C40DF-6A1D-4521-8AEF-6D67D8EAED69

Similar to above tech note, A table can be created called W_SDS_UOM_CONVERSION in EBS source system; same as what is defined in SDS schema.  You can develop a stored procedure which will truncate the table and load it with each of the four SQL statements as given in the above tech note (remove all the to_char conditions from the select clause, as that is used for generating CSV files).  This stored procedure should ultimately be scheduled to run prior to the running of the load plan.  Then configure GoldenGate to extract and replicate this table to the SDS.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.