Bring Your Own Database,
also known as BYOD,
lets you export data from Microsoft Dynamics 365 for
Finance and Operations to
an external Microsoft Azure SQL Database.
There are three main scenarios that you
should consider the BYOD feature
for: exporting data from
Finance and Operations into your own data warehouse;
using analytical tools that require T-SQL access to data;
and exporting data to
an external database to perform
batch integration with other systems.
Before you implement the BYOD feature,
you should be aware of the differences between
the entity store and BYOD.
For more information, see the export entities to
your own Azure SQL Database topic on docs.microsoft.com.
Before you can use the BYOD feature,
you must configure that target Azure SQL Database and
then publish the data entries to
update the target database schema.
You must complete these steps only once.
You can then export data from
Finance and Operations to
the target database on a recurring basis.
The first step is to create
an Azure SQL Database in your own Azure subscription.
In this example, an Azure SQL Database
that's named Demo BYOB has been
created on the server that's named
demobyod.database.windows.net.
If you're using the BYOD feature for
integration with the business intelligence tool,
you should consider creating
an Azure SQL premium database to take
advantage of its Clustered Column Store Indexes,
also known as CCIs.
A premium database and
Clustered Column Store Indexes help improve
the performance of read queries that are
typical and analytical and reporting workloads.
In Microsoft SQL Server Management Studio,
you can see that when the new Demo
BYOD Azure SQL Database is first created,
it has no tables to store Finance and Operations data.
We'll now configure the Azure SQL Database
in Finance and Operations.
In Finance and Operations,
in the data management workspace,
click the "Configure Entity Export" to database tile.
Click "New" to declare the new target database.
Enter a unique source name,
a description, and the connection string.
The expected format for
the connection string is described in
the export entities to
your own Azure SQL Database
topic that we mentioned earlier.
We recommend that you copy
the logical server named from the Azure portal.
Note that the "Create Clustered
Columns Store Indexes" option
optimizes the destination database for
the selected queries by defining
Clustered Column Store Indexes for
entities that are copied from Finance and Operations.
Currently, Clustered Column Store Indexes are
supported only with Azure SQL premium databases.
Therefore, if you didn't create
an Azure SQL premium database in the Azure portal,
you should set this option to "No".
Validate the connection string to make sure that
Finance and Operations can
connect to the target database.
After you've validated the connection, click "Publish".
You must publish the entities before you can
export them on a recurring basis.
Select the data entities to
publish to the target database.
In this example, we've publish
the customer's V2 data entity.
If you're planning to export data incrementally,
make sure that change tracking is
turned on for the relevant data entities.
Three options are available.
Select "Enable Primary Table" to track
all changes that are made to
the primary table of the entity.
When changes are made to the primary table,
the corresponding record is inserted
into or updated in the destination database.
Although data from the whole entity
is written to the destination table,
the system triggers the "Insert" or
"Update" option only when the primary table is modified.
Select "Enable Entire Entity"
to track all changes to the entity,
in other words, changes to
all the tables that make up the entity.
Select "Enable Custom Query"
if you must track changes from only
some of the fields based on
a query that a developer has defined.
You can also select this option
when the entities that will be
exported were built by using a hierarchy of nested views.
Currently, "Change Tracking" can't be
turned on for entities that don't have unique keys.
Therefore, those entities can't be
exploited by using incremental push.
If you want to incrementally export
entities but unique keys aren't defined for them,
you can extend the entities by adding keys.
Click "Publish Again" to update
the schema of the target database.
A published job is now scheduled to
update the schema of the target database.
When the schedule published job is run
the schema and the target databases updated.
Let's see the result by using Management Studio.
Notice that the target database now contains
the cost customer V2 staging table.
You can also see that the cost customer
v2 staging table doesn't yet contain any data.
So far, we've published only the entity schema,
not the actual data.
We'll now export data to the target database.
First, we'll show how to do a full push.
Note that data export jobs are
specific to the legal entity that they are created in.
In this example, only data from
the US RT company will be exported.
In Finance and Operations
and the data management workspace,
click the "Export" tile.
Enter a group name and a description.
Click "Add Entity" to
add the data entities that should be
synchronized with the target database
as part of this export job.
In the "Entity Name" field,
select the entity to export.
In the "Target Data Format" field,
select the source string that you
configured for this database.
In the "Default Refresh Type" field,
select full push only.
If you don't want to run methods on
the data entity as part of the export job,
we recommend that you set
the "Skip Staging" option to a
"Yes" to help improve performance.
In this example, customer's V2
is the data entity that's being added,
and the Demo BYOD is the target data format. Click "Add".
Click "Export" to start the data export immediately.
Because we're exporting interactively,
we can see the progress of the export job.
To refresh the status,
click the "Refresh" button in the upper right corner.
After the export job is finished running,
the execution summary page provides a report of the job.
To verify the exported of data,
we created a Microsoft Power BI report
that shows the location of customers.
This report contains a list of customers on the right,
a map that shows the location of each customer,
and a chart that shows the distribution
of customers by country or region.
In this example, you can see that all the customers for
the US RT legal entity have been synchronized.
We'll now schedule a recurring job to
export data to the target database incrementally.
In Finance and Operations
in the data management workspace,
click the "Export" tile.
Enter a group name and description.
Click "Add Entity" to
add the data entities that should be
synchronized with the target database
as part of this export job.
In the entity name field,
you select the "Entity to Export".
In the target data format field,
select the "Source String" that you
configured for this database.
In the default refresh type field,
select "Incremental Push Only".
Remember to set the skip's
staging option to "Yes" if you don't
want to run methods on the data entity
as part of the export job.
In this example, customer's V2
is the data entity that's being
added and Demo BYOD is
the target data format. Click "Add".
Click "Export Options" and then click "Export in Batch".
Configure how the export job will run in
the background and define the recurrence,
alerts, and batch job parameters. Click "Okay".
We'll now create a customer in Finance and Operations and
verify that it's automatically
synchronized with the target database.
In Finance and Operations,
click "Accounts Receivable" click
"Customers" and then click "All Customers".
Then on the All Customers page, click "New".
Fill in the information for
the new test customer and then click "Save".
In this example, the new customer
is an organization that's
named BYOD New Customer and that's based in Iceland.
After the batch job for
the data export that we
scheduled earlier has finished running,
the new record is available in the target database.
In the Power BI report,
you can see that the new record for
BYOD new customer is now present.
Note that the latest best practices and
limitations that are related to BYOD are
documented in the export entities to
your own Azure SQL Database topic on docs.microsoft.com.
This brings us to the end of this presentation.
We hope you found the information
useful. Thank you for watching.