Today, business changes more rapidly than ever before! Sales forces are realigned, territories are split in two, and products are combined into different product lines. When these events occur, master files in transaction-based applications – customer master, inventory master – are updated to reflect the new organization.
Once the changes have been made in the application system, data imported to DataTracker/Stratum will reflect the new organization. However, historical information in DataTracker is NOT automatically updated to reflect the change. DataTracker /Stratum provides utility functions to update historical data in the DataTracker database. This process is called ‘Realignment’ or ‘Change Sets’.
The Change Set process involves four steps:
- Describe the change to be made, such as changing the region for selected customers
- Indicate which dimensions to search and the dimensions to replace. For example, you might search for customer and replace the value for region.
- Specify the actual dimension values to search and replace. For example, search for customer number “1234A” and replace region with “MW.”
- Apply or execute the change. This process will update the DataTracker warehouse to reflect the specified changes.
Each of these steps has a corresponding screen in the Administration application (Utilities | Change Sets – which is used for steps 1, 2 and 3 and Utilities Apply Change Sets – for step 4). The Apply Change Set function can also be initiated via the DataTracker iSeries command DACHGSET. For infrequent or low-volume changes, the existing screens and processes work fine.
When a large number changes need to be made – or – the same type of change is routinely made, many companies automate the change set process. This helps to insure that changes made to master file entries in the transaction-based application (i.e. customer master information) are reflected in DataTracker’s historical data.
To automate this process, it is first essential to be able to identify changes made to the application master file. This can be accomplished using existing audit trails that may exist in the application. If audit files are not available, it may be necessary to modify the master file maintenance program itself to produce the information needed when fields used to populate DataTracker dimensions are changed.
There are two DataTracker tables that need to be set-up, which correspond to the first two steps of the change set process. The CHGSETHDR table maintains information about the change set itself – descriptive information, last run date, etc. The CHGSETDTL table is used to identify which dimensions are used to search for records in the DataTracker database and which dimensions will be replaced, once those records are found.
Typically, the maintenance screens in the Administration application are used to populate both the CHGSETHDR and CHGSETDTL tables. Because the information for a change set changes infrequently, the programming effort to add or modify these files is not justified.
The next step involves populating the DataTracker table that is used in the third step which was described – specifying the actual dimension values to search and replace. The audit trail information generated by the master file maintenance program of the source application is used to populate the CHGSETDTA table in the DataTracker database library. The table is originally created during the Create Tables function within the DBInstall application (Installation | Other Installs). The table is built using alldimensions defined in the database. An example of the structure of this table is shown and explained in attachment A. An example of a populated CHGSETDTA table is shown in attachment B.
At this point, the Apply Change Set screen or the DACHGSET command can be used to execute the process, which updates the DataTracker database.
Optionally, a CL program can be written to execute the DACHGSET command. This CL program can be scheduled to run at specific times to apply the change set records that exist in the CHGSETDTA table.
If the option of Process Type is set to ‘Reverse Transaction’, reversing transaction records will be placed in a change set extract pending table denoted by structure code. The name of the change set extract pending table depends on the type of the structure code:
If the structure code is a Periodic type, the pending file is: FULCHGsc
If the structure code is a Daily-Sparse type, the pending file is: EXTCHGsc
Where sc is the structure code identifier.
For example, if the structure code is AS and it is a periodic structure code, the change set extract pending table will be FULCHGAS.
When the DACHGSET process is executed, for each existing DataTracker record to be changed, two records are written to the change set extract pending table. The first is a record to back out the current DataTracker record. It has the current dimension values and reverses the sign of all detail values. The second record contains the new dimension values and uses the sign of the original detail values. The table can be queried to review the potential changes that will be made before the transactions are applied to the datamart. Make sure that the extract change set pending table is clearedprior to running the DACHGSET command.
To ‘apply’ the transactions in an EXTCHGsc type change set table (i.e. EXTCHGMS – for a Daily-Sparse structure code) to the datamart, use the DALOAD command with the following parameters:
To ‘apply’ the transactions in a FULCHGsc type change set table (i.e. FULCHGAS – for a Periodic structure code) to the datamart, use the DALOAD command with the following parameters:
Format of CHGSETDTA Table
|Field||Notes||Data Type||Field Length||Buffer Length|
|CSETID||This is an identifier fro the change set||CHAR||10||10|
|SEQ||A sequential number starting at 1 for each change record, within the same CSETID||BINARY||4 0||2|
|SVAL1||Search dimension 1 Value||CHAR||1||1|
|SVAL2||Search dimension 2 Value||CHAR||1||1|
|SVAL3||Search dimension 3 Value||CHAR||10||10|
|SVAL4||Search dimension 4 Value||CHAR||8||8|
|SVAL5||Search dimension 5 Value||CHAR||21||21|
|SVAL6||Search dimension 6 Value||CHAR||10||10|
|SVAL7||Search dimension 7 Value||CHAR||16||16|
|RVAL1||Replace dimension 1 value||CHAR||1||1|
|RVAL2||Replace dimension 2 value||CHAR||1||1|
|RVAL3||Replace dimension 3 value||CHAR||10||10|
|RVAL4||Replace dimension 4 value||CHAR||8||8|
|RVAL5||Replace dimension 5 value||CHAR||21||21|
|RVAL6||Replace dimension 6 value||CHAR||10||10|
|RVAL7||Replace dimension 1 value||CHAR||16||16|
Note: Putting values into the Search and Replace fields MUST correspond to how the Change Set has been defined – i.e. if the Change Set indicates that records should be searched based on dimension 4 and the value for dimension 7 updated in DataTracker, values MUST ONLY exist in SVAL4 and RVAL7 in the CHGSETDTA table.
The number of search values and replace values is determined by how many dimensions are defined in the datamart. The associated dimension determines the field and buffer lengths.
Example of CHGSETDTA Table
A Change Set (named CHGREP) is defined to search for Dimension 4 (Customer Number) and replace Dimension 7 (Sales Rep ID). Next, we define which customers need to be updated:
|Customer #||New Sales Rep ID|
The records in the CHGSETDTA table will look like:
|Field||Record 1||Record 2||Record 3|