Clarity PPM

Expand all | Collapse all

Error job Load Data WareHouse

Jump to Best Answer
  • 1.  Error job Load Data WareHouse

    Posted 11-10-2015 05:47 PM

    Hi

     

     

    I have a problem when I run the job Load Data Warehouse, I throw the error like that shows up in the picture attached, the procedure was Segui restore database ppm_dwh, but when you run the job again, again came the error .

    load.png

     

     

    Anyone with information that can help solve this situation, would greatly appreciate it.

     

     

    Thanks and regards



  • 2.  Re: Error job Load Data WareHouse

    Posted 11-11-2015 10:09 AM

    Hi Mariela,

     

    The job is looking for one of the PMO attributes that are included in some of the reports, and it looks like it cannot find it.
    Please could you do the following:

     

    1. Go to Administration - Objects - Status Report
    2. Find Report Status in the list
    3. Double click on the attribute to open the attribute properties
    4. Uncheck the attribute for Including in DWH
    5. Save
    6. Run Load DWH job with Full Load. The job may fail this is okay.
    Once it's done, please do the following:
    7. Go to Administration - Objects - Status Report
    8. Double click on the attribute Report Status to open the attribute properties. It's important to make the changes from the properties page.
    9. Check the attribute for DWH, Save
    10. Run Load DWH job with Full Load, this should work

    Please let me know how it goes.

     

    Kind Regards

     

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 3.  Re: Error job Load Data WareHouse

    Posted 11-11-2015 11:22 AM

    Hello Nika

     

    I appreciate your response, but not if I'm doing wrong instructions you gave me, I send you some screenshots of what I was doing because even doing this procedure even me the same error keeps coming.

     

    •      Take the first Status report because it belongs PMO.

     

    status report.png

     

    • Then, I remove the check to include a dwh, and run the job Load DWH indeed shown me a new error.include_dwh.png
    • This is the error.

            error_1job.png

    • And then returned to the DWH include the project status and all the attributes,

    attributes.png

     

    And then returned to the DWH include the project status and all attributes, ran the job again, but again I came out the error.

     

    Could you tell if something is wrong in the process, I'd appreciate it.

    Thanks



  • 4.  Re: Error job Load Data WareHouse
    Best Answer

    Posted 11-11-2015 11:47 AM

    Hi Mariela,

     

     

    It looks like you unchecked the Status Report object and not the attribute Report Status. The names are similar but you had to go to Attributes first, not remain on the object. No worries, we will fix this.

     

    I don't see your current error message, but please try the following solution then:

     

    1.            Go to UI - Administration - Objects
    2.            Open Status Report Object and uncheck it for DWH if not unchecked already from before
    3.            Save
    4.            Now run Load DWH job with Full Load. It may fail but this is okay.
    5.            When completed/failed, please go to UI - Administration -  Content Addins - PMO
    6.            Go to items
    7.            Filter for Status Report - type should be Object
    8.            Check the object for update: click on Apply
    9.            Once done, go back to UI - Administration - Objects. Find Status Report in the list, open the object
    10.          Recheck the object for DWH, Save
    11.          Now go to object's Attributes, open each one of them and check for DWH. Do not enable them for DWH from the list, there is a bug and it will not work. You have to open each one and check Include in DWH.
    12.          Once done, run another full load of DWH.

     

    Kind Regards

     

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 5.  Re: Error job Load Data WareHouse

    Posted 11-11-2015 01:03 PM

    This procedure worked out well, I ran the job without mistakes, I appreciate your information and help ..

     

     

    Thanks and Regards.



  • 6.  Re: Error job Load Data WareHouse

    Posted 02-21-2017 11:24 AM

    Nika in ppm 15.1 The check box in step 11 appears gray and I can not update  it



  • 7.  Re: Error job Load Data WareHouse

    Posted 02-21-2017 11:32 AM

    Hi Ricardo

     

    Yes starting 14.4 those fields will be grayed out.

    Did you check my answer here? https://communities.ca.com/message/241959937?commentID=241959937&et=watches.email.thread#comment-241959937 

     

    If this doesn't help, please check if the Status report fields are not set to IS_DELETED=1 in DWH_META_COLUMNS table on PPM database. You can check this with query:

     

    select * from dwh_meta_columns where object_code = 'cop_prj_statusrpt' and is_deleted =1

     

    If you see any, reset them to is_deleted =0 and run Load DWH - Full.

     

    Please let me know how it goes. Thanks -Nika



  • 8.  Re: Error job Load Data WareHouse

    Posted 02-21-2017 03:30 PM

    I followed the steps indicated but the job LOAD DWH failed again.

    When I wanted to execute the query, I did not find the IS_DELETED field in the table. I found the DROP_*** = 0 field in all rows.

    I'm still in trouble.



  • 9.  Re: Error job Load Data WareHouse

    Posted 02-21-2017 03:43 PM
    SELECT TOP 1000 [DWH_TABLE]
          ,[DWH_COLUMN]
          ,[SRC_TABLE]
          ,[SRC_COLUMN]
          ,[IS_MULTIVALUED]
          ,[ATTRIBUTE_TYPE]
          ,[ATTRIBUTE_DATA_TYPE]
          ,[LOOKUP_TABLE]
          ,[DWH_RELATED_LOOKUP_KEY]
          ,[SRC_ADDIN]
          ,[DROP_FLAG]
          ,[COL_MODIFIED]
          ,[DIM_KEY]
          ,[DIM_DISPLAY]
          ,[IS_KEY]
          ,[OBJECT_CODE]
          ,[ATTRIBUTE_CODE]
          ,[EXTENDED_TYPE]
          ,[IS_FISCAL_TSV]
      FROM [ppm_dwh].[ppm_dwh].[DWH_META_COLUMNS]


  • 10.  Re: Error job Load Data WareHouse

    Posted 02-21-2017 04:02 PM

    Hi Ricardo

     

    Please make sure you execute the query on the PPM database not the DWH. I think this is why you don't see the field.

    Let me know how it goes.

     

    Thanks -Nika



  • 11.  Re: Error job Load Data WareHouse

    Posted 02-21-2017 05:01 PM

    Ok sorry, and thanks



  • 12.  Re: Error job Load Data WareHouse

    Posted 02-22-2017 08:24 AM

    Yes, I was wrong, I found 21 row with the field IS_DELETED = 1 in PPM database.
    But when I run LOAD DWG is FALLED.

    Job                                    Job Type                     Job ID    Job Status Scheduled
    Logs Load Data Warehouse Load Data Warehouse 5190103     Failed

     

    the following are the errors after of run Load DWH

     

    bg-ca.log

     

    ERROR 2017-02-22 10:06:33,238 [Dispatch Load Data Warehouse : bg@CACLTDW02 (tenant=clarity)] niku.njs (clarity:admin:68149973__CE192B2D-FDDA-410F-AFB2-95394A5AD6F1:Load Data Warehouse)
    java.lang.Exception: ETL Job Failed. Please see log bg-dwh.log for details.
    at com.ca.clarity.etl.kettle.AbstractKettleJob.runETLJob(AbstractKettleJob.java:212)
    at com.ca.clarity.etl.kettle.AbstractKettleJob.executeETLJob(AbstractKettleJob.java:89)
    at com.ca.clarity.njs.listeners.ETLJobListener.scheduledEventFired(ETLJobListener.java:31)
    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:395)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    ERROR 2017-02-22 10:06:33,300 [Dispatch Load Data Warehouse : bg@CACLTDW02 (tenant=clarity)] niku.njs (clarity:admin:68149973__CE192B2D-FDDA-410F-AFB2-95394A5AD6F1:Load Data Warehouse) Error executing job: 5190103
    java.lang.Exception: ETL Job Failed. Please see log bg-dwh.log for details.
    at com.ca.clarity.etl.kettle.AbstractKettleJob.runETLJob(AbstractKettleJob.java:212)
    at com.ca.clarity.etl.kettle.AbstractKettleJob.executeETLJob(AbstractKettleJob.java:89)
    at com.ca.clarity.njs.listeners.ETLJobListener.scheduledEventFired(ETLJobListener.java:31)
    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:395)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

     

    bg-dwh.log

     

    ERROR 2017-02-22 10:06:32,865 [dwh_fin_benefit_period_facts UUID: 6b8d22ef-98b9-4f15-8521-b568083cf902] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
    Couldn't execute SQL: DECLARE @V_DBNAME VARCHAR(100);
    SELECT @V_DBNAME = DB_NAME();

    EXEC ('ALTER DATABASE ' + @V_DBNAME + ' SET RECOVERY BULK_LOGGED;');
    EXEC CMN_SAVEDROP_CREATE_MVIEWS_SP 'DWH_FIN_BENEFIT_PERIOD_FACTS', 'SAVE_DROP';

    [CA Clarity][SQLServer JDBC Driver][SQLServer]The operation cannot be performed on database "ppm_dwh" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

    INFO 2017-02-22 10:06:32,883 [Thread-22722] dwh.event dwh_etl_master - Job execution finished
    ERROR 2017-02-22 10:06:33,238 [Dispatch Load Data Warehouse : bg@CACLTDW02 (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
    2017/02/22 10:06:29 - audit table name - & DWH_CFG_AUDIT
    2017/02/22 10:06:31 - audit table name - & DWH_CFG_AUDIT
    2017/02/22 10:06:32 - ClarityDB - isOracle? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
    2017/02/22 10:06:32 - ClarityDB - isOracle? - Couldn't execute SQL: DECLARE @V_DBNAME VARCHAR(100);
    2017/02/22 10:06:32 - ClarityDB - isOracle? - SELECT @V_DBNAME = DB_NAME();
    2017/02/22 10:06:32 - ClarityDB - isOracle? -
    2017/02/22 10:06:32 - ClarityDB - isOracle? - EXEC ('ALTER DATABASE ' + @V_DBNAME + ' SET RECOVERY BULK_LOGGED;');
    2017/02/22 10:06:32 - ClarityDB - isOracle? - EXEC CMN_SAVEDROP_CREATE_MVIEWS_SP 'DWH_FIN_BENEFIT_PERIOD_FACTS', 'SAVE_DROP';
    2017/02/22 10:06:32 - ClarityDB - isOracle? -
    2017/02/22 10:06:32 - ClarityDB - isOracle? - [CA Clarity][SQLServer JDBC Driver][SQLServer]The operation cannot be performed on database "ppm_dwh" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
    null



  • 13.  Re: Error job Load Data WareHouse

    Posted 02-22-2017 09:41 AM

    Hi Ricardo,

     

    Glad to hear you got this corrected, there is a new error message now:

    [CA Clarity][SQLServer JDBC Driver][SQLServer]The operation cannot be performed on database "ppm_dwh" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

     

    We do not support SQL server mirroring, this is why it fails. Please ask your DBA to remove the mirroring on ppm_dwh database and try again.

     

    Hope this helps -Nika



  • 14.  Re: Error job Load Data WareHouse

    Posted 02-22-2017 03:33 PM

    ok Thank you very much.

    I'll tell you when I removed that property from the database



  • 15.  Re: Error job Load Data WareHouse

    Posted 02-23-2017 03:51 PM

    Hi Nika, after your suggestions, you can run the jobs ok.
    But just in JASPERSOFT, when I try to create an Ad_Hoc_View, more precisely when I want to add a field to the view, it displays the following error: "No message found under code` null` for locale `en_US"

    I tried to correct it with this suggestion: https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec1386656.html .... but still unable to create the view



  • 16.  Re: Error job Load Data WareHouse

    Posted 02-24-2017 04:00 PM

    Just all good... thanks Nika.



  • 17.  Re: Error job Load Data WareHouse

    Posted 01-10-2018 08:48 AM

    Hi RnFernandezNika_Hadzhikidi

     

    Can you please share how you resolved that issue "No message found under code` null` for locale `en_US"? 

    I'm currently facing the same but as RnFernandez wrote, that TEC article didn't help me either. 

     

    Note: DWH jobs run successfully. 

    Thanks

    Matej



  • 18.  Re: Error job Load Data WareHouse

    Posted 03-03-2017 04:20 PM

    hi Nika in other environment when I install jasper, this is the error:

    bg-ca.log

     

    ERROR 2017-03-03 18:01:38,542 [Dispatch Post Timesheets : bg@CACLTHW02 (tenant=clarity)] xql2.xbl (clarity:admin:68138777__7C043BA6-8AC0-4E96-A05D-DA67F5A95B6C:Post Timesheets) ****POSTTIMESHEET: Couldn't post adjustmented timesheet-->adjustedTimesheetID=5040603; adjustedTimeEntryID=5189697; assignmentID=5084113
    ERROR 2017-03-03 18:05:11,416 [Dispatch Load Data Warehouse : bg@CACLTHW02 (tenant=clarity)] kettle.JasperSoftDomainUpdates (clarity:admin:68138740__9F4C95BC-86D8-4E3E-9FAC-FD7B7E16F137:Load Data Warehouse)

    Data warehouse is not up to date, DWH meta column sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key is not in DWH table dwh_inv_status_report_ln hence quiting from domain updates


    ERROR 2017-03-03 18:05:11,432 [Dispatch Load Data Warehouse : bg@CACLTHW02 (tenant=clarity)] kettle.JasperSoftDomainUpdates (clarity:admin:68138740__9F4C95BC-86D8-4E3E-9FAC-FD7B7E16F137:Load Data Warehouse) Error occurred while processing domain attributes. com.niku.union.persistence.PersistenceException:
    SQL error code: 208
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'dwh_inv_status_report_ln'.
    Executed:
     select sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key from dwh_inv_status_report_ln
    Derived from statement:
    <?xml version="1.0" encoding="UTF-8"?>
    <statement sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd">
      <sql dbVendor="all">
        <text>
              select @select@ from @from@
            </text>
        <param name="select" direction="IN" dynamicReplacement="@select@" expressionListDelimiter=","/>
        <param name="from" direction="IN" dynamicReplacement="@from@" expressionListDelimiter=","/>
      </sql>
    </statement>

    Referenced by:
    <?xml version="1.0" encoding="UTF-8"?>
    <statementRef id="njs.validateDWHTableColumn_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>

    Using input:
    {select=sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key, from=dwh_inv_status_report_ln}
    Using dynamic SQL:
    tag: @select@ SQL:sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key
    tag: @from@ SQL:dwh_inv_status_report_ln

    ERROR 2017-03-03 18:05:11,447 [Dispatch Load Data Warehouse : bg@CACLTHW02 (tenant=clarity)] niku.njs (clarity:admin:68138740__9F4C95BC-86D8-4E3E-9FAC-FD7B7E16F137:Load Data Warehouse) Error executing job: 5192103
    com.niku.union.persistence.PersistenceException:
    SQL error code: 208
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'dwh_inv_status_report_ln'.
    Executed:
     select sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key from dwh_inv_status_report_ln
    Derived from statement:
    <?xml version="1.0" encoding="UTF-8"?>
    <statement sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd">
      <sql dbVendor="all">
        <text>
              select @select@ from @from@
            </text>
        <param name="select" direction="IN" dynamicReplacement="@select@" expressionListDelimiter=","/>
        <param name="from" direction="IN" dynamicReplacement="@from@" expressionListDelimiter=","/>
      </sql>
    </statement>

    Referenced by:
    <?xml version="1.0" encoding="UTF-8"?>
    <statementRef id="njs.validateDWHTableColumn_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/>

    Using input:
    {select=sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key, from=dwh_inv_status_report_ln}
    Using dynamic SQL:
    tag: @select@ SQL:sr_cost_effort_status, sr_report_status, sr_schedule_status, sr_scope_status, status_report_key
    tag: @from@ SQL:dwh_inv_status_report_ln