Data and data export in D365FO

ERP systems are one of the most significant data sources for many companies. It contains a huge amount of operation data, which tells how the company performs. The ERP system tracks value in different stages of the company’s operations, for instance, the value of WIP for production orders or the value of sales orders shipped to customers. This is valuable information to understand where to improve and what you should focus on to improve performance. This article will shortly explain what data categories exist in D365FO and how to export them. To help users make data analysis of companies' performance. ?

Before going to the export part, it is essential to understand the different data categories in D365FO for data analysis. The data categories will shortly be explained below:?

?

• Parameters – Parameter data is the configuration data of D365FO. It is the data that determines the behavior and functions of D365FO. This data is required to set up a deployment or a module for a specific build or customer. The table contains only one record, where the columns are values for settings. These are, e.g., tables for Accounts payable, General ledger, client performance options, workflow, etc.?Enums – Enums are data that fit into the “Parameter” category because they are related to the configuration of behavior and functions in D365FO. Enums are hardcoded values that trigger specific behaviors or functions. Enums are crucial to understanding and correctly exporting. Otherwise, a number value and not a label will be exported.?
• References – Reference data is required to execute business processes. Examples of reference data can be units, dimensions, and tax codes. This is primary business data used in multiple places when running operations. This is simple reference data in small quantities.?
• Master data – Master data is the assets of the business. It is data that is basic in creating document data for creating transactions. Master data is complex reference data of larger quantities, e.g., customers, vendors, and projects. ?
• Documents – Documents are worksheet data that is converted into transactions later. Documents have a complex structure, like several line items for each header record. This could, e.g., be sales orders, purchase orders, open balances, and journals. ?
• Transactions – Transactions are the records of operational business actions. This could be inventory transactions showing moving inventory from location to location or in/ out of warehouses. It could also be pending invoices. ?

?

There are multiple ways to export data from D365FO. It is possible to use data management for data export by entities to a BYOD (Bring your own database) or other formats like CSV or EXCEL. Another way is to export data into a self-configured Excel sheet using the embedded Excel function. The last option explained in this article is exporting data into a data lake.?

Data management is a workspace where it is possible to configure a connection string to a BYOD or export data into an Excel sheet, CSV file, etc. ?

?

If data should be exported to a BYOD, the connection string should be configured under “Configure entity export to database.”?

?

The data export is done through entities, which you can get an overview of by pressing “Data entities.” Entities are specific columns for a data area combined in one entity. The entity covers all or part of the data columns in a table. ?

?

To create a data export through entities, press “Export.” ?

?

Here, the different entities can be selected, and a format for the export. E.g., the export can be done to a BYOD, Excel sheet, or CSV file. When creating the export, you can modify the mapping, change the refresh type and filter, and choose to “skip staging.” ??

?

If you press “View map,” it will be possible to make Enum values exported as labels. ?

?

Otherwise, press “Export now” to export the data. The export can be done in batch if needed. All data can be downloaded as a package if more entities are included in the export. Otherwise, you can download the single file by clicking “Download file.” ?

?

If you need to extract data into an Excel sheet regularly, the export to Excel function on the different forms is applicable. ?

?

Here, you can select between different standard templates. Configuring your own is possible if these templates do not meet the requirements. This is done using the “Open in Excel” function and designing your export Excel sheet based on this template. It is done the following way. ?

“Download” the Excel sheet and log in to modify the layout—press “Design” to fetch data from other tables or fields. ?

Then, it is possible to choose an entity data source. ?

Press ”Next.” Then, it will be possible to see the available fields that can be added. After adding them, press “Done.”?

?

Then, the data is added after pressing “Refresh.” Save the Excel sheet on your laptop before it can be uploaded to D365FO. ?

?

To add this template in D365FO, go to “Office integration” > “Document templates” and press “New.”?

?

When adding the template, D365FO will identify which form it is related to. After adding it, go to the specific form and press the office icon to download data in your self-design Excel format. ?

The last data export option explained in this article is data lack export. The data lake export is a microservice running in LCS. That forwards data from D365FO into a data lake on Azure. ?

The first step is to configure Azure data lake for data export. MS defined the following steps, which must be fulfilled in Azure to create the data lake and set up access. ?

The second step is activating the Power Platform integration on LCS to install the “Export to data lake” add-in. The picture below shows where the add-in will be installed in LCS when the Power Platform Integration is set up. Remember always to have your D365FO partner involved before doing this yourself because after enabling the “Power platform integration,” it will be impossible to disable it. The only way to remove it is by deleting the environment. ?

?

To find this site in LCS, select the relevant environment in LCS and press “Full detail.”?

Afterward, the “Export data to Azure data lake” feature must be enabled in D365FO.?

The following information is needed in D365FO to configure the connection to the data lake: the “Application ID,” “Application Secret,” “DNS name,” and “Secret name.” The connection is configured under the “System administration” module. ?

It is possible to export “Entity Store” data to the data lake. The “Entity Store” is aggregated measurements for reporting. The export will happen with a fixed interval. This is not something that will be elaborated further in this article. The data export to the data lake can be for a table or entity. When the export is activated, data will be exported to the data lake whenever a change is made in the table or entity.? ?

?

To learn more about data and other features in D365FO like how to:?

• Use Data management to migrate data??
• Configure the entity store export to the data lake.?
• Configure and use change tracking for data management export??
• Configure the connection string to a BYOD.?
• Understand and use data for analysis??
• Copy data to other legal entities or export data from other legal entities simultaneously??
• Understand and use data entities??
• Use data projects and utilize information in job history.?
• Enable the “Power Platform integration” and install the “Export to data lake” add-in in LCS.?
• Get started with Synapse Link when Data lake export is deprecated by the 24th of October 2024?

?

Reach out and get help.?