Edit

Model-driven app reporting capabilities and considerations

  • Article

Model-driven apps have many capabilities that allow customers to surface business data that helps them drive decisions and interact with their customers and their data more effectively.

These are described in the reporting overview and include

As the volume of data held in the app's 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.

Reporting infrastructure

In an 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.

The reporting capabilities built in to Microsoft Dataverse are designed to let users run reports on datasets that span shorter periods of time. Considering this, note 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 rows, which provide significant flexibility to satisfy most operational reporting needs.

  • To improve query response, we recommend that detailed reports minimize the display of large numbers of rows. To do this, apply suitable filtering to reduce the number of rows that are returned. When you create aggregated or summarized reports, queries should push the aggregation to the query rather than fetch detailed rows to perform aggregation in the report. More information: Aggregate data using FetchXml and report prefiltering.

  • The Report Viewer page is subject to the platform API limits. Multiple executions of a long-running report might result in an error, and the user can wait few minutes and try again. This is a per user limit and shouldn't affect the normal usage of reports by multiple users.

  • For charts and grids displayed in dashboards, your apps 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, the message "The maximum row limit is exceeded. Reduce the number of rows" is returned. 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 don't exceed these settings and to improve report querying performance in general, consider the following best practices.

  • When creating 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.

  • Limit the number of tables that are needed to return the result. This helps reduce the time required to run the query and return the result set.

  • Reduce the rows in detailed reports. Suitable filtering can be used to reduce the number of rows 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 rows 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 won't exceed the dataset limit.

If 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 Dataverse to another 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 your apps data.

  • Extract transform load (ETL) tools provide a new tool set for creating analysis of data by combining multiple data sources or extracting data to the data warehouse solution if SSIS isn't in use. ETL tools provide comprehensive solutions for connecting with Dataverse to move data.

Important

When you use these tools, we recommend that moving or synchronizing data takes place 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 a Dynamics 365 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]?