Microsoft Project Plug In not Working - hangs on loading PPM Global Data

Products

• Clarity PPM SaaS

Issue/Introduction

Performance Issue. In a Postgres Database environment that has a larger than average number of Roles, we can see a performance issue in MSP. The PRJ_NK_RESOURCE_REMAIN_AVAIL_FCT function used in odf pmd - MSP query is slow. 

Steps to Reproduce: 

1. Use MSP to open a project with many roles 
2. odf-pmd.resource.select-no-security.map is used to do the query 

Expected Results: Query returns results in a few minutes or less. 

Actual Results:  Query takes 8-13 minutes to complete. 

Environment

Release : 16.0.1, 16.0.2 

Component : Clarity Microsoft Project (MSP) 

Postgres Database 

Cause

DE65146 -

• In environments with many roles defined (hundreds or thousands), there is a database query that is timing out.
• The MSP connector runs this query after connecting to clarity to fetch all of the roles. 

Workaround:

1. Edit the following 2 database functions
   • PRJ_NK_RESOURCE_TOTAL_ALLOC_FCT
   • PRJ_NK_RESOURCE_REMAIN_AVAIL_FCT
2. Edit the data type of the "v_resourceid" variable - Change it from numeric to "bigint" 
   • For example:

CREATE OR REPLACE FUNCTION prj_nk_resource_total_alloc_fct(p_resource_id numeric, p_start timestamp without time zone DEFAULT NULL::timestamp without time zone, p_finish timestamp without time zone DEFAULT NULL::timestamp without time zone)
 RETURNS numeric
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  v_return    numeric;
  v_avail     numeric;
  v_demand    numeric;
  -- v_resourceid numeric;
  v_resourceid bigint; 

Resolution

Targeted Fix in Release 16.0.3