Clarity PPM

Expand all | Collapse all

Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

Jump to Best Answer
  • 1.  Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-04-2016 03:05 PM

    We recently upgrade our environment from 13.3 to 14.3 with Jaspersoft Server 6.1. In our Test environment, Load Data Warehouse hangs then fails. The ETL Job finishes successfully, but the next portion, the Jaspersoft domain updates, get hung and eventually fail.

     

    Things to know:

    • Load DWH works in DEV which is a standalone server. All DB schemas are housed on the same DB server.
    • Time Slicing, Create and Update Jaspersoft Users, Load Data Warehouse Access Rights, Datamart Settings jobs all run successfully
    • The DB setup in DEV and Test are the same.
    • All DBs are available

     

    Any suggestions on how to resolve this?

     

    Thanks in advanced!



  • 2.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-04-2016 04:05 PM

    Hi Nemish,

     

    If your Load Data Warehouse job is failing, this is because of inability to connect to Jaspersoft. The job itself is completing properly, so the issue is really with the connection to Reporting, your DWH data is now uploaded and updated fine.

     

    Are you able to access the reports in Advanced Reporting? If not, can you check if Jasperserver is up and running? If yes, please could you run the Create and Update Jaspersoft user jobs and confirm if it runs successfully.

     

    If it fails, you should have a more definite error message in your app-ca.log and bg-ca.log. Please post it here and we will progress from there.

     

    Kind Regards

     

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 3.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-04-2016 04:52 PM

    Hey Nika,

     

    That's for reaching out to me so quickly.

     

    I'm able to access the reports list, but when I click on a report. I get the below error. The login information for ppm_dwh is correct. Also, the DWH and Jaspersoft DB are available.

    The Create and Update Jaspersoft User job completes successfully.

     

    Thanks,

    Nemish



  • 4.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-04-2016 05:59 PM

    Hi Nemish,

     

     

    Looks like the Jaspersoft server cannot connect to the DWH database. We don't see the full error to check if there is more information in the stack trace though.

     

    Please do the following:

    1. Ensure all ppm_dwh database requirements as per the Installation Guide are met. Here is the full list:
    https://docops.ca.com/ca-ppm/14-3/installing-and-upgrading/set-up-the-data-warehouse
    Look for section: Configure the Data Warehouse Database (Microsoft SQL Server)
    2. Ensure you are able to connect from your Jaspersoft server to your database server and required ports are open
    3. Go to the Jaspersoft server UI and Test the CA PPM DWH BEAN connection. See if it tests successfully. For more information on how to do this, check https://communities.ca.com/thread/241737361 section Datasources
    4. Try to create a new Datasource and point to your DWH database, and see if it works when you click on Test Connection

     

    Hope this helps.

     

    Kind Regards

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 5.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 11:03 AM

    Good morning Nika,

     

    1. When setting up the ppm_dwh db, we followed all guidelines in the installation guide. The same steps we followed for DEV.
    2. We are able to telnet from the Jaspersoft server to the port used on the database server.
    3. The CA PPM BEAN and CA PPM DWH BEAN connections passed.
    4. The test connection to the ppm_dwh database completed successfully as well.

     

    We still get the unable to login into ppm_dwh error.

     

    Thanks,

    Nemish



  • 6.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 12:51 PM
      |   view attached

    Hey Nika,

     

    I attached the full error we are receiving in Jaspersoft. It occurs when selecting a report.

     

    Thanks,

    Nemish

    Attachment(s)



  • 7.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 03:39 PM

    Hi Nemish,

     

    Please can you try running the following commands one by one on your Clarity PPM server:

     

    admin update jasperParameters

    admin jaspersoft syncPPMContext -userName superuser -password superuser

    Then retry and see if you still have the issue?

     

    Many thanks

     

    Nika Hadzhikidi



  • 8.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 03:49 PM

    We're still having the same issue. The first line of the error references a database "ppm_dwh)" but all of our settings for the dwh points to ppm_dwh as the database. I check the properties.xml files and they read the same thing.

     

    Cannot open database "ppm_dwh)" requested by the login. The login failed. ClientConnectionId:23c6305b-f7de-49f7-8290-1a362d1ff2cd



  • 9.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 04:37 PM

    I spoke our DBA and he mentioned that the parentheses is how MS SQL displays the error.



  • 10.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 04:39 PM

    Hi Nemish,

     

    Thanks for providing all the details. Looks like something might be wrong with the reports' data connection/Domains , since the rest of the data is working fine or we already reset with commands.

    Please try this command to reset the Report's Domains:

     

    admin content-jaspersoft csk restoreDomains -userName superuser -password superuser

    See if this resets the reports.

     

    Looking forward to hearing from you. 

     

    Kind Regards

    Nika Hadzhikidi



  • 11.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 04:50 PM

    Hey Nika,

     

    I ran the command successfully, but the same error is still occurring.

     

    Thanks,

    Nemish



  • 12.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 05:04 PM

    Hi Nemish,

     

    If you have no custom reports on this server, please proceed with the following workaround. If you do, you will have to backup your custom content first.



    Reimport the reports on this server by doing the following:

     

    1. Connect to Jaspersoft Console UI - Organizations and delete your organization

    2. Run the following query on your Clarity PPM database:
    delete from cmn_install_history where install_id='contentPack::csk::jaspersoft';
    commit

    3. Once done, run the import content command:
    admin content-jaspersoft csk -userName superuser -password superuser -retryCount 50

    4. Connect to Clarity PPM and run Create and Update Jaspersoft Users job

     

    See if that helps.

     

    Looking forward to hearing from you. 

     

    Kind Regards
    Nika Hadzhikidi



  • 13.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates
    Best Answer

    Posted 02-05-2016 05:32 PM

    Hey Nika,

     

    That definitely fixed the issue with the reports, but now I'm getting the following error when running Load Data Warehouse:

     

    dwh_etl_master - org.pentaho.di.core.exception.KettleJobException:

    Unable to begin processing by logging start in logtable DWH_CFG_LOG

     

    Couldn't execute SQL: DELETE FROM DWH_CFG_LOG WHERE ID_JOB= -1

     

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_CFG_LOG'.

     

     

    at org.pentaho.di.job.Job.beginProcessing(Job.java:1044)

    at org.pentaho.di.job.Job.execute(Job.java:474)

    at org.pentaho.di.job.Job.run(Job.java:407)

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

    Couldn't execute SQL: DELETE FROM DWH_CFG_LOG WHERE ID_JOB= -1

     

    [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'DWH_CFG_LOG'.

     

    at org.pentaho.di.core.database.Database.execStatement(Database.java:1432)

    at org.pentaho.di.core.database.Database.execStatement(Database.java:1380)

    at org.pentaho.di.core.database.BaseDatabaseMeta.getNextBatchIdUsingLockTables(BaseDatabaseMeta.java:1980)

    at org.pentaho.di.core.database.BaseDatabaseMeta.getNextBatchId(BaseDatabaseMeta.java:2006)

     

    Please note: Create and Update Jaspersoft Users & Load Data Warehouse Access Rights complete successfully.

     

    Thanks,

    Nemish



  • 14.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 05:44 PM

    Hi Nemish,

     

    Glad to hear your reports are now fixed. Now let's see your Load Data Warehouse job.

     

    From the error message it seems like table DWH_CFG_LOG does not exist on your ppm_dwh database. Could you please check with your DBA that the table is there and that ppm_dwh is the owner of the database?

     

    If the table is missing, you will have to reimport the Data Warehouse database backup, and then run Load Data Warehouse job to fix the issue.

     

    Kind Regards
    Nika Hadzhikidi

     



  • 15.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 05:48 PM

    Hey Nika,

     

    I ran the following command to recompile invalid objects and now it is running without issue.

     

    admin db compile -Ddb.id=Datawarehouse

     

    You know what could've caused all of this? You think the data may have gotten corrupted?

     

    Thanks for all your help!

    Nemish



  • 16.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 06:09 PM

    Hi Nemish,

     

    No worries, it seems like your objects were invalid for some reason and compile fixed it. Just check and ensure there is no more invalid objects on the database. That would not have caused a corruption of the data.

     

    Have a good day.  

     

    Kind Regards
    Nika Hadzhikidi



  • 17.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-05-2016 07:15 PM

    Hey Nika,

     

    I mean in reference to the issue I initially had with the Load DWH job getting hung at Updating Jaspersoft Domains. I want to make sure to avoid it when we go live. We followed all steps in the guideline yet it occurred in Test, but not DEV.

     

    Thanks,

    Nemish



  • 18.  Re: Load Data Warehouse Fails/Hangs - Jaspersoft Domain Updates

    Posted 02-08-2016 10:25 AM

    Hi Nemish,

     

    I see, you want to make sure this kind of issue does not reoccur on your other environments.

    In this case just ensure that after restoring the PPM_DWH database backup, there is no error with the database restore, and all the objects are compiled successfully. Once done, you may go and run the Load Data Warehouse job, and this will avoid you from hitting the issue.

     

    Hope this helps.

     

    Kind Regards
    Nika Hadzhikidi