![]() |
|
Optimizing PerformanceEvery 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:
About Subject AreasYou 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 AreaIf you are using a Reporting subject area, follow these guidelines:
Guidelines for Improving PerformanceFollow these guidelines to improve performance: Build Reports IncrementallyDo the following:
Minimize the Use of Record Types That Cross Fields or ColumnsFollow these guidelines:
Use Indexed FieldsDo the following:
Limit the Organizational Hierarchy to Five LevelsThe 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 FiltersUsing filters restricts the amount of data that is returned when you access an analysis. Filtering can increase the speed of running your report.
However, consider these guidelines when adding filters:
Follow the Guidelines for Defining PromptsYou can define prompts for your report in the analysis editor. 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:
Provide Drilldown Links to Detail ReportsInstead of creating a report that presents a long list of data tables and graphs, do the following:
The following procedure describes how to link reports to tables. To link reports to tables
Limit the Number of Set OperationsAdvanced 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 NarrativesWhen using HTML in connection with SQL, do the following:
Remove Columns in Pivot TablesPivot 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 ResultsWhenever 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 ScalableReports 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 FiltersMany 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 improve the performance of analyses that use filters on optimized picklist fields, use the Optimized Picklist Fields (Code) values in the filter instead of the values from the Optimized Custom Fields folders, which are dependent on the company language. |
Published 6/21/2021 | Copyright © 2005, 2021, Oracle and/or its affiliates. Legal Notices. |