Siebel Business Analytics Applications Installation and Administration Guide > DAC Quick Start > DAC Common Tasks >

Tracking Deleted Records

The Siebel Data Warehouse change capture process uses delete triggers to identify records for deletion on the Siebel transactional database. The deleted records are stored in S_ETL_D_IMG tables. During the change capture process, the DAC server moves the data from the S_ETL_D_IMG tables to the S_ETL_I_IMG tables, where D appears in the OPERATION column to show the records were deleted. During the change capture sync process, the records in the S_ETL_D_IMG tables that were moved to the S_ETL_I_IMG tables are flushed. In the DAC, you can view the SQL that runs during the change capture and change capture sync processes by navigating in the DAC to Design > Task Definitions > Description.

The preconfigured ETL process captures deleted records for the target tables W_ORG_D and W_PERSON_D, the source tables for which are S_ORG_EXT, S_CONTACT, and S_PRSP_CONTACT. These source tables need to have delete triggers created in the Siebel transactional database in order for deleted records to be tracked.

For vertical applications, the preconfigured ETL process captures deleted records for W_FUND_F and W_ALIGNMT_DH. You need to create delete triggers in the transactional database for the following additional tables: S_MDF_TXN, S_ASGN_GRP_POSTN, S_ASGN_RULE_ITEM.

In the Siebel Data Warehouse, preconfigured visibility tables are inactivated. If you activate visibility tables, you should also create delete triggers on the optional tables. You can activate visibility tables in the DAC by navigating to Design > Tables.

The preconfigured SIA Account and Contact visibility tables are activated by default for vertical applications. If your organization is not going to use any of the visibility tables, you need to inactivate them in the Tables tab of the Design view in the DAC client.

On the target tables for which deleted records are tracked, a D appears in the INACTIVE_FLG column to show the records as deleted when the source records are deleted. This method of flagging a record as deleted is known as a soft delete, as compared to a hard delete when the record is physically deleted. When deleted records are tracked on visibility-related data warehouse tables, the records are physically deleted. The general rule is that soft deletes should be used for tables that are referenced by other tables. If a table is not referenced by any other table, then you can use hard deletes.

Aggregate tables are rebuilt during each ETL process. Therefore, records can be physically deleted from the base tables without consequence. If you want to use the soft delete method, you should consider changing the aggregate building mappings so that the deleted records are omitted.

NOTE:  The Analytics Server does not recognize soft deletes. Therefore, you have to modify the .rpd file so that it does not pick up soft-deleted records for reporting.

To create delete triggers for preconfigured ETL change capture

1. From the DAC menu bar, click Tools > ETL Management > Configure.
2. In the Sources dialog box, select the database platform for the target and transactional databases, and click OK.
3. In the Data Warehouse Configuration Wizard, select the Create Delete Triggers in Transaction Database check box, and click Next.

   The Delete Triggers tab is active.

4. Select one of the following:

   Option	Description
   Create Triggers	Executes the trigger statements directly.
   Write Script to File	Writes the trigger statements to a file, which can be executed by a database administrator.

5. Select the database type as defined in the DAC.
6. For DB2 zSeries databases, enter the base table owner.
7. (Optional) Select the Include Optional Triggers check box to create triggers for the optional tables.
8. Click Start.

To create delete triggers for new source tables

1. In the DAC, navigate to Design > Tables.
2. Select the table for which you want to track deleted records.

   Make sure the table has an image suffix.

3. Right-click the table and select Change Capture Scripts > Generate Image and Trigger Scripts.
4. In the Triggers and Image Tables dialog box, select the database type of the source database.
5. Make sure the Generate Image Table Scripts and Generate Trigger Script(s) options are selected.
6. Execute the script on the database.

To track deleted records

1. Make sure the delete triggers are enabled for the appropriate tables.
2. Write custom Informatica workflows with a clause WHERE operation = 'D' to the appropriate I_IMG table to take them across to the dimension and fact tables.
3. In the DAC, register the workflows as tasks.
4. Define the appropriate dependencies.

   For an example of such a workflow, see the preconfigured task SDE_OrganizationDimension_LoadDeletedRows.