Generating serial letters as merge document with mass data

Destinations

Selection and processing of mass data for generating one or more serial letters.
Executing several serial letter campaigns (different serial letters)
The target groups of each serial letter campaign are essentially based on different contract selection criteria.
It should be possible to process selection sets > 100,000 per serial letter campaign (tests at CURSOR Software AG have been carried out with a database and with an upper limit of 130,000 sets).
Only one contact person is written to per business partner.
A separate activity is created for each contact person. The activity is linked to the contact person, the business partner, and the document.
To reduce mailing costs, the selection result of a serial letter campaign should be sorted by ZIP Code. (It is not possible to sort by ZIP code for all serial letter campaigns.)
The serial letter document only displays data from the business partner, contact person, and their address.

General conditions

Client computer

The client computer on which the serial letter is started should have 2GB of RAM.
Individual configuration of the client application during the serial letter campaigns is necessary. The JVM of the client application must be configured with enough RAM (1.4 GB in the case of a 32-bit client operating system). The client is then called with the parameters XMS 1400m XMX 1400m

CODE

<Client-Bin-Verzeichnis>\run XMS 1400 XMX 1400

Application server

RAM

The application server must also be configured individually during serial letter campaigns. The increased allocation of RAM here depends on the underlying operating system.

Variant 1 - JBoss application server on a 32-bit operating system
Allocated memory: 1.1GB (more is not possible)
Empirical values: Processing large selection sets (>100,000) uses almost all the resources of the application server.
Variant 2 – JBoss application server on a 64-bit operating system
Allocated memory: 2GB
Empirical values: Processing large selection sets (>100,000) still leaves sufficient resources available for the application server. CURSOR Software AG recommends this variant.

The memory setting must be changed in the <JBoss-Bin-Directory>\run.bat file.

POWERSHELL

set JAVA_OPTS=%JAVA_OPTS% -server -Xms2048m -Xmx2048m

Database drivers

Both 32-bit driver variants of the database have been tested for access to the database: OCI driver and JDBC Thin driver. In both cases, the serial letter could be created and the data volume processed in our tests. The memory consumption and performance of the JDBC Thin driver were lower.

In the case of a 64-bit installation of JBoss, the 64-bit OCI driver must be used.

Database server

The database server should also have sufficient main memory and the tablespaces should be sufficiently large to hold the data volume. A fast hard disk system is certainly an advantage here.

Data volumes for two serial letters approx. 381,000 activities:

Data tablespace + 0.4 GB
Index tablespace + 0.9 GB

Empirical values: Additional queries to database servers should be avoided during the serial letter campaign (creation of new activities and their links). The performance during this action is significantly slowed down (a select statement, which was normally handled under 1 second, took 45 seconds on our test server during creation of the activity).

Preliminary work

Right to execute large selection sets

Larger serial letter campaigns lead to high system utilization. It is therefore necessary to restrict the right to execute large selection sets to individual employees. The following SQL statements must be executed for each authorized employee.

The text [Employee token] must be replaced by the token for the authorized employee. The primary key must be unique for each employee.

Step 1

User-specific release for selecting large data sets (>10000) ====

SQL

DELETE FROM Propertymapper WHERE pk = '<Mitarbeiterkürzel>' || 'TopCount_UseMax'; 
INSERT INTO PropertyMapper (Pk, id, propertyValue, propertyType, principal, Active, CreateDate, CreateUser, UpdateDate, UpdateUser)
VALUES ('<Mitarbeiterkürzel>' || 'TopCount_UseMax', '/de/cursor/jevi/client/swingclient/gui/search/ExtendedSearch$!!$TopCount_UseMax', 'false', 'USER', (SELECT pk FROM employee WHERE shortcut ='<Mitarbeiterkürzel>'), 1, SYSDATE, 'TECH_USER', SYSDATE, 'TECH_USER');

Step 2

User-specific optimization for selection using the IN clause

SQL

DELETE FROM Propertymapper WHERE pk = '<Mitarbeiterkürzel>' || 'Use_Select_For_In'; 
INSERT INTO PropertyMapper (Pk, id, propertyValue, propertyType, principal, Active, CreateDate, CreateUser, UpdateDate, UpdateUser)
VALUES ('<Mitarbeiterkürzel>' || 'Use_Select_For_In', 'USE_SELECT_FOR_IN', 'true', 'USER', (SELECT pk FROM employee WHERE shortcut ='<Mitarbeiterkürzel>'), 1, SYSDATE, 'TECH_USER', SYSDATE, 'TECH_USER');

Permissions

In the course of a serial letter campaign, activities are also generated in addition to the document. All generated datasets include the permission of the executing user. In order to ensure that all users have access to the activities or the document, it is necessary to assign to the executing user the DefaultTemplate rights template, or another template that collects all the necessary permissions for this serial letter.

It is necessary to check whether a new virtual employee is created for the serial letter selection. This could be used as Delegated by and Delegated to in the generated activities.

Background

All activities of the employee are displayed in the Employee mask. As a result, loading the employee dataset will take quite a long time if, for example, changes have to be made to the employee set. You can avoid this drawback by using your own employee for serial letters.

What has to be considered in a serial letter campaign?

No other user should be in the system during the serial letter campaign. Application performance drops significantly. Large serial letter campaigns should therefore be processed at the end of a working day.
Check whether the JBoss application server has enough configured memory, otherwise this must be done. The application server then needs to be restarted for the changes to take effect (see the section on RAM).
The client should be restarted before the serial letter campaign! Ideally it is recommended to log in with the new 'virtual' employee for serial letters.
Within a serial letter campaign, it may be necessary to execute several searches, for example: a search for the default business partners and a search for alternative business partners in the contract.
In order to maintain ZIP code sorting over both selection sets, both selection sets can be mixed and then sorted before the serial letter is actually generated.

Process description for generating a serial letter

Select Marketing / Serial letter menu item
Wizard page "Template selection"
Select desired template
Wizard page “Select addressees”
There are two options for selecting recipients:
1. Classic variant: Search for addressees via the search mask
2. Load of saved addressees. The user can access stored addressees via Explorer.
Both selection variants can be repeated as often as required. The result of each selection is added to the total result set each time.
When adding the data, it is also checked whether the structures of both quantities match each other. If they do not match, a corresponding message appears and the add operation is canceled.
Check for duplicates
If addressees are added several times, it is very likely that the overall results set will contain duplicates. These duplicates can be purged using the "Check for duplicate addressees" button.
Viewing and sorting
Here, the total result quantity can be displayed and reworked manually. Sets in the upper area are removed, sets in the lower area remain in the total result set
The current number of datasets is output in the button. The total results set can also be sorted here (e.g. by ZIP code).
Save the total result quantity
The current total result can be saved as an * .ser file via Explorer.
This means that the same results set can be reloaded at a later point in time to carry out specific follow-up actions.
Wizard page "Variant of export"

Hinweis

The "Excel" export format can only contain approx. 65,000 entries. Otherwise, the CSV export format should always be preferred if more entries need to be exported (faster processing in Word).
Wizard page "Subject for the serial letter"
Enter a unique subject
Wizard page "Choice of attachments"
Recommendation: In order not to 'inflate' the amount of data generated, no further attachment should be added in the serial letter wizard.
"Create activity” wizard page
Select logging or reminder activity, select the type of activity/activities: Collector activity or single activity
Recommendation: In the case of a single activity, the amount of data generated should not be 'inflated' any further. Therefore, in such a case, only one activity should be selected in the serial letter wizard.
"Create activity” wizard page
Fill out the activity with default values and change employee assignment if necessary.
Serial letter start
The actual serial letter campaign is started by confirming with the 'Done' button. The serial letter document with the data is available after a few minutes. The activities and links are created in parallel on the application server.
Processing and parcel printing of the serial letter document.
No more than 32,000 pages can be edited within Word. We therefore recommend that you create and print the serial letter document "one at a time".
The EVI client can now be closed. The generation of activities and links continues to run on the application server.

Hints and tips

In the case of large serial letter campaigns, you should always re-index the database. This action should ideally be carried out automatically at night.
Duplicates in mixed selection result. A doublet is created if a business partner is selected as both an alternate and default business partner in the contract.