I'm configuring our Data Warehouse in 18.104.22.168 version Clarity PPM.
I've created DBLink and it went fine. I see "Available" status in CSA.
I executed "Time Slicing" job and ended OK.
Then both DW loading jobs failed inmediately after its execution, and these are their logs:
1) After executing "Load Data Warehouse - Access Rights" job:
dwh_db_check_ - An error occurred executing this job entry :
Couldn't execute SQL: DECLARE
P_DB_LINK := 'PPMDBLINK';
P_BATCH_SIZE := 50000;
P_DB_LINK => P_DB_LINK,
P_BATCH_SIZE => P_BATCH_SIZE
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "PERMISSION_KEY": invalid identifier
ORA-06512: at "PPM_DWH.DWH_INV_SECURITY_PRC", line 37
ORA-06512: at line 8
Then I check DWH_INV_SECURITY_PRC procedure and I see a query like this: SELECT INVESTMENT_KEY, USER_KEY, USER_NAME, INVESTMENT_TYPE_KEY, GLOBAL_VIEW_RIGHT, PERMISSION_KEY FROM DWH_INV_SECURITY_V@'||P_DB_LINK
Indeed, if we look for PERMISSION_KEY column in DWH_INV_SECURITY_V view, we can't find it, neither GLOBAL_VIEW_RIGHT column.
2) After executing "Load Data Warehouse" job:
Couldn't execute SQL: BEGIN
P_DBLINK => 'PPMDBLINK'
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
ORA-02063: preceding line from PPMDBLINK
ORA-06512: at "PPM_DWH.DWH_CFG_PRE_CONFIG_SP", line 37
ORA-06512: at line 2
When I review DWH_CFG_PRE_CONFIG_SP code, I see there are lots of inconsistencies, thus several tables are not placed in our tablespace (CMN_LANGUAGES, ODF_CUSTOM_ATTRIBUTES, CMN_LIST_OF_VALUES, etc).
Can anyone give us a hand?
The problem looks like the import of Dataware house template is not correct as its unable to find the columns, one thing you can try is to drop the datawarehouse database and reimport, create the database link and run the job again.
We had a problem with the DBLink, it was pointing to the wrong database.
Thank you for your answer and apologise for wasting your time.
Glad that you fixed it. Have a good one.