Skip to Content
Technical Articles
Author's profile photo Lara Golden

Using CDL to purge data from Stage Tables in SAP Commissions

Summary:
This article will explain how to create a CDL template to purge data from the Stage Tables in SAP Commissions.

Details:

When using Commissions Data Loader (CDL) to load data into SAP Commissions, all records are loaded into a series of Staging Tables prior to being imported into the application. As more data is imported into the environment over time, performance for imports can degrade due to the growing volume of stage data. Fortunately, once stage data has been imported, there is often no need to keep it in the stage tables. Therefore, it’s a good idea to regularly purge data from the stage tables. This can be done in the user interface, but it’s also possible to use CDL to perform the purge.

 

There are two advantages to cleaning up stage data from the database:

1) Increased performance in the CDL import process

2) Reduced storage

 

In prior versions of SAP Commissions, the ODPU template was used to perform purges. This template has been replaced with the PIPELINE template, which is used to perform all the Pipeline processes.

The PIPELINE template can be downloaded from theĀ CDL TemplatesĀ link in the SAP Commissions online help.

Format and Naming

Commissions Data Loader has the same naming conventions as its predecessor, ODI. File naming conventions are very specific and must conform to the following requirements:

cust_PIPELINE_<DEV or TST or PRD>_<yyyymmdd>_<hhmiss>_<xxxxxx>.<file-suffix>

Table 1 below shows a breakdown of the elements of the file name:

cust The four-digit tenant identifier assigned to each customer
DEV or TST or PRD The type of target environment
yyyymmdd The date of the transmission, for example 20210701
hhmiss The time stamp, for example 1:42:57 PM is “134257”
xxxxxx A unique content tag used to identify the batch, such as TRANSACT01

For example, for a tenant ID “5555” in the production environment, the file name might be:

5555_PIPELINE_PRD_20210701_134257_MyPurge.txt

Contents of the PIPELINE template for a Purge batch

Table 3 below shows how the PIPELINE template should be populated for a purge.

COLUMN VALUE
Command PipelineRun
StageTypeName Purge
ProcessingUnitName PU_Americas
PeriodName {Leave Blank}
RunMode {Leave Blank}
BatchName Complete file name including .txt extension. See Table 1 for an example.
Module Refer to the following table
StageTables Refer to the following table
RunStats false
GenerateODSReports false
Defer false

Modules and Stage Tables

Table 3 below contains a complete list of modules and stage tables.

Modules Stage Tables
TransactionalData [“TransactionAndCredit”]
[“Deposit”]
OrganizationData [“Participant”]
[“Position”]
[“Title”]
[“Relationship”]
ClassificationData [“Category”]
[“ClassifierAssignments”]
[“Customer”]
[“Product”]
[“PostalCode”]
[“GenericClassifier”]
PlanData [“FixedValue”]
[“VariableAssignements”]
[“LookupTable”]
[“Quota”]

Below are some facts about the behavior of the Purge process.

  • The process purges records batch-by-batch.
  • Batches are never partially purged. Either the whole batch is purged or none of it is purged.
  • Any batch with unprocessed records will not be purged.
  • Archived records will be in the EXT schema with a _HIST suffix in the table name, e.g. XXXXEXT.CS_STAGEPARTICIPANT_HIST

Comments:

All Boolean fields in the file should be in lower case.

If you have a lot of data in the system, the purge process will take a while. Please benchmark the process in the lower environments to get an idea of how long it takes to clean up the data.

It is best to run this process on a regular basis to avoid excessive buildup of stage data.

 

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.