BYODW optimization techniques in Microsoft Dynamics 365 Finance and Operations One version

BYODW optimization techniques in Microsoft Dynamics 365 Finance and Operations One version

Using BYODW bring your own data warehouse with D365FO and looking to optimize it?

Sharing some best practices:

1. Size your Azure SQL of BYODW according to volume, performance, reads and downstream usage
2. If using out of box entities, could duplicate your development to create a trim down version as needed by BYODW.
   1. This also ensures when entities gets changes in subsequent version your BYDOW is not impacted
   2. Turn off validations at your custom entities e.g.
      • Run business logic in insert or update method
      • Run business validations
      • Run business validations in validate Field method
   • Enable change tracking to allow for incremental push
     • Incremental push is ideal over full push over most scenarios
     • Options to enable change tracking
       • On primary table - trigger happens when any data is changed at primary table of data entity
       • On entire entity - trigger happens when any data is changed in any table of data entity
       • Custom option - development option for selective trigger
3. Utilize data management export project in batch mode
4. Turn off staging using option 'skip staging' where possible
   1. e.g. when not using post load to run custom logic
5. Try multiple task using data management - framework parameters
6. Use multiple processing to execute data entity export using Execution unit and levels and sequence
   1. Ideally group related information together according to business needs and also leverage the push type
   2. At times the entity which takes the most time should try to put in its own parallel execution
7. Optimize SQL time out setting in bring your database options in data management - framework parameters

Happy D365FO