Diagnose Performance Using Subject Areas

This example illustrates how you could use the OTBI Usage Real Time and Performance Real Time subject areas to understand usage and performance, so that you can diagnose performance bottlenecks and understand whether analyses are running slowly or could be optimized.

The OTBI Usage Real Time subject area monitors usage trends for OTBI by user, analysis and dashboard, and subject area. The OTBI Performance Real Time subject area monitors usage as well as analysis execution time, execution errors, and database physical SQL execution statistics.

In this example, you create an analysis to determine the current number of users accessing OTBI so that you can determine system load, and a histogram analysis that identifies trends in long-running queries.

Analyze the Number of Users

1. In the Reports and Analytics work area, click Create and select Analysis. Select the OTBI Usage Real Time subject area and click Continue.

2. In the Select Columns page, expand your subject area and folders. Select User Count from the Facts - Usage Metrics folder and click Next. Because you aren't including a dimension in the analysis this provides a total of all users.

3. In the Select Views page, select a Table view and click Next.

4. In the remaining pages, click Next to accept the defaults.

5. In the Save page, enter a name for the analysis, select a catalog folder to save it in, and click Submit. The result is a single-column table with the number of users on the system. To refine this analysis, you could add the Report Name and Report Path columns from the OTBI Report folder to the analysis to determine which reports are in use.

Analyze Query Performance

1. In the Reports and Analytics work area, click Create and select Analysis. Select the OTBI Performance Real Time subject area and click Continue.

2. In the Select Columns page, expand your subject area and folders. Select Report Name from the OTBI Report folder and click Add.

3. Add Total Execution Time from the Derived Metrics folder in the Facts - Performance Tracking folder two times.

4. Add Report Row Count from the Query Execution Metrics folder. Click Next

5. Add Report Count from the Usage Metrics folder. Click Next

6. In the Select Views page, select a Pivot view and click Next

7. In the remaining pages, click Next to accept the defaults.

8. In the Save page, name the analysis Performance Histogram, select a My Folders, and click Submit.

9. Click Browse Catalog and, in the catalog, navigate to My Folders and click Edit for your analysis.

10. Select the Criteria tab. Click the Options button and select Sort Ascending for the first Total Execution Time column.

11. Click the Options button for the second Execution Time and select Edit Formula. In the Edit Formula dialog box, select Custom Headings enter Total Execution Time Bin, and enter the following statement in the Column Formula to bin by ranges of execution time, ranging from less than five seconds to greater than five minutes.:

    CASE  WHEN "Derived metrics"."Total Execution Time" <= 5 THEN 'Less than 5
    Seconds' WHEN "Derived metrics"."Total Execution Time" BETWEEN 5 AND 30 THEN
    'Between 5 and 30 Seconds' WHEN "Derived metrics"."Total Execution Time"
    BETWEEN 30 AND 60 THEN 'Between 30 and 60 seconds' WHEN "Derived metrics".
    "Total Execution Time" BETWEEN 60 AND 120 THEN 'Between 60 and 120 seconds' 
    WHEN "Derived metrics"."Total Execution Time" BETWEEN 120 AND 300 THEN 
    'Between 120 and 300 seconds' ELSE 'Greater than 300 seconds' END

12. In the Results tab, click Edit View for the pivot table.

13. In the Layout pane of the Pivot Table editor:

    • Move the Total Execution Time Bin column to the Rows section.

    • Move Report Count to the Measures section.

    • Move the rest of the columns to the Excluded section so they aren't shown in the pivot table.

14. Click Done.

    You can present this information as a graph or table to view how many reports are running too long. Those with the highest usage and the longest execution times can then be prioritized.