In a production environment for Dynamics 365 for Finance & Operations, the primary database is locked down so that you cannot query or maintain the database directly.

But there are a lot of times that we would like to have access to the data so that we can create integrations with other systems, to publish data out for others view or even to report off through other reporting tools.

So the big question is how do you do this?

The answer is through the BYODB (Bring Your Own Database) feature within Dynamics 365 that allows us to attach an external SQL database to our instance and then publish any of the data entities that are available within the Data Management framework out to the database.

In addition to doing full exports of the data, we can also schedule incremental updates as well that take advantage of the change tracking feature to push only the data that has changed within the entity rather than all of the data.

Although this may seem like a daunting effort, it’s not really that hard, and in this walkthrough we will show how to get everything wired up.

Topics Covered

• Creating an Azure SQL Database
  
• Configuring an Entity Export to Database
  
• Publishing Entities to the BYODB Datasource
  
• Creating an Entity Export Project
  
• Enabling Change Tracking on Data Entities
  
• Creating an Incremental Data Export Project
  

Creating an Azure SQL Database

The first step in the process is to create a new Azure SQL Server Database that we will use as the BYODB for Dynamics.

Spoiler Alert: This will cost you some Azure $ to set up.

How to do it…

We will start off by opening up our Azure Portal by going to portal.azure.com.

Here we also created a new workspace that we can use to track all of the resources that we create for this project.

Now we will want to create a new SQL Server resource that we will house our BYODB in.

To do this, just click on the Create a resource button on the left hand side of the Azure portal.

To find the SQL server resource template, just type in sql into the filter box. Then select the SQL server (logical server) resource type.

When the details for the SQL server (logical server) are displayed, just click on the Create button to create the resource.

This will take us to the configuration pane for the SQL server that we are creating.

We will now need to give our SQL Server a Server name.

For this database we will set the Server name to mufifebyodb.

Next we will want to set the Server admin login username that we will be using to authenticate with.

For this example we set the Server admin login to the same as the Server name to make it easier to remember.

And then we will want to specify the Password that we will be using to authenticate against the database with.

Next we will want to either create a new Resource group that we will put the SQL Server resource into, or use an exisiting Resource group.

To make it easier to track down all the resources used for this project we will create a new Resource group and set it to mufifebyodb.

And finally we will want to add this resource to our workspace so we checked the Pin to dashboard option.

After we have done that we can just click on the Create button to get Auzre to configure the resource for us.

That will return us to our workspace and we will see that the resource is queued for creation.

After a couple of seconds, we will have a new SQL server resource and we will be taken to the configuration page.

Now we will want to add a new SQL Database to the server.

To do this, just click on the + New database link in the menu bar for the SQL server.

This will open up the SQL database configuration form where we will be able to specify the details for our new BYODB database.

Start off by giving the new SQL database a Database name.

For this example we will keep everything consistent and give our SQL database a Database name of mufifebyodb.

Next we will want to choose the type of database that we will want to create. If we click on the Pricing for field we will see all of the different SQL options and also how much it will cost us a month to run the SQL server database.

By default the Standard database is selected, which (at the time of writing this) is going to cost about $15 a month.

If you are feeling richer then you could select a Premium database, but for this example it’s a little bit too expensive.

So we will select the Standard database template and click on the Apply button.

After we have done that we can check the Pin to dashboard option and then click on the OK button.

This will kick off the process to create the Azure SQL database.

After a few seconds we will have a newly minted SQL database that we can start using.

Before we move onto the next step we will want to find the Connection string for the database, and to do that we will want to click on the Show database connection string link.

This will open up a page with all of the connection string examples that we will need later on in the process.

Review

Congratulations. We now have a SQL server database that we can use to export out our data entities to.

Configuring an Entity Export to Database

Now that we have created our BYODB database that is hosted outside of the main production environment we can link it with Dynamics 365.

How to do it…

To do this we will want to open up the Data management workspace by clicking on the Data management tile on the home page of Dynamics 365.

This will open up the Data management workspace.

Now we will want to click on the Configure Entity export to database tile within the Import/Export panel.

This will open up the Entity store maintenance form. Here we can create new and also maintain existing BYODB data sources.

We will want to create a new connection, so we will want to click on the + New button in the menu bar.

This will open up the New Entity Store form where we will want to define our connection details.

We will start off by giving the Entity store a Source name.

For this example we will set it to MUFIFEBYODB.

Next we will add a Description for the BYODB.

For this example we set the Description to MuFife BYODB.

Now we will return back to our Azure portal and copy the connection string for the database that we just created.

And then we will paste it into the Connection string field.

By default, the connection string doesn’t include the actual username and password for the SQL database, so we will want to update the {your username} and {your password} placeholders in the connection string.

After we have done that we can click on the Validate button to validate that the connection is correct.

If everything is good then the test will complete without any issues.

Since this is not a Premium database
then we will also want to uncheck the Create clustered column store indexes.

After we have done that we can click on the Save button.

When we return back to the Entity store page we will see that we now have a connection to the external SQL database defined.

Review

How easy was that. Now we have a connection between Dynamics 365 and an external database that we can start publishing entity data to.

Publishing Entities to the BYODB Datasource

The next step is to allow the data entities that we want to export to the BYODB to be published to the new BYODB entity store.

How to do it…

To do this we will want to find the entities within the data management workspace. To do this we click on the Data entities tile.

This will open up a list of all of the entities that are available to be published.

To whittle down the options we can filter out the list just to the usual suspects. Here we set the filter to customers so that we see the entities that relate to the customer record.

Now we will want to select the entity that we want to publish to the entity store.

In this example we will want to select the Customers V3 entity, and then we will want to click on the Publish button in the menu bar.

This will take us over to a list of all the Entity Stores that we have configured. Here we just select he BYODB that we just configured and click on the Publish button.

This will kick off a job to publish the entity to the BYODB.

When we return back we will see that the entity has been published.

If we want to see what we have just done, we can return to our Azure SQL database and click on the Query editor link.

This will open up the SQL Data Explorer form. To start seeing the data in the database we will want to login by clicking on the Login button.

This will open up a Login page with most of the data defaulted in.

All we need to do here is type in the Password that we have set up for the SQL Admin and click on the OK button.

After logging in we will be able to see the database within the SQL explorer.

If we expand the Tables folder we will be able to see the new entity has been added to the database.

Review

Now we are cooking with gas. We have linked our entity to the BYODB and we have a table that we can start accessing. But we still don’t have any data there.

Creating an Entity Export Project

To populate the table that we have within the BYODB we will create an Export project within the Data management tools, and copy all of the data that we have in the entity over there.

How to do it…

To do this we will return back to the Data management workspace and click on the Export tile.

This will open up the Export configuration page where we can build our data export.

We will start off by giving our project a Group name.

For this example we set the Group name to CustomerBYODBExport-Full.

Then we can add a Description to the project.

For this Export we set the Description to Customer BYODB Export (Full).

Now we will want to add our entity that we just published to the BYODB data source into the project.

We do that by clicking on the + Add entity button.

Now we can select the published entity that we want to include in the export.

Next, we will want to choose the Target data format that we will want to export the data in. When we scroll through the list of different options, we will see that our BYODB Entity Store is now showing up on the list.

So now we will select the MUFIFEBYODB as our Target data format.

Now we will want to select the Default refresh type that we will use for this export. We can either just select the incremental updates or we can get all of the data and do a full update.

For this export we want to publish all of the data that is in the table, so we will want to set the Default refresh type to Full push only.

After we have done that we can click on the Add button to add the entity and format mapping to the project.

Once we have done that we will see that behind the form, the entity mapping has been added. At this point we can just close the Add entity dropdown dialog.

Now that we have defined our project and the entity mapping that we want to use to push the data over to the BYODB Entity store, we can start the transfer process.

To do this we just need to click on the Export button in the menu bar.

This will schedule the export job to run in the background, and we can click on the Close button to dismiss the message box.

We will then see the current export job’s details. To see the progress we just click on the Refresh button.

It won’t take too long before the job will have processed and we will see that the data has been pushed from the entity over to the BYODB Entity Store.

In this example 48 customer records have been exported.

To see the data in the Entity Store we can return over to the Azure SQL database and click on the + New Query to start a new SQL query pane.

Inside the query we can enter a simple SQL select statement to see all of the data in the table.

All we need to do is type Select * from dbo.CustCustomerV3Staging and then click on the Run icon.

This will run the query and in the Results panel we will see all o the records that are now in the BYODB database.

If we scroll over just a little we will be able to see the customer details including the name and the customer account.

Review

How easy was that. Once we have created our Export entity in the Data Management workspace then we can add any published entity to the export projects and push the data over there.

Enabling Change Tracking on Data Entities

Now that we have all of our data transferred over to the BYODB Entity Store, we don’t want to stop there. We want to also make sure that any new data that is added to the table within Dynamics 365 is also transferred over.

We could just rerun the full export job that we just created to add new data, but that would be a little inefficient especially for Entities that may have a lot of data in them.

So now we will want to create another export project that just pushed the incremental changes. To do this we will first need to turn on Change tracking on the entity that we want to push so that the system will watch for changed records.

To do this we will open up the Target entities list and find the entity that we just created the full export for. In this case it’s the Customers V3 entity.

Then we will want to open up the CHANGE TRACKING action panel and we will see that there are a few different options for tracking changes on the entity.

For this example we will want to track all of the changes, including the changes to subsidiary tables, so we will click on the Enable entire entity button.

This will enable change tracking on the entity.

Review

That was easy.

Creating an Incremental Data Export Project

Now we that we have enabled change tracking on the Customers V3 entity we can use the change tracking to create an incremental update export job that will run in the background and continuously check for new and changed data to move over to the BYODB Entity Store.

To do this we will return to the Data management workspace and click on the Export tile to create another export project.

This will open up the Export project definition form.

We will want to give our export project a unique Group name.

In this example we will set the Group name to Customer BYODBExport-Incremental.

Next we will give out export project a Description.

Here we wet the Description to Customer BYODB Export (Incremental).

Now we can start adding our entities that we want to export by clicking on the + Add entity button.

We will want to click on the Entity name dropdown list and select the entity that we want to do the incremental update for.

In this example we will select the Customers V3 entity from the list, which is the one that we enabled change tracking on.

And next we will select the BYODB Entity Store from the Target data format dropdown.

For this example we selected the MUFIFEBYODB
Target data format.

This time we will want to select the Incremental push only from the Default refresh type which will tell it to just look for the changed data.

After we have done that we can add it to the export project by clicking on the Add button.

This will add the entity export to the project and we can close the Add entity dropdown form.

Now that we have the project created we will want to schedule this to run periodically rather than having to manually kick the process off.

To do this we will want to click on the Create recurring data job menu item.

This will open up the panel that will allow us to define the batch job details.

We will start off by giving our batch job a unique Name.

For this example we set the Name to be CustomerBYODBExport-Incremental.

Now we want to schedule the recurrence frequency of the job.

We can do that by clicking on the Set processing recurrence link.

This will open up the Define recurrence panel where we are able to set the frequency of the job runs.

For this job, we will want it to refresh each day, so we will select the Day recurrence pattern.

Then we can click on the OK button.

This will return us back to the Create recurring data job panel.

There is one last thing that we need to do here and that is to give the batch job an Application ID from Azure that it will use to authenticate through with.

Here is a quick cheat. Open up the Azure Active Directory applications form and there should be some there that have already been authorized to use Dynamics and we can copy the Client Id from here.

All we need to do is paste in the Application Id into the field.

Finally, we will just want to check the Enable flag for the Application Id that we just added and then click on the OK button.

This will open up a dialog box asking us if we want to enable the recurring job and we will want to click on the Yes button.

After we have one that we are done. The incremental export will run every day and update the BYODB Entity Store with any new or changed data.

Review

How easy was that?

Conclusion

Congratulations. We have now set up a new SQL database in Azure that other applications can access and we have not only published the data from an existing entity in Dynamics 365, but we have also created a refresh job that will go out and add any incremental data to the BYODB Entity store periodically.

This is very

The model-driven apps are a new way to create PowerApps taking advantage of CDS 2.0. If you haven’t created one of them before then here is a quick walkthrough to show you how they work and also how you can create your first Model Driven PowerApp.

How to do it…

Start off by opening up PowerApps and selecting a CDS 2.0 environment.

After you have done that you will notice that if you expand the Choose a design mode tab at the bottom left of the page and you will see that there are two design options. The traditional Canvas mode and also the new Model-driven mode.

All we need to do here is select the Model-driven design mode.

The Home page will then switch over to the Model-driven design mode, and we can create a new app just by clicking on the + New app button.

This will open up the Create a New App page where we can start defining our app details.

We will start off by giving our new app a Name.

For this example, we just set the Name to AccountsApp.

After you have done that, just click on the Done button.

This will take us to the App designer screen where we can start building our app from the models in CDS.

The first thing that we will want to do is update the Site Map which will allow us to include models into the app. We can see that we need to make a change here because of the warning that some configurations are missing.

To do this just click on the Arrow to the right of the Site Map block.

This will open up the Site Map view.

We will now want to give our app a better name by clicking on the New Area tile.

This will open up the We will now want to give our app a better name Area Properties panel.

Here we can change the Title to something a little more applicable to the app.

In this case, we will set the Title to Accounts.

Now we will want to add a Subarea to the app and include the models that we want to have surfaced within the app.

To do this, we just click on the New Subarea tile within the Groups area of the designer.

This will open up the Sub-Area properties panel.

Now we will select the type of area that we want to display by clicking on the Type dropdown list.

We will want to use one of the models within CDS here so we will want to select the Entity option.

Now we will see that some of the fields have been disabled, but there is an option to select the Entity to assign to the sub-area.

Now we can click on the Sub-area dropdown list and select the entity that we want to display in the app.

For this example, we will select the Account entity.

Now that we have done that we can just click on the Save And Close button to finish the setup of the sub-area.

That will take us back to the main designer, and we can click on the Save And Close there as well to save the design.

After we have done that the Validate and Publish options will become enabled and we can finish the process of publishing the app.

To do this, just click on the Publish button.

When we return back to the PowerApps Home page, we will see that there is a new App there for us to use.

If we hover over the app, then we will see that we can run or edit the app.

Right now we want to see it in action so we will click on the Play icon.

That will open up our new app, and we will see that we can access all of the Accounts in this one view.

If we drill into an account, then we will be able to see all of the data is available to us.

Review

How easy was that?

The Cross-Company Sharing feature has been in Dynamics 365 for a couple of versions now, and it allows us to create sets of tables that we can mark to be replicated between two or more legal entities within the system.

This is great because, for a lot of the codes and controls, they will be the same for the most part between all of the different legal entities. There are only so many ways that you can set up discount codes.

I thought that I would take it for a test drive today and created a new blank legal entity and thought that I would add some additional sharing policies to see how this works.

How to do it…

For this example, we have created a new Legal Entity within Dynamics 365 (USPS) that we want to configure. The problem is that a lot of the codes are not populated – for example, the Cash discounts.

I could set all of the discounts up the hard way by copying them by hand from another Legal Entity, or I could use the Cross Company Data Sharing to do it all for me. Because all of the discount codes that I want are sitting in the USMF legal entity already.

To do this, all we need to do is to open up the Cross Company Data Sharing maintenance form. Here we can see that I have already imported a number of new templates into the system, but the Cash discounts are missing.

All I need to do is click on the + New button to create a new record.

Then we will want to give our sharing template a Name. Here we set it to Cash discount.

When we click on the Save button, we will see that the + Add link becomes enabled.

When we click on the + Add link, a search box will appear that will allow us to find the tables that we want to include in this template.

If we search through the Tables, we will be able to find the different tables to share between the organizations. In this case, we want to find the CASHDISC table and select it.

Then we can add it to the sharing project by clicking on the Add table button.

That will add the Cash discount entity to the sharing project.

If we expand the Cash discounts entity, we can see all of the fields that we can sync. We don’t have to share all of the data if we don’t want to. But for now, we will keep all of the fields.

Now we will want to tell the system what companies we will share the data among. To do that we will want to click on the + Add button in the companies panel to create a new record.

We will start off by selecting the USMF company from the company dropdown list.

And then we will want to add another company. So we will click on the + Add button again to create a new company record.

Now we will choose the new company that we created that is blank (USPS) from the dropdown list.

After we have done that our cross-company sharing project is done. All we need to do now is click on the Enable button in the menu bar.

This will open up a dialog that will ask us if we want to copy all of the data between the companies. We want to so we will just click on the Yes button.

That will kick off the initial process to synchronize the data between the two companies.

And after a few seconds, we will return back to the Cross-company data sharing configuration page.

To see what this has done, we just open up the Cash discounts maintenance form within the USPS company that we wanted to populate and click on the refresh button.

Now we will see that all of the Cash discount codes have been copied from the USMF company over to the USPS company.

Summary

If we set up all of the different codes and controls that we want to populate in our new companies, then almost all of the tedious setup will be done for us through the cross-company sharing.

How cool is that.

I am reviving an old project that started a while ago and has started up a new project blog to track the progress. Being a lifelong fan of Dungeons & Dragons, with the unfortunate problem that I cannot find anyone to play with I have decided to create a test implementation Dynamics AX in the AD&D format just to see how it would work and if I can find some creative ways to use Dynamics AX and chose to implement the Waterdeep Trading Company as an example where I can track their many legal (and not so legal) entities within Faerûn.

In this project, we will walk through a simpler setup of Dynamics 365 for Finance and Operations, using the sample demo database that is delivered with the demo systems so that you can quickly create your own Waterdeep Trading Company legal entity and even tweak it so that it is more in the spirit of adventuring in the Dungeons & Dragons theme.

All of the content for this will be available through the Dynamics Companions website as Premium Member Content only and will include the walkthrough and the PowerPoint companions that you will be able to download in their original formats.

Here is a snippet of the first step in the guides.

Read More

In this example we will show a number of different ways that we can manage integration using standard tools. We will start off with a generic SQL database, use LogicApps to send changes to a Service Bus, then use Microsoft Flow to send the data first to the Common Data Service and then to Dynamics 365 for Sales, and finally use the standard integration to update Dynamics 365 for Finance & Operations.

SQL to Service Bus via Logic Apps

How it works…

We will start off with a simple database that is deployed in Azure to simulate a legacy system. In this case we deployed an Azure SQL database and loaded it with the Northwind sample data.

We then created a Service Bus within Azure that we will use to send all of our update messages through.

Using Azure LogicApps we can create an integration monitor that checks the Customers table within the Azure SQL database for new records and then sends a message to the Azure Service Bus when changes are detected.

Whenever a change is made then a message is triggered and a message is sent to the Service Bus.

Service Bus to Common Data Service via Microsoft Flow

How it works…

Now that the message has been sent to the Message Bus, we can trigger events to collect the changes in the Common Data Service using a simple Flow.

When a message is received in the Azure Service Bus the Flow will create a new record within the Common Data Service Accounts Entity.

Here is the new record that was created because of the flow being triggered.

Common Data Service to Dynamics 365 Sales via Microsoft Flow

How it works…

Now that the record is being created within the Common Data Service we can start disseminating it to the core systems. We will use Microsoft Flow again to send the new Account to Dynamics 365 Sales.

Once the flow is triggered we will see that there is now an Account record within Dynamics 365 for Sales.

Dynamics 365 Sales to Finance & Ops via PowerApps

How it works…

The final step to is to synchronize the data within Sales with the Finance & Operations Customers. To do this we will use the Business Platform Admin Centers Data Integration service which maps the data between the two entities.

The Data Integration project will periodically run and create the new Customer records based off the Accounts within Sales.

Within Finance & Operations the Data Management workspace tracks all of the Data Integration Calls.

We are able to see all of the activity and the inserts into the system.

We can even see all of the data that is staged by the integration service.

Now the Customer record is available within Finance & Operations.

Review

How cool is that?

With Platform Update 14 a new feature was added that allows us to add in PowerApps directly into the forms. In addition, this was demonstrated in this presentation: https://www.youtube.com/watch?v=x3qyA1bH-NY

If you want to create the demo then here is how you do it.

How to do it…

Start off by opening up the PowerApps site and then click on the Apps link on the left hand side menu.

When the Apps page is displayed click on the Import package (preview) button link in the header.

This will open up the Import package page where we can start importing in the zip file. To do this, click on the Upload button.

This will open up the file browser where we will want to navigate to the folder where we have the PackageTracker zip file and then click on the Open button.

After we have done that, the process of importing in the project package will start running.

After the package has been processed we will be able to see all of the package information that will be imported in. Notice here that there is only a PowerApp and no data that will be loaded in.

Now we just need to click on the Import button.

After a minute, the project will be loaded and we will get a notification of the successful import. To see the app in action, just click on the Open app link that is in the Next steps… area of the page.

When the app loads we will be able to see the shell of the PowerApp.

Now we will want to link this app into Dynamics 365 for Finance and Operations. To do this we will need to find the application id that will point to the PowerApp. To do this, look in the URL and copy the app id which is the last part of the URL.

Now we will want to switch over to Dynamics 365 for Finance & Operations and click on the PowerApps icon within the menu bar and select the Insert a PowerApp button.

This will open up the Insert a PowerApp form where we can start defining the app details.

Start off by giving the PowerApp a Name.

Then paste in the app ID that we copied from the PowerApp into the App ID field.

Next we will want to specify the reference field that we will pass through into the PowerApp which will contain the tracking number that we will looking up. For our example we are storing the tracking number in the Customer reference field.

Then we will want to change the Application size to Thin since this is a phone style app which is taller than wider.

After we have done that we can just click on the Insert button.

When we return to the form we will get a message to say that the page needs to be refreshed. So just press F5 and refresh the form.

Finally we will want to add the tracking reference to the sales order header.

Valid tracking numbers for the app are:

1Z48263893 (Shipped)

8F28103A73 (out for delivery)

How it works…

To see the PowerApp in action, all we need to do is click on the PowerApps icon in the heading and then click on the Package Tracker
link.

This will open up the Package Tracker PowerApp and show the status of the shipment.

Review

How cool is that!