Here is a super quick guide for what NOT to use as base line information when building reporting:
Do NOT create reporting on data originating from Sales Table and Sales Lines. These data change a lot, and are not permanent. Think of Sales Table and Sales Lines as a “journal”, that have little value after the transactions have been posted. Also, remember that Sales Table and Sales Lines can easily be deleted and archived to keep the Dynamics 365 lean and up to date. Aim to do the reporting on CustInvoiceTable/line instead for invoiced orders. For “faster” reporting to show what orders came in yesterday you can do some highly filtered reporting, but as soon as the sales orders are invoiced, think that the transactions are gone. Power BI is optimized for aggregated and summarized data, not for processing large volumes of transaction-level detail in real-time.
The same applies to Purchase Table and Purchase Lines
Do NOT create reporting on data originating from InventTrans. Especially do not enable track changes on this table to get “delta” updates in BYOD or in Azure Synpase. It just slows down the transactions, and you end up with a sluggish and slow system. You will also experience a lot of blocking in the DB. Also remember that Dynamics 365 will archive inventory transactions, so they are not permanent.
Do NOT create reporting transactional on-hand tables. Use inventory visibility instead.
In short – really, really, really rethink on what data you are using for reporting. You will thank yourself afterwards, and your “lessons learned” list will be shorter.
Kurt Hatlevik - Dynamics 365 Blog 
Hello Kurt! Thanks for this amazing article.
I have a PowerBI report to do and one of the columns in a visual table requires me to calculate the inventory value for each item on a Selected Date. I was able to match the inventory value if no date is selected (comparing it to the Inventory Value Report in D365), but there are some more logics to be applied when I go “back in time”.
Currently I calculate everything using InventTrans table with a simple logic:
If there is a CostPosted+CostAmountAdjustment then I sum that one, else, I sum CostAmountPhysical for that transaction.
This works only when I use all the transactions available, but there is a logic that I am currently missing. What would you do in my situation if you want to replicate a D365 report in PBI? Thanks
LikeLike
Hey Kurt, accoring to https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-troubleshooting-guide
Azure Synapse Link service requires enabling the Row version change tracking metadata property.
Does this mean we have no choice but to enable it if we need tables like InventTrans?
LikeLike
Yupp. Then you need to enable it. But watch out for performance issues if you see a lot of events being triggered if you start processes that performs mass updates on inventtrans. Like deleting lot’s of unused transferorders, purchase lines or sales lines.
LikeLike
Hello,
On InventTrans the checkbox “AllowRowVersionChangeTracking” is already on “Yes” (standard Microsoft). Why is it not good to have this activated?
LikeLike
Change tracking is a capability available in SQL Server and SQL Database, designed to monitor modifications, including deletions, on tables. It operates by recognizing table alterations as part of transactions. However, for this feature to function at the data entity level, supplementary logic is required beyond SQL’s inherent change tracking. This extra layer of logic or events may lead to reduced transaction efficiency in the inventtrans table, leading to database locks and performance degradation in high-volume scenarios. Consequently, organizations face a trade-off between operational speed and the depth of reporting capabilities.
LikeLike
So what data sources should be used for sales by item or purchase orders by line? There seems to be a limited number of data entities to pick from, and not every D365 client has a data warehouse or data lake connected.
LikeLike
Hi. My recommendation is to use the posted entities. Like SalesInvoiceHeaderV2Entity, SalesInvoiceLineV3Entity. For unposted, we can use confirmations SalesOrderConfirmationHeaderEntity, SalesOrderConfirmationLineEntity, but then you also need some DAX logics to filter out duplicates etc.
LikeLike
Hi Kurt
I was wondering about this comment from you:
Also remember that Dynamics 365 will archive inventory transactions, so they are not permanent.
Could you enlightning me?
BR Jan
LikeLike
Hey Jan. Check this out : https://learn.microsoft.com/en-us/dynamics365/supply-chain/inventory/archive-inventory-transactions
LikeLike
Ahh thanks – I havn’t noticed that 🙂
LikeLike