Edit

Dynamics 365 Customer Engagement (on-premises) reporting considerations

  • Article

Microsoft Dynamics 365 Customer Engagement (on-premises) have 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 include views, charts, dashboards, and SQL Server Reporting Services reports. Also included is 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 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 Customer Engagement 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, 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 Customer Engagement (on-premises) are designed to let users run reports on datasets that span shorter periods of time. Considering this, we have 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.

  • For charts and grids displayed in dashboards, Power Apps environments allow 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, a message is returned stating “The maximum record limit is exceeded." Reduce the number of records.” The dataset practical setting helps to ensure optimal performance of the app.

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 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 app 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 Customer Engagement to another SQL Server datastore by using a data integration solution.

  • Adapters are used in conjunction with SQL Server Integration Services (SSIS) to extend the capabilities for integration with Dynamics 365 Customer Engagement data.

  • Extract transform load (ETL) tools provide a new tool set for creating analysis of Dynamics 365 Customer Engagement 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 Customer Engagement 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 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 data integration tools available. More information: Find the right partner

Third-party adapters for SSIS

ETL tools

See also

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