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 