Clarity

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 .

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

Thanks and regards

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

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.

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

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

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

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

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

Thanks and Regards.

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

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

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.

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

Ok sorry, and thanks

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

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

ok Thank you very much.

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

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

Just all good... thanks Nika.

Hi RnFernandez, Nika_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

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