Clarity

  • 1.  Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Posted Sep 15, 2017 09:03 AM

    Hi Experts,

     

    My DataWarehouse job had been running without any problems, for months. However, suddenly an error was thrown. The job runs ok only with the "full load" option.

    The log is as follows, after running the job (incremental, not full load)

    ERROR 2017-09-15 09:32:39,484 [dwh_tme_entry_facts UUID: 80751433-d2fe-4887-9b6b-4eabf1aa39e7] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry : Couldn't execute SQL: DELETE FROM DWH_TME_ENTRY_FACTS WHERE EXISTS (SELECT 1 FROM [CLARITYDBLINK].niku.niku.DWH_TME_ENTRY_FACTS_V src WHERE DWH_TME_ENTRY_FACTS.TIMEENTRY_KEY = src.TIMEENTRY_KEY AND src.SLICE_CREATED_DATE BETWEEN CONVERT(DATETIME,'2017-09-15 08:52:39') AND CONVERT(DATETIME, '2017/09/15 09:29:35')) [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid column name 'SLICE_CREATED_DATE'.

     

    Does anyone had the same issue ?

    Thanks in advance !

     

    Vitor.



  • 2.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Broadcom Employee
    Posted Sep 15, 2017 09:06 AM

    Hi Vitor,

     

    Can you attach the BG-dwh all logs to this thread to take a look.

     

    Regards
    Suman Pramanik 



  • 3.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Posted Sep 15, 2017 09:11 AM
      |   view attached

    Hi Suman,

     

    Yes. For sure. Here it is.

     

    Thanks.

    Attachment(s)

    zip
    bg-dwh.log.zip   211 KB 1 version


  • 4.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Posted Sep 15, 2017 09:18 AM
      |   view attached

    This is the entire "bg-dwh.log" file.

    Tks.

    Attachment(s)

    zip
    bg-dwh.zip   218 KB 1 version


  • 5.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Broadcom Employee
    Posted Sep 15, 2017 10:12 AM
      |   view attached

    Hi Vitor,

     

    From the DWH logs it looks like in PPM there is a view DWH_X_RES_AVAIL_PER_FACTS_V and the columns slice_created_date is not available. 

     

    Can you check the view please DWH_X_RES_AVAIL_PER_FACTS_V . I am attaching the view for your reference.

    Attachment(s)



  • 6.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Posted Sep 15, 2017 11:25 AM
      |   view attached

    Hi Suman,

     

    The weird thing is that the view really has the column. The definition of the view is attached.

    And the column "SLICE_CREATED_DATE" is properly showed if I run a select of the view.

     

    I compared this view with the one you sent me. They are a little bit different, maybe because my PPM uses SQLServer.

     

    However, I tried to run the SQL below and I really got the same error as showed in the bg_dwh log.

     

    SQL:

    select * FROM [PPM_DWH].[ppm_dwh].DWH_X_RES_AVAIL_PER_FACTS
    WHERE EXISTS (SELECT 1
    FROM [CLARITYDBLINK].niku.niku.DWH_X_RES_AVAIL_PER_FACTS_V src
    WHERE DWH_X_RES_AVAIL_PER_FACTS.RESOURCE_KEY = src.RESOURCE_KEY AND DWH_X_RES_AVAIL_PER_FACTS.PERIOD_KEY = src.PERIOD_KEY
    AND src.SLICE_CREATED_DATE BETWEEN CONVERT(DATETIME,'2017-09-14 07:00:51') AND CONVERT(DATETIME, '2017/09/15 07:00:49'))

     

    RESULT:

    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'SLICE_CREATED_DATE'.

     

    Thanks,

     

    Vitor.

    Attachment(s)



  • 7.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Broadcom Employee
    Posted Sep 15, 2017 11:27 AM

    Check your DB link it show multiple time niku 

     

    FROM [CLARITYDBLINK].niku.niku.DWH_X_RES_AVAIL_PER_FACTS_V src

     

    Or some permissions are not set correctly in DB

     

    Regards

    Suman Pramanik 



  • 8.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Posted Sep 15, 2017 11:29 AM

    Ok. Makes sense. I will take a look on it. 


    Regards,

    Vitor.



  • 9.  Re: Load Data Warehouse job failed with "invalid column name SLICE_CREATED_DATE"

    Posted Sep 15, 2017 11:43 AM

    It really seems to be a problem with the DBLINK.

     

    When running a query through the DB LINK the column is showed with another name (CLARITY_UPDATED_DATE).

     

     

    I will take a look on the DB LINK definition.

     

    Tks. Vitor.