Improve the performance of ER solutions by adding parameterized CALCULATED FIELD data sources

This article explains how you can take performance traces of Electronic reporting (ER) formats that are run, and then use the information from those traces to help improve performance by configuring a parameterized Calculated field data source.

As part of the process of designing ER configurations to generate business documents, you define the method to retrieve data from the application and enter it in the generated output. By designing a parameterized ER data source of the Calculated field type, you can reduce the number of database calls and significantly reduce the time and cost that are involved in collecting the details of ER format execution.

Prerequisites

• To complete the examples in this article, you must have access to one of the following roles:

  • Electronic reporting developer
  • Electronic reporting functional consultant
  • System administrator

• The company must be set to DEMF.

• Follow the steps in Appendix 1 of this article to download the components of the sample Microsoft ER solution that you need to complete the examples in this article.

• Follow the steps in Appendix 2 of this article to configure the minimal set of ER parameters that you need to use the ER framework to help improve the performance of the sample Microsoft ER solution.

Import the sample ER solution

Imagine that you must design an ER solution to generate a new report that shows vendor transactions. Currently, you can find the transactions for a selected vendor on the Vendor transactions page (go to Account payable > Vendors > All vendors, select a vendor, and then, on the Action Pane, on the Vendor tab, in the Transactions group, select Transactions). However, you want to have all vendor transactions together in one electronic document in XML format. This solution consists of several ER configurations that contain the required data model, model mapping, and format components.

The first step is to import the sample ER solution to generate a vendor transactions report.

1. Sign in to the instance of Microsoft Dynamics 365 Finance that is provisioned for your company.

2. In this article, you create and modify configurations for the Litware, Inc. sample company. Make sure that this configuration provider is added to your Finance instance and is marked as active. For more information, see Create configuration providers and mark them as active.

3. In the Electronic reporting workspace, select the Reporting configurations tile.

4. On the Configurations page, import the ER configurations that you downloaded as a prerequisite into Finance, in the following order: data model, model mapping, format. For each configuration, follow these steps:

   1. On the Action Pane, select Exchange > Load from XML file.
   2. Select Browse, and select the appropriate file for the ER configuration in XML format.
   3. Select OK.

Review the sample ER solution

Review the model mapping

1. On Configurations, in the configuration tree, expand Performance improvement model, and select Performance improvement mapping.

2. On the Action Pane, select Designer.

3. On Model to datasource mapping, on the Action Pane, select Designer.

   This ER model mapping is designed to perform the following actions:

   • Fetch the list of vendor transactions that are stored in the VendTrans table (Trans data source).
     • For every transaction, fetch, from the VendTable table, the record of a vendor that the transaction is posted for (#Vend data source).

   Note

   The #Vend data source is configured to fetch the corresponding vendor record by using the existing many-to-one relation @.'>Relations'.VendTable_AccountNum.

   The model mapping in this configuration implements the base data model for any ER formats that are created for this model and run in Finance. Therefore, the content of the Trans data source is exposed for ER formats such as abstract model data sources.

   

4. Close Model mapping designer.

5. Close Model to datasource mapping.

Review format

1. On Configurations, in the configuration tree, expand Performance improvement model, and select Performance improvement format.

2. On the Action Pane, select Designer.

3. On Format designer, on the Mapping tab, select Expand/Collapse.

4. Expand the Model, Data, and Transaction items.

   This ER format generates a vendor transactions report in XML format.

   

5. Close Format designer.

Run the sample ER solution to trace execution

Imagine that you finish designing the first version of the ER solution. You want to test the solution in your Finance instance and analyze the execution performance.

Turn on the ER performance trace

1. Select the DEMF company.

2. Follow the steps in Turn on the ER performance trace to generate a performance trace while an ER format runs.

   

Run the ER format

1. Go to Organization administration > Electronic reporting > Configurations.
2. On Configurations, in the configuration tree, select Performance improvement format.
3. On the Action Pane, select Run.

Use the performance trace to analyze model mapping performance

1. On Configurations, in the configuration tree, select Performance improvement mapping.
2. On the Action Pane, select Designer.
3. On Model mappings, on the Action pane, select Designer.
4. On the Model mapping designer page, on the Action Pane, select Performance trace.
5. Select the most recently generated trace, and then select OK.

New information is now available for some data source items of the current model mapping:

• The actual time spent getting data by using the data source
• The same time expressed as a percentage of the total time spent running the whole model mapping

The Performance statistics grid shows that the Trans data source calls the VendTrans table one time. The value [265][Q:265] of the Trans data source indicates that 265 vendor transactions are fetched from the application table and returned to the data model.

The Performance statistics grid also shows that the current model mapping duplicates database requests while the #Vend data source runs. This duplication occurs for the following reasons:

• The vendor table is called two times for each of the 265 iterated vendor transactions, for a total of 530 calls:

  • One call is made to enter the vendor account number.
  • One call is made to enter the vendor name.

• The vendor table is called for each iterated vendor transaction, even though the fetched transactions are posted for only five vendors. Of the 530 calls, 525 are duplicates. The following illustration shows the message that you receive about duplicate calls (database requests).

Of the total model mapping execution time (approximately eight seconds), more than 80 percent (approximately six seconds) is spent retrieving values from the VendTable application table. That percentage is too large for two attributes of five vendors, compared with the volume of information from the VendTrans application table.

To reduce the number of calls that get the vendor details for every transaction and to improve the performance of the model mapping, you can use caching for the #Vend data source.

By caching the #Vend data source, you reduce the number of duplicated calls from 525 to 260, but you don't completely eliminate the duplication. To completely eliminate duplication, you can configure a new parameterized data source of the Calculated field type.

Improve the model mapping based on information from the execution trace

Change the logic of the model mapping

Follow these steps to use caching and a data source of the Calculated field type, to help prevent duplicate calls to the database.

1. On Configurations, in the configuration tree, select Performance improvement mapping.

2. On the Action Pane, select Designer.

3. On Model mappings, on the Action pane, select Designer.

4. On Model mapping designer, add a data source of the Table records type to access records in the VendTable application table:

   1. In the Data source types pane, expand Dynamics 365 for Operations, and select Table records.
   2. Select Add root.
   3. In the dialog box, enter Vend in the Name field.
   4. Enter VendTable in the Table field.
   5. Select OK.

5. You can parameterize calls to data sources of the Calculated field type only if those data sources reside in a container. Therefore, add a data source of the Empty container type to hold a new parameterized data source of the Calculated field type:

   1. In the Data source types pane, expand General, and select Empty container.
   2. Select Add root.
   3. In the dialog box, enter Box in the Name field.
   4. Select OK.

   

6. Add a parameterized data source of the Calculated field type:

   1. In the Data sources pane, select Box.
   2. In the Data source types pane, expand Functions, and select Calculated field.
   3. Select Add.
   4. In the dialog box, enter Vend in the Name field.
   5. Select Edit formula.
   6. On Formula designer, select Parameters to specify the parameters that must be provided when this data source is called.
   7. In Parameters, select New.
   8. Enter parmVendAccNumber in the Name field.
   9. Select String in the Type field.
   10. Select OK.
   11. In the Formula field, enter FIRSTORNULL(FILTER(Vend, Vend.AccountNum=parmVendAccNumber)).
   12. Select Save, and close Formula designer.
   13. Select OK to add the new data source.

7. Mark the added data source as cached during the execution:

   1. In the Data sources pane, select Box\Vend.
   2. Select Cache.

8. Update the nested Trans\#Vend data source so that it uses the Box\Vend data source:

   1. In the Data sources pane, expand Trans.
   2. Select the Trans\#Vend data source, and then select Edit > Edit formula.
   3. On Formula designer, enter Box.Vend(@.AccountNum) in the Formula field.
   4. Select Save, and then close Formula designer.
   5. Select OK to complete your changes to the selected data source.

9. Select Save.

   

10. Close Model mapping designer.

11. Close the Model mappings page.

Complete the modified version of the ER model mapping

1. On Configurations, on the Versions FastTab, select version 1.2 of the Performance improvement mapping configuration.
2. Select Change status > Complete, and then select OK.

Run the modified ER solution to trace execution

Repeat the steps in the Run the ER format section earlier in this article to generate a new performance trace.

Use the performance trace to analyze adjustments to the model mapping

1. On Configurations, in the configuration tree, select Performance improvement mapping.
2. On the Action Pane, select Designer.
3. On Model mappings, on the Action Pane, select Designer.
4. On Model mapping designer, on the Action Pane, select Performance trace.
5. Select the most recent trace that was generated, and then select OK.

Notice that the adjustments you made to the model mapping eliminated duplicate queries to the database. The number of calls to database tables and data sources for this model mapping is also reduced.

The total execution time is reduced by about 20 times (from about 8 seconds to about 400 milliseconds). Therefore, the performance of the whole ER solution is improved.

Appendix 1: Download the components of the sample Microsoft ER solution

Download the following files and store them locally.

Appendix 2: Configure the ER framework

Before you can start to use the ER framework to improve the performance of the sample Microsoft ER solution, you must configure the minimal set of ER parameters.

Configure ER parameters

1. Go to Organization administration > Workspaces > Electronic reporting.

2. On the Localization configurations page, in the Related links section, select Electronic reporting parameters.

3. On the Electronic reporting parameters page, on the General tab, set the Enable design mode option to Yes.

4. On the Attachments tab, set the following parameters:

   • In the Configurations field, select the File type for the DEMF company.
   • In the Job archive, Temporary, Baseline, and Others fields, select the File type.

For more information about ER parameters, see Configure the ER framework.

Activate an ER configuration provider

Every ER configuration that you add is marked as owned by an ER configuration provider. Use the ER configuration provider activated in the Electronic reporting workspace for this purpose. Therefore, you must activate an ER configuration provider in the Electronic reporting workspace before you start to add or edit ER configurations.

Review the list of ER configuration providers

1. Go to Organization administration > Workspaces > Electronic reporting.
2. On Localization configurations, in the Related links section, select Configuration providers.
3. On Configuration provider table, each provider record has a unique name and URL. Review the contents of this page. If a record for Litware, Inc. already exists, skip the next procedure, Add a new ER configuration provider.

Add a new ER configuration provider

1. Go to Organization administration > Workspaces > Electronic reporting.
2. On Localization configurations, in the Related links section, select Configuration providers.
3. On Configuration providers, select New.
4. In the Name field, enter Litware, Inc..
5. In the Internet address field, enter https://www.litware.com.
6. Select Save.

Activate an ER configuration provider

1. Go to Organization administration > Workspaces > Electronic reporting.
2. On Localization configurations, in the Configuration providers section, select the Litware, Inc. tile, and then select Set active.

For more information about ER configuration providers, see Create configuration providers and mark them as active.

Additional resources

• Electronic Reporting overview
• Trace the execution of ER formats to troubleshoot performance issues
• Support parameterized calls of ER data sources of the Calculated field type