Siebel Data Purging/Archival Strategies

Key Considerations: -

1.      Identify the Data that needs to be Archived ( For this, we need to identify the Parent-child Entity relationships and the corresponding Tables. Ex: Service Request which are closed beyond Certain period of time (Business has to define this) and the corresponding child Activities and Attachments.

2.      Export the Identified data to any Flat File or CSV File

3.      Purge the Identified Data in the Siebel Data Base

4.      Load the Archived data (from Flat File or CSV File) into External Data Base using data Loader (Note: Target database structure like database Tables, Columns, Referential Integrity etc. needs to analyzed)

5.      Using VBC pull the Archived data into Siebel from the External System on a Need Basis.

For Identifying the Archive data and Export, below are the options through which we can do:

1.1   Using Business Rules and Validations, build a SQL query to fetch the data and write it in a Flat files in Unix Environment ( Writing a .KSH file which intern calls the Stored procedure to fetch the data into Flat File)

1.2   Export the Data from Siebel DB to CSV file using a Workflow

Query the Records using EAI Siebel Adapter - QueryPage Method with Search Spec (as per business validation)

              Convert the Output to XML Hierarchy using EAI Integration Object to XML Hierarchy Converter with Output as XML Hierarchy

Write the Output message to a File using EAI XML Write to File

Convert the output file to CSV file using EAI XSLT Service Business Service

Put the Output CSV file in a location using EAI File Transport Business Service

Note: To check the last record in the Query Page, we have to put the logic in the WF with Decision Box as If Last Record(Process property from Query by Page) == True then End the Workflow, else [&Iteration Counter] + [&Number of Output Objects]

Data Purging:

1. Identify relationship and the tables.

2. Identify the corresponding EIM Tables

3. Use EIM to run export process and load EIM tables.(Make sure that these have all required columns which you need later to import data). We can do this from either of the below options

Option 1: Create a stored procedure with cursor Query to load that data into EIM Tables

Option 2: we have the SQL Loader utility to load that data into EIM tables. For this, we need to pass Control File (tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into EIM Tables) and Input data File ( input text file that contains the data that needs to be loaded) as an inputs.

              SQL Loader gives two Output Files 1. Log File ( No of records inserted) and BAD File ( No of records rejected)

4. Create an IFB file with EIM TABLE, TYPE and BATCH NUMBER.

              we have to use Type = DELETE EXACT (which deletes only those records which are present in EIM table) for the better tractability instead of DELETE MATCHES and DELETE CASCADE

5. Run the IFB/EIM job.

Display the Archived Data in Siebel Applets:

Create a VBC in Siebel and display the Archived Data in Siebel Applets on a Need Basis.

Note: Available tools for the Data Archival in the market are SOLIX and IBM Optim. Below are the Pros and Cons of IBM Optim Tool

Advantages: The tool has options to archive, delete and retrieve data to/from Siebel. It uses Siebel VBCs and IBM Optim SQLs to retrieve data. It is also possible to restore data back to Siebel from archive files, if needed. 

Limitations:

1. IBM Optim does not support indirect joins (it supports only basic joins) and does not support MVGs. You need a separate way of displaying mvg data if you are continuing to display archived data on the UI.

Pop-up will not work.

Even Pick data would need to show only the picked value, no pick applets worked.
2. They use a WASCE server which keeps crashing. Needs continuous maintenance, even with automated jobs. Performance issues have to be dealt with.
3. The tool has a unique way of initially setting up Relationships, which is a very tedious process.