BMC Helix ITSM - Smart Reporting Troubleshooting Performance Issues - INCLUDES VIDEO

Knowledge Article

BMC Helix ITSM - Smart Reporting Troubleshooting Performance Issues - INCLUDES VIDEO
How To Troubleshoot Smart Reporting Performance Issues?
Remedy AR System Server
Smart Reporting Remedy
Smart Reporting - All versions
Symptom:
- Navigation is slow in Smart Reporting Console
- No data while fetching/refreshing the reports.

Reproducing the Issue:
- Cross Launch to Smart Reporting from MidTier/Smart IT takes longer time than expected.
- Smart Reporting navigation is slow. 
- No data while fetching/refreshing the reports.
Smart Reporting DB is full due to cache
This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

Verify which of the following behaviors apply to the issue:

1. Slowness with cross-launch

If Cross-launch from MidTier/Smart IT is taking longer than expected, then go through the following steps:
  • The default entry home page might be set to ‘Dashboards’. To fix this, change the default home page to either ‘Timeline’ or ‘Browse’.
  • For a user who has never accessed Smart Reporting, set the entry page as ‘Browse’.
  • For a user who has accessed Smart Reporting without making any changes, set the entry page as ‘Browse’.
  • For a user who has accessed Smart Reporting and changed the entry page, set the entry page as set in the display settings by user.
  • If the user changes the personal display preferences, then the global settings do not apply to the user. To update the default entry page for all the users, use the below query in the Smart Reporting database:
UPDATE Configuration SET ConfigData ='Browse'  WHERE ConfigCode = 'DEFAULTENTRYPAGE' AND IpOrg = 1

Note: IPOrg ='1' is for the default organization. Change the IpOrg value while updating this for client organizations. Each client organization will have unique IpOrg value.
  • ​​Restart the Smart Reporting service and Default entry page = ‘Browse’ will be applied globally to all the users. Refer to next video:

2. Slowness with navigation

If navigation is slow in the Smart Reporting console, then check the steps below:

(I) Tomcat configuration
Apply the Tomcat tuning following knowledge article 000125200: https://selfservice.bmc.com/casemgmt/sc_KnowledgeArticle?sfdcid=kA214000000PIQrCAO&type=Solution 
(II) Smart Reporting tables growing in size
  • Check the size of "Event", "EventArchive" and "DocumentData" tables in the Smart Reporting repository database.
Event: This table stores all Smart Reporting usage data, such as User logins, Running reports, Imports/exports. This data is all used for auditing only.

EventArchive: This table stores all of the archived event data, so the data here is simply data that was moved from the 'Event' table after a specified period. There is a possibility that "Event" and "EventArchive" tables count might go to some millions of rows. In this case, truncate both tables:
Truncate table Event;
Truncate table EventArchive;

Apply the below tuning to keep the number of records in the "Event" and "EventArchive" tables under control. This is good practice because these tables can get quite big as they record many different types of Smart Reporting events. The way to create these 2 jobs and configure the number of days is by running the following 2 queries, keeping in mind that the last value of each INSERT query represents the number of days.
 
Archive tasks from the "Event" table, the job runs every day, searches for events older than xnumber of days ( eg. 30 days):
INSERT INTO Configuration VALUES (1, ‘SYSTEM’, ‘EVENTMAXDAYS’, 30)
Delete records from the EVENTARCHIVE table older than x number of days ( eg. 60 days):
INSERT INTO Configuration VALUES (1, ‘SYSTEM’, ‘EVENTARCHIVEMAXDAYS’, 60)
  • DocumentData: This table stores a lot of report related data. Not meta-data, actual data such as: cached report result sets. There are a set of delete queries to shrink this table, do not truncate this table directly, refer to attached document "Data Hygine Smart Reporting Stale Data Identification SQL Scripts.SQL"
Once the "DocumentData" is cleared, set the report folders option “Keep the latest result set”
User-added image

This has to be done for all the folders manually or with the below SQL it can applied for all folders:
UPDATE ContentManagement SET VersionHistoryRequiredFlag=false;

This setting will make sure the report cache does not grow again.

Refer to next video:

(III) Adjust Event Cache.
 
If performance is not observed after applying above steps, it will be necessary to adjust Event Cache settings.
Refer to attached document "Adjusting Event Cache.docx"

Note: There is no need to apply this step if performance is observed after applying all previous recommendations.

3) Slowness when fetching/refreshing the reports.

If the slowness is observed while refreshing the reports, disable the RLS implementation and increase the API timeout following the attached documents and the below video:

(I) Disable-New-RLS-Implementation.pdf  (attached).
(II) Increase AR API Timeout if timeout errors are observed.


For Windows follow attached document "Steps to increase AR API timeout.docx".
  • For non-Windows refer to knowledge article 000171034.
 


 
4) Slowness in AR Server and SmartIT due to Smart Reporting
If you want to keep the AR production database unaffected and yet allow BMC Remedy Smart Reporting users to continue with the reporting operations, you must connect your AR System data source with a read-only replicated AR database.

With the above configuration, Smart Reporting performance should definitely improve.

See also:  BEST FAQ on Smart Reporting with BMC Helix Business Workflows
Remedy AR System Server
Smart Reporting Remedy
Smart Reporting - All versions
Remedy AR System Server
Smart Reporting Remedy
Smart Reporting - All versions
Attachment(s):
KA000151842.zip
Data Hygine Smart Reporting Stale Data Identification SQL Scripts