Share via

Microsoft Dynamics CRM Online reporting considerations

  • Article

 

Applies To: Dynamics CRM 2013

Microsoft Dynamics CRM Online has a number of capabilities that allow customers to surface business data that helps them drive decisions and interact with their customers more effectively. Capabilities that are available within CRM Online include views, charts, dashboards, and Microsoft SQL Server Reporting Services reports. Also included is Microsoft Office Excel integration that allows users to easily build self-service reports by using the Power BI features PowerView, PowerPivot, and PowerQuery. As the volume of data held within the CRM Online database continues to grow it becomes more important than ever to think about your BI strategy and determine the most effective mechanisms for reporting and visualizing large datasets.

In a CRM Online environment, the reporting infrastructure is shared and separate from the database. In this architecture, although customers share the resources required to run the report, each report runs against the customers’ individual database instance. Additionally, with CRM Online, users can run as many reports as they need whenever they want to run them to meet business goals. We do not place time restrictions on reports.

The reporting capabilities built in to CRM Online are designed to let users run reports on datasets that span shorter periods of time. Considering this, Microsoft Dynamics CRM has the following fixed settings:

  • Reports and queries can execute for up to five minutes. When the maximum period is reached, the report will time-out and a message is returned to the user. Within the five-minute duration, reports and queries are allowed to span large datasets that are beyond 50,000 records, which provides significant flexibility to satisfy most operational reporting needs.

  • To improve query response, we recommend that detailed reports minimize the display of large numbers of records. To do this, apply suitable filtering to reduce the number of records that are returned. When you create aggregated or summarized reports, queries should push the aggregation to the query rather than fetch detailed records to perform aggregation in the report. This can be done by using Fetch XML aggregation. More information: Use FetchXML aggregation

  • For charts and grids displayed in dashboards, Microsoft Dynamics CRM allows users to run queries that have a dataset that is under 50,000 rows. Should a user run a dashboard query that span a dataset of 50,000 or more rows, Microsoft Dynamics CRM returns the message “The maximum record limit is exceeded. Reduce the number of records.” The dataset practical setting helps to ensure optimal performance of the CRM Online application.

In This Topic

Tips and solutions for reporting

Third-Party Microsoft Dynamics CRM adapters for SSIS

ETL tools

Tips and solutions for reporting

Typically, for most organizations’ reporting needs, these settings are adequate. To make sure that your users do not exceed these settings and to improve report querying performance in general, consider the following best practices.

  • When you create custom reports or dashboards, design them to query smaller datasets over shorter periods of time by adding a time-based filter in the report to limit the results, such as the current month or quarter.

  • We recommend that you limit the number of entities that are needed to return the result. This helps reduce the time required to run the query and return the result set.

  • We recommend that you reduce the number of records shown in detailed reports. Suitable filtering can be used to reduce number of records returned by the query to reduce timeouts.

  • For aggregated or summarized reports, queries must be used to push the aggregation to the database and not fetch detailed records and perform aggregation in the Microsoft SQL Server Reporting Services report.

  • When appropriate for your business, users should run the default (out-of-box) reports and dashboards. These reports and dashboards are typically designed to query per user datasets, so in most cases will not exceed the dataset limit.

If CRM Online users must run reports that exceed these settings, we recommend that you review the following options for assistance with complex reporting needs. Both options effectively offload reporting workloads from CRM Online to another SQL Server datastore by using a CRM data integration solution.

  • Third-Party Microsoft Dynamics CRM adapters for SSIS are used in conjunction with SQL Server Integration Services (SSIS) to extend the capabilities for integration with CRM Online data.

  • Extract transform load ETL tools provide a new tool set for creating analysis of CRM Online data by combining multiple data sources or extracting data to the data warehouse solution if SSIS is not in use. ETL tools provide comprehensive solutions for connecting CRM systems to move data.

Important

Notice that, when you use these tools, we recommend that you move or synchronize data during nonbusiness hours.

If needed, there are many Microsoft Dynamics Partners who can help provide a solution for your specific reporting needs such as creating an offline copy of the data specifically used for running large reports. These Partners are knowledgeable with the CRM data integration tools available. More information: Find the right partner

Third-Party Microsoft Dynamics CRM adapters for SSIS

ETL tools

See Also

Microsoft Dynamics CRM 2013 Report Authoring Extension (with SQL Server Data Tools support)
Developers guide to reports for Microsoft Dynamics CRM
Using Power View in Excel 2013 to Analyze CRM Data
Introduction to Microsoft Power Query for Excel
Best practices for reports
Report Writers Guide for Microsoft Dynamics CRM 2013

© 2016 Microsoft Corporation. All rights reserved. Copyright