Load Data Warehouse Access Rights - Job Failed

[Anon Anon]

Hello,

 

I'm configuring a Data Warehouse in Clarity PPM 14.2

 

Things seem to be configurated correctly as I get the "available" status...

 

 

However, when I start the Load Data Warehouse Access Rights job, I get the following error message :

 

The log file is as follow :

 

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event Job Parameters

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event SRC_DATABASE_URL: jdbc:clarity:sqlserver://localhost:1433;DatabaseName=niku;InsensitiveResultSetBufferSize=0;ProgramName=Clarity

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event SRC_DATABASE_CLASS: com.ca.clarity.jdbc.sqlserver.SQLServerDriver

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event SRC_DATABASE_USER: niku

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event TGT_DATABASE_URL: jdbc:clarity:sqlserver://localhost:1433;DatabaseName=ppm_dwh;InsensitiveResultSetBufferSize=0;ProgramName=Clarity

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event TGT_DATABASE_CLASS: com.ca.clarity.jdbc.sqlserver.SQLServerDriver

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event TGT_DATABASE_USER: ppm_dwh

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event CLARITY_DB_TYPE: mssql

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event DWH_DB_TYPE: mssql

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event SRC_DBNAME: niku

INFO  2015-12-15 15:56:27,649 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event DB_LINK: PPMDWHDBLINK

INFO  2015-12-15 15:56:27,772 [Thread-136] dwh.event dwh_sec_load_master - Start of job execution

ERROR 2015-12-15 15:56:27,848 [dwh_inv_security UUID: 3cc09039-ea65-4cfe-a6cb-4dfe6fff5c2f] dwh.event IS ORCL? - An error occurred executing this job entry :

Couldn't execute SQL: UPDATE DWH_CFG_AUDIT SET DW_LOAD_START_DATE = GETDATE() WHERE TABLE_NAME='DWH_INV_SECURITY'

 

[CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_CFG_AUDIT'.

 

INFO  2015-12-15 15:56:27,855 [Thread-136] dwh.event dwh_sec_load_master - Job execution finished

ERROR 2015-12-15 15:56:27,860 [Dispatch pool-5-thread-7 : bg@Clarity-13 (tenant=clarity)] dwh.event ETL Job Failed. Log details below:

 

2015/12/15 15:57:03 - IS ORCL? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :

2015/12/15 15:57:03 - IS ORCL? - Couldn't execute SQL: UPDATE DWH_CFG_AUDIT SET DW_LOAD_START_DATE = GETDATE() WHERE TABLE_NAME='DWH_INV_SECURITY'

2015/12/15 15:57:03 - IS ORCL? -

2015/12/15 15:57:03 - IS ORCL? - [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_CFG_AUDIT'.

 

When I query the database through MSSQL Server, everything goes fine :

 

 

 

Does anyone have any ideas on how I can make it work ?

 

Thanks in advance and regards,

Eric

[Nika Hadzhikidi]

Hi Eric,

 

It looks like the job doesn't find the DWH_CFG_AUDIT table properly, and this is the first table in the list, so I would think the issue is with the SQL server connection.

 

Have you applied all the recommended SQL Server settings and permissions as per the Installation Guide? Please could you go through the list here in section Review the Data Warehouse Imported Database Options:

https://docops.ca.com/ca-ppm/14-2/installing-and-upgrading/set-up-the-data-warehouse

Please ensure they are all applied, then retry and see if it works.

 

One other thing to check is how you specified your db link. Does it work if you run the following query (please replace niku if this is not your database name):

SELECT count(1) FROM PPMDWHDBLINK.niku.niku.srm_resources

If it doesn't work, you may have to recreate your database link. To do this, you should specify the link name in NSA (different from the current one), and click on Save. This will create the link automatically.

 

Please let me know how it goes. Also, please could you confirm if Load Data Warehouse job is running fine (not the Access Rights one).

 

Kind Regards

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

[Anon Anon]

Hello Eric,

 

We are facing the exactly same issue in our environment. We are facing the same error. How did you resolve this error.

 

Kind Regards,

Abhijit