Unifier ETL Performance Degradation When Running PROC_POPULATE_SECURITY Scenario, Populate Project Security Step (Doc ID 2357306.1)

Last updated on JUNE 16, 2021

Applies to:

Symptoms

ACTUAL BEHAVIOR

While executing an ETL for a Unifier datasource, the ETL execution can take a long time to complete (for example, 2 hours).

Note:

• The ETL log file is showing all the substeps to complete in a much quicker time than the total amount of time the ETL finishes.
• When reviewing the ETL steps within the ODI Studio:
  • The delay is stemming from the PROC_POPULATE_SECURITY scenario step which runs the following tasks (in order): "Load User S" --> "Unifier Company Security" --> "Populate Project Security" --> "Populate Resource Security"
  • The issue in this scenario step is with the "Populate Project Security" task while processing a large amount of rows.
    • SQL from "PROC_POPULATE_SECURITY" scenario, "Populate Project Security" task:
      
      begin
      sec_load_pkg.project_security(#VAR_DATASOURCE_ID);
      end;
      
      Which, when using a Unifier source, executes the following merge statement in a loop:
      
        if vproduct = 'UNIFIER'
        then
          for urec in (select s.user_id, s.user_name from w_user_ss s inner join w_user_s u on u.user_object_id = s.user_id and u.datasource_id = vsrc_id and u.datasource_id = s.datasource_id)
          loop
         execute immediate   
         'MERGE INTO w_project_security_s ps
              USING
              (
          SELECT USER_OBJECT_ID, USERNAME,  PROJECT_OBJECT_ID,    CASH_FLOW_FLAG, COST_SHEET_FLAG, BP_FLAG FROM SYS_STG_PROJSEC@'||vdblink || ') q
          ON (ps.project_sec_key = q.user_object_id AND ps.project_object_id = q.project_object_id AND datasource_id = '|| vsrc_id ||')
              WHEN MATCHED THEN
                UPDATE SET
                        ps.user_name  = username
          WHEN NOT MATCHED THEN
            INSERT
          (project_sec_key, user_name, project_object_id, security_type, cost_flag, project_wid,
          cash_flow_flag, cost_sheet_flag, bp_flag, datasource_id)
          VALUES(q.USER_OBJECT_ID, q.USERNAME,  q.PROJECT_OBJECT_ID,''U'', ''Y'', null, q.CASH_FLOW_FLAG, q.COST_SHEET_FLAG, q.BP_FLAG,'|| vsrc_id ||')';
      
        end loop;
      
      

EXPECTED BEHAVIOR

While running an ETL for a Unifier datasource, for the processing of "W_PROJECT_SECURITY_S" data (which occurs during the end of an ETL) to complete in a quicker threshold of time.


The issue can be reproduced at will with the following steps:

1. Login to the Analytics Administration application
2. Note the amount of time it takes for a Unifier ETL to complete.

Changes

Cause