Clarity PPM

Expand all | Collapse all

Load DWH job fails

Jump to Best Answer
  • 1.  Load DWH job fails

    Posted 11-29-2016 06:42 AM

    Hi,

    we have completed the dwh and jaspersoft installation and configurations. Both show as 'Available' in the CSA.

    When I run the query to see if my dblink is set up correctly, data is returned.

     

    So, when the time came to run the load dwh job it fails almost immediately.

    In the bg-dwh.log file I see:

     

    INFO  2016-11-29 11:28:51,740 [Thread-14] dwh.event dwh_etl_master - Start of job execution

    ERROR 2016-11-29 11:28:52,398 [Thread-14] dwh.event dwh_db_check_ - An error occurred executing this job entry :

    Couldn't execute SQL: EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'

     

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Procedure or function DWH_CFG_PRE_CONFIG_SP has too many arguments specified.

     

    INFO  2016-11-29 11:28:52,406 [Thread-14] dwh.event dwh_etl_master - Job execution finished

    ERROR 2016-11-29 11:28:52,437 [Dispatch Load Data Warehouse : bg@Server (tenant=clarity)] dwh.event ETL Job Failed. Log details below:

    2016/11/29 11:28:52 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :

    2016/11/29 11:28:52 - dwh_db_check_ - Couldn't execute SQL: EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'

    2016/11/29 11:28:52 - dwh_db_check_ -

    2016/11/29 11:28:52 - dwh_db_check_ - [CA Clarity][SQLServer JDBC Driver][SQLServer]Procedure or function DWH_CFG_PRE_CONFIG_SP has too many arguments specified.

    null

     

     

     

     

     on the application front end I see:

     

    "Financial plans has invalid start or end periods."

     

    Can anyone please advise? Thanks.



  • 2.  Re: Load DWH job fails

    Posted 11-29-2016 07:05 AM

    I think I might have found the answer on another thread - 

    https://communities.ca.com/thread/241753362

     

    There was a database mismatch between our dwh db & the niku db.

    I ran the command to upgrade the dwh db and it completed successfully.

     

    We will attempt the load dwh job again to see how it goes.



  • 3.  Re: Load DWH job fails

    Posted 11-29-2016 08:01 AM

    Unfortunately the Load DWH failed again...

     

     

    Should we restart the services after the dwh database is updated?



  • 4.  Re: Load DWH job fails

    Posted 11-29-2016 08:40 AM

    So we tried restarting the services but we get the same error when we try the Load DWH:

     

    ERROR 2016-11-29 13:36:23,277 [Thread-13] dwh.event dwh_db_check_ - An error occurred executing this job entry :
    Couldn't execute SQL: EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Procedure or function DWH_CFG_PRE_CONFIG_SP has too many arguments specified.

    INFO 2016-11-29 13:36:23,286 [Thread-13] dwh.event dwh_etl_master - Job execution finished
    ERROR 2016-11-29 13:36:23,310 [Dispatch Load Data Warehouse : bg@APP_SERVER (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
    2016/11/29 13:36:23 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
    2016/11/29 13:36:23 - dwh_db_check_ - Couldn't execute SQL: EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'
    2016/11/29 13:36:23 - dwh_db_check_ -
    2016/11/29 13:36:23 - dwh_db_check_ - [CA Clarity][SQLServer JDBC Driver][SQLServer]Procedure or function DWH_CFG_PRE_CONFIG_SP has too many arguments specified.
    null



  • 5.  Re: Load DWH job fails

    Posted 11-30-2016 08:59 AM

    Just bumping this as we have no idea how to proceed to resolve this. Load DWH fails almost immediately with this error:

     

    An error occurred executing this job entry :

    2016/11/29 11:28:52 - dwh_db_check_ - Couldn't execute SQL: EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'

    2016/11/29 11:28:52 - dwh_db_check_ -

    2016/11/29 11:28:52 - dwh_db_check_ - [CA Clarity][SQLServer JDBC Driver][SQLServer]Procedure or function DWH_CFG_PRE_CONFIG_SP has too many arguments specified.

    null

     



  • 6.  Re: Load DWH job fails

    Posted 11-30-2016 03:56 PM

    Please run this query.

    1> DWH - select * from cmn_dwh_install_history order by installed_date
    2> CA PPM - select * from cmn_install_history order by installed_date

    If the version of DWH is different then you should manually upgrade the DWH s



  • 7.  Re: Load DWH job fails

    Posted 11-30-2016 04:41 PM

    Sorry my last post was not complete.

     

    I believe the version may still not match per the behavior. Please run this query:

     

    Please run this query.

    1> DWH - select * from cmn_dwh_install_history order by installed_date
    2> CA PPM - select * from cmn_install_history order by installed_date

    If the version of DWH is different then you should manually upgrade the DWH schema.



  • 8.  Re: Load DWH job fails

    Posted 12-01-2016 05:11 AM

    Thank you Elizabeth. Unfortunately we have already done that. There was indeed a database mismatch between the dwh db and the niku db. But we manually updated the dwh db and now both are showing as having the same version when we run the queries you suggested. We also restarted services after this change but the same error is being thrown when we try to run the Load DWH.



  • 9.  Re: Load DWH job fails

    Posted 12-01-2016 05:53 AM

    Actually just noticed that our 'Create and Update Jaspersoft users job is failing.

     

    Failed to get an instance of ClarityJasperAdmin :: Exception getting ClarityJasperAdmin : Could not login to http://reportserver:8080/reportservice with username jasperadmin

     

    When I go to Advanced Reporting I get an error:

    Error 401 - User not registered

    ErrorErrorUser is not registered on the reporting server.

     

    But when I go to: http://reportserver:8080/reportservice and check Manage Users - I see my jasperadmin user listed and the organization is specified. The same user id is specified in the CSA under the reporting tab.

     

    Do I need to redo my Trust keys or something?



  • 10.  Re: Load DWH job fails
    Best Answer

    Posted 12-06-2016 10:05 AM

    Ok so we finally got this resolved.

    It turned out that the issue was down to the PPM and DWH database versions not being the same.

    So I deleted my existing ppm_dwh and login. Then I downloaded the 15-1 ISO again, and restored the correct ppm_dwh base database.

     

    Now, when I run the below queries, both databases, PPM and DWH, show the same version.

    DWH - select * from cmn_dwh_install_history order by installed_date
    CA PPM - select * from cmn_install_history order by installed_date

     

    After this I created the ppm_login and permissions, as well as the DBLINK,

     

    Restarted all services.

     

    Now, the Load DWH and Access rights jobs are running.



  • 11.  Re: Load DWH job fails

    Posted 12-06-2016 10:06 AM

    Many thanks to Suman for his hel and advice with this one.