Electronic Reporting in Depth, Data Model Mapping

charles OWkXt1ikC5g unsplash resized 1

charles OWkXt1ikC5g unsplash resized 1

This blog post will explain the concept of the model mapping within Electronic Reporting. The model mapping is the binding of the data source to the data model and after this post you should have a general idea of what encompasses a model mapping and how to start with your own model mapping.

This post assumes that you are familiar with the basics of Electronic Reporting and that you have a good understanding of data models. Due to the complexity of a model mapping only the framework and components in a model mapping will be explained. More detailed explanations will follow in separate blog posts.

Creating a Model Mapping

As we have detailed in the previous post, a data model is the central structure to which you bind both the data sources and the output. We illustrated this with the image below.

er components orig
er components orig

Since Electronic Reporting can be used for both importing data and exporting data, the image above does not tell the full story. A model mapping can have two directions, To Destination which is used for importing data and To Model which is used for exporting data. The same goes for a format mapping, when exporting data, the mapping is performed directly in the designer and when importing data, the mapping is performed via the model mapping in the format mapping. The image below illustrates this.

ER Model drawing v2
ER Model drawing v2

We will go into detail on format mappings in a later blog post. For now, we will focus on the model mapping. To create a model mapping select the data model you want to create the mapping for and click Create configuration. Enter a name and select the data model definition you want to use in the mapping and click create configuration.

How to create a model mapping

We have now done the first step in creating a model mapping and that is creating the configuration. The second step is to create the actual mapping and by this telling Electronic Reporting the direction we are creating the mapping for. Click on Designer and you will see that Electronic Reporting defaulted in a To Model direction (export) for our model mapping.

Model Mapping Default Direction
Model Mapping Default Direction

Model Mapping Designer Menu Overview

When you open the to model direction model mapping designer you will see several menu items.

Model Mapping To Model Menu Overview
Model Mapping To Model Menu Overview
Model Mapping To Model Menu Overview
Model Mapping To Model Menu Overview

There are 3 menu panes, each with their own items and functionalities:

Action Pane Menu Items, these menu items control the general lay out and functionalities of the designer.
  • Save, saves the current configuration setup. Please keep in mind that there is no auto save in Electronic Reporting. You can either save manually, or when you exit the designer you will be asked to save the changes.
  • Show Details, by default Electronic reporting will show the name (and label if configured) of the data source type. If show details is enabled the contents of the data source type will be shown as well. In our example this would show the formula of the CustGroup10 node and the table name of the CustTable node. This option is very useful when trying to get a quick detailed overview of a model mapping.
  • Show Name First, in our example we named the name and the label of the data source type the same. It can happen however that these names differ, for example with translations. When this is the case Electronic Reporting will show the data source as <label>(<name>) (without <>). Selecting this option inverts this and will make Electronic Reporting show the data source as <name>(<label>) (without <>). This does not change bindings or so and is only a preference thing.
  • Group View, by default each data source node is shown by the hierarchy it was configured in to, either as root or as child of a root. This option changes the view to have the nodes grouped by their type, so all calculated fields nodes will be grouped, all table record nodes will be grouped and so on. This is useful when searching for specific nodes and you do not know where they are exactly nested.
  • Show all, this is a dropdown with 3 options. This option is useful when you want to analyze an existing model mapping and see the actual used nodes or when configuring a new model mapping.
    • Show all, this shows all the nodes in the data sources overview and the data model definition overview
    • Show not-mapped only, this will change the view to only show the items that have not been mapped yet
    • Show mapped only, this will change the view to only show the items that have been mapped
Data Source Menu Items, these menu items control the functionalities of the data source types and the selected data sources. When a data source node is displayed in bold text this means that this was custom added, for these the below options are available. If the node or child node is in regular format then this means that it is a record result of the parent node, in this case the options below are not available
  • Add Root, this adds the selected data source type as a root node to the data sources. After clicking this menu item, you will be asked to give in the required parameters for the selected data source.
  • Add, this adds the selected data source type as a child node to the selected data source node. The reason you want child nodes is that they inherit filtering and options from their parent nodes.
  • Edit, this opens the parameter screen for the selected data source node.
  • Delete, this deletes the selected data source node. You can only delete a node if there are no custom child nodes present. Delete these first before deleting the parent node.
  • Search, this searches the data source nodes for the input text. Please note that you can also press Control + F from your browser, however this will only search the opened nodes. This search function will search the collapsed nodes as well. Use the up and down arrows to dictate the direction, the search function does not loop.
  • Cache, Electronic Reporting can use caching, this menu item enables or disables caching for the selected node. By default, caching is disabled and the Electronic Reporting engine processes each binding (determined from the output format configuration) as a new call to the database. This is a fire and forget process. If you enable caching, Electronic Reporting will cache the collected records, and this will reduce the amount of database calls and as a result this will increase performance. Cache tuning requires you to know the exact structure and logic of your format and your mapping. Proper cache tuning can give great results, especially for big output files, I have been able to achieve 30% to 40% performance increase just by proper cache tuning.
Destination Menu Items, these menu items control the functionalities on the data model definition.
  • Bind, the bind button binds the selected data source node to the selected data model definition node. You can only select this button if the data types of the data source node and the data model definition node are matching. So proper data model setup is key here.
  • Edit, this edits the selected data model definition node. This allows you to add formulas to the binding. For example, if you have an integer data source node which you want to bind to a string data model definition node the edit button allows you to add a formula which converts this. From a performance point of view, it is not recommended to use formula conversions on a large scale, proper data model setup is the way to go.
  • Unbind, this removes the selected binding or formula.
  • Search, this searches the data model definition nodes for the input text. Please note that you can also press Control + F from your browser, however this will only search the opened nodes. This search function will search the collapsed nodes as well. Use the up and down arrows to dictate the direction, the search function does not loop.

Besides the mentioned menu items above you will notice that there is a validations menu as well in the available data source types. This menu allows you to put data validation in your model mapping. The usage of this varies case by case. However, for export configurations you are better of incorporating validation in the format configuration. So, for now we will not go in depth on validations.

To Model Direction Designer

As stated earlier the To Model Direction is used for export configurations. The data sources are mapped from the data source to the model. To configure the actual model mapping, enter the configuration designer by clicking Design like we did in the previous step and then select the model mapping you want to design and click on the Designer button.

Designer To Model
Designer To Model

In this designer we can see 3 areas. These are the areas you will be using for the model mapping design. Please keep in mind that the size of the areas can be adjusted by sliding and that the areas can sometimes be very small be default.

Model Mapping Designer overview
Model Mapping Designer overview

The way you design the model mapping is that you select your data source types from the left pane. After selecting, the data source types will appear in the middle pane, and after this you optionally can apply filtering, validations, calculations and a lot more to it via formulas. Lastly you will bind the data source to the data model definition. As an example, we will add the CustTable as data source and filter it on CustGroup 10 and bind this to the data model definition (this is purely an example, the bindings make no sense from a functional perspective).

Create a filter formula

To Destination Direction Designer

As stated earlier the To Destination direct is used for import configurations. The imported file components are mapped to the destination. To configure a To Destination model mapping enter the model mapping configuration by clicking the design button and then click on new. Select the to destination option from the dropdown and enter a name for the configuration. Optionally you can delete the default created export mapping as well.

Create a to destination mapping

For importing configurations, the data source is the data model and the destination can be a table or a data entity (although you can select a map and a view, these will not work). This means that in the data source window you will have to add a data model definition. Off course, after adding the data model definition you can add all sorts of other formulas as well. For a data source we must add the table or data entity we want to import to. After this the mapping is performed in the same manner as a to model mapping.

As we saw the designer for to destination mappings is nearly equal to the designer of the to model mapping. The differences are the integration point field and the destination menu under the data model area.

Designer to Destination
Designer to Destination

Via the destination menu is a dropdown with 3 options:

  • Add, via the add button you can add one or more destinations.
  • Edit, via the edit button you can edit a selected destination.
  • Delete, via the delete button you delete the selected destination.

When adding a destination, a menu similar to the table records menu is shown. However, there are some differences.

Add destination menu
Add destination menu
  • Table, the table option defines the actual destination you are sending your imported data to. You can select from the types table, data entity, map and view however the latter 2 are not usable. When selecting a table, you must keep in mind that Electronic Reporting will treat this as database inserts meaning all logic is skipped, so for example a default customer address will not be filled in automatically. This is not the case for data entities, Electronic Reporting will treat this as an insert into the entity and the entity will handle the further logic.
  • Name, defines the name of the destination node.
  • Record Action, defines how Electronic Reporting handles the data send to the destination. It is recommended to take data validation and record lookup into account in your design of the data sources so the record actions can be properly used.
    • Insert, this will insert all data into the destination table, duplicate data can occur (or errors depending on the destination).
    • Insert or Update, this will insert all new data in the destination table and if an existing entry is found in the destination it will not insert this data but rather update the data.
    • Update, Electronic Reporting update the applicable records in the destination.
    • Delete, Electronic Reporting deletes the applicable records in the destination.
  • Record Infolog, this option determines whether the validation errors and warnings occurring during import should be shown in the Infolog.
  • Integration point, the integration point can be used to reference the import mapping in D365FO menu items. This is configured (by a developer) via the lookup method on the ERModelMappingTable.
public Common lookupReference()
        {
            return ERModelMappingTable::lookup(this, classStr(ERTableDestination) + '#' + tableStr(<document>));
        }
  • Skip record action validation, select this option if you want to skip record validation during import. Please keep in mind that this is table level validation and not the validation you would normally get when creating new records from the user interface

Data Source Types

In the model mapping designer, you can select from a wide variety of data source types. Selecting the correct data source type for your purpose is crucial for creating a successful model mapping. Below are the data source types and their explanations. In general, each of the data source types will have a name, label and help input these will not be detailed, only the relevant input types will be discussed below. As the data sources can become quite complex only a brief description is provided. In later posts the source types will be explained in more depth with examples.

Data model, these data source types are related to your data model and they are set up there.
  • Data model, this adds the entire data model as a data source types. This is a required data source for To Destination (import) configurations.
    • Model, the model you want to use. Unless specifically required otherwise select your parent model here.
    • Definition, the definition from the model you want to use.
    • Version, the version of the model you want to use. I keep this empty most of the time, the most recent version will be entered by default if you set the model mapping to complete.
  • Enumeration, retrieves the selected enumeration from the data model and allows you to use formula’s or other logic to define the used enumeration manually.
  • Enumeration user input parameter, retrieves the selected enumeration from the data model and prompts the user on run time to select the correct enum value. If applicable you can enable a multiple selection, this depends on your enumeration setup.
Dynamics 365 for Operations, these data source types are the core data sources and logic within D365FO.
  • Class, this allows you to select any class within D365FO and use it as a data source. All related input / output fields for the class are retrieved.
  • Enumeration, this allows you to select any enumeration within D365FO and use it as a data source.
  • Number sequence, this allows you to select a number sequence as data source for easy number assignment. This will execute all the logic from the number sequence as well (continuous, next available number, etc).  
  • Object, this allows you to select any class object.
  • Table, retrieves the static objects on a table.
  • Table records, retrieves the table and its records.
    • Table, allows you to select a table / view / map or data entity.
    • Cross Company, by default Electronic Reporting executes queries in the same company as the company the configuration was called from. This disables that logic and allows you to control the company sources
    • Ask for query, unless specified otherwise Electronic Reporting will retrieve the entirety of a table. You can either control this via a filter in calculated field or by asking for a query. This allows you to filter the table beforehand, when executing an electronic report all known query parameters get filled in by default. 
Format, these data source types allow you to use format configurations as a source for your model mapping.
  • Export format, this allows you to retrieve format mappings of the export type and use these in bindings.
  • Import format, this allows you to retrieve format mappings supporting import and use these in bindings.
Functions, functions are they way you can modify or filter other data source types in Electronic Reporting
  • Calculated field, this allows you to configure field calculations via formulas. An overview of the available formulas can be found here.
  • Financial dimensions details, this allows you to select a financial dimension set for your report.
  • Group by, this allows you to group by and aggregate record lists much like the group by statement in SQL. Keep in mind though that if you use records from the group by list that are not in scope of the group by, you will get strange results.
  • Inventory dimensions details, this allows you to select inventory dimensions to use for your report.
  • Join, this allows you to join related data creating a combined record list based on the join.
  • Table metadata, allows you to generate the meta data of tables and their respective fields based on your selection.
General, these data source types are for general structure or user input.
  • Empty container, creates an empty container to which you can bind child nodes.
  • Endless list, creates an list type container to which you can bind child nodes.
  • User input parameter, allows you to create a custom input parameter for the user during run time of the report.
Tax, these data source types are related to taxable documents
  • Taxable document source, creates a data source node in which you can generate records from a specific taxable document.

Conclusion

You now should have a general idea on how to set up your own data model mapping and what components you will see in the designer. This should help you on the way into creating your own model mapping and give you an idea on what you are working with. In the next part we will go into detail on format mappings.

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *