Data management tools & concepts in D365 F&O and LCS

Data management tools & concepts in D365 F&O and LCS 

 

This blog post is part one of the three-part data management concepts and tools blog series, where I will be discussing the data management concepts and tools within D365 F&O, LCS, and discuss supported data integration with D365 F&O.

Data Management Tools

In D365 F&O, the implementation team can use various tools and frameworks for data management. The following section and following posts explains the different tools and frameworks available for data management in D365 F&O. Let's start with the primary and most useful tool to handle various data management scenarios – data management framework in D365 F&O.

Data Management Framework in D365 F&O

The data management framework in the D365 F&O Enterprise edition is based on the Data Import/Export Framework (DIXF). This tool, in conjunction with other tools available in LCS, can help partners, your customers quickly set up an initial environment with new data, manage the configuration throughout the project lifecycle, plan and execute the data migration with validation checklist and help move the data from one to another environment.

1.  Data management concepts

Objectives

• Understand the use of data management workspace.

Importing Data

• From source, e.g., Excel, etc. for import in D365.
• From another D365 legal entity and copy into another legal entity [cross company data sharing].
• From Excel etc. for ongoing import of master and transactional data.
• From another system/database for ongoing import of master and transactional data into D365.

Exporting Data

• Export to a target source, excel, etc. to create a template for initial D365 data import.
• To another system/database for ongoing transactional or master file data migration.

Based on the assigned security role, data management workspace can be accessed from your dashboard. OOB, the System administrator role has access to the data management workspace.

Note: Data Management workspace can also be accessed from within System administration module [System administration/workspaces/data management workspace]

 
What are the various tiles used for: 

Tile	How used
Import	Create/modify import data projects
Export	Create/modify export data projects
Templates	Create / modify templates
Configure Data Source	File types – indicates delimited, fixed, column and record delimiters, etc.  Azure SQL db for BYOD (bring your own database)
Configure Entity export to database	Set up remote data sources (specify connection string) to export data to
Data Entities	View / create / modify data entities
Framework Parameters	Import/Export framework parameters setup. e.g. Create local directory with admin permissions and define the path to the directory. Refresh the entity list when a custom entity is created in the environment.
Staging Cleanup	Delete data from staging tables
Set up roles for data projects	Limit who can run a data project (processing group)
Copy into legal entity	Copy configuration date from one legal entity to other legal entity
Dual-task automation	Lets you repeat many types of data tasks and validate the outcome of each job. E.g., You can automate the creation and configuration of data projects.
Dual Write	Platform version : Update 33 (App version 10.0.9) Dual-write is an out-of-box infrastructure that provides near-real-time interaction between model-driven apps in Microsoft Dynamics 365 and Finance and Operations apps

Data management workspace has several functions other than data import/ export; you can export data entities to external Azure SQL database, get a list of data entities, set up roles, and facilitate other useful capabilities and views to manage the data flow quickly.

2. Setup Considerations

Setup:

• Framework Parameters
• Source Data Formats
• Roles (optional)
• Remote Data Sources (optional)
• Data Entities (if not standard)

 2.1. Framework Parameters

The following diagram shows the architecture of the Data Import/Export framework

System administration>Data management workspace>Framework parameters tile

Notes:

• Technical people will typically define this screen as part of the installation
• At present Standard view or Enhanced view can be used – by the user. The Standard view is was deprecated in early 2018.

Link to the user guide to setup data import export framework parameters is given as below:

2.2. Source Data Formats

System administration>Data management workspace>Configure data source tile 

Notes:

• Data sources are just file formats
• This screen may initially be blank

Type	Description
Operations	Copy configuration data between legal entities
File	A file is a single entity, eg. CustomersV3
Package	A package is a zip file containing multiple entities, eg. Customers, Addresses, The zipped file contains an XML manifest that indicates what is inside and separate data for each entity The entities are processed in the order specified.
Azure SQL DB	Custom data source that need to be setup as first step for BYOD (bring your own database) to access data outside D365 F&O by connecting to Azure SQL database.

 2.3. Setup Roles For Data Projects

This form allows us to limit who can run/view the results of specific data projects.

Setting up roles will allow financial people to run those data projects linked to ledger journals and supply chain to run those related to products, purchasing, sales. If permissions are not specified, anyone with access to the data projects form can run/view the results of any data project. So, once HR import everyone's salary, it would be visible to anyone with access to the job history form!

System administration> Workspaces> Data management> Setup of roles for processing group tile 

3. Data Entities

What is a data entity?

Data entities provide a conceptual abstraction and encapsulation of the underlying table schema that represents the data concepts and functionalities.

A customer and its addresses sit in multiple tables in D365.The Customer normalizes this into a more comfortable to work with structure.

Example Customer Entity: 

OOB, there are standard data entities that come in the D365 system. Custom entities can be created in visual studio.

Note: Refresh the data entity list to see the custom entities via Framework Parameters.

 A data entity is somewhat like a view.

• A data entity has tables
  • Tables are joined by relations
• Each table has fields
  • Simple and computed fields
• Imported data goes first to a staging table and then to the target entity

 The following lists what other functionalities are supported by the data entities:

• Look at child entities (if this is a composite entity – will look at these later)
• Review/modify mappings from the staging table fields to the target entity fields
• Analysis/modify target fields
• Entity Structure
• Change Tracking – exports will only pick up changed rows
• Publish changes

4. Stages

When you export data, the data goes straight from D365 (source) to say Excel or any other target database.
For imports, the data can go from source (say Excel) to staging to target in D365 F&O.

For simple imports with no errors – the staging step is automatically skipped.

5. Mapping

When data is imported, the values in the source file are mapped to the available fields within the entities in D365.

If the file has column headings, and these headings are the same as the field names in D365, the mapping happens automatically.

For this reason, it is useful to first export data from a D365 table to use as a starting point for file import – as the correct column names are used

If the automatic mapping fails, it can be done manually.

6. Composite Entities

A composite entity consists of multiple entities linked together and expected to be imported or exported together. A good example is a journal that has a header and lines. There is a header entity, a line entity, and a composite entity.
 

 
7. Categories of entities

Entities are categorized based on the functions and the type of data that they serve. The following are six main categories for data entities:

• Master – Data assets of the business, E.g., Customers, Vendors.
• Transactions – These are operational transaction data of the business and are excluded during a full dataset copy. E.g., Pending invoices.
• Reference – Simple reference data that is required to operate a business process. E.g., Units, Unit conversions.
• Document – Worksheet data that is converted tot transaction data later for import, especially the operational data of a business. E.g., Sales order, purchase orders, open balances, and journals.
• Parameters – Functional or behaviour parameters (verbs). E.g., Global address parameters

 Entity category overview form lists all the available entities within the selected categories.

 

Reference Links:
https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/data-import-export-framework-user-guide-dixf-dmf
https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/data-entities
https://docs.microsoft.com/en-us/learn/modules/work-data-management-finance-operations/