Reports Integration Guide › Managing CA DLP Reports in InfoView › Report Diagnostics

Report Diagnostics

View the report SQL

If you have the required permissions, you view the underlying SQL for BusinessObjects reports for CA DLP. In InfoView, run the report. Then click Edit Report, Edit Query, View SQL.

If you cannot edit these reports

If your BusinessObjects user account belongs to any of CA DLP user group, you cannot edit a report in the CA DLP Standard Reports folder (because the Edit button is not displayed). The workaround is to save the report to a different folder:

1. In InfoView, run a CA DLP standard report.
2. Save the report to a different folder, such as the Favorites folder.

   Note: Members of the CA DLP Reports Viewer group do not have permission to save reports to different folders.

3. Close the original report.
4. Open the report that you saved in the Favorites folder.

   The Edit Report button is now displayed.

5. Click the Edit Report button.

Note: The CA DLP user groups are CA DLP Reports Administrator, CA DLP Reports Author, and CA DLP Reports Viewer.

SQL Server CA DLP CMS: View Performance Metrics

Database administrators can monitor and analyze the data warehouse query performance by using a SQL Server Profiler trace, available from the Tools menu in SQL Management Studio.

When prompted, enter the server name of the CA DLP CMS database and the credentials for a user with ‘ALTER TRACE’ system privileges (typically the ‘sa’ user).

To prevent the trace capturing database activity that is unrelated to data warehouse queries, add a session login filter based on the Data Warehouse User. In the Trace Properties dialog, go to the Events Selection tab and add a SessionLoginName filter. For example, if the database account name for your Data Warehouse User is WgnReporting, edit the filter as follows:

SessionLoginName Like WGNREPORTING

When you add or remove events and data columns from the trace file, the following events are of particular interest:

• Errors and Warnings: Attention, Exception, and (possibly) Performance Warnings.
• Stored Procedure: RPC Start, RPC End, SP End
• TSQL: SQLBatchCompleted
• Performance: Showplan Statistics Profile or Showplan Statistics XML Profile

The following data columns are of particular interest:

• SPID
• EventSequence
• TextData
• CPU
• Duration
• Writes
• Reads
• RowCounts
• BinaryData (if performance plans are being collected)

Oracle CMS: View Performance Metrics

Oracle installations include trace functionality for BusinessObjects-data warehouse query performance problems. A sys user can use the following procedures to control tracing of these queries.

WGN_TRC.ENABLE_TRACE;

Enables a trace for all BusinessObjects users.

WGN_TRC.SET_USER(<CA DLP_User>>);

Enables a trace only for the specified CA DLP user name.

WGN_TRC.CLEAR_USER;

Trace all users.

WGN_TRC.DISABLE_TRACE;

Disable traces for all BusinessObjects users.

A single report run may create several traces, one for each query contributing to a BusinessObjects report for CA DLP. The trace file name contains the user name.

A sys user can query the folder path to the trace files by running this command in SQL*Plus:

SELECT * from V$PARAMETER WHERE name = 'user_dump_dest';