BYOD in D365 F&O for BI: Setup, Incremental Export, and Why Rows Go Missing
In my first article in this series I walked through the four roads out of D365 Finance and Operations for BI: OData, the entity store, BYOD, and the Synapse and Fabric links. I promised to come back to BYOD, because it is still the road most existing warehouses on F&O sit on, and because it is the one that fails most quietly. A BYOD feed rarely crashes. It keeps running green every hour while the numbers in the warehouse drift a little further from the ledger. Today I want to explain how BYOD really works under the covers, how I set it up, and why an incremental export can miss rows without ever reporting an error.
WHAT BYOD ACTUALLY IS
Bring your own database is not a replication service. It is the Data management framework, the same engine you use for imports and exports of files, pointed at an Azure SQL Database that you own instead of at a file. Every BYOD table you see in your database is the output of a data entity, exported by a data project, running as a batch job on the F&O batch servers. That one sentence explains almost every behaviour you will meet:
• The unit of export is the data entity, not the table. You get the entity's denormalised shape, with its joins, its computed columns, and its field names, not the raw AxDB tables.
• Exports consume F&O batch capacity. A heavy BYOD schedule competes with posting, master planning, and every other batch job on the same environment.
• Incremental export depends on SQL change tracking on the AxDB tables behind the entity. What change tracking is told to watch is exactly what incremental export can see, and nothing more.
Microsoft's direction for new builds is clearly the Synapse Link and Fabric route, which I will cover next. But BYOD is supported, it works with ordinary SQL tooling, and a well run BYOD feed is perfectly respectable. The trick is running it well.
SETTING IT UP: THE TARGET DATABASE
Start with an Azure SQL Database in the same Azure region as the F&O environment. Cross region exports work, but every row crosses the wire, and a large full push across regions turns a twenty minute job into an hour. Size the database for the write burst, not for the steady state: the busiest moment of a BYOD database is a full push of the general ledger or inventory transactions entities, and an undersized tier throttles the export, which in turn holds an F&O batch thread for longer.
In the Data management workspace, under Configure entity export to database, you register the target with an ADO.NET connection string and validate it. Two options matter at this point. Creating clustered columnstore indexes on the target tables is the right choice for almost every reporting workload, because BYOD tables are wide and your queries read a few columns across many rows. Enabling triggers in the target database lets triggers you create on the BYOD tables fire during the bulk insert; I only switch it on when I have a specific downstream reason, because it slows every export.
Then you publish the entities you need. Publishing creates the table in the target with the entity's columns, named after the entity's staging table. Publishing is also how you pick up schema changes after an update adds fields to an entity, and that has a consequence I will return to in the failure section: republishing drops and recreates the table.
FULL PUSH VERSUS INCREMENTAL PUSH
Each entity in a BYOD export project has a default refresh type. A full push deletes every row in the target table and reloads the whole entity. An incremental push reads the change tracking information on the AxDB tables, finds the rows that changed since the last successful run, and applies inserts, updates, and deletes to the target. The first incremental run is always a full load, because there is no previous version to compare against.
My rule of thumb is simple. Small entities (configuration, chart of accounts, financial dimension values, customer groups, warehouses) are cheaper and safer as a nightly full push: a few thousand rows, no state to go wrong, and the table heals itself every night. Large transactional entities (general journal account entries, inventory transactions, sales and purchase lines) need incremental, because a full push of millions of rows every hour is not realistic. Master data in the middle (customers, vendors, released products) usually goes incremental during the day with a weekly full push as a safety net.
Two settings in the export project are worth checking. Skip staging should be on for BYOD exports, so rows go from the entity straight to the target rather than through the staging table first; it roughly halves the work. And keep each recurring job focused: one project for hourly incremental transactional entities, one for nightly full push reference entities. A single project with sixty entities becomes impossible to reason about when one of them fails.
HOW CHANGE TRACKING REALLY BEHAVES
This is the heart of the article. On the data entity list in Data management you enable change tracking per entity, with three choices, and the choice decides which edits incremental export will ever notice.
Enable primary table tracks only the root data source of the entity. If the entity is built on a single table, that is fine. But most entities you care about for BI are joins. A customer entity reads the customer table, the party, the postal address, the contact information, and more. With primary table tracking, a changed address or a renamed party does not touch the customer table, so the row is never flagged, and the warehouse keeps the old value until the next full push. Nothing fails. The value is just wrong.
Enable entire entity tracks every table the entity reads. This is what I default to for BI feeds, with two caveats. It flags more rows as changed, because an edit to any joined table marks the entity row, so incremental volumes go up. And not every entity supports it; entities built on certain constructs refuse entire entity tracking, and the option simply errors.
Enable custom query lets a developer supply the query that defines which tables are tracked. It is the precise answer when entire entity is refused, at the cost of X++ work that has to be maintained through updates.
Two more behaviours catch people out. First, computed and virtual fields are calculated at export time, so a field derived from a table outside the tracking scope is only refreshed when something inside the scope changes. Second, change tracking keeps history for a retention period. If an incremental job does not run successfully within that window (an environment paused for maintenance, a job stuck in error over a long weekend), the next run cannot compute the changes, and you need a full push to resynchronise.
SIZING THE BATCH SCHEDULE
Ask the business how fresh the data needs to be, and then halve their answer, because most people say hourly when they mean twice a day. For most finance and supply chain reporting, incremental every one to four hours plus a nightly full push of reference data is plenty. Anything that genuinely needs minutes belongs on the Fabric link, not on BYOD.
Then look at what else runs on the batch servers. I avoid scheduling BYOD exports over month end close, the master planning run, and large posting jobs, and I put the BYOD jobs in their own batch group so the system administrator can see and throttle them. Watch the duration of each run for a few weeks. An incremental job that creeps from five minutes to forty is telling you something: usually that entire entity tracking is flagging far more rows than expected, or that the target database tier is too small.
Finally, make your ETL wait for BYOD. The warehouse load should start after the export has finished, not at a fixed clock time that usually comes later. Reading a table halfway through a full push gives you half a table.
WHAT GOES WRONG
These are the patterns I see again and again on BYOD feeds:
• Primary table tracking on a joined entity. Address, name, and dimension changes never arrive. Symptom: master data in the warehouse is "mostly right" and nobody can say why some rows are stale.
• Republishing during the week. An update adds fields, someone republishes, the table is dropped and recreated, and the next incremental run starts from an empty table. Plan republishing like a deployment, followed immediately by a full push.
• Two projects writing the same entity. A full push in one project wipes the rows another project just loaded incrementally. One entity, one owning project.
• Retention gaps. A job in error for longer than the change tracking retention period silently needs a full push. Alert on failed runs, not just on missing data.
• Database refreshes. Copying production into a sandbox copies the data management configuration with it, including the BYOD connection string. Before anyone re enables batch jobs in the sandbox, repoint or remove the BYOD target, or the sandbox will happily write test data into your production reporting database.
• Reading during the push. The warehouse load runs on a timer while a long export is still writing. Totals dip for one refresh and recover the next, which is the most confusing symptom of all.
The cure for all of them is the same habit: reconcile. A simple row count by company, compared between BYOD and the source, catches most drift early. For example, against the BYOD database:
SELECT DATAAREAID, COUNT(*) AS ROWS_IN_BYOD FROM dbo.CUSTCUSTOMERV3STAGING GROUP BY DATAAREAID ORDER BY DATAAREAID;
Compare that with the record count per legal entity in F&O for the same entity, and do the same for the big transactional entities against a period total. For ledger feeds I go further and tie the sum of accounting currency amounts per company and period back to the trial balance; I will write a full article on reconciliation later in the series. A weekly full push of the critical entities, scheduled over the weekend, is cheap insurance that heals whatever incremental missed.
WHERE BYOD STILL FITS
If you have a working BYOD feed and a SQL Server warehouse built on it, there is no emergency. Fix the tracking scope, separate the projects, add reconciliation, and it will serve you well. If you are starting from nothing, I would not build a new platform on BYOD today: the batch load on F&O and the entity shaped output are real costs that the newer route avoids.
Next time I will look at that newer route: Azure Synapse Link for Dataverse and the Fabric link for F&O tables, what replaced Export to Data Lake, how raw tables arrive in Delta format, and what changes in your warehouse design when you stop reading entities and start reading tables.
In this series: previous article The D365 F&O Data Landscape for BI: AxDB, Data Entities, Entity Store, BYOD, and the Lake
In my first article in this series I walked through the four roads out of D365 Finance and Operations for BI: OData, the entity store, BYOD, and the Synapse and Fabric links. I promised to come back to BYOD, because it is still the road most existing warehouses on F&O sit on, and because it is the one that fails most quietly. A BYOD feed rarely crashes. It keeps running green every hour while the numbers in the warehouse drift a little further from the ledger. Today I want to explain how BYOD really works under the covers, how I set it up, and why an incremental export can miss rows without ever reporting an error.
WHAT BYOD ACTUALLY IS
Bring your own database is not a replication service. It is the Data management framework, the same engine you use for imports and exports of files, pointed at an Azure SQL Database that you own instead of at a file. Every BYOD table you see in your database is the output of a data entity, exported by a data project, running as a batch job on the F&O batch servers. That one sentence explains almost every behaviour you will meet:
• The unit of export is the data entity, not the table. You get the entity's denormalised shape, with its joins, its computed columns, and its field names, not the raw AxDB tables.
• Exports consume F&O batch capacity. A heavy BYOD schedule competes with posting, master planning, and every other batch job on the same environment.
• Incremental export depends on SQL change tracking on the AxDB tables behind the entity. What change tracking is told to watch is exactly what incremental export can see, and nothing more.
Microsoft's direction for new builds is clearly the Synapse Link and Fabric route, which I will cover next. But BYOD is supported, it works with ordinary SQL tooling, and a well run BYOD feed is perfectly respectable. The trick is running it well.
SETTING IT UP: THE TARGET DATABASE
Start with an Azure SQL Database in the same Azure region as the F&O environment. Cross region exports work, but every row crosses the wire, and a large full push across regions turns a twenty minute job into an hour. Size the database for the write burst, not for the steady state: the busiest moment of a BYOD database is a full push of the general ledger or inventory transactions entities, and an undersized tier throttles the export, which in turn holds an F&O batch thread for longer.
In the Data management workspace, under Configure entity export to database, you register the target with an ADO.NET connection string and validate it. Two options matter at this point. Creating clustered columnstore indexes on the target tables is the right choice for almost every reporting workload, because BYOD tables are wide and your queries read a few columns across many rows. Enabling triggers in the target database lets triggers you create on the BYOD tables fire during the bulk insert; I only switch it on when I have a specific downstream reason, because it slows every export.
Then you publish the entities you need. Publishing creates the table in the target with the entity's columns, named after the entity's staging table. Publishing is also how you pick up schema changes after an update adds fields to an entity, and that has a consequence I will return to in the failure section: republishing drops and recreates the table.
FULL PUSH VERSUS INCREMENTAL PUSH
Each entity in a BYOD export project has a default refresh type. A full push deletes every row in the target table and reloads the whole entity. An incremental push reads the change tracking information on the AxDB tables, finds the rows that changed since the last successful run, and applies inserts, updates, and deletes to the target. The first incremental run is always a full load, because there is no previous version to compare against.
My rule of thumb is simple. Small entities (configuration, chart of accounts, financial dimension values, customer groups, warehouses) are cheaper and safer as a nightly full push: a few thousand rows, no state to go wrong, and the table heals itself every night. Large transactional entities (general journal account entries, inventory transactions, sales and purchase lines) need incremental, because a full push of millions of rows every hour is not realistic. Master data in the middle (customers, vendors, released products) usually goes incremental during the day with a weekly full push as a safety net.
Two settings in the export project are worth checking. Skip staging should be on for BYOD exports, so rows go from the entity straight to the target rather than through the staging table first; it roughly halves the work. And keep each recurring job focused: one project for hourly incremental transactional entities, one for nightly full push reference entities. A single project with sixty entities becomes impossible to reason about when one of them fails.
HOW CHANGE TRACKING REALLY BEHAVES
This is the heart of the article. On the data entity list in Data management you enable change tracking per entity, with three choices, and the choice decides which edits incremental export will ever notice.
Enable primary table tracks only the root data source of the entity. If the entity is built on a single table, that is fine. But most entities you care about for BI are joins. A customer entity reads the customer table, the party, the postal address, the contact information, and more. With primary table tracking, a changed address or a renamed party does not touch the customer table, so the row is never flagged, and the warehouse keeps the old value until the next full push. Nothing fails. The value is just wrong.
Enable entire entity tracks every table the entity reads. This is what I default to for BI feeds, with two caveats. It flags more rows as changed, because an edit to any joined table marks the entity row, so incremental volumes go up. And not every entity supports it; entities built on certain constructs refuse entire entity tracking, and the option simply errors.
Enable custom query lets a developer supply the query that defines which tables are tracked. It is the precise answer when entire entity is refused, at the cost of X++ work that has to be maintained through updates.
Two more behaviours catch people out. First, computed and virtual fields are calculated at export time, so a field derived from a table outside the tracking scope is only refreshed when something inside the scope changes. Second, change tracking keeps history for a retention period. If an incremental job does not run successfully within that window (an environment paused for maintenance, a job stuck in error over a long weekend), the next run cannot compute the changes, and you need a full push to resynchronise.
SIZING THE BATCH SCHEDULE
Ask the business how fresh the data needs to be, and then halve their answer, because most people say hourly when they mean twice a day. For most finance and supply chain reporting, incremental every one to four hours plus a nightly full push of reference data is plenty. Anything that genuinely needs minutes belongs on the Fabric link, not on BYOD.
Then look at what else runs on the batch servers. I avoid scheduling BYOD exports over month end close, the master planning run, and large posting jobs, and I put the BYOD jobs in their own batch group so the system administrator can see and throttle them. Watch the duration of each run for a few weeks. An incremental job that creeps from five minutes to forty is telling you something: usually that entire entity tracking is flagging far more rows than expected, or that the target database tier is too small.
Finally, make your ETL wait for BYOD. The warehouse load should start after the export has finished, not at a fixed clock time that usually comes later. Reading a table halfway through a full push gives you half a table.
WHAT GOES WRONG
These are the patterns I see again and again on BYOD feeds:
• Primary table tracking on a joined entity. Address, name, and dimension changes never arrive. Symptom: master data in the warehouse is "mostly right" and nobody can say why some rows are stale.
• Republishing during the week. An update adds fields, someone republishes, the table is dropped and recreated, and the next incremental run starts from an empty table. Plan republishing like a deployment, followed immediately by a full push.
• Two projects writing the same entity. A full push in one project wipes the rows another project just loaded incrementally. One entity, one owning project.
• Retention gaps. A job in error for longer than the change tracking retention period silently needs a full push. Alert on failed runs, not just on missing data.
• Database refreshes. Copying production into a sandbox copies the data management configuration with it, including the BYOD connection string. Before anyone re enables batch jobs in the sandbox, repoint or remove the BYOD target, or the sandbox will happily write test data into your production reporting database.
• Reading during the push. The warehouse load runs on a timer while a long export is still writing. Totals dip for one refresh and recover the next, which is the most confusing symptom of all.
The cure for all of them is the same habit: reconcile. A simple row count by company, compared between BYOD and the source, catches most drift early. For example, against the BYOD database:
SELECT DATAAREAID, COUNT(*) AS ROWS_IN_BYOD FROM dbo.CUSTCUSTOMERV3STAGING GROUP BY DATAAREAID ORDER BY DATAAREAID;
Compare that with the record count per legal entity in F&O for the same entity, and do the same for the big transactional entities against a period total. For ledger feeds I go further and tie the sum of accounting currency amounts per company and period back to the trial balance; I will write a full article on reconciliation later in the series. A weekly full push of the critical entities, scheduled over the weekend, is cheap insurance that heals whatever incremental missed.
WHERE BYOD STILL FITS
If you have a working BYOD feed and a SQL Server warehouse built on it, there is no emergency. Fix the tracking scope, separate the projects, add reconciliation, and it will serve you well. If you are starting from nothing, I would not build a new platform on BYOD today: the batch load on F&O and the entity shaped output are real costs that the newer route avoids.
Next time I will look at that newer route: Azure Synapse Link for Dataverse and the Fabric link for F&O tables, what replaced Export to Data Lake, how raw tables arrive in Delta format, and what changes in your warehouse design when you stop reading entities and start reading tables.
In this series: previous article The D365 F&O Data Landscape for BI: AxDB, Data Entities, Entity Store, BYOD, and the Lake
No comments yet. Be the first to comment!
