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.

Table 9.1. FIRST FOUR ROWS OF THE ORIGINAL DATA BEFORE CHANGES

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

Table 9.2. FIRST FOUR ROWS OF THE DATA AFTER CHANGES

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.