Using 0RECORDMODE for Delta Load
In Sections 9.3 and 9.4, we loaded data first into the ODS object, and then into the InfoCube. The data in the InfoCube came from an initial loading process (Screen 9.40). Now let's see how ODS uses the characteristic 0RECORDMODE values – X, D, and R – to control consequent delta loading processes.
You use 0RECORDMODE value X to mark rows to be skipped in the delta data load. You use D and R to delete or remove rows, respectively, from the active ODS table if the rows can be identified by the table primary key. Otherwise, D and R work just like X – they do not affect either the change log or the active data table.
Now let's illustrate how the ODS delta load works.
Table 9.1 lists the first four rows of the original sales data from Table 7.1. What happens to the data in the ODS object and the InfoCube after loading the Table 9.2 data into them? Table 9.2 has an extra column that contains the 0RECORDMODE values. Also, the sales quantity is doubled.
| IO_ORD | IO_CUST | IO_SREG | IO_SOFF | IO_SREP | IO_MAT | IO_QUAN | 0UNIT | IO_PRC | 0CALDAY |
|---|---|---|---|---|---|---|---|---|---|
| ORD0000001 | CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 2 | CS | 1 | 19980304 |
| ORD0000002 | CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 100 | CS | 0.75 | 19980305 |
| ORD0000003 | CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 10 | CS | 0.5 | 19980306 |
| ORD0000004 | CUST002 | EAST | NEW YORK | SREP02 | MAT002 | 2 | CS | 2 | 19990526 |
| IO_ORD | IO_CUST | IO_SREG | IO_SOFF | IO_SREP | IO_MAT | IO_QUAN | 0UNIT | IO_PRC | 0CALDAY | 0RECORD-MODE |
|---|---|---|---|---|---|---|---|---|---|---|
| ORD0000001 | CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 4 | CS | 1 | 19980304 | |
| ORD0000002 | CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 200 | CS | 0.75 | 19980305 | X |
| ORD0000003 | CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 200 | CS | 0.5 | 19980306 | D |
| ORD0000004 | CUST002 | EAST | NEW YORK | SREP02 | MAT002 | 4 | CS | 2 | 19990526 | R |
Repeating the procedure outlined in Section 9.3 for loading data into the ODS object, we see that the PSA data in Screen 9.44 match the data in Table 9.2. This result is expected.
SCREEN 9.44
After loading the PSA data into the ODS object, the table /BIC/AODS_DEMO10 contains the new data (Screen 9.45). The second row of Table 9.2 does not appear here, which indicates that BW detected the value X in the 0RECORDMODE column and then skipped the second row.
SCREEN 9.45
After activating the ODS data, Screen 9.46 shows that four rows are appended to the end of the change log table, /BIC/B0000842000. The first of these rows matches the first row in Table 9.2. The remaining three rows match the first, third, and fourth rows in the table /BIC/B0000842000, except that "-" is added to the /BIC/IO_QUAN value.
SCREEN 9.46
Screen 9.47 shows the active data in the table /BIC/AODS_DEMO00. This result comes from compressing the change log; that is, the "-" sign actually makes the number on its left negative, and the result of the compression deleted the first, third, fourth, and last three rows from the change log.
SCREEN 9.47
Now we load the ODS data into the InfoCube as shown in Screen 9.39. This time we select the Delta Update option (Screen 9.48).
SCREEN 9.48
BW makes the corresponding selection automatically in the InfoPackage definition (Screen 9.49).
SCREEN 9.49
After we launch the delta load process, changes to the data appear in the InfoCube fact table (Screen 9.50). Three new rows are added as another data load request. The values in the three new rows are obtained by compressing the last four rows in Screen 9.46. The "-" sign has the same meaning – it makes the number on its left negative.
SCREEN 9.50
Result
These steps prove what we said at the beginning of this section: You use 0RECORDMODE value X to mark rows to be skipped in the delta data load. You use D and R to delete or remove rows, respectively, from the active ODS table if the rows can be identified by the table primary key. Otherwise, D and R work just like X – they do not affect either the change log or the active data table.