Optimizing Report Performance
By user558890 on Apr 07, 2011
When users run reports, they expect the data to pop up relatively quickly. There are many factors that can affect how quickly data is returned in reports. The most common factors that affect report performance are not related to the performance of the application itself, but with how reports are designed and built.
The Optimizing Analytics Performance document contains key information, tips, and design suggestions to help you create reports that display as quickly as possible. You can access this document anytime from the Training and Support Center. Just click the Training and Support link from CRM On Demand and search for 'analytics performance'. You can also find it in My Oracle Support (Doc ID: 804313.1).
Below are five tips that you should follow to avoid some of the most common issues that affect report performance. These and many other tips and techniques are included in the Optimizing Analytics Performance document.
1. Use Analytics Subject Areas Whenever Possible
Analytics subject areas pull data from the data warehouse, which is optimized for analytics purposes. Because these reports access data from a separate database, they do not compete with all of the other transactions (creating, updating, and deleting records) being performed in the transactional database.
So, whenever possible, use these subject areas, rather than Reporting subject areas, to build reports.
There are several reasons why an Analytics subject area may perform better than a Reporting subject area. The following table outlines some of the major differences that may affect performance.
2. Incrementally Build Your Reports
Start small, test the report as you build it, and incrementally progress to the finished report. That means that you should begin with a few columns and test them before adding more columns.
In addition, you must apply filters as a first step to building a report. Do not even run the first preview of a report until you apply filters.
The flow chart below outlines the ideal process for building reports incrementally. Start in the upper left and think through the decision points outlined in the gray squares before moving to the process of building the report.
3. Use Filters Liberally
Using filters restricts the amount of data that is returned when you access an analysis. This can make your report run faster. Follow these guidelines when adding filters to your report:
Test Your Filters
Test your filters to determine the best sequence in which to use them. Different sequences can increase or decrease performance. For the best performance, add the broadest filter first and then add the more specific filters.
For instance, if you want to compare expected and closed revenue for opportunities in a certain region, add the region filter first because that filter will filter out many records. That means that subsequent filters have fewer results to filter, which will increase the overall performance of the report. The screen shot below shows an example.
Filter on Your Driving Object
Filter on your driving object as a priority. For example, when using an Activities subject area, filter on Activities.
Use Indexed Fields for Filtering
Use indexed fields whenever you can for filtering and sorting data in reports. Indexed fields are optimized so your report results will appear more quickly.
You want to plan ahead when you set up your data to make sure the right data is stored in the indexed fields. Following that practice will allow you to build reports that leverage the indexed fields, incorporating them at the best possible places.
Oracle CRM On Demand also has a limited number of indexed custom fields available to you. (The number varies according to the field type and object.)
Indexed fields have the lowest degree of performance impact. However, if you do not have an available indexed field for your filter, use a field with the least possible performance impact.
The following list (also shown in section 4 of this document) shows the degree of performance impact (from lowest to highest performance impact):
- Indexed fields provided with the application by default (least impact)
- Indexed custom fields
- Other fields provided with the application by default
- Other custom fields
- Picklists provided with the application by default
- Indexed custom picklists
- Other custom picklists (most impact)
4. Minimize Cross-Object Use
When you select columns from more than one object for your analysis, you are adding complexity to your query, which can cause your report to display less quickly. Specifically, you should minimize cross-object use when using column selectors, prompts, and filters:
- Column Selectors: Whenever possible, keep all column selectors within the same object.
- Prompts: Try to make all prompts reference fields within the same object folder.
- Filters: Filter on the driving object for your report whenever possible. However, when cross-object filtering is necessary, consider using the JoinFieldValue option to improve report performance.
- More information about the JoinFieldValue option is available from the Training and Support Center.
5. Use Dashboards instead of complex reports
When you need to analyze different objects in the same location or when you have a complex report, consider creating a custom dashboard. Dashboards allow you to build several simple reports and display them on a single dashboard. Dashboards with simple reports will run more quickly and will be easier to setup and maintain than complex reports.
- Complex reports may contain:
- More than one primary purpose
- Multiple views
- A large number of columns
- Complicated filters
- More than one driving object
If your report contains any of the above, consider creating a custom dashboard. The "Creating Custom Dashboards" webinar is available on the Training and Support Center.
Again, these five tips are merely a sample of what you will find in the Optimizing Analytics Performance document. Following the suggestions included in that document will help you create reports that perform better using Oracle CRM On Demand Answers.
To get the Optimizing Analytics Performance document, click the Training and Support link from CRM On Demand and search for 'analytics performance', or search by the My Oracle Support Doc ID: 804313.1.