TV 3.7.0
ATTENTION: Stop the batch server before continuing with this guide. Start the batch server at the end of this guide again.
¶ 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:
¶ 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('DBITSA,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(
'DBITSA' -- 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(
'DBITSA' -- 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(
'DBITSA' -- 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. Set BDATE
The next step is to set a BDATE. This can be done using the script API_REPPSA.MODIFY_TSA_BDATE_EXPR. For the variable 'bdatecols' one has to fill an array in the format ('1,column1','2,column2',...,'x, columnx') with all columns that should be used for the BDATE. The variable 'bdate_expr' is used to set a specific expression, that defines the BDATE based on the BDATE columns set before, using the numbers as placeholders for column values. In this example coalesce is used, meaning, in the order the dates are provided in the brackets, this function looks for changes. If the first value provided has changed, this value is used for BDATE. If the first hasn't changed, the function looks at the second provided column and uses this value if it changed. If this hasn't changed either it looks at the third and so on.
¶ Example
EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_BDATE_EXPR(
'DBITSA' -- p_obj_schema
,'TEST_PERSON' -- p_obj_table
,'coalesce(to_date(@2), to_date(@1))' -- p_bdate_expr
,ARRAY('1,CreatedOn','2,UpdatedOn') -- ARRAY p_bdatecols
);
After the command above has been issued, the entry with the object schema 'DBITSA' and the object table 'TEST_PERSON' in the table REP_TSA_OBJECTS has been changed as can be seen in the table below.
| object_schema | object_table | bdate_expression |
|---|---|---|
| DBITSA | TEST_PERSON | coalesce(to_date(@2),to_date(@1)) |
The entry in the table REP_TSA_COLUMNS shows how the bdate_expr will be interpreted.
| tsa_column_name | data_type | ordinal_position | idc_pos | bsk_pos | bk_pos | bdate_pos |
|---|---|---|---|---|---|---|
| CustomerId | DECIMAL(19,0) | 7 | 1 | 1 | 1 | (null) |
| FirstName | VARCHAR(100) UTF8 | 8 | (null) | (null) | (null) | (null) |
| LastName | VARCHAR(100) UTF8 | 9 | (null) | (null) | (null) | (null) |
| Salary | DECIMAL(8,2) | 10 | (null) | (null) | (null) | (null) |
| Birthday | DATE | 11 | (null) | (null) | (null) | (null) |
| CreatedOn | TIMESTAMP(3) | 12 | (null) | (null) | (null) | 1 |
| UpdatedOn | TIMESTAMP(3) | 13 | (null) | (null) | (null) | 2 |
¶ 4. Refresh DBIADM
For the new TSA table information to be accesible, a refresh is necessary. Therefore the following command has to be issued:
EXECUTE SCRIPT API_DBIADM.REFRESH();
¶ 5. 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);
This command results in an entry in the view 'PERSON_DV' in the schema 'PSA_PERSON'. In the view PSA_PERS.PERSON_DV one can for example also see the bdate expression, but in this case all the variables are filled out with the according values.
¶ 6. Refresh
The refresh below is necessary, so all the tables needed for the next step already exist beforehand.
EXECUTE SCRIPT API_REDADM.REFRESH();
¶ 7. 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(
'DBITSA' -- 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 |
|---|---|---|---|---|---|
| DBITSA | TEST_PERSON | AT | BD | 2024-08-01 | 9999-12-31 |
¶ 8. 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('DBITSA.TEST_PERSON') -- ARRAY p_tsa_objects
);
TIPP: If an empty array is provided, all tables are refreshed.
¶ 9. 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.