MB-500: Microsoft Dynamics 365: Finance and Operations Apps Developer

Change Record

Version	Date	Change
1.1	19 Dec 2023	Initial release
1.0	29 Sep 2023	Tested against 10.0.37

Objective

In this lab, we will export records from a table to an external database. This external database can be used for reporting purposes or for any other reasons. In this lab, we already have an on-premises SQL Server instance. Hence, we will create a database named BYOD in the SQL Server instance. Data from finance and operations apps will be pushed to the external database BYOD.

Exercise 1: Configure BYOD

Task 1: Create a new database

Note: In this task, we will create the new database BYOD in the on-premises SQL Server instance. We will also configure the password of the default SQL Server login ‘sa’. We will need this login and password later in the exercise.

Open Microsoft SQL Server Management Studio 18 in your Virtual Machine (VM). To open it, you can select the Start icon in the bottom left corner and type “ssms”.
A Connect to Server prompt will appear asking the credential to connect to the SQL Server instance.
Select Windows Authentication in the Authentication field and select the Connect button.
Under Object Explorer, right click the Databases node and select New Database.
Type BYOD in the Database name field and select the OK button at the bottom.
A new database BYOD will be created.
Expand the Security node and again expand the Logins node.
Right click on the sa login and select the Properties option.
In the Password and Confirm password fields, type [password].
1. The password is the same password you used to get into the VM, with the user localadmin, or you may ask your instructor. No brackets.
Uncheck the Enforce password policy field and select the OK button at the bottom.
To check if the new password is working, you can close SQL Server Management Studio and reopen it.
In the Connect to Server prompt, select SQL Server Authentication in the Authentication field. Type sa and [password previously discussed] in the Login and Password field and select the Connect button.
You should be able to get inside the SQL Server with this credential.

Task 2: Configure the Data entity to be exported

Note: In this task, we will enable the change tracking property of the data entity Customers V3. This will help to configure the incremental data export from the CustTable to the BYOD database.

Navigate to the finance and operations apps home page and select the Data management workspace.
Select the Framework parameters tile under the Import/Export fastTab in the Data management workspace.
In the Entity settings tab page, select Refresh entity list. You will see a blue bar stating that The Refresh entity list job is added to the batch queue. Note: It may take a few minutes to refresh all data entities
In the Data management workspace, select the Data entities tile under the Import/Export fastTab.
In the Quick Filter box type Customers V3.
Select the Customers V3 entity that appears in the grid.
Select the Enable primary table button under the Change tracking menu in the action pane.
The value of the Change tracking field is changed to Primary table of the record Customers V3 entity.

Task 3: Create Azure SQL DB data source

Note: In this task, we will create a target data source of type Azure SQL DB. In Azure hosted instances, we create the external database in Azure SQL. The connection string of the Azure hosted SQL database should be provided while creating the target data source. In this lab, we will provide the connection string of the on-premises SQL database.

Select the Configure entity export to database tile under the Import/Export fastTab in the Data management workspace.
Create a new record by selecting the New button in the action pane.
Type BYOD in the Source name field.
Type BYOD in the Description field.
In the Connection string field type Data Source=[DB server name],1433; Initial Catalog=BYOD; Integrated Security=False; User ID=sa; Password=[password previously discussed]
1. The DB server name is the root node of Object Explorer in the SQL Server Management Studio.
2. Remove all brackets.
3. You may Validate if you like.
Save the record by selecting the Save button in the action pane.
Select the Publish button in the action pane.
Select the entity Customers V3 and select the Publish button again in the action pane.
In a short time the entity Customers V3 will be published. You can check the Show published only check box and find the value of the Published column is Yes for the record Customer V3 entity.

Testing

Select the Export tile under the Import/Export fastTab in the Data management workspace.
Type CustExportBYOD in the Group name field.
Select the Add entity button in the Selected entities fastTab.
Select Customers V3 in the Entity name field.
Select BYOD in the Target data format field.
Select Incremental push only in the Default refresh type field.
Select the Add button.
Once you get the message “Customer V3 entity mapping has completed successfully”, select the Close button.
In the action pane, select the Save button.
Select the Export now button, under Export options in the action pane.
Execution summary page will appear with a “Checked” image in the Execution summary::Export fastTab.
Navigate back to the SQL Server Management Studio and expand the BYOD database under the Databases node. You will find a new table CustCustomerV3Staging will be created under the Tables node. Right click on dbo.CustCustomerV3Staging and select the option Select Top 1000 Rows. Note: This table is the replica of the staging table which is created when the Customer V3 data entity was created.
You will find all the customer records are exported to this table in the BYOD database.
Navigate to Module > Accounts receivable > Customers > All customers.
Enter a new Customer by entering data into the following fields and save the record
1. Account: Demo01
2. Name: Demo Customer
3. Customer group: 10
Navigate back to the Data management workspace and select the project CustExportBYOD in the All projects tab page under Data projects fastTab.
Select Load project in the toolbar of the Data project grid.
Select the Export now button again, under Export options in the action pane.
Execution summary page will appear again with a “Checked” image in the Execution summary::Export fastTab.
Navigate back to SQL Server Management Studio and expand the BYOD database under the Databases node. Right click on dbo.CustCustomerV3Staging under the Tables node and select the option Select Top 1000 Rows.
You will find the newly created customer record is also exported to this table in the BYOD database.

Note: You can create a batch process for this export operation, which will pick up the new or changed records on regular basis and export them to the BYOD database, without any human interaction.