Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Common Components of the Siebel Customer-Centric Enterprise Warehouse > Filtering and Deleting Records >

About Working with Primary Extract and Delete Mappings

The primary extract and delete mappings serve a critical role in identifying which records have been physically deleted from the source system. However, there are some instances when you can disable or remove the primary extract and delete mappings, such as when you want to retain records in the data warehouse that were removed from the source systems' database and archived in a separate database.

Because delete mappings use Source IDs and Key IDs to identify purged data, if you are using multiple source systems, you must modify the SQL Query statement to verify that the proper Source ID is used in the delete mapping. In addition to the primary extract and delete mappings, the configuration of the delete flag in the ADI also determines how record deletion is handled.

You can manage the extraction and deletion of data in the following ways:

• Deleting the configuration for source-archived records
• Deleting records from a particular source
• Enabling delete and primary-extract sessions
• Configuring the Record Deletion flag
• Configuring the Record Reject flag

This topic provides procedures for these management tasks.

Deleting the Configuration for Source-Archived Records

Some sources archive records in separate databases and retain only the current information in the main database. If you have enabled the delete mappings, you must reconfigure the delete mappings in the Siebel Customer-Centric Enterprise Warehouse to retain the archived data.

To retain source-archived records in the Siebel Customer-Centric Enterprise Warehouse, perform two tasks on each delete mapping:

1. Create a parameter for the archive date.
2. Edit the SQL Query field in the Source Qualifier.

   For a list of all delete sessions, see the discussion on disabling delete and primary extract sessions in About Working with Primary Extract and Delete Mappings.

To create a parameter for the archive date

1. In PowerCenter Designer, open the applicable source system configuration folder, and open a delete mapping.
2. Select Mappings > Parameters and Variables and select Add.
3. Enter $$ARCHIVE_DK as the name, and select Parameter for the type.
4. Select Date/Time for the data type, using the format that matches your source system.
5. Set the precision or scale required.
6. Double-click the Source Qualifier of your delete mapping.
7. Select the Variables tab, then select the parameter you just created, and click OK.

To add a clause to your Source Qualifier

1. In the delete mapping, open the Source Qualifier to edit the SQL Query field.
2. In the Properties tab, edit SQL Query field.

   Use the Archive Date as a filter in the WHERE clause.

   For example, if you were to create this statement for sales order lines, your clause would look like this:

   SELECT <column(s)> FROM OD_SALES_ORDLNS

   WHERE CREATED_ON_DK > $$ARCHIVE_DK AND NOT IN TO_SALES_ORDLNS_PE

3. Validate the SQL, and save your changes to the repository.

Deleting Records from a Particular Source

Delete mappings use Source IDs and Key IDs to identify the data that has been purged from the source system. Therefore, if you are using multiple source systems (where each type is identified by a Source ID), you must verify that the proper Source ID is used in the delete mapping so that only the desired records are deleted. To specify the correct source, edit the SQL statement in the delete mapping.

To specify the source for the delete mapping

1. In PowerCenter Designer, open the applicable source system configuration folder.
2. Open the delete mapping.
3. Open the Source Qualifier transformation to edit the SQL statement in the SQL Query field.

   Edit the SOURCE_ID expression in the OD table by adding a source abbreviation. A sample source abbreviation is shown in the following table.

   Source	Source Abbreviation for SOURCE_ID
   Oracle 11i	OAP11i

4. Validate the SQL statement and save your changes to the repository.

Enabling Delete and Primary Extract Sessions

If you want to remove your source-deleted records in the Siebel Customer-Centric Enterprise Warehouse, you need to enable the delete and primary extract sessions for your application.

To enable primary extract and delete sessions

1. In PowerCenter Workflow Manager, open the applicable source system configuration folder.
2. Double-click the session to open the Edit Tasks dialog box.
3. Edit the session, and then clear the Disable the task check box to enable the session.
4. Repeat these steps for each applicable primary extract and delete sessions.

Configuring the Record Deletion Flag

Record deletion is performed in the ADI if there are records you want to delete regardless of whether or not they have been deleted from the source. Record deletion is also performed in the ADI when the primary extract cannot determine if records have been deleted from the source, as in the case of Web logs where there are no primary keys for the extract to recognize. The Delete Flag determines how the record deletion is handled.

You can configure the Delete Flag in the Source Adapter mapplet by modifying the transformation for the EXT_DELETE_FLAG port. To reconfigure the handling of deletions, you can modify the Delete Flag definition. There are different values that you can use when defining your Delete Flag; these values depend on whether you are dealing with a fact table or a dimension table.

When you define the Delete Flag for fact tables, it is recommended you use a conditional statement. For example, you could enter the following statement:

IIF(<SOURCE>.<COLUMN_NAME> = 'Y', 'Y', 'N')

For fact tables, there are two values for which you can set the Delete Flag—Y and N. By setting your Delete Flag to Y, records that already exist in the data warehouse are purged from the fact table by the use of delete mappings. By setting your Delete Flag to N or any other value besides Y, your records are not deleted. If the record is marked as deleted by the source and has not yet been loaded into the data warehouse, then the record is not loaded.

When defining the Delete Flag for dimension tables, it is recommended that you use a conditional statement as well. For example, you could enter the following statement:

IIF(<SOURCE>.<SOME_COLUMN_NAME> = 'Y', 'D', 'N')

For dimension tables, there are two values for which you can set your Delete Flag—D and N. By setting your Delete Flag to D, your records are marked for deletion, but are not be purged from the dimension table just in case you want to query these values at a later time. If you wish to analyze historical dimension records, you must enable Type II functionality, which updates records by inserting a new record and leaving the old record intact. For more information about Type II dimensions, see Type I and Type II Slowly Changing Dimensions.

NOTE:  If you set the Delete flag as P for a dimension record, the deletion logic behaves as if it was marked as D. No dimensions are ever purged from the data warehouse.

To configure the Delete Flag

1. In PowerCenter Designer, open the applicable source system configuration folder.
2. Open the applicable Source Adapter mapplet.
3. Double-click the Expression transformation to open the Edit Transformations box.
4. In the Ports tab, edit the expression for the EXT_DELETE_FLAG port.

   For example, if your source system sets the document type to DEL when a record is to be deleted, this expression contains a statement similar to the following:

   IIF (DOCUMENT_TYPE = 'DEL', 'D', 'N')

5. Validate and save your changes to the repository.

Configuring Record Reject Flag

The purpose of rejecting records is to verify that they are not loaded into the data warehouse. You can set up the rejection logic in one of two places—the Source Qualifier in the Business Component mapplet of an extract mapping or the Source Adapter mapplet of a load mapping.

By default, the Siebel Customer-Centric Enterprise Warehouse provides a reject flag in the Source Adapter mapplet that you can use to set up your record rejection logic. If the Reject Flag is set to Y for any records, the ADI skips those records and does not load it into the data warehouse. However, if the Reject Flag is set to N or any other value, the ADI processes the record. The reject logic must be configured in the Source Adapter mapplets according to your requirements.

You can configure the Reject Flag in the Source Adapter mapplet by modifying the transformation for the port EXT_REJECT_FLAG.

NOTE:  If you want to set up the rejection logic in the Source Qualifier in the extract mapping, you can do so. The Siebel Customer-Centric Enterprise Warehouse performs this in the load mapping because some extract mappings load multiple staging tables in the data warehouse.

To configure the Reject flag

1. In PowerCenter Designer, open the applicable source system configuration folder.
2. Open the applicable Source Adapter mapplet.
3. Double-click the Expression transformation to open the Edit Transformations box.
4. In the Ports tab, edit the expression for the EXT_REJECT_FLAG port.
5. Validate and save your changes to the repository.