15.4.2 Incremental Snapshot Load Example
This scenario is applicable when source may to portions of data at different point in time, or handover only the records changed since the last load. This is contrary to example explained under the Complete snapshot load section.
The same scenario in case of incremental snapshot load will appear as below.
Note:
Only the changed source records are stamped with the new load run identifer.Table 15-6 Incremental Snapshot Load example
| FIC_MIS_DATE | V_ACCOUNT_NUMBER | V_GAAP_CODE | N_LOAD_RUN_ID | N_EOP_BAL |
|---|---|---|---|---|
| 01-JAN-15 | LOAN1000 | USGAAP | 1 | 4066.213 |
| 01-JAN-15 | LOAN1001 | USGAAP | 1 | 34538.905 |
| 01-JAN-15 | LOAN1002 | USGAAP | 1 | 667.357 |
| 01-JAN-15 | LOAN1001 | USGAAP | 2 | 34540.000 |
| 01-JAN-15 | LOAN1002 | USGAAP | 2 | 670.000 |
NOTE
After each load you need to run fn_register_load_details function mentioned in Post Stage Load Process and Updating Load as Completed which is explained in the following sections
In order to enable downstream applications to consume only the latest set of record, you need to call another function named fn_pop_load_run_map. This is mandatory in case of incremental snapshot load scenario. This function populates a intermediate processing table that keep track of latest incoming record identifier.
Function - Populate Load Run Map
Parameters - Batch Id, MIS Date, Stage Table Name, Load Run Id, Load Run Name
Table 15-7 Populate Load Run Map example
| Parameters | Source of Values | Example Values |
|---|---|---|
| Batch ID | Auto generated if you are using OFSAA Framework | OFSBFNDIN- FO_20150101_1 |
| MIS-Date | Input from Customer | 01/01/2015 |
| Stage Table Name | Input from Customer | STG_CASA |
| Load Run ID | Input from Customer | 1 |
| Load Run Name, | Input from Customer | FSDF_Load |
Example
Declare
Result number;
Begin
Result: = fn_pop_load_run_map('OFSBFNDINFO_20150101_1','20150101','STG_CASA',1,'FSDF
_LOAD');
END;
Note:
For troubleshooting any errors while making the function calls , refer to fsi_message_log table for more details.For the example mentioned above, records in FSI_ACCOUNT_LOAD_RUN_MAP table will appear as below:
Table 15-8 Records in the FSI_ACCOUNT_LOAD_RUN_MAP table
| FIC_MIS_DATE | V_ACCOUNT_NUMBER | V_GAAP_CODE | N_LOAD_RUN_ID | F_LATEST_LOAD_RUN_FL AG |
|---|---|---|---|---|
| 1-Jan-15 | LOAN1000 | USGAAP | 1 | Y |
| 1-Jan-15 | LOAN1001 | USGAAP | 1 | N |
| 1-Jan-15 | LOAN1002 | USGAAP | 1 | N |
| 1-Jan-15 | LOAN1001 | USGAAP | 2 | Y |
| 1-Jan-15 | LOAN1002 | USGAAP | 2 | Y |