TV 3.7.0
ATTENTION: Stop the batch server before continuing with this guide. Wait to start the batch server again until you're told to do so later in this guide.
¶ Important tables
- REPPSA.REP_TSA_OBJECTS: Shows current TSA tables with schema and table name as well as bdomain name
- REPPSA.REP_TSA_COLUMNS: Shows the name, data type and other information about TSA columns and to which TSA object they belong
- REPPSA.REP_PSA_OBJECTS: Shows current PSA tables with schema and table name
- REPPSA.REP_PSA_COLUMNS: Shows the name and ordinal position of PSA columns
- REPPSA.REP_PSA_TSA_OBJECTS: Shows which TSA object should be mapped to which PSA object
- PREPSA.REP_PSA_TSA_COLUMNS: Shows which TSA column should be mapped to which PSA column
Before a TSA table can be imported into a PSA table a TSA table has to exist. Therefore one first has to follow this HowTo Guide:
ATTENTION: Do not upload the file yet, wait until the batch server framework is started again.
¶ 1. Add Object
To add a PSA Object one can use the script API_REPPSA.ADD_OBJECT. One has to provide the name of the psa schema and table and choose the 'slowly changing dimension' (scd) type, which can be either type 2, meaning master data, which never or nearly never changes and type 3 which constitutes of factual data, meaning data that never changes, but rather adds new entries instead, such as orders which come every day but with different quantities. Furthermore, one has to choose if the BDATE ('B') or TDATE ('T') should be closed if an entry is deleted. The last variable makes it possible to define all tsa tables which should be combined into one psa table. This is necessary as a tsa table is created for each bdomain. They can be defined in the following format: ARRAY('tsatable1','tsatable2',...,'tsatablex') and each tsatable has to be defined like the following: 'TSA_SCHEMA,TSA_TABLE,BDOMAIN'.
In the example below type 2 is chosen for the slowly changing dimension and TDATE is chosen as the date that should be closed if an entry is deleted.
¶ Example
EXECUTE SCRIPT API_REPPSA.ADD_OBJECT(
'PSA_PERS' -- p_psa_obj_schema
,'PERSON' -- p_psa_obj_table
,2 -- p_scd_type
,null -- p_comment
,'T' -- p_del_method
,ARRAY('CFITSA,TEST_PERSON,TESTING') -- ARRAY p_tsa_tables
);
After the command above has been issued, a new entry is added to the table REPPSA.REP_PSA_OBJECTS, as can be seen in the table below.
| Object_schema | object_table | comment | scd_type | del_method |
|---|---|---|---|---|
| PSA_PERS | PERSON | (null) | 2 | T |
¶ 2. Modify TSA Key Columns
Due to the step before, the table already exists. This step is necessary to set the key columns. Key columns can either be of the type IDC, meaning identity code, which is the primary key for lookups, of the type BSK, meaning business surrogate key, which is the most important key, as this key can always be used to uniquely identify and show a specific entry even if historization is used or of the type BK, meaning business key.
In this case all three key columns are set to use CustomerId as the key value using the script API_REPPSA.MODIFY_TSA_KEY_COLUMNS. The array can be filled with as many columns as necessary, to be able to uniquely identify one entry. This can be done by filling it in the following format: ('1, key1','2, key2',...,'x',keyx'). The order in which these keys are added to the array, is also saved and is then used for hashing as well.
¶ Example
EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
'CFITSA' -- p_obj_schema
,'TEST_PERSON' -- p_obj_table
,'IDC' -- p_type
,ARRAY('1,CustomerId') -- ARRAY p_key_cols
,true -- p_reset_flags
);
EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
'CFITSA' -- p_obj_schema
,'TEST_PERSON' -- p_obj_table
,'BSK' -- p_type
,ARRAY('1,CustomerId') -- ARRAY p_key_cols
,true -- p_reset_flags
);
EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
'CFITSA' -- p_obj_schema
,'TEST_PERSON' -- p_obj_table
,'BK' -- p_type
,ARRAY('1,CustomerId') -- ARRAY p_key_cols
,true -- p_reset_flags
);
After the commands above have been issued, the entry in the table REPPSA.REP_TSA_COLUMNS has changed as follows.
| tsa_column_name | data_type | data_length | ordinal_position | is_nullable | idc_pos | bsk_pos | bk_pos |
|---|---|---|---|---|---|---|---|
| CUSTOMERID | DECIMAL(36,0) | 36 | 1 | true | 1 | 1 | 1 |
¶ 3. Compile
The PSA table metadata now has to be compiled, so it can be saved to the database and implemented.
EXECUTE SCRIPT API_REPPSA.COMPILE(true);
¶ 4. Refresh
The refresh below is necessary, so all the tables needed for the next step already exist beforehand.
EXECUTE SCRIPT API_REDADM.REFRESH();
¶ 5. Define Load Cycle
In this step the load cycle for changes within the source database is being defined. It matches a calendar logic with a TSA table for a specific timeframe. First, one has to provide the name of the TSA schema and table to the function API_REDADM.ADD_TSA_LOAD_CYCLE so the function knows which TSA table should be updated. Furthermore one has to provide a calendar name and key to the function as well as a time frame during which this calendar logic should be used to update the TSA table.
¶ Example
EXECUTE SCRIPT API_REDADM.ADD_TSA_LOAD_CYCLE(
'CFITSA' -- p_tsa_schema
,'TEST_PERSON' -- p_tsa_table
,'AT' -- p_load_cycle_cal_name
,'BD' -- p_load_cycle_cal_key
,'2024-08-01' -- p_load_cycle_from
,'9999-12-31' -- p_load_cycle_to
,null -- p_delivery_offset
,null -- p_alert_1_offset
,null -- p_alert_2_offset
);
The command above results in an entry in the table REDADM.RED_TSA_LOAD_CYCLES as can be seen below.
| tsa_schema | tsa_table | load_cycle_cal_name | load_cycle_cal_key | load_cycle_from | load_cycle_to |
|---|---|---|---|---|---|
| CFITSA | TEST_PERSON | AT | BD | 2024-08-01 | 9999-12-31 |
¶ 6. Refresh Time
The next step is to refresh the calendar for the TSA objects. This can be achieved by providing a true for the full refresh variable and an array containing the tables that should be refreshed in the format ('schema1.table1','schema2.table1',...,'schemax.table1')) as can be seen below.
EXECUTE SCRIPT API_REDADM.REFRESH_TIME(
true -- p_full_refresh
,ARRAY('CFITSA.TEST_PERSON') -- ARRAY p_tsa_objects
);
TIPP: If an empty array is provided, all tables are refreshed.
¶ 7. Add PSV Security Layer
To add a PSV security layer the following command has to be issued:
EXECUTE SCRIPT API_SCURTY.REP_REFRESH();
Now the PSA table PSA_PERS.PERSON is successfully created and accessible.
At this point the batch server framework can be resumed by entering '
redctl resume' in the RED server command line tool. Afterwards the file can be uploaded.
ATTENTION: When uploading a file add a .tra at the end of the file to prevent unfinished files from being loaded. Make sure that the complete file has been uploaded before removing the .tra ending. If the file is loaded without any errors it can be found in the archive folder, otherwise it will be moved to the error folder as explained here.