Microsoft Dynamics 365 (online) reporting considerations

  • Article

 

Applies To: Dynamics 365 (online), Dynamics CRM Online

Microsoft Dynamics 365 (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 Dynamics 365 (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 using the Power BI features PowerView, PowerPivot, and PowerQuery. As the volume of data held in the Dynamics 365 (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 Dynamics 365 (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 instances. Additionally, with Dynamics 365 (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 Dynamics 365 (online) are designed to let users run reports on datasets that span shorter periods of time. Considering this, Microsoft Dynamics 365 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 365 allows users to run queries that have a dataset that has fewer than 50,000 rows. Should a user run a dashboard query that spans a dataset of 50,000 or more rows, Microsoft Dynamics 365 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 Dynamics 365 (online) application.

In this topic

Tips and solutions for reporting

Third-party Microsoft Dynamics 365 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, such as the current month or quarter, to limit the results.

  • 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 the 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-the-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 Dynamics 365 (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 Dynamics 365 (online) to another SQL Server datastore by using a Dynamics 365 (online) data integration solution.

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

  • Extract transform load ETL tools provide a new tool set for creating analysis of Dynamics 365 (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 Dynamics 365 systems to move data.

Important

When you use these tools, we recommend 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 Dynamics 365 data integration tools available. More information: Find the right partner

Third-party Microsoft Dynamics 365 adapters for SSIS

ETL tools

See Also

Microsoft Dynamics CRM 2015 Report Authoring Extension (with SQL Server Data Tools support)
Developers guide to reports for Microsoft Dynamics CRM
Introduction to Microsoft Power Query for Excel
Dynamics CRM OData Feeds and Power Query: What’s the [Record]?
Using Power View in Excel 2013 to Analyze CRM Data
Reporting & Analytics with Dynamics 365

© 2016 Microsoft. All rights reserved. Copyright