Data Provider Publish Fails with SQL Error 207

Products

• Clarity PPM On Premise

Issue/Introduction

Adding time-varying attributes to data provider leads to SQL Error Code 207

Steps to replicate:

1. From MUX go to Reporting > Go to Data Provider
2. Add 'Investment', 'Resources', 'Projects' and 'Cost Plan Detail' to the data provider
3. Join the Objects and publish

Expected Behaviour:

The Data Provider published 

Actual Behaviour:

Finding "This data provider cannot be published. Please correct the following items and republish... API-1019: Could not process the request due to internal error."

App logs:

ERROR ... Error occurred while validating the query com.niku.union.persistence.PersistenceException: SQL error code: 207 Error message: Invalid column name 'crv_cust_z_sum_cost_odf'. Executed:.....

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'crv_cust_z_sum_cost_odf'.

 

We had a look into the issue and figured out, that the issue lies with three time varying attributes in the data provider: 

• crv_cust_z_sum_act_odf
• crv_cust_z_sum_cost_odf
• crv_cust_z_sum_rev_odf

As soon as we removed the attributes from the data provider, it could be published. (That is the current workaround)

All three of these attributes have the same setup: 

• time varying (fiscal, money)
• no API ID
• included in DWH

Environment

Clarity 16.3.3

Resolution

Verify Column Existence in DWH Database 

• Connect directly to your Clarity Data Warehouse database (the one associated with ppm_dwh schema).
• Run a query to check if the column crv_cust_z_sum_cost_odf exists in the dwh_fin_plan_crv_facts table.
  sql SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'dwh_fin_plan_crv_facts' AND COLUMN_NAME LIKE 'crv_cust_z_sum_cost_odf%'; -- Use LIKE for flexibility
• Repeat for crv_cust_z_sum_act_odf and crv_cust_z_sum_rev_odf.

If the related columns are not available, then run the following:

1. Exclude Data Warehouse property for these Time varying attributes i.e. uncheck "include in DWH" and run a full DWH load job
2. Include Data Warehouse property. i.e. check "include in DWH" and run a full DWH load job