In this article, we will show you how you can easily import data into Microsoft Dynamics 365 for Finance and Operations (D365FO) using the Data Management Workspace and the Data Import Export Framework, known as DIXF.
In this article
- Data Management Workspace
- Data entities
- Import data
- Mapping fields
- Import settings
- Sequencing
- Handling errors
- Improve import speeds
- Import data again
Data Management Workspace
The Data Management Workspace is where you will find everything to do with importing and exporting data via DIXF.
Go to Workspaces > Data Management
To access the Data Management Workspace, you will need to be assigned one of the following security roles:
- System Administrator
- Data Management Administrator
- Data Management Migration User
- Data Management Operations User
On the left, you will see tiles where you can create data projects and configure settings. In the middle, you will find all your data projects and on the right you will be able to view the history of your data jobs.
Data entities in D365FO
Think of a data entity like a layer that sits on top of multiple tables in a database. An entity collects data from all of these tables and presents them to you in a single view. For example, the data relating to a customer is spread across many tables in the D365FO database. The customer entity sits on top of all of these tables giving you the ability to easily import and export customer data in just a few clicks.
Pro Tip: Data entities not tables
In D365FO, you can only import and export data using data entities. You cannot import and export data directly to tables like you can in previous versions of the software (AX2009 and AX2012).
You can find a list of all the available data entities by going to Workspaces > Data Management > Data Entities
Here you will find a list of all the entities you can use in the Data Management Workspace
If you cannot see any entities here, then you need to refresh the entity list. To do this, go to Workspaces > Data Management > Framework Parameters
Select the Entity Settings tab and click Refresh Entity List.
Import data into D365FO
1. Pick your company
Firstly, make sure you are in the right company (or “legal entity”). The Data Management Workspace is company-specific, so you can only import and export data from the legal entity you are in.
2. Create a import data project
Next, it’s time to create a import data project. A data project will contain all the entities you want to export. Go to Workspaces > Data Management. Then click on the Import tile.
Make sure you are in the Enhanced View.
Give your import data project a name and description (optional).
3. Upload a file
A data project can be used to import multiple data entities at the same time.
To upload a file, click Add File. Select the entity you want to import and then choose what format the file is in (e.g. Excel). Make sure the Skip Staging flag is set to No. Then upload your file.
Uploading Excels files with multiple Sheets
If you have an Excel file with multiple Sheets, then D365FO will ask you which Sheet you would like to upload.
The entities you have added will appear in the Selected Entities FastTab.
4. Mapping
The alert icon in the View Map column tells you that some of the fields in your uploaded file are not properly mapped to the staging entity fields.
What is a staging entity?
When you import data into D365FO via DIXF, it first goes through a staging entity and then into the respective target entity. The staging entity captures any errors, allowing us to resolve them, before we push them to the “target” (i.e. the D365FO database). This helps keep the database clean and tidy.
To map the fields, click the alert icon in the View Map column.
D365FO provides two screens to map your fields to the staging entity. Using these two screens you can map the column headers of your uploaded file to the staging entity fields.
The first screen is the Mapping Visualisation tab. Here you can map the fields by dragging and dropping lines.
The second screen is the Mapping Details tab. Here you can map your uploaded file to the staging entity by using the drop down fields.
Once all your fields are correctly mapped, click Save.
If all the fields in your uploaded file are mapped, then the alert icon will be replaced with a black file icon, in the View Map column on the Select Entities FastTab.
Pro Tip: Automatically map files
If the headers in your file have exactly the same name as the staging fields, then D365FO will automatically do the mapping for you.
5. Sequencing
Next, let’s talk about sequencing. If you have multiple entities in a single import data project, you will to sequence them. In other words, you will need to decide what order the entities are imported.
For example, unless a customer exists, you cannot import a customer’s contacts. Therefore, if you had an import data project that included both customers and contacts, you would have to import customers first and then contacts.
What is sequencing?
If you have multiple entities in a single import data project, you will need to decide what sequence (or order) the entities are imported.
Entities are imported in sequence. The order is controlled by the values in the Execution unit, then the Level in execution unit, and finally the Sequence.
If entities have the same Execution unit, Level in execution unit, and Sequence then they will be imported at the same time.
In the image above, the first entities to be imported have a Level of 30 and a Sequence of 20. Once these are imported, D365FO will import the next entity in the sequencing (the entity which has a Level of 30 and a Sequence of 22). The last entity to be exported will be the Customer details V2 entity which has a Level of 500 and a Sequence of 10.
Auto-sequencing
D365FO can sequence entities for you, this is known as auto-sequencing. To auto-sequence your import data project, click Standard View.
Then click Entity Sequence.
In the bottom right hand corner of the screen, click Auto-Sequence.
Once D365FO has worked its magic, click OK and return to the Enhanced View.
Your entities should now be sequenced correctly and ready to be imported.
6. Import settings
On the Selected Entities FastTab, scroll to the right and you will see three columns:
- Fail Batch on error for execution unit
- Fail batch on error for level
- Skip staging
Fail batch on error for execution unit stops the import of the other entities if there is an error in the Execution Unit.
Fail batch on error for level stops the import of the other entities if there is an error in the Execution Level.
Skip staging imports the data directly into the target entity, missing out the staging entity. We do not recommend this, it’s not best practise.
If you cannot see these three columns, then right click on the Selected Entities grid and click Personalize: EntitiesGrid.
Next, click Add a Field.
Select the fields and then press Insert.
7. Import now
To import the entities, go to Import Options in the ribbon and click Import Now.
8. Handling errors in staging
Once the import is complete, D365FO will redirect you to the Execution Summary page. This page shows you the status and stats of your import, including: how many records have been created, updated, failed to import, and how many are held in staging.
To view the errors, click View Staging Data.
To see all the errors, type the word Error in the filter box. Click on the row and you will see the error message for that record at the bottom of the screen.
Click Edit and then amend the data in the staging table.
You can either amend the data in the staging table. Or, instead, you can add reference data manually in D365FO before you re-import the records that have a status of error. For example, in the image above, the Customer Group 400 does not exist in D365FO – that’s why we got an error. So instead of changing the customer group of this record, I could go to Modules > Accounts Receivable > Setup > Customer Groups and add 400 as a customer group.
Once you have resolved all the errors, click Copy Data to Target.
Next, select Criteria from the Run For drop-down field and set the Rows with Previous Errors flag to Yes. Then click OK.
Then click Run.
If you have resolved all the errors, then the status of every record in Staging should be Completed.
Click Refresh on the Execution Summary screen, and you will see that the entity has a status of Succeeded.
Do this for all the entities in your project until every status is Succeeded.
You have now successfully imported your data. Well done!
Improving import speeds
It can sometimes be a slow process to import large amounts of data. However, there is a way to speed things up.
When you click Import Now you run the import sequentially. By clicking Import in Batch, you break the import down into a series of batch jobs that can run parallel to one another – meaning your data gets imported faster.
To set this up, go to Workspaces > Data Management > Framework Parameters.
Click on the Entity Settings tab and then click Configure Entity Execution Parameters.
Search for a entity, type a number in the Import Threshold Record Count column and the Import Task Count.
In the image above, the Import Threshold Record Count is 200 and the Import Task Count is 100 for the Products V2 entity.
In this example, if you were to click Import in Batch as opposed to Import Now, you were importing records using the Products V2 entity and your uploaded file contained more than 200 records; D365FO would create 100 import batch jobs and run them in parallel – massively improving the speed at which the records are imported.
When you use Import in Batch you can track the status of the import batch jobs by going to Common > Inquiries > Batch Jobs > My Batch Jobs.
Import data again
You can easily import data again by rerunning your data project, go to Workspaces > Data Management. Select your data project and then click Load Project.
Then go to Import Options in the ribbon and click Import Now or Import in Batch.