Configuration Guide

10.4 HotFix 3

Back Next

Configuring Hard Delete Detection

You can configure the

MDM Hub

to detect hard deletes in the source systems.

To configure the

MDM Hub

to detect hard deletes, you need to create the hard delete detection table and register the job metric type. You must also set up landing and staging tables, and create mappings. After you set up the landing and staging tables, populate the hard delete detection table with the staging table information. Finally, implement a user exit that calls hard delete detection functionality to detect hard deleted records.

You can combine all the SQL statements that are provided to configure the hard delete detection table and run them as a single set of SQL statements.

Configure the hard delete detection table.

To create the hard delete detection table, run the following SQL statement:

On Oracle.

CREATE TABLE C_REPOS_EXT_HARD_DEL_DETECT
(
  ROWID_TABLE               CHAR(14 BYTE),
  HDD_ENABLED               INTEGER             DEFAULT 0           NOT NULL,
  HDD_TYPE                  VARCHAR2(14 BYTE),
  DELETE_FLAG_COLUMN_NAME   VARCHAR2(30 BYTE),
  DELETE_FLAG_VAL_ACTIVE    VARCHAR2(14 BYTE),
  DELETE_FLAG_VAL_INACTIVE  VARCHAR2(14 BYTE),
  DELETE_FLAG_VAL_PARTIAL   VARCHAR2(14 BYTE),
  HAS_END_DATE              INTEGER             DEFAULT 0,
  END_DATE_COLUMN_NAME      VARCHAR2(30 BYTE),
  END_DATE_VAL              DATE																DEFAULT 'SYSDATE',
  TRAN_HDD_ENABLED          INTEGER             DEFAULT 0
  HDD_LANDING_PKEY_COLUMNS  VARCHAR2(<TBD> BYTE),
  EMPTY_LANDING_TABLE_CHECK_OFF     INTEGER     DEFAULT 0
);

On Microsoft SQL Server.

CREATE TABLE [dbo].[C_REPOS_EXT_HARD_DEL_DETECT]
(
	[ROWID_TABLE] 													[nchar](14) 			NOT NULL,
	[HDD_ENABLED] 													[bigint] 						NOT NULL,
	[HDD_TYPE] 																[nvarchar](14) NULL,
	[DELETE_FLAG_COLUMN_NAME] 	[nvarchar](30) NULL,
	[DELETE_FLAG_VAL_ACTIVE] 		[nvarchar](14) NULL,
	[DELETE_FLAG_VAL_INACTIVE] [nvarchar](14) NULL,
	[DELETE_FLAG_VAL_PARTIAL] 	[nvarchar](14) NULL,
	[HAS_END_DATE] 												[bigint] 						NULL,
	[END_DATE_COLUMN_NAME] 				[nvarchar](30) NULL,
	[END_DATE_VAL] 												[datetime2](7) NULL,
	[TRAN_HDD_ENABLED] 								[bigint] 						NULL,
 [HDD_LANDING_PKEY_COLUMNS] [nvarchar](<TBD>) NULL,
 [EMPTY_LANDING_TABLE_CHECK_OFF] 								[bigint] 						NULL
) 
 ON [CMX_DATA]

To add constraints to the hard delete detection table, run the following SQL statements:

On Oracle.

ALTER TABLE C_REPOS_EXT_HARD_DEL_DETECT ADD (
  CHECK (HDD_TYPE IN ('DIRECT','CONSENSUS')));

ALTER TABLE C_REPOS_EXT_HARD_DEL_DETECT ADD (
  CHECK (HDD_ENABLED IN (0,1)));

ALTER TABLE C_REPOS_EXT_HARD_DEL_DETECT ADD (
  CHECK (HAS_END_DATE IN (0,1)));

ALTER TABLE C_REPOS_EXT_HARD_DEL_DETECT ADD (
  UNIQUE (ROWID_TABLE));

On Microsoft SQL Server.

ALTER TABLE [dbo].[C_REPOS_EXT_HARD_DEL_DETECT] ADD  CONSTRAINT [CH_C_REPOS_EXT_HARD_DEL_DETECT_HDD_TYPE]  CHECK (HDD_TYPE IN ('CONSENSUS','DIRECT'))
GO
ALTER TABLE [dbo].[C_REPOS_EXT_HARD_DEL_DETECT] ADD  CONSTRAINT [CH_C_REPOS_EXT_HARD_DEL_DETECT_HDD_ENABLED]  CHECK (HDD_ENABLED IN (0,1))
GO
ALTER TABLE [dbo].[C_REPOS_EXT_HARD_DEL_DETECT] ADD  CONSTRAINT [CH_C_REPOS_EXT_HARD_DEL_DETECT_HAS_END_DATE]  CHECK (HAS_END_DATE IN (0,1))
GO
ALTER TABLE [dbo].[C_REPOS_EXT_HARD_DEL_DETECT] ADD  CONSTRAINT [UQ_C_REPOS_EXT_HARD_DEL_DETECT_ROWID_TABLE]  UNIQUE (ROWID_TABLE)
GO

To create a foreign key constraint on the ROWID_TABLE column of the hard delete detection table to the ROWID_TABLE column of C_REPOS_TABLE, run the following SQL statements:

On Oracle.

ALTER TABLE C_REPOS_EXT_HARD_DEL_DETECT ADD (
  CONSTRAINT FK_ROWID_TABLE_FOR_HDD FOREIGN KEY (ROWID_TABLE) 
    REFERENCES C_REPOS_TABLE (ROWID_TABLE)
    ON DELETE CASCADE);

On Microsoft SQL Server.

ALTER TABLE [dbo].[C_REPOS_EXT_HARD_DEL_DETECT] ADD  CONSTRAINT [FK_ROWID_TABLE_FOR_HDD]  
FOREIGN KEY (ROWID_TABLE) REFERENCES [dbo].[C_REPOS_TABLE](ROWID_TABLE) ON DELETE CASCADE
GO

Define the foreign key with the ON DELETE CASCADE clause so that the

MDM Hub

deletes the appropriate metadata if the parent record is removed from C_REPOS_TABLE.

Run the following SQL statement to register the job metric type code as 100:

On Oracle.

INSERT INTO C_REPOS_JOB_METRIC_TYPE
  (METRIC_TYPE_CODE, CREATE_DATE, CREATOR, LAST_UPDATE_DATE, UPDATED_BY, METRIC_TYPE_DESC, SEQ)
VALUES
  (100, SYSDATE, 'hdd', SYSDATE, 'hdd', 'Flagged as Deleted', 100);

On Microsoft SQL Server.

INSERT INTO [dbo].[C_REPOS_JOB_METRIC_TYPE]
           ([METRIC_TYPE_CODE],[CREATE_DATE],[CREATOR],[LAST_UPDATE_DATE],[UPDATED_BY],[METRIC_TYPE_DESC],[SEQ])
     VALUES
           (100,getDate(),'HDD',getDate(),'HDD','Flagged as Deleted',100)
GO

Configure the landing and staging tables.

Determine the base object records that need delete flags and end dates.

Verify that you included all the landing and staging table columns that provide data to the base objects.

In the

Schema

tool of the Hub Console, verify that the value you specify for

DELETE_FLAG_ COLUMN_NAME

in the hard delete detection table is visible. Also, verify that the default value is A, I, or P.

If the record is active, and if the record has a end date, verify if the end date is null or a system date.

If you provide a null value for the end date, enable the

Allow Null Update

check box for the end date column.

In the

Mappings

tool of the Hub Console, map the columns between landing and staging tables.

In the

Schema

tool of the Hub Console, enable delta detection for the staging table for which you need to detect hard deletes.

If you enable the option to detect deltas by using specific columns, include the delete flag column name in the column list to detect deleted records.

To define the metadata in the hard delete detection table, run the following SQL statement:

On Oracle.

INSERT INTO C_REPOS_EXT_HARD_DEL_DETECT (
   ROWID_TABLE, HDD_ENABLED, HDD_TYPE, 
   DELETE_FLAG_COLUMN_NAME, DELETE_FLAG_VAL_ACTIVE, DELETE_FLAG_VAL_INACTIVE, 
   DELETE_FLAG_VAL_PARTIAL, HAS_END_DATE, END_DATE_COLUMN_NAME, 
   END_DATE_VAL, TRAN_HDD_ENABLED, HDD_LANDING_PKEY_COLUMNS) 
 SELECT
    ROWID_TABLE, 1, <Column to enable hard delete detection>,'<hard delete detection type such as DIRECT or CONSENSUS>', 
    '<name of the delete flag column>', '<Value of delete flag column for active records>', '<Value of delete flag column for inactive records>',
    '<Value of delete flag column for partially active records>', <Indicator whether end date is used in hard delete detection or not>,
    <End date column name if staging table has end date column>, '<End date value as MM/DD/YYYY>', <Transactional Hard Delete Detection indicator>,
    '<Comma-separated list of column names that contribute to the primary key>',
    FROM C_REPOS_TABLE WHERE table_name = '<Staging table name>'

On Microsoft SQL Server.

INSERT INTO [dbo].[C_REPOS_EXT_HARD_DEL_DETECT]
           ([ROWID_TABLE]
           ,[HDD_ENABLED]
           ,[HDD_TYPE]
           ,[DELETE_FLAG_COLUMN_NAME]
           ,[DELETE_FLAG_VAL_ACTIVE]
           ,[DELETE_FLAG_VAL_INACTIVE]
           ,[DELETE_FLAG_VAL_PARTIAL]
           ,[HAS_END_DATE]
           ,[END_DATE_COLUMN_NAME]
           ,[END_DATE_VAL]
           ,[TRAN_HDD_ENABLED]
           ,[HDD_LANDING_PKEY_COLUMNS])
SELECT
    [ROWID_TABLE]
				,1--Column for which hard delete detection must be enabled
				,'<hard delete detection type such as DIRECT or CONSENSUS>'
				,'<name of the delete flag column>'
				,'<Value of delete flag column for active records>'
				,'<Value of delete flag column for inactive records>'
				,'<Value of delete flag column for partially active records>'
				,<Indicator whether end date is used in hard delete detection or not>
				,<End date column name if staging table has end date column>
				,'<End date value>'
				,<Transactional Hard Delete Detection indicator>
    ,'<Comma-separated list of column names that contribute to the primary key>'
FROM [dbo].[C_REPOS_TABLE] WHERE table_name = '<Staging table name>'
GO

Implement user exits for hard delete detection.

Implement Post Landing and Post Stage Java user exit and add logic to the implementation to call the hard delete functionality.

The user exit interfaces to implement are in

mdm-ue.jar

Package the implemented Post Landing and Post Stage user exit classes into a JAR file.

Upload the packaged JAR file to the

MDM Hub

by registering the user exits.

Hard Delete Detection

Download Guide

Watch

Comments