2. Import DBI Data into the PSA Layer

Attention

Suspend the batch server (``redctl suspend```) first. Wait to resume the batch server again until you’re told to do so later in this guide.

2.1. 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

Make sure that the TSA table already exists:

2.2. Add PSA Objects

To add a PSA Object use the script API_REPPSA.ADD_OBJECT. 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, or type 3 which representing event and transaction data. Furthermore, choose if the BDATE (‘B’) or TDATE (‘T’) should be closed if an entry is deleted. The last parameter defines 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 TSA table has to be defined in the format ‘TSA_SCHEMA,TSA_TABLE,BDOMAIN’.

In the example below the PSA target table is defined as type 2 with TDATE termination for deleted rows.

Note

The term “SCD type 3” for event/transaction tables does not comply to the usage of this term in common literature. However, the RED framework uses this term as a synonym for such event tables.

Example

1EXECUTE SCRIPT API_REPPSA.ADD_OBJECT(
2   'PSA_PERS'                            -- p_psa_obj_schema
3   ,'PERSON'                             -- p_psa_obj_table
4   ,2                                    -- p_scd_type
5   ,null                                 -- p_comment
6   ,'T'                                  -- p_del_method
7   ,ARRAY('DBITSA,TEST_PERSON,TESTING')  -- ARRAY p_tsa_tables
8);

After the command above has been issued a new entry is added to the table REP_PSA_OBJECTS.

OBJECT_SCHEMA

OBJECT_TABLE

COMMENT

SCD_TYPE

DEL_METHOD

PSA_PERS

PERSON

(null)

2

T

2.3. Modify TSA 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 historisation 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. Use the following format: (‘1, key1’,‘2, key2’,…,‘x’,keyx’).

Example

1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
2     'DBITSA'                -- p_obj_schema
3    ,'TEST_PERSON'           -- p_obj_table
4    ,'IDC'                   -- p_type
5    ,ARRAY('1,CustomerId')   -- ARRAY p_key_cols
6    ,true                    -- p_reset_flags
7);

1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
2     'DBITSA'                -- p_obj_schema
3    ,'TEST_PERSON'           -- p_obj_table
4    ,'BSK'                   -- p_type
5    ,ARRAY('1,CustomerId')   -- ARRAY p_key_cols
6    ,true                    -- p_reset_flags
7);

1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
2     'DBITSA'                -- p_obj_schema
3    ,'TEST_PERSON'           -- p_obj_table
4    ,'BK'                    -- p_type
5    ,ARRAY('1,CustomerId')   -- ARRAY p_key_cols
6    ,true                    -- p_reset_flags
7);

A new entry is created in the table REP_TSA_COLUMNS.

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

2.4. Set BDate

Use the script API_REPPSA.MODIFY_TSA_BDATE_EXPR to define an SQL expression for the evaluation of the effective BDate. The parameter p_bdatecols contains all columns used by the final expression. Use LUA array syntax: (‘1,column1’,‘2,column2’,…,‘x, columnx’). The parameter p_bdate_expr finally defines the SQL expression for the BDate using placeholders based on the numbering in p_bdatecols…

Example

1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_BDATE_EXPR(
2     'DBITSA'                               -- p_obj_schema
3    ,'TEST_PERSON'                          -- p_obj_table
4    ,'coalesce(to_date(@2), to_date(@1))'   -- p_bdate_expr
5    ,ARRAY('1,CreatedOn','2,UpdatedOn')     -- ARRAY p_bdatecols
6);

This command results in a modified entry in REP_TSA_OBJECTS.

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

2.5. Refresh DBIADM

For the new TSA table information to be accesible, a refresh is necessary. Therefore, issue the following command:

1EXECUTE SCRIPT API_DBIADM.REFRESH();

2.6. Compile and Refresh

The PSA table metadata now has to be compiled, which means that all necessary tables and views in the PSA layer are created.

1EXECUTE SCRIPT API_REPPSA.COMPILE(true);

Check the view PSA_PERS.PERSON_DV for the correct column definitions and expressions.

Next, refresh depending metadata views.

1EXECUTE SCRIPT API_REDADM.REFRESH();

2.7. Define a Load Cycle

Add a load cycle for a TSA table using API_REDADM.ADD_TSA_LOAD_CYCLE.

Example: a load is expected on all business days according to the Austrian calendar

 1EXECUTE SCRIPT API_REDADM.ADD_TSA_LOAD_CYCLE(
 2     'DBITSA'        -- p_tsa_schema
 3    ,'TEST_PERSON'   -- p_tsa_table
 4    ,'AT'            -- p_load_cycle_cal_name
 5    ,'BD'            -- p_load_cycle_cal_key
 6    ,'2024-08-01'    -- p_load_cycle_from
 7    ,'9999-12-31'    -- p_load_cycle_to
 8    ,null            -- p_delivery_offset
 9    ,null            -- p_alert_1_offset
10    ,null            -- p_alert_2_offset
11);

The command above results in an entry in the table RED_TSA_LOAD_CYCLES.

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

Refresh calendar information for the TSA table.

Example

1EXECUTE SCRIPT API_REDADM.REFRESH_TIME(
2     true                          -- p_full_refresh
3    ,ARRAY('DBITSA.TEST_PERSON')   -- ARRAY p_tsa_objects
4);

Tip

If an empty array is provided, all tables are refreshed.

2.8. Refresh the PSV Security Layer

To refresh the views in the PSV security layer issue the following command:

1EXECUTE SCRIPT API_SCURTY.REP_REFRESH();

Now the PSA table PSA_PERS.PERSON is successfully created and accessible via the corresponding PSV schemas.

Note

At this point the batch server framework can be resumed by entering redctl resume in the RED server command line tool.