Improve performance of ER solutions by reducing the number of table fields that are fetched at runtime

Article
08/12/2022

You can design Electronic reporting (ER) formats that generate outgoing documents in various formats. When a document is generated, an ER format calls data sources that were configured in a corresponding ER model mapping. To configure access to application tables, queries, or entities for record retrieval, you can use ER data sources of the Table records type. By default, a data source of the Table records type retrieves the values of all fields in the requested records. However, you can configure this type of data source so that it fetches only the field values that are required for the running ER format. This configuration helps reduce memory consumption of the application server that performs data retrieval and further record caching.

To learn more about how to limit the list of fetched fields of data sources of the Table records type, complete the example in this article.

Example: Reduce the number of table fields that are fetched at runtime

The following procedures show how a user in the System administrator or Electronic reporting developer role can configure an ER model mapping so that it fetches only the fields that are required to run the ER format, to help reduce the consumption of application server memory.

These procedures can be completed in the USMF company in Microsoft Dynamics 365 Finance. No coding is required.

To complete this example, you must have access to the USMF company for one of the following roles:

Electronic reporting functional consultant
System administrator

In this example, you will use the ER configurations that are provided for the Litware, Inc. sample company. Make sure that the configuration provider for the Litware, Inc. (http://www.litware.com) sample company is listed for the ER framework, and that it's marked as Active. If this configuration provider isn't listed, or if it isn't marked as Active, follow the steps in Create a configuration provider and mark it as active.

Configure the ER framework

Follow the steps in Configure the ER framework to set up the minimal set of ER parameters. You must complete this setup before you start to use the ER framework to modify data sources of the provided ER solution.

Import the sample ER configurations

If you haven't yet completed the example in the Design a new ER solution to print a custom report article, download and locally store the XML files for the following configurations of the provided ER solution.

Content description	File name
ER data model configuration	Questionnaires model.version.1.xml
ER model mapping configuration	Questionnaires mapping.version.1.1.xml
ER format configuration	Questionnaires format.version.1.1.xml

Then follow these steps to upload the configurations of the provided ER solution to your Finance instance.

Go to Organization administration > Workspaces > Electronic reporting.
Select Reporting configurations.
On the Configurations page, import the ER data model configuration.
1. Select Exchange, and then select Load from XML file.
2. Select Browse, find and select the Questionnaires model.version.1.xml file, and then select OK.
Import the ER model mapping configuration.
1. Select Exchange, and then select Load from XML file.
2. Select Browse, find and select the Questionnaires mapping.1.1.xml file, and then select OK.
Import the ER format configuration.
1. Select Exchange, and then select Load from XML file.
2. Select Browse, find and select the Questionnaires format.1.1.xml file, and then select OK.
In the configuration tree, expand Questionnaires model.
Review the list of imported ER configurations in the configuration tree.

Review the provided ER model mapping

On the Configurations page, select Questionnaires mapping.
On the Action Pane, select Designer.
On the Model to datasource mapping page, select Designer.
On the Model mapping designer page, on the Action Pane, select Group view to turn on Group view.
In the Data model pane, expand Questionnaire.

Notice that the Questionnaire data source has been configured to access the KMCollection application table.
In the Data sources pane, expand Table records > Questionnaire > Fields.

Notice how many fields from the KMCollection application table are exposed by the Questionnaire data source of the Table records type.
On the Action Pane, select Group view again to turn off Group view, and then select Show all > Show mapped only.

Notice that a few fields of the KMCollection application table are used to fill in the Questionnaire record list in the ER data model:
- Active
- Description
- questionMode
- kmCollectionId

Turn on the ER performance trace

Follow the steps in Turn on the ER performance trace to set the ER user parameters that enable the execution of ER components to be traced.

Run the provided ER format by using the provided model mapping

Follow the steps in Run a designed format from ER to run the provided ER format for a single questionnaire from the Configurations page.

Review the execution trace of the first run

Go to Organization administration > Electronic reporting > Configurations.
On the Configurations page, expand Questionnaires model, and select Questionnaires mapping.

Note

The details on the Versions FastTab indicate that you selected the draft version of the Questionnaires mapping configuration. Therefore, you can modify the content of this model mapping.
On the Action Pane, select Designer.
On the Model to datasource mapping page, select Designer.
On the Model mapping designer page, on the Action Pane, select Performance trace.
In the Performance trace result settings dialog box, select the trace that was generated during the last format run.
Select OK.
On the Details FastTab, filter the Questionnaire path that points to the Questionnaire data source.
Review the details of the database query that was generated when the Questionnaire data source was called.

Notice that all fields of the KMCollection application table were fetched at runtime when the Questionnaire data source was called.

Modify the provided ER model mapping

On the Model mapping designer page, in the Data sources pane, select the Questionnaire data source.
In the Data sources pane, select Edit.
In the Data source properties dialog box, select Select fields to specify the list of fields of the referenced KMCollection application table that will be fetched at runtime when the editable Questionnaire data source is called.
On the Select fields page, select Autofill.

The Selected fields list is automatically filled in, based on preconfigured artefacts of the model mapping. All fields and relations of the referenced table that are mentioned in any binding, formula, or data source of the model mapping are added to the list.
Select Save, and close the Select fields page.
Select OK to store the changes that you've made to the data source settings.
On the Action Pane, select Show all.

Notice that the Questionnaire data source now shows the text <Fields are filtered>. This text indicates that the data source has been configured to fetch a limited number of fields from the referenced application table.
Select Save to store the changes that you've made to the editable model mapping.

Note

At runtime, ER analyzes the added relations and adds all fields that are used in them to the database query, even if those fields weren't explicitly added to the list of fetched fields at design time.

Run the provided ER format by using the updated model mapping

Follow the steps in Run a designed format from ER to run the provided ER format for a single questionnaire from the Configurations page.

Review the execution trace of the second run

Go to Organization administration > Electronic reporting > Configurations.
On the Configurations page, expand Questionnaires model, and select Questionnaires mapping.
On the Action Pane, select Designer.
On the Model to datasource mapping page, select Designer.
On the Model mapping designer page, on the Action Pane, select Performance trace.
In the Performance trace result settings dialog box, select the trace that was generated during the last format run.
Select OK.
On the Details FastTab, filter the Questionnaire path that points to the Questionnaire data source.
Review the details of the database query that was generated when the Questionnaire data source was called.

Notice that only the fields that are required to fill in the data source were fetched at runtime from the KMCollection application table when the Questionnaire data source was called.

Note

Some fields, such as the fields for the partition ID, data area ID, and record ID, are automatically added by the Data Management framework of the Finance app.

You can use this technique to reduce the number of fetched records when you must reduce memory consumption by the running ER model mapping and ER format.

Limitations

When you limit the number of fetched fields for a data source of the Table records type, you can't use the methods of an application table that the data source refers to, because the application metadata doesn't provide information about table fields that are required to call those methods.

Usage notes

Although the Autofill command automatically adds fields, it doesn't automatically delete previously added fields, even if they are no longer used in bindings, formulas, and data sources of the editable model mapping.

When you select Autofill, ER analyzes bindings, formulas, and the data sources that the editable model mapping had when you opened it for editing. If you change bindings, formulas, and data sources of the editable model mapping, and you want to use the Autofill command, close the model mapping designer, and then re-open it to edit your model mapping.