Clarity PPM

Expand all | Collapse all

Load Data warehouse fails

Jump to Best Answer
  • 1.  Load Data warehouse fails

    Posted 05-20-2016 06:45 AM

    Hi,

     

    While running the Load Data warehosue job, we faced with the following error(please see the screenshot)

     

    I've enabled the custom object and all the custom attributes (both custom object and Pre-defined object)

     

    When I ran first time, I got the first error(dwh_error)and second time, I got different error(dwh_error1).

     

    While running the query "select * from DWH_meta_columns where DWH_Coulmn = 'Custom_attribute_Key'", I've two rows which are similar only with the differences in the following fields and their values

     

    1. ATTRIBUTE_DATA_TYPE

      - NUMBER(30)

      - NUMBER(32)

    2. ATTRIBUTE_CODE

      - DWH_PK

      - Custom_table_ID

    3. Extended Type

      - Null

      - Lookup

     

    So will this be the cause for the first error "Couldn't execute SQL: ALTER TABLE DWH_ODF_WM_BUDGET_LINE ADD WM_BUDGET_LINE_KEY NUMBER(32)

     

    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01430: column being added already exists in table"

     

    Also for the second error - Merge, how do I solve it?

     

    Please advise ASAP

     

    Could you please help?

     

    FYI, we've set the DWH schema with the same database as Clarity schema. DB link has been created. Timeslicing has been run before Load DWH.

     

    Thanks

     

    Sreeram



  • 2.  Re: Load Data warehouse fails

    Posted 05-20-2016 11:41 AM

    Hi Sreerambabu,

     

    You are hitting a known PPM issue: CLRT-78960 The "Load Data Warehouse" fails if the ID of a custom master object and one of its attributes are the same.

    Basically the fact that you have a custom object X and custom attribute on it with name X is creating a conflict and duplicate columns.

    This defect is resolved in PPM 14.4, so consider upgrading to have it fixed.

     

    If you cannot upgrade at this point, the workaround would be to:


    1. Go to PPM UI - Objects - open the custom object
    2. Uncheck the custom object from Data Warehouse
    3. Save and run Load Data Warehouse with Full Load
    4. If this is successful, you should be able to readd the object back, just ensure you don't add the attribute with the same name
    5. Run the job again.
    6. If step 3 is not successful, you may need to manually delete the fields from PPM, please raise a Support case for assistance.

     

    Hope this helps.

     

    Kind Regards -Nika



  • 3.  Re: Load Data warehouse fails

    Posted 05-22-2016 11:02 PM

    Hi Nika,

     

    Thanks for your Answer. I believe this would help. But when I unchecked the Datawarehouse from Custom object. I ran the job and got the following error.

     

    Do I need to delete the DB link/delete any tables related to DWH before I run the job? Please advise.

     

    INFO  2016-05-23 10:53:43,894 [Thread-868] dwh.event dwh_etl_master - Job execution finished

    ERROR 2016-05-23 10:53:44,011 [Dispatch pool-5-thread-7 : (tenant=clarity)] dwh.event ETL Job Failed. Log details below:

    2016/05/23 10:53:43 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :

    2016/05/23 10:53:43 - dwh_db_check_ - Couldn't execute SQL: MERGE INTO DWH_META_COLUMNS A USING

    2016/05/23 10:53:43 - dwh_db_check_ - (

    2016/05/23 10:53:43 - dwh_db_check_ - SELECT

    2016/05/23 10:53:43 - dwh_db_check_ -   UPPER(SRC_TABLE_NAME) SRC_TABLE_NAME

    2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(SRC_COLUMN_NAME) SRC_COLUMN_NAME

    2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(DWH_TABLE_NAME) DWH_TABLE_NAME

    2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(DWH_COLUMN_NAME) DWH_COLUMN_NAME

    2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(ATTR_TYPE) ATTR_TYPE

    2016/05/23 10:53:43 - dwh_db_check_ - , CASE

    2016/05/23 10:53:43 - dwh_db_check_ - WHEN (UPPER(ATTR_DATA_TYPE)='DATE' OR UPPER(ATTR_DATA_TYPE)='INTEGER' OR UPPER(ATTR_DATA_TYPE)='TIMESTAMP') THEN UPPER(ATTR_DATA_TYPE)

    2016/05/23 10:53:43 - dwh_db_check_ - WHEN (UPPER(ATTR_TYPE)='CLOB' AND UPPER(ATTR_DATA_TYPE) NOT LIKE '%VARCHAR%') THEN UPPER(ATTR_DATA_TYPE)

    2016/05/23 10:53:43 - dwh_db_check_ -   ELSE UPPER(ATTR_DATA_TYPE)||'('||NVL(ATTR_DATA_SIZE,'')||')'

    2016/05/23 10:53:43 - dwh_db_check_ -   END ATTR_DATA_TYPE_SRC

    2016/05/23 10:53:43 - dwh_db_check_ - , IS_LOOKUP_ATTR

    2016/05/23 10:53:43 - dwh_db_check_ - , IS_MULTIVALUED

    2016/05/23 10:53:43 - dwh_db_check_ - , LOOKUP_TYPE

    2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(LKP_DIM_TABLE_NAME) LKP_DIM_TABLE_NAME

    2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(DWH_LOOKUP_KEY) DWH_LOOKUP_KEY

    2016/05/23 10:53:43 - dwh_db_check_ - , IS_CUSTOM

    2016/05/23 10:53:43 - dwh_db_check_ - , IS_KEY

    2016/05/23 10:53:43 - dwh_db_check_ - , IS_DELETED DROP_FLAG

    2016/05/23 10:53:43 - dwh_db_check_ - , 0 COL_MOD

    2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(DIM_KEY) DIM_KEY

    2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(DIM_DISPLAY) DIM_DISPLAY

    2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(OBJECT_CODE) OBJECT_CODE

    2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(ATTRIBUTE_CODE) ATTRIBUTE_CODE

    2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(EXTENDED_TYPE) EXTENDED_TYPE

    2016/05/23 10:53:43 - dwh_db_check_ - FROM

    2016/05/23 10:53:43 - dwh_db_check_ - DWH_META_COLUMNS@PPMDBLINK

    2016/05/23 10:53:43 - dwh_db_check_ - ) B

    2016/05/23 10:53:43 - dwh_db_check_ - ON

    2016/05/23 10:53:43 - dwh_db_check_ - ( A.DWH_TABLE = B.DWH_TABLE_NAME AND A.DWH_COLUMN = B.DWH_COLUMN_NAME AND A.SRC_TABLE = B.SRC_TABLE_NAME AND A.SRC_COLUMN = B.SRC_COLUMN_NAME )

    2016/05/23 10:53:43 - dwh_db_check_ - WHEN MATCHED THEN UPDATE SET

    2016/05/23 10:53:43 - dwh_db_check_ - A.ATTRIBUTE_TYPE = B.ATTR_TYPE,A.ATTRIBUTE_DATA_TYPE = B.ATTR_DATA_TYPE_SRC,A.LOOKUP_TABLE = B.LKP_DIM_TABLE_NAME,A.DWH_RELATED_LOOKUP_KEY = B.DWH_LOOKUP_KEY,

    2016/05/23 10:53:43 - dwh_db_check_ - A.DROP_FLAG = B.DROP_FLAG,A.IS_MULTIVALUED = B.IS_MULTIVALUED,A.DIM_KEY = B.DIM_KEY,A.DIM_DISPLAY = B.DIM_DISPLAY,A.IS_KEY = B.IS_KEY,A.OBJECT_CODE = B.OBJECT_CODE,

    2016/05/23 10:53:43 - dwh_db_check_ -   A.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE, A.EXTENDED_TYPE = B.EXTENDED_TYPE

    2016/05/23 10:53:43 - dwh_db_check_ - WHEN NOT MATCHED THEN INSERT

    2016/05/23 10:53:43 - dwh_db_check_ - (A.DWH_TABLE,A.DWH_COLUMN,A.SRC_TABLE,A.SRC_COLUMN,A.ATTRIBUTE_TYPE,A.ATTRIBUTE_DATA_TYPE,A.DWH_RELATED_LOOKUP_KEY,A.LOOKUP_TABLE,A.DROP_FLAG,A.COL_MODIFIED,A.IS_MULTIVALUED

    2016/05/23 10:53:43 - dwh_db_check_ - ,A.DIM_KEY,A.DIM_DISPLAY,A.IS_KEY,A.OBJECT_CODE,A.ATTRIBUTE_CODE,A.EXTENDED_TYPE)

    2016/05/23 10:53:43 - dwh_db_check_ - VALUES

    2016/05/23 10:53:43 - dwh_db_check_ - (B.DWH_TABLE_NAME,B.DWH_COLUMN_NAME,B.SRC_TABLE_NAME,B.SRC_COLUMN_NAME,B.ATTR_TYPE,B.ATTR_DATA_TYPE_SRC,B.DWH_LOOKUP_KEY,B.LKP_DIM_TABLE_NAME,B.DROP_FLAG,B.COL_MOD,B.IS_MULTIVALUED,

    2016/05/23 10:53:43 - dwh_db_check_ - B.DIM_KEY,B.DIM_DISPLAY,B.IS_KEY,B.OBJECT_CODE,B.ATTRIBUTE_CODE,B.EXTENDED_TYPE)

    2016/05/23 10:53:43 - dwh_db_check_ -

    2016/05/23 10:53:43 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in the source tables

    2016/05/23 10:53:42 - audit table name -  & DWH_CFG_AUDIT

    2016/05/23 10:53:42 - audit table name -  & DWH_CFG_AUDIT



  • 4.  Re: Load Data warehouse fails

    Posted 05-23-2016 01:46 AM
      |   view attached

    Hi Nika,

     

    Thanks for your Answer. I believe this would help. But when I unchecked the Datawarehouse from Custom object. I ran the job and got the following error.(please check the attached logs)

     

    Do I need to delete the DB link/delete any tables related to DWH before I run the job? Please advise.

     

    Thanks

     

    Sreeram

    Attachment(s)

    zip
    bg-dwh.log.zip   1K 1 version


  • 5.  Re: Load Data warehouse fails

    Posted 05-23-2016 05:37 PM

    Hi Sreerambabu,

     

    You have the same error message:
    ORA-30926: unable to get a stable set of rows in the source tables

    As I said above, unchecking the object does not always work, and if it doesn't, you should raise a case with Support to get the extra entries removed manually with a SQL statement. We can help you with that.

    This issue is not related to the DBLINK and you should not delete anything else. What we will do together is removing the duplicate entries, and this will allow us to run the Load Data Warehouse job with no issue.

     

    Kind Regards -Nika



  • 6.  Re: Load Data warehouse fails

    Posted 05-24-2016 05:09 AM

    Thanks Nika, I already raised the case in Support. They have advised not to remove anything manually. will it be ok?

     

    I've tried the following query

     

    select src_table, src_column, dwh_table, dwh_column, count(*)

    from dwh_meta_columns

    group by src_table, src_column, dwh_table, dwh_column

    having count(*) > 1

     

    Got the output of Count 2 for the Custom attribute

    src_tablesrc_columndwh_tabledwh_columncount(*)
    DWH_custom_obj_VCust_attribute_KeyDWH_Custom_ObjCust_attribute_Key2

     

    So should I remove from DWH table manually? Please advise

     

    FYI, Cust_Attribute_Key is not the attribute, rather its the Lookup attribute.

     

    Thanks

     

    Sreeram



  • 7.  Re: Load Data warehouse fails

    Posted 05-24-2016 01:21 PM

    Hi Sreerambabu

     

    Yes this is the query we use, but for DWH side. For PPM side this will be the query:


    select src_table_name, src_column_name, dwh_table_name, dwh_column_name, count(*)
    from dwh_meta_columns
    group by src_table_name, src_column_name, dwh_table_name, dwh_column_name
    having count(*) > 1

     

    Normally after unchecking the custom object you should not be having it there anymore.

     

    I found your Support case : please let me take a look and I'll advise. Thanks -Nika



  • 8.  Re: Load Data warehouse fails
    Best Answer

    Posted 06-08-2016 10:05 AM

    This issue was resolved after manually removing the additional row from the PPM table - DWH_META_COLUMNS. The conflicting attribute was later unchecked of the Data Warehouse and the load Data Warehouse job with Full load was run successfully. The workaround relates to CLRT-78960 which Nika mentioned above.