The D365 F&O Data Landscape for BI: AxDB, Data Entities, Entity Store, BYOD, and the Lake
I have spent most of my career on the other side of the ERP: not configuring Dynamics 365, but getting numbers out of it that finance, operations and leadership can trust. This article opens a series on exactly that. Over the coming posts I will work through Dynamics 365 from the BI, data warehousing and database point of view: how the data is shaped, how to extract it, how to model it, and how to make the figures in a report tie back to what the ERP says. My colleagues here write about how the processes run inside D365; I want to write about what those processes leave behind in the database and how to turn it into reporting.
The first question on every BI workstream for Finance and Operations is the same: where do we read the data from? Teams arriving from on-premises AX are used to pointing SSIS at the production database and getting on with it. In the cloud that road is closed, and the replacements each have a very different character. This article is the map I wish I had been handed the first time: the four ways out of the F&O database, what each one is really for, and how I decide which to use.
THE STARTING POINT: AXDB AND WHY YOU CANNOT TOUCH IT
Underneath every F&O environment is a transactional Azure SQL database, still called AxDB in development and tooling. It is a classic OLTP schema: several thousand tables, heavy normalisation, every row keyed by RecId, company data separated by a DataAreaId column, and a Partition column that is almost always the same value but still sits in every index. Enumerations are stored as integers, financial dimensions are stored as RecId references into DimensionAttributeValueCombination rather than as readable strings, and date and time fields are stored in UTC while pure date fields are not converted at all.
In production you have no direct SQL access to this database, and that is deliberate. Microsoft runs it as a managed service and protects the transactional workload from heavy analytical queries. In a tier 2 sandbox you can request just in time read access for diagnostics, and on a developer box you have the whole database locally, which is invaluable for learning the schema. But neither is a production BI source. I treat the AxDB as something to study, not something to query, and every extraction design starts from that rule.
ROAD ONE: DATA ENTITIES OVER ODATA
Data entities are the public face of the F&O data model. An entity is a denormalised view over one or more tables, with business friendly field names, enum labels, and financial dimensions flattened into display values. They power the data management framework, Excel integration, and the OData endpoint. For a developer building an app, OData against an entity is excellent: authenticated, secured by F&O roles, and always current.
For BI it is the wrong tool, and I still see it used. OData returns rows in pages, is subject to service protection throttling, competes with the users working in the system, and has no efficient notion of "give me what changed since last night". A Power BI dataset that refreshes general ledger entries over OData works in a demo with one month of data and falls over in production with five years. I use OData only for small reference lookups or for an app that needs live values, never to feed a warehouse.
ROAD TWO: AGGREGATE MEASUREMENTS AND THE ENTITY STORE
The entity store is an operational analytics database that sits inside the F&O service. Developers define aggregate measurements in Visual Studio (a star of measures and dimensions built over entities), and a staged refresh job copies that model into a columnstore database. The embedded Power BI reports in the standard workspaces read from it, and you can build your own embedded reports over it as well. Egel covered the user experience side of this well in his article on embedding Power BI inside F&O, so I will stay on the data side.
What the entity store is good at: KPIs that belong next to a transaction, refreshed on a schedule, secured by the same roles as the forms around them, with no infrastructure for you to run. What it is not: an enterprise warehouse. The model is defined in code, so a new measure means a development and deployment cycle. It only contains F&O data, so you cannot blend in CRM, budgets from a planning tool or a legacy system. And refresh frequency is a balance against batch capacity on the F&O side. I use it for in-app operational dashboards and nothing more ambitious.
ROAD THREE: BYOD, BRING YOUR OWN DATABASE
BYOD lets you register an Azure SQL database of your own as a data management destination and export data entities into it, either as a full push or, for entities with change tracking enabled, as an incremental push on a recurring batch schedule. The result is a SQL database you own, with one table per entity, that you can query with T-SQL, index as you like, and feed into SSIS, Data Factory or a SQL Server warehouse with tooling every BI team already knows.
That familiarity is its great strength, and it is why BYOD remains common in established estates. The weaknesses are just as real. The export runs as batch work inside F&O, so heavy exports compete with the business for batch capacity. The shape of your data is fixed by the entity, so if the entity does not expose a field you need, someone has to extend it. Incremental push depends on change tracking behaving as you expect on entities built over several tables, which it does not always do. And BYOD is no longer where Microsoft invests; it still works, but new capability arrives on the lake side. I will go deep on BYOD setup and its traps in the next article, because many teams will be running it for years.
ROAD FOUR: SYNAPSE LINK AND THE FABRIC LINK
The old Export to Data Lake feature, which replicated F&O tables as CSV files into your own storage account, has been retired. Its successor runs through Dataverse: once the F&O environment is linked to a Power Platform environment, you can choose F&O tables in Azure Synapse Link for Dataverse and have them land in your Azure Data Lake Storage in Delta format, or use the Link to Microsoft Fabric so the same tables appear in OneLake through shortcuts, without you managing the storage at all.
This is the road I recommend for any new warehouse build. You get tables rather than entities, so nothing is hidden behind an entity definition. Changes flow continuously without consuming F&O batch capacity. Delta format gives you efficient incremental reads and works natively with Spark, the Fabric lakehouse and warehouse, and SQL endpoints. The price is that you inherit the raw schema from the first section: RecIds, integer enums, UTC timestamps, dimension combinations as references. Every join that an entity would have done for you, you now write yourself in the transformation layer. In my experience that is a good trade, because you control the logic and can see it, but it needs people who are willing to learn the F&O table model properly.
A small example of what that means in practice. An entity hands you a ready made string such as a main account and two dimension values joined by delimiters. From the tables you resolve it yourself, along these lines:
SELECT g.RecId, c.DisplayValue, a.MainAccountId FROM GeneralJournalAccountEntry g JOIN DimensionAttributeValueCombination c ON c.RecId = g.LedgerDimension JOIN MainAccount a ON a.RecId = c.MainAccount
Three joins for one readable column is typical, and it is the reason I build a conformed dimension layer once rather than letting every report author write those joins again.
HOW I CHOOSE
My rule of thumb is short. If the requirement is an operational view that users want inside an F&O workspace, use the entity store. If you are building or rebuilding an analytical platform, start from Synapse Link or the Fabric link and design your staging layer around raw tables. If you already have a stable SQL Server or Azure SQL warehouse fed by BYOD and it meets the business need, keep it running, but stop adding new subject areas to it and plan the move rather than waiting for it to become urgent. And never use OData as a warehouse feed.
A few questions settle most debates quickly:
• How fresh does the data need to be? Hourly or better points to the lake; daily is fine on any road.
• Does the report need data from outside F&O? If yes, the entity store is out.
• Who will maintain the transformations? A team fluent in T-SQL and SSIS will be productive on BYOD on day one; a team comfortable with Spark or Fabric will be faster on the lake.
• Is the F&O environment already linked to Power Platform? If not, that is a prerequisite project for road four, with its own governance conversation.
WHAT GOES WRONG
• Power BI over OData. It refreshes in testing, then times out or gets throttled once volume arrives, and users blame Power BI rather than the design.
• Forgetting DataAreaId. A join between two tables without the company column quietly multiplies rows across legal entities, and the totals look plausible enough to escape notice.
• Time zones. Transaction date and time fields are UTC, so a posting made late in the evening in a western time zone lands on the next day unless you convert it. Pure date fields such as the accounting date are not converted, which is why I always report on accounting date for finance.
• Enum integers in reports. A status column showing 3 instead of Invoiced is the first thing users notice. Build an enum translation table early and use it everywhere.
• Mixing roads for the same subject area. Sales from BYOD and inventory from the lake, refreshed at different times, will never reconcile cleanly. Pick one road per subject area.
• Treating the entity store as the warehouse. It works until someone asks for a measure that needs a code deployment, or for CRM data next to the sales figures.
WHERE THIS SERIES GOES NEXT
Next time I will look at BYOD in depth: registering the database, choosing between full and incremental push, how change tracking really behaves on multi table entities, sizing the batch schedule, and the pitfalls that make an incremental export silently miss rows. After that I will move on to Synapse Link and the Fabric link, and then to designing the warehouse itself.
I have spent most of my career on the other side of the ERP: not configuring Dynamics 365, but getting numbers out of it that finance, operations and leadership can trust. This article opens a series on exactly that. Over the coming posts I will work through Dynamics 365 from the BI, data warehousing and database point of view: how the data is shaped, how to extract it, how to model it, and how to make the figures in a report tie back to what the ERP says. My colleagues here write about how the processes run inside D365; I want to write about what those processes leave behind in the database and how to turn it into reporting.
The first question on every BI workstream for Finance and Operations is the same: where do we read the data from? Teams arriving from on-premises AX are used to pointing SSIS at the production database and getting on with it. In the cloud that road is closed, and the replacements each have a very different character. This article is the map I wish I had been handed the first time: the four ways out of the F&O database, what each one is really for, and how I decide which to use.
THE STARTING POINT: AXDB AND WHY YOU CANNOT TOUCH IT
Underneath every F&O environment is a transactional Azure SQL database, still called AxDB in development and tooling. It is a classic OLTP schema: several thousand tables, heavy normalisation, every row keyed by RecId, company data separated by a DataAreaId column, and a Partition column that is almost always the same value but still sits in every index. Enumerations are stored as integers, financial dimensions are stored as RecId references into DimensionAttributeValueCombination rather than as readable strings, and date and time fields are stored in UTC while pure date fields are not converted at all.
In production you have no direct SQL access to this database, and that is deliberate. Microsoft runs it as a managed service and protects the transactional workload from heavy analytical queries. In a tier 2 sandbox you can request just in time read access for diagnostics, and on a developer box you have the whole database locally, which is invaluable for learning the schema. But neither is a production BI source. I treat the AxDB as something to study, not something to query, and every extraction design starts from that rule.
ROAD ONE: DATA ENTITIES OVER ODATA
Data entities are the public face of the F&O data model. An entity is a denormalised view over one or more tables, with business friendly field names, enum labels, and financial dimensions flattened into display values. They power the data management framework, Excel integration, and the OData endpoint. For a developer building an app, OData against an entity is excellent: authenticated, secured by F&O roles, and always current.
For BI it is the wrong tool, and I still see it used. OData returns rows in pages, is subject to service protection throttling, competes with the users working in the system, and has no efficient notion of "give me what changed since last night". A Power BI dataset that refreshes general ledger entries over OData works in a demo with one month of data and falls over in production with five years. I use OData only for small reference lookups or for an app that needs live values, never to feed a warehouse.
ROAD TWO: AGGREGATE MEASUREMENTS AND THE ENTITY STORE
The entity store is an operational analytics database that sits inside the F&O service. Developers define aggregate measurements in Visual Studio (a star of measures and dimensions built over entities), and a staged refresh job copies that model into a columnstore database. The embedded Power BI reports in the standard workspaces read from it, and you can build your own embedded reports over it as well. Egel covered the user experience side of this well in his article on embedding Power BI inside F&O, so I will stay on the data side.
What the entity store is good at: KPIs that belong next to a transaction, refreshed on a schedule, secured by the same roles as the forms around them, with no infrastructure for you to run. What it is not: an enterprise warehouse. The model is defined in code, so a new measure means a development and deployment cycle. It only contains F&O data, so you cannot blend in CRM, budgets from a planning tool or a legacy system. And refresh frequency is a balance against batch capacity on the F&O side. I use it for in-app operational dashboards and nothing more ambitious.
ROAD THREE: BYOD, BRING YOUR OWN DATABASE
BYOD lets you register an Azure SQL database of your own as a data management destination and export data entities into it, either as a full push or, for entities with change tracking enabled, as an incremental push on a recurring batch schedule. The result is a SQL database you own, with one table per entity, that you can query with T-SQL, index as you like, and feed into SSIS, Data Factory or a SQL Server warehouse with tooling every BI team already knows.
That familiarity is its great strength, and it is why BYOD remains common in established estates. The weaknesses are just as real. The export runs as batch work inside F&O, so heavy exports compete with the business for batch capacity. The shape of your data is fixed by the entity, so if the entity does not expose a field you need, someone has to extend it. Incremental push depends on change tracking behaving as you expect on entities built over several tables, which it does not always do. And BYOD is no longer where Microsoft invests; it still works, but new capability arrives on the lake side. I will go deep on BYOD setup and its traps in the next article, because many teams will be running it for years.
ROAD FOUR: SYNAPSE LINK AND THE FABRIC LINK
The old Export to Data Lake feature, which replicated F&O tables as CSV files into your own storage account, has been retired. Its successor runs through Dataverse: once the F&O environment is linked to a Power Platform environment, you can choose F&O tables in Azure Synapse Link for Dataverse and have them land in your Azure Data Lake Storage in Delta format, or use the Link to Microsoft Fabric so the same tables appear in OneLake through shortcuts, without you managing the storage at all.
This is the road I recommend for any new warehouse build. You get tables rather than entities, so nothing is hidden behind an entity definition. Changes flow continuously without consuming F&O batch capacity. Delta format gives you efficient incremental reads and works natively with Spark, the Fabric lakehouse and warehouse, and SQL endpoints. The price is that you inherit the raw schema from the first section: RecIds, integer enums, UTC timestamps, dimension combinations as references. Every join that an entity would have done for you, you now write yourself in the transformation layer. In my experience that is a good trade, because you control the logic and can see it, but it needs people who are willing to learn the F&O table model properly.
A small example of what that means in practice. An entity hands you a ready made string such as a main account and two dimension values joined by delimiters. From the tables you resolve it yourself, along these lines:
SELECT g.RecId, c.DisplayValue, a.MainAccountId FROM GeneralJournalAccountEntry g JOIN DimensionAttributeValueCombination c ON c.RecId = g.LedgerDimension JOIN MainAccount a ON a.RecId = c.MainAccount
Three joins for one readable column is typical, and it is the reason I build a conformed dimension layer once rather than letting every report author write those joins again.
HOW I CHOOSE
My rule of thumb is short. If the requirement is an operational view that users want inside an F&O workspace, use the entity store. If you are building or rebuilding an analytical platform, start from Synapse Link or the Fabric link and design your staging layer around raw tables. If you already have a stable SQL Server or Azure SQL warehouse fed by BYOD and it meets the business need, keep it running, but stop adding new subject areas to it and plan the move rather than waiting for it to become urgent. And never use OData as a warehouse feed.
A few questions settle most debates quickly:
• How fresh does the data need to be? Hourly or better points to the lake; daily is fine on any road.
• Does the report need data from outside F&O? If yes, the entity store is out.
• Who will maintain the transformations? A team fluent in T-SQL and SSIS will be productive on BYOD on day one; a team comfortable with Spark or Fabric will be faster on the lake.
• Is the F&O environment already linked to Power Platform? If not, that is a prerequisite project for road four, with its own governance conversation.
WHAT GOES WRONG
• Power BI over OData. It refreshes in testing, then times out or gets throttled once volume arrives, and users blame Power BI rather than the design.
• Forgetting DataAreaId. A join between two tables without the company column quietly multiplies rows across legal entities, and the totals look plausible enough to escape notice.
• Time zones. Transaction date and time fields are UTC, so a posting made late in the evening in a western time zone lands on the next day unless you convert it. Pure date fields such as the accounting date are not converted, which is why I always report on accounting date for finance.
• Enum integers in reports. A status column showing 3 instead of Invoiced is the first thing users notice. Build an enum translation table early and use it everywhere.
• Mixing roads for the same subject area. Sales from BYOD and inventory from the lake, refreshed at different times, will never reconcile cleanly. Pick one road per subject area.
• Treating the entity store as the warehouse. It works until someone asks for a measure that needs a code deployment, or for CRM data next to the sales figures.
WHERE THIS SERIES GOES NEXT
Next time I will look at BYOD in depth: registering the database, choosing between full and incremental push, how change tracking really behaves on multi table entities, sizing the batch schedule, and the pitfalls that make an incremental export silently miss rows. After that I will move on to Synapse Link and the Fabric link, and then to designing the warehouse itself.
No comments yet. Be the first to comment!
