Data Migration 102: Data Entities in Dynamics 365 ERP technology
Before reading this blog, I recommend reading my previous post: Data Migration 101: Working in the Cloud with Dynamics 365 ERP technology.
D365SCM and D365F requires the use of the Data Entities. You can find abundant resources online explaining what these entities are and how they work, but every end-user has always told me the same thing. While the resources are helpful, being guided through an example run in invaluable and teaches you more than reading the manual. When driving a car, do we learn to drive by reading the manual or having someone sit in the passenger seat to teach us the fundamentals while new instincts are formed? Here, I will cover fundamentals from my personal experience which I think will help IT Staff hit the ground running.
Found in the DMF, Data Entities allow an end-user to import and export data from the database.
When starting out, the initial efforts with data migration will be heavily focused on imports, which includes conversion and validation efforts. That’s the import process, but data entities also control data exports in the cloud. In SQL, we have the ability to do a “select * from table” and get all records and data from a table. Think of a data entity export as that, but within the DMF framework.
To perform any import or export in DMF, a “project” must be created, and here is where we will cover the two basic approaches:
- Import Projects
- Export Projects
End users will first experience imports during testing efforts such as data migration testing itself, integration testing, and UAT. When creating a project in the DMF, we specify that the project is an import, which will immediately give us a project waiting for at least one entity to be specified. Selecting the entity and adding it to the project is straight forward, but now comes the million-dollar question. How does one provide data to the entity?
Out of the box, Microsoft offers a “template file” for each entity. This template file is an empty excel spreadsheet with column headers. Certain columns are mandatory and others are optional. For example, if we were working with the Customers Entity, CustomerID is one of the mandatory columns you will find in this spreadsheet, and indeed it is there.
One thing that is missing, however, is guidance on how the data shall be structured for each column. For instance, if it’s a string, how many characters can the field hold? If it’s an enumerated list, what are the options?
To give a simple answer on these questions, AlfaPeople has developed an add-in which gives guidance on how each column is to be filled.
Guidance for each field includes critical points such as:
- Mandatory Field: Fields which are not mandatory can be left blank.
- Field Type: String, Number, Boolean, Enumerated List, Etc.
- Field Length: Number of Characters allowed if it’s a string or number.
- Enumerated Options: If it’s an enumerated list, what are the options?
We save you the effort of figuring out the specifics of each column header, which is a huge time saver during the initial loads.
Next, the import process proceeds as such:
- The Project is executed
- Data is Validated to ensure it’s a match to the entity prior to loading to the entity staging table
- Data is Loaded onto the Entity staging table
- Data is validated on the entity staging table ensuring what’s to be loaded into production is a valid entry
- Data is loaded to the database “Target Table” which is the SQL table the entity sends the data to
Export projects are extremely simple compared to imports.
A project is created and identified as an export. Once that’s done, we add the entity to the project, and then immediately run the project. This will result in the desired export of the entity and thus the source table connected to the entity.
Like with imports, we have the ability to specify what kind of file we want to come out. In my experience, this is almost always an Excel spreadsheet, but other options such as XML and CSV are available.
Stay tuned for the next post in this Data Migration series, System Modifications!