Clarity PPM1

Expand all | Collapse all

Load Data Warehouse Issues

  • 1.  Load Data Warehouse Issues

    Posted 12-16-2015 08:22 AM

    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.

     

     



  • 2.  Re: Load Data Warehouse Issues

    Posted 01-04-2016 03:40 PM

    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