This documentation supports the 20.02 version of Remedy Smart Reporting.

To view an earlier version, select the version from the Product version menu.

Troubleshooting performance issues

This topic describes the issues related to performance in Remedy Smart Reporting(Smart Reporting).

Issue symptomIssue scopeResolution

Accessing the Smart Reporting Console from the Mid Tier takes a long time.

The default entry page in Smart Reporting is set to Dashboards. Because loading the Dashboards takes time, launching Smart Reporting becomes slow.

  1. Set the Entry Page of Smart Reporting to Timeline or Browse depending on the following scenarios:

    • For a user who has never accessed Smart Reporting, set the Entry Page to Browse.
    • For a user who has accessed Smart Reporting without making any changes in Profile Settings > Display Preferences, set the Entry Page to Browse.
    • For a user who accessed Smart Reporting and changed the Entry Page in Profile Settings > Display Preferences, set the Entry Page based on the display preference of that user.

      Because the global settings do not apply to these users, run the following query in the Smart Reporting database to update the default entry page:

      UPDATE Configuration SET ConfigData ='Browse' WHERE ConfigCode = 'DEFAULTENTRYPAGE' AND IpOrg = 1

      Since the value of the IpOrg field for a default org is 1, manually change the value of this field while updating the settings for client orgs. Each client org has a unique IpOrg value. To find the IpOrg of an active client org, run the following query in the Smart Reporting database:

      select org.OrgName,org.IpOrg,rvc.DatabaseURL,rvc.UserName from Organisation org inner join IpRltshp rlt on (rlt.IpChild = org.IpOrg and rlt.EndReasonCode is null and rlt.RltshpTypeCode = 'CUSTOMER') inner join ReportViewSource rvc on (rvc.IpOrg = org.IpOrg and DatabaseTypeCode = 'BMCACTIONREQUEST')
  2. Restart the Smart Reporting service.
    The default entry page will be set to Browse globally for all users.

When IT Service Management users who are not administrators run reports in Smart Reporting, the report takes a long time to fetch the data.

A row-level security clause is added to the SQL query of the report, which causes the report to take a long time to fetch the data.

Disable the row-level security implementation as described in the following steps.

  1. Log in to the Remedy AR System server as an administrator, and select AR System Administration > AR System Administration Console.

    (Click the image to expand).
     
  2. On the Remedy AR System Administration Console, go to System > General > Centralized Configuration.

    (Click the image to expand).
     
  3. On the Centralized Configuration form, from the Component Name drop-down list, select com.bmc.arsys.server.shared > shared Component Name > Setting Name.

    (Click the image to expand).
     
  4. Click Add, and change the value of the Disable-New-RLS-Implementation setting to T.

  5. Click Apply.

    (Click the image to expand). 
     

  6. Restart the Remedy AR System server.

Smart Reporting is running very slow. The navigation from one screen to another takes a long time. The CPU and RAM usage of Smart Reporting Tomcat is very high on the server.

This behavior is observed due to the size of Smart Reporting database. For long running sessions of Smart Reporting, the size of the configuration database can increase. 

The table below explains the workarounds for database tables.

Workarounds for database tables that cause Smart Reporting to run slow

Issue symptomIssue causeIssue workaround
Event and Event Archive

The Event table stores all Yellowfin usage data, such as user login, running reports, and import and export information. This data is used for auditing purposes only.

The Event Archive table stores the archived event data. The data from the Event table is moved in this table after a specific time period.

  • If the number of records in the Event and Event Archive table exceeds to more than a million rows, truncate both the tables by using the following commands:

    Truncate table Event;
    Truncate table Eventarchive;
  • Perform the following tuning-related steps to keep the number of records in the Event and Event Archive tables under control.
    1. Create the job and configure the number of days by running the following queries. Ensure that the last value of each INSERT query represents the number of days.
      • EVENTMAXDAYS—This job runs every day and searches for events older than X number of days (for example, 30 days).

        INSERT INTO Configuration VALUES (1, ‘SYSTEM’, ‘EVENTMAXDAYS’, 30)
      • EVENTARCHIVEMAXDAYS—This job deletes records that are older than X number of days from the Event Archive table (for example, 60 days). Run the following query for creating this job:

        INSERT INTO Configuration VALUES (1, ‘SYSTEM’, ‘EVENTARCHIVEMAXDAYS’, 60)
    2. Run the following query to get the IpOrg value of a tenant:

      select
      org.OrgName,org.IpOrg,rvc.DatabaseURL,rvc.UserName from Organization org 
      inner join IpRltshp rlt
      on (rlt.IpChild = org.IpOrg and rlt.EndReasonCode is null and
      rlt.RltshpTypeCode = 'CUSTOMER') 
      inner join ReportViewSource rvc on (rvc.IpOrg = org.IpOrg and 
      DatabaseTypeCode = 'BMCACTIONREQUEST')
    3. Change the default value of IpOrg from 1 to the IpOrg of the tenant by running the following query:

      INSERT INTO Configuration VALUES (<IpOrgOfTenant>, ‘SYSTEM’, ‘EVENTARCHIVEMAXDAYS’, 60)

      The IpOrg value reduces the data of the Event table to 30 days and that of EventArchive table to 60 days.

Now, the Smart Reporting administrator can see the user audit trail information for the last 60 days only. The rest of the audit trail history is deleted.

DocumentData

Stores report-related data. The table does not store the metadata, but stores the actual data such as cached report results sets.

Note: Do not truncate the DocumentData table directly.

Shrink the table through the user interface by disabling report caching for each relevant report category.

Alternatively, you can modify the record in the database directly by running the following query:

UPDATE ContentManagement SET VersionHistoryRequiredFlag=false;

This setting ensures that the report cache does not increase again.

You can also run the following query to identify what is stored in the table including the data count:

select di.DocumentTypeCode, di.DocumentCode, di.StatusCode, di.MediaTypeCode, count(di.DocumentId) as DocumentItemCount, dr.DocumentRevisionCount, dd.DocumentDataCount 
from DocumentItem di 
left outer join ( 
select i.DocumentTypeCode, i.DocumentCode, i.StatusCode, i.MediaTypeCode, count(r.RevisionId) as DocumentRevisionCount 
from DocumentItem i, DocumentRevision r 
where i.DocumentId = r.DocumentId 
group by i.DocumentTypeCode, i.DocumentCode, i.StatusCode, i.MediaTypeCode 
) dr 
on di.DocumentTypeCode = dr.DocumentTypeCode 
and di.DocumentCode = dr.DocumentCode 
and di.StatusCode = dr.StatusCode 
and di.MediaTypeCode = dr.MediaTypeCode 
left outer join ( 
select i.DocumentTypeCode, i.DocumentCode, i.StatusCode, i.MediaTypeCode, count(d.DataSeqNbr) as DocumentDataCount 
from DocumentItem i, DocumentRevision r, DocumentData d 
where i.DocumentId = r.DocumentId 
and r.RevisionId = d.RevisionId 
group by i.DocumentTypeCode, i.DocumentCode, i.StatusCode, i.MediaTypeCode 
) dd 
on di.DocumentTypeCode = dd.DocumentTypeCode 
and di.DocumentCode = dd.DocumentCode 
and di.StatusCode = dd.StatusCode 
and di.MediaTypeCode = dd.MediaTypeCode 
group by di.DocumentTypeCode, di.DocumentCode, di.StatusCode, di.MediaTypeCode, dr.DocumentRevisionCount, dd.DocumentDataCount
ReportInstanceStores all the historical versions of the report.

Run the following queries to clear the table and keep all the KPI reports and cached reports intact:

DELETE FROM ReportInstance WHERE DocumentId = 0 OR DocumentId IS NULL OR ReportId not in (SELECT ReportId FROM ReportHeader WHERE RoleCode = 'KPI')
INSERT INTO Configuration (IpOrg, ConfigTypeCode, ConfigCode, ConfigData) VALUES (1, 'SYSTEM', 'CREATEREPORTINSTANCES', 'false');

These commands ensure that the records are not created in the ReportInstance and the ReportInstanceFilter table each time a report is run.

Was this page helpful? Yes No Submitting... Thank you

Comments