Print      Open PDF Version of Online Help

Analytics > Designing an Analysis > Optimizing Performance

Optimizing Performance

Every time you access a report or analysis, your data is retrieved from the database, processed by the reporting server and displayed on the page. The more complex the analysis, the more time it can take to query the database to retrieve and present the data. This topic contains guidelines to help you create reports that display as quickly as possible.

Before you begin. Do the following:

• Review the prebuilt reports available in Oracle CRM On Demand. These reports might address most of your reporting needs.
• Take training courses.
• Gather your reporting requirements and have them approved by the stakeholders.
• Check your browser settings to make sure the browser client is properly configured for performance.
• Understand report performance. For more information, see About Report Performance.

About Subject Areas

You can use the two subject area types depending on your reporting and business needs.

The Analytics subject areas should be used whenever possible to build reports. These subject areas are built using a specially constructed data warehouse that is tuned for better query performance queries. The data warehouse used for Analytics subject areas is refreshed daily and reports built using these subject areas display results faster than Reporting subject areas even for large and complex queries.

The Reporting subject areas are built using the same database in which all other user transactions in the application occur, and hence, compete for same resources that your application depends on when data is retrieved for reports.

Guidelines for Using a Reporting Subject Area

If you are using a Reporting subject area, follow these guidelines:

• When possible, use fields from the Address (Extended) folders.
• When possible, minimize the use of the following:
  • Custom picklist fields.
  • Fields from any Owned By User folder. Instead, use the Owner field.
  • Fields from the Primary Contact folder within the Account folder.
  • CAST functions for Date or Date/Time fields.
  • Parent Account, Last Modified By, Created By, and Manager fields.
  • Fields from Date-type folders, such as Date Created and Date Closed folders.

    Although these folders provide a convenient way to group the date attribute to week, month, quarter, or year, using them also makes the query more complex and may affect the report performance. If experiencing performance problems with fields from the date folders, try using the Date or Date/Time fields from the primary record type folder. For example, open the Opportunity subject area, and select the Close Date field in the Opportunities folder instead of a field from the Close Date folder.

Guidelines for Improving Performance

Follow these guidelines to improve performance:

Build Reports Incrementally

Do the following:

• Start small, and test the report as you build it. Begin with a few fields, and test them before adding more columns.
• Test your filters to determine the best sequence to use.

  Different sequences can increase or decrease performance, but only trial-and-error helps you determine this.

Minimize the Use of Record Types That Cross Fields or Columns

Follow these guidelines:

• Columns. When you select columns from more than one record type for your analysis, you are adding complexity to the data query. This complexity can affect the speed of displaying results. So, consider this when including cross-record type fields in a report.
• Column Selectors. Keep all column selectors within the same record type. Whenever possible, do not use a column selector that crosses to another record type.
• Prompts. Try to make all prompts reference fields within the same record type folder.

Use Indexed Fields

Do the following:

• Use indexed field to filter data in real-time reports.

  Indexed fields are optimized so your report results appear more quickly.

• Plan ahead when you set up your data to make sure that the correct data is stored in the indexed fields.

  Following this practice allows you to build reports that use the indexed fields, incorporating them at the best possible places. Oracle CRM On Demand also has a limited number of indexed custom fields. The number varies according to the field type and record type.

Limit the Organizational Hierarchy to Five Levels

The higher a user is in the organizational hierarchy, the more data is returned and the slower the report runs. Therefore, limit the hierarchy to a maximum of five levels.

Use Filters

Using filters restricts the amount of data that is returned when you access an analysis. Filtering can increase the speed of running your report.

• Apply filters as a first step to building a report. If your company has a lot of data do not run the first preview of a report until you first apply filters.
• Filter your driving record type as a priority. For example, when using an Activities subject area, filter Activities.
• Choose the most restrictive filters first, and then, after experimenting, ease restrictions as needed.

However, consider these guidelines when adding filters:

• If a filter references a column with an underlying CASE formula, this filter might adversely affect reporting performance, depending on the formula and the related processing required. When filtering a field with a CASE statement or other formula, experiment to determine how this filtering affects reporting performance.
• Before using a CASE formula, ask yourself these questions:
  • Can this classification be performed in Oracle CRM On Demand using the Workflow feature?
  • Does the classification code appear in other reports? If so, then consider creating a new column and building a workflow to populate it.
• A report that filters a custom Date field can run more quickly than a similar report that filters a custom Date and Time field, because all Date and Time fields involve converting time zones, which affects reporting performance. Therefore, when possible, filter custom Date fields rather than custom Date and Time fields.
• Avoid using an OR clause on different record types or columns (for example, where SR.Type = 'A' OR SR.Priority = 'High' OR Account.Industry = 'Medical'). Instead, try the following:
  • Remove the OR clause, and perform further classification in the pivot table.
  • Break the report in to multiple, smaller reports, and merge the result using the combined analytics.

Follow the Guidelines for Defining Prompts

You can define prompts for your report in Step 3 of the Build and View Analysis page in Oracle CRM On Demand. These prompts allow those users who access the finished report to make selections to limit the data in the report. When running a report that uses prompts, a user’s experience is affected by the prompt-processing time and by the report-processing time.

If you add prompts to your report, do the following:

• Minimize the number of prompts used for a report.
• Avoid using high-resolution images for Image prompts. It is recommended that you do not use an image on a report that exceeds 100 dots per inch (dpi). For best performance, use images of 50 dpi or lower.
• When defining prompts, under the What Values Should Be Shown to the User menu, choose either None or All Values for the fastest performance. Avoid using the Filter Limited Values option. This option restricts the prompt selections that the report displays. The restriction is based on other filters and prompts that are applied to the report. If a report contains large volumes of data, this option can slow reporting performance.

  When deciding whether to select the None, All Values, or Filter Limited Values option, it is recommended that you strike a balance between the level of reporting performance that you want and the optimal presentation of selectable values to users.

Provide Drilldown Links to Detail Reports

Instead of creating a report that presents a long list of data tables and graphs, do the following:

• Design an initial report to show the summary information or trends, and let the user drill down on the details.
• Build reports for more detailed information by targeting data from the first report. Then, provide drilldown links to the summary report.

The following procedure describes how to link reports to tables.

To link reports to tables

1. Click the Format Column icon to open the Column Properties dialog box.
2. Click the Column Format tab.
3. In the Value Interaction Type drop-down list, choose Navigate.
4. In the Navigate to Another Analysis field, browse for a detailed report that you want to drill down on, and then click OK.

Limit the Number of Set Operations

Advanced features allow you to combine queries. You can then perform set operations, such as unions, intersections, and other joining actions on those queries to build a final report. The greater the number of query results combined, the more processing time is required to run the final report. For fastest processing, limit the number of set operations to no more than three.

Clarify HTML Code and Narratives

When using HTML in connection with SQL, do the following:

• Make sure that the HTML is clear and precise and does not contain any unnecessary tags or superfluous information.
• Make sure the narratives are clear and precise and do not contain unnecessary instructions.

  Precise narratives make the specified formatting more efficient and reduce performance issues.

Remove Columns in Pivot Tables

Pivot tables allow you to show the report in multiple views without writing multiple reports, but they also might affect performance. Whenever possible, remove the columns from the report criteria (in Step 1 – Define Criteria) that are not used in the pivot table itself.

Use Graph Pivoted Results

Whenever possible, for each pivot table that requires a graph, use the Graph Pivoted Results option instead of creating a separate graph view. Multiple graphs in an analysis can require more processing, because Oracle CRM On Demand must create the graphs individually, rather than simultaneously with the pivot table.

Ensure that Reports Are Scalable

Reports might run well in a test before all of your production data has been imported. After all the production data has been imported, the increased data volume adversely affects reporting performance. If you are in the process of implementing Oracle CRM On Demand, allow time to retest and tune reports after all the production data has been imported.

Use Optimized Code and UTC Fields in Filters

Many reporting subject areas include special fields in their dimensions that are optimized to reduce the query time when they are used in filters. Optimized fields end with the words Code or UTC. For example, the Account dimension has an Account Type field. There is also an Account Type Code field, which is the optimized version of the Account Type field. Both optimized and nonoptimized fields produce the same results in reports, but using the optimized field in the filter generates faster queries. This method is faster than using the nonoptimized field. Using these fields in filter conditions reduces additional table joins and avoids timestamp conversions that are based on your company’s time zone.

Note: These optimized fields support language translations for all supported languages, but they do not support record type renaming.

To determine if optimized filtering fields exist for a specific reporting subject area, see the online help for that subject area, and look for the heading Optimized Filtering Fields.