A lot of times customers have questions around how to improve reporting performance in Dynamics CRM. Of course, with every CRM deployment and report the answer may be different, but some tips can be applied more generally. In this post I will discuss some basic tips and tricks to improve your Dynamics CRM reports, specifically around query tuning and using Visual Studio’s Business Intelligence Development Studio. This is the second blog post in the CRM Reporting Series, you can find the first post in the following link which focuses on the different options for querying data and building reports out of CRM: Custom Reporting in Microsoft Dynamics CRM - Fetch vs. Filtered Views
When thinking about reporting, the total time that it requires to generate a report can be split into three different actions:
There are many possible culprits of reporting performance issues and often users jump to the conclusion that it has to be a server side issue. Maybe so, though many times we see that report performance issues in CRM are directly related to the datasets, queries and report formatting logic within the report that could be optimized for better performance. The following are suggestions for optimization as you begin to troubleshoot:
Suggestions for all Dynamics CRM Deployment Types:
The following summarizes the recommendations that can be performed using reporting tools for both CRM On-Prem and CRM Online, specifically what can be done using either SQL Queries OR FETCHXML, which is the only option for CRM Online.
Optimize Report Queries & Retrieve the Minimum Amount of Data Needed in your Report
Reporting Services (Business Intelligence Studio) Recommendations
CRM On-Prem Only Suggestions
Additional Considerations to take when optimizing your reports:
For additional perspective on the different options that the Microsoft Dynamics CRM 2011 SDK provides for retrieving data programmatically, check out a recent post by Austin Jones explaining Dynamics CRM 2011 SDK Query Limitations by API.
If you are interested, our PFE team is ready to help you with this, we can assist with query examples and various other engagements to help you improve your CRM reports and performance. In addition, we have many other services we offer such as reporting workshops, developer training, admin workshops, and code reviews. If you would like to have another Microsoft PFE or I visit your company and assist with the ideas presented on our blog, contact your Microsoft Premier Technical Account Manager (TAM) for booking information. For more information about becoming a Microsoft Premier Customer email PremSale@microsoft.com.
Microsoft Premier Field Engineer