- The built-in Production performance content covers production orders with fixed measure definitions. It doesn't include sales or inventory.
- F&O data goes out through Azure Synapse Link or Link to Fabric, with no development in F&O. The table structure is publicly documented.
- In our experience, about 10 to 20 tables cover sales and production orders, inventory and basic customer data.
Contents 9 sections
- 01What do the built-in reports cover?
- 02How do you get F&O data out?
- 03F&O data structure
- 04Tables for manufacturing reporting
- 05What the tables contain
- 06Calculating OEE from Dynamics 365 data
- 07Glossary: everyday words and Dynamics 365 terms
- 08On your own, or with us
- 09FAQ
What do the built-in reports cover?
Supply Chain Management includes the built-in Power BI content Production performance: on-time and in-full delivery, scrap rate in ppm and cost variances on production orders (Production performance).
| Aspect | Production performance |
|---|---|
| Definitions | fixed: a finished order is in the Reported as finished or Ended status |
| Scope | production and batch orders, no kanbans, sales or inventory |
| Data source | aggregations in the Entity store inside F&O |
| Customization | visuals in the web editor in F&O, the model in Power BI Desktop in an F&O development environment (customizing analytical workspaces) |
A custom model in Power BI reads F&O tables directly. The model's author decides the measure definitions, how production joins with sales and inventory, and any other sources outside F&O.
How do you get F&O data out?
Microsoft sends F&O data for analytics through Dataverse along two paths (F&O data in Synapse Link):
- Azure Synapse Link writes changes in the selected tables to your ADLS Gen2 storage account, as incremental CSV updates or as Delta tables.
- Link to Fabric, also called Fabric Link, keeps a Delta-format copy of the tables in Dataverse storage, and Fabric points to it with shortcuts.
Tables are selected in Power Apps on the Link data page. Standard F&O tables need no setup and no development. Our default choice is Synapse Link: the data sits in your storage account and the copy doesn't take up Dataverse capacity. The Dynamics 365 F&O to Fabric guide compares the billing and freshness of both paths.
F&O data structure
F&O tables, their columns and relationships are publicly documented in the Common Data Model reference for Finance and Operations (F&O tables in Common Data Model). These rules hold across the application:
- `DataAreaId` in transaction tables identifies the company, that is, the legal entity.
- `RecId` is the row's technical key, which relationships between tables point to.
- Enums, for example the order status, come as numbers. Synapse Link supplies their labels in the `GlobalOptionsetMetadata` file (F&O data and metadata).
- Inventory transactions point to their source document through `InventTransOrigin`, which carries the document type and its number (InventTransOrigin).
- Product names sit in `EcoResProductTranslation` by language, customer names and addresses in the global address book `DirPartyTable` (global address book).
Tables for manufacturing reporting
These are the tables we build models on for manufacturing companies on Dynamics 365.
F&O tables for manufacturing reporting
| Area | Table | Contents |
|---|---|---|
| Sales | `SalesTable`, `SalesLine` | sales orders and lines, requested and confirmed ship and delivery dates |
| Sales | `CustPackingSlipJour`, `CustPackingSlipTrans` | packing slips and their lines |
| Sales | `CustInvoiceJour`, `CustInvoiceTrans` | invoices and their lines |
| Manufacturing | `ProdTable` | production orders and their status |
| Manufacturing | `ProdRouteTrans` | operation reporting, good and error quantities |
| Manufacturing | `ProdBOM` | material on the production order |
| Manufacturing | `ProdCalcTrans` | estimated and actual order costs |
| Inventory | `InventTrans`, `InventTransOrigin` | inventory transactions and their source document |
| Inventory | `InventDim` | warehouse, site, batch and other dimensions of the transaction |
| Products | `InventTable`, `EcoResProduct`, `EcoResProductTranslation` | released products and their names |
| Customers | `CustTable`, `DirPartyTable`, `LogisticsPostalAddress` | customers, names and addresses |
What the tables contain
Sales orders. `SalesLine` carries requested and confirmed ship and delivery dates, the packing slips in `CustPackingSlipJour` carry the actual ship date. F&O can calculate the confirmed dates with capable-to-promise (delivery dates with CTP).
Production orders. An order goes through the statuses Created, Estimated, Scheduled, Released, Started, Reported as finished and Ended. In the Ended status, estimated costs are replaced by actual costs (production process overview). `ProdRouteTrans` carries good and error quantities by operation and resource, `ProdCalcTrans` estimated and actual costs. Resetting an order's status cancels the reported quantities and time (resetting order status).
Inventory. A transaction in `InventTrans` carries a receipt status, for example Received or Purchased, or an issue status, for example Deducted or Sold. The physical date captures the movement, the financial date the cost posting (inventory transactions). Inventory transaction consolidation, still in preview, moves old closed transactions to `InventTransArchive` (inventory transaction consolidation).
Finished order. F&O records four different moments: the Reported as finished status, the Ended status, the packing slip posting and the invoice. The model holds all four, and the measure decides which one it uses.
Calculating OEE from Dynamics 365 data
OEE, overall equipment effectiveness, is the product of three components: availability × performance × quality. Availability is run time divided by planned production time. Performance is the ideal time per piece times the pieces produced, divided by run time. Quality is the number of good pieces divided by all pieces produced.
| Component | Where it comes from in Dynamics 365 |
|---|---|
| Quality | good and error quantities from operation reporting in `ProdRouteTrans` |
| Availability | machine downtime that workers record in the production floor execution interface, along with the reason (production floor execution) |
| Performance | the operation's ideal time from the route, against the time and quantities reported in the route card and job card journals (production reporting) |
Example: planned time 450 minutes, downtime 45 minutes, ideal time 1 minute per piece, 360 pieces produced, 18 of them defective. Availability is 405 / 450 = 90%, performance 360 / 405 = 88.9% and quality 342 / 360 = 95%. OEE comes out at 0.90 × 0.889 × 0.95 = 76%. Across several machines or shifts, OEE is calculated from the sums of minutes and pieces, not as an average of percentages.
Glossary: everyday words and Dynamics 365 terms
| Everyday word | In Dynamics 365 |
|---|---|
| work order, job | production order |
| parts list | bill of materials, BOM |
| manufacturing steps | route |
| step at a work center | operation |
| booking finished goods | report as finished |
| closing the order | end |
| scrap, defective pieces | error quantity |
| idle time | downtime |
| customer order | sales order |
| delivery note | packing slip |
| stock movement | inventory transaction |
| stock attributes (site, warehouse, batch) | inventory dimension |
On your own, or with us
A model over these tables needs no development in F&O. It needs knowledge of Power BI and of the relationships between the tables. We can build the first model with your team and hand it over for further development.
FAQ
Are the built-in Production performance reports enough?
They cover on-time and in-full delivery, scrap rate and cost variances on production and batch orders, with fixed definitions. They don't include kanbans, sales or inventory.
How do we get data from Dynamics 365 F&O into Power BI?
Through Azure Synapse Link to your storage account, or through Link to Fabric to OneLake. Tables are selected in Power Apps, and standard F&O tables need no development. The Dynamics 365 F&O to Fabric guide describes the differences between the two paths.
How many F&O tables do we need for reporting?
In our experience, about 10 to 20 tables, listed above, cover sales and production orders, inventory, products and customers. Other areas, such as purchasing, add more tables.
How do you calculate OEE in Power BI on Dynamics 365?
As a measure over sums: planned time and downtime give availability, ideal time and pieces produced give performance, good and error quantities from `ProdRouteTrans` give quality. The calculation and an example are in the OEE section above.
Does this also apply to Dynamics AX or Business Central?
Synapse Link and Link to Fabric serve Dynamics 365 Finance and Operations in the cloud. From Dynamics AX 2012 R2 and R3, the path to Finance and Operations is an upgrade, which also carries over the transaction history (upgrade from AX 2012). Business Central is a different product with its own tables and its own Power BI app for manufacturing (manufacturing analytics in Business Central).
Where do we find a description of the F&O tables and columns?
In the Common Data Model reference for Finance and Operations on Microsoft Learn. Each table lists its columns, data types and relationships to other tables.
Where do you stand?
Data Probe
€840excl. VAT
One day at your company: conversations with the people who put the numbers together, and a look at what you use today. Within three working days you get one page with a map of your systems, the three biggest findings and one recommended step.
