Creating a Data Provider Receive Invalid object name 'PPMDBLINK.niku.ENTITY'.

Products

Clarity PPM On Premise

Issue/Introduction

In an environment using MSSQL, when creating a Data Provider with a 'Per Period Metrics', an error will appear:

Unable to process the request: Invalid object name 'PPMDBLINK.niku.ENTITY'.

STEPS TO REPRODUCE -
1. Create a new Data provider using either Investment or Project data providers.
2. Make no changes to any options and publish it.
3. If you load data using any fields available in the "Per Period Metrics" section, no data appears in the preview, and the error is displayed.

EXPECTED RESULTS:

No error should be seen

ACTUAL RESULTS:

No data appears in the preview, and the error is displayed. Error in logs:

com.ca.ppm.rest.exception.APISystemException: Unable to process the request: Invalid object name 'PPMDBLINK.niku.ENTITY'.
...
Caused by: com.niku.union.persistence.PersistenceException:
SQL error code: 208
Error message: Invalid object name 'PPMDBLINK.niku.ENTITY'.
Executed:
/*[reporting|data_provider|DP00000027]*/
declare @calendar CRV_CALENDAR insert into @calendar select * from ppm_dwh.DWH_CRV_CALENDAR_FCT('months',CONVERT(datetime,'2025-01-01',20),NULL,12,'fiscal',',ENTITY=Ent1') select q.* ,(select ppm_dwh.DWH_CRV_SUM_TOTAL_FCT(sqlc.crv_bdgt_oper_cost) from dwh_inv_investment_crv_facts sqlc where sqlc.investment_key = q.inv____investment_key) as inv____budget_cost_op__tsscalar from ( select dwh_inv_investment.originating_idea_name inv____orig_idea_name,dwh_inv_investment.investment_key inv____investment_key, row_number() over( ORDER BY (SELECT 1) ) AS row_num , 5000000 + row_number() over( ORDER BY (SELECT 1) ) AS odf_pk , COUNT(1) OVER() AS rows_count
from dwh_inv_investment dwh_inv_investment
where 1 = 1 and ((dwh_inv_investment.sub_type in ('project','idea','cit')) or (dwh_inv_investment.investment_key is null)) ORDER BY row_num OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY ) q left join ( select investment_key tsv_key, slices.ts1 ts1, slices.ts2 ts2, slices.ts3 ts3, slices.ts4 ts4, slices.ts5 ts5, slices.ts6 ts6, slices.ts7 ts7, slices.ts8 ts8, slices.ts9 ts9, slices.ts10 ts10, slices.ts11 ts11, slices.ts12 ts12,slices.total_periods total_periods,slices.grand_total grand_total from dwh_inv_investment_crv_facts cross apply ppm_dwh.DWH_CRV_PERIOD_PIVOT_FCT(crv_bdgt_oper_cost,@calendar) slices WHERE 1 = 1 AND crv_bdgt_oper_cost IS NOT NULL AND 1 = 1 ) budget_cost_op on budget_cost_op.tsv_key = inv____investment_key ORDER BY row_num
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd">
<sql dbVendor="all" tsvTemplateAttrCode="">
<text>
@DWH_SELECT@
@DWH_FROM@
@DWH_WHERE@
</text>
<param direction="IN" dynamicReplacement="@DWH_FROM@" expressionListDelimiter=","/>
<param direction="IN" dynamicReplacement="@DWH_WHERE@" expressionListDelimiter=","/>
</sql>
</statement>

Referenced by:
<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="reporting.getDWHData_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>

Using input:
{@DWH_FROM@= from , @DWH_WHERE@= where 1 = 1 , @DWH_SELECT@=}
Using dynamic SQL:
tag: @DWH_WHERE@ SQL: where 1 = 1 and ((dwh_inv_investment.sub_type in ('project','idea','cit')) or (dwh_inv_investment.investment_key is null)) ORDER BY row_num OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY ) q left join ( select investment_key tsv_key, slices.ts1 ts1, slices.ts2 ts2, slices.ts3 ts3, slices.ts4 ts4, slices.ts5 ts5, slices.ts6 ts6, slices.ts7 ts7, slices.ts8 ts8, slices.ts9 ts9, slices.ts10 ts10, slices.ts11 ts11, slices.ts12 ts12,slices.total_periods total_periods,slices.grand_total grand_total from dwh_inv_investment_crv_facts cross apply @[email protected]_CRV_PERIOD_PIVOT_FCT(crv_bdgt_oper_cost,@calendar) slices WHERE 1 = 1 AND crv_bdgt_oper_cost IS NOT NULL AND 1 = 1 ) budget_cost_op on budget_cost_op.tsv_key = inv____investment_key ORDER BY row_num
tag: @DWH_FROM@ SQL: from dwh_inv_investment dwh_inv_investment
tag: @DWH_SELECT@ SQL: declare @calendar CRV_CALENDAR insert into @calendar select * from ppm_dwh.DWH_CRV_CALENDAR_FCT('months',CONVERT(datetime,'2025-01-01',20),NULL,12,'fiscal',',ENTITY=Ent1') select q.* ,(select @[email protected]_CRV_SUM_TOTAL_FCT(sqlc.crv_bdgt_oper_cost) from dwh_inv_investment_crv_facts sqlc where sqlc.investment_key = q.inv____investment_key) as inv____budget_cost_op__tsscalar from ( select dwh_inv_investment.originating_idea_name inv____orig_idea_name,dwh_inv_investment.investment_key inv____investment_key, row_number() over( ORDER BY (SELECT 1) ) AS row_num , 5000000 + row_number() over( ORDER BY (SELECT 1) ) AS odf_pk , COUNT(1) OVER() AS rows_count

This works for other DB platforms

Environment

Clarity 16.3.3, 16.4.0 using MSSQL Database

Cause

DE179108

Resolution

This issue is fixed in:

Clarity 16.3.3 Patch 3 (16.3.3.3)
Clarity 16.4.0 Patch 1 (16.4.0.1)
Clarity 16.4.1