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:
Any suggestions on how to resolve this?
Thanks in advanced!
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.
Nika HadzhikidiCA TechnologiesPrincipal Support Engineer
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.
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-warehouseLook 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 open3. 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 Datasources4. 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.
Good morning Nika,
We still get the unable to login into ppm_dwh error.
I attached the full error we are receiving in Jaspersoft. It occurs when selecting a report.
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?
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
I spoke our DBA and he mentioned that the parentheses is how MS SQL displays the error.
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.
I ran the command successfully, but the same error is still occurring.
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.
Kind RegardsNika Hadzhikidi
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'.
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Please note: Create and Update Jaspersoft Users & Load Data Warehouse Access Rights complete successfully.
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.
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!
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.
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.
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.