Load Data Warehouse Issues

[Legacy User]

Hi All

 

I have a problem with running Load Data Warehouse, Data Warehouse settings have been set in System Options, both language and Entity for Fiscal Periods are set. The Time Slicing Job have been run and completed.

And I still get errors when running the job.

 

First off a little information about the setup.

It is an old setup from 12.1 updated to 14.3, originally the database was created in SQL2005 which means is is using Latin1_General_CI_AS and not SQL_Latin1_General_CP1_CI_AS as collation.

Because of the if was forced to change the collation of the dwh database to match the ppm database, or I would get a collation does not match error.

 

Now when I try and run the Load Data Warehouse job i get two SQL errors:

1.

Execute SQL script - org.pentaho.di.core.exception.KettleStepException:

Error while running this step!

Couldn't execute SQL: update

  DWH_CFG_AUDIT

set

  DW_UPDATED_DATE = (select dwh_dim_start_date from dwh_cfg_settings),

  DW_LOAD_END_DATE = CONVERT(DATETIME, '2015/12/16 14:00:34')

WHERE

  TABLE_NAME='DWH_CMN_EXCHANGE_RATE'

[CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'dw_updated_date', table 'ppm_dwh.ppm_dwh.DWH_CFG_AUDIT'; column does not allow nulls. UPDATE fails.

  at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)

  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)

  at java.lang.Thread.run(Thread.java:745)

Caused by: org.pentaho.di.core.exception.KettleDatabaseException:

Couldn't execute SQL: update

  DWH_CFG_AUDIT

set

  DW_UPDATED_DATE = (select dwh_dim_start_date from dwh_cfg_settings),

  DW_LOAD_END_DATE = CONVERT(DATETIME, '2015/12/16 14:00:34')

WHERE

  TABLE_NAME='DWH_CMN_EXCHANGE_RATE'

 

2.

dwh_tr_post_execution_sql - Errors detected!

User Defined Java Class - Unexpected error

User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_CMN_EXCHANGE_RATE

  at Processor.processRow(Processor.java:64)

  at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)

  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)

  at java.lang.Thread.run(Thread.java:745)

 

Especially error 1 is weird with the column does not allow nulls, seems like Clarity is inserting null instead of the current data,

 

Hope any of you have some idea about what is going on.

 

 

[Nika Hadzhikidi]

Hi,

 

 

It's great that you changed the collation to a supported one. The SQL collations between PPM and PPM_DWH should always match.

 

From the error message you described, when it's running the job, it looks like this part is returning a null:

select dwh_dim_start_date from dwh_cfg_settings

Please could you try the following:

 

1. Run the query on your PPM_DWH database:

select dwh_dim_start_date from dwh_cfg_settings

See if that returns any results.

2. If it does not or returns Null, we may have to remove/readd the Load Data Warehouse Language and settings. Please ensure you always add English as well if you are using any other language as official. Fiscal periods for the entity should be Monthly.

 

The second error you are getting is just a consequence of the first one, so you can safely ignore it.

 

Please let me know how it goes.

 

Kind Regards

 

Nika Hadzhikidi
CA Technologies
Principal Support Engineer