(703) 860-6945

Optimize Performance for CRM Dashboards

Optimize Performance for CRM Dashboards

Dashboards in Microsoft CRM are very popular with end users, but if improperly created, they can suffer from poor performance.

 

Below are some best practices that will optimize the performance of your dashboards, and minimize the impact on the system.

 

Optimization Guidelines

Users want reports to come up quickly, with as little impact as possible on other users of the Microsoft Dynamics CRM database. As a result, the design and deployment of reporting functionality can be a major factor in overall report performance.

 

To help ensure optimal report performance, keep in mind the following guidelines:

 

  • Configure reports to display data from a specified time frame, for example the previous 30 days, rather than to display all records in the Microsoft Dynamics CRM database.
  • Reports with a large dataset or a complex SQL query should not be available on-demand to all users. Instead, schedule a snapshot in Report Manager during a time schedule when the system is lightly loaded.
  • Deploy reports through Microsoft CRM, and then use Report Manager to run the reports at a scheduled time and have the results posted.
  • Reports should access the fewest datasets possible to meet business requirements.

 

Optimization Techniques

Consider the following techniques to help ensure that reports perform optimally and minimize the potential impact of reporting on the rest of the system.

 

  • Use SQL ‘Group By’
    Use SQL ‘Group By’ to ensure that summary level data is gathered directly rather than by retrieving thousands of records and then post aggregating in reporting services. This helps to prevent the computer running Microsoft SQL Server from being hammered in gathering, transmitting then processing large volumes of data. Instead, it uses the natural indexing and grouping ability of SQL Server to massively reduce this overhead.

 

  • Create Custom Attributes
    Provide custom attributes to ensure that all the reporting data is available on the [CustomEntity]ExtensionBase table, rather than on a combination of the [CustomEntity]Base and {CustomEntity}ExtensionBase tables. This avoids a join, which bypasses both processing and additional temp db use. It also incurs overhead in the entity callouts to copy small amounts of data to shadow attributes in the extensionbase table (using the sdk) to enable single table reporting.

 

  • Make Reports Pre-Filterable
    When you create a report, you can make it “pre-filterable” by configuring it with a default filter that users can edit before they run the report. For each user that customizes and runs a pre-filterable report, the result is to effectively reduce the size of the data set and limit the amount of data pulled.

 

A key advantage of making a report pre-filterable is that the default filter selects active records that were modified in the last 30 days to prevent users from unintentionally running the report on all records. If you have the Manage Reports privilege, you can define specific default criteria for the default filter for each report. An advantage for users is the ability to edit the filter so that it locates the specific data that they require on the initial run.

For Microsoft Dynamics CRM to make a report pre-filterable, you must specify the CRMAF_ prefix in your SQL query when you create your report in Report Designer. When you add this prefix to at least one filtered view in the query, Microsoft Dynamics CRM adds a default filter to the report. For each filtered view that has this prefix in the query, users can edit filter criteria. For example, if your query includes the FilteredAccount and FilteredContact views, and your SQL query uses CRMAF_FilteredAccount and FilteredContact, the report will have a default filter. Users will be able to edit criteria related to accounts, but will be unable to edit criteria related to contacts.

  • Using Dynamic Excel or Filtered View Queries
    To limit the number of records a report returns if you are using a dynamic Excel worksheet or using a Filtered View query (this includes FilteredView queries in custom Microsoft Dynamics CRM SQL Reporting Services reports), consider making it more restrictive. If a field in the WHERE clause is used frequently, verify that a non-clustered index exists on that field.

 

0 Comments

Leave a reply

Your email address will not be published. Required fields are marked *

*