D365 BYOD Configuration

 BYOD is a feature in D365 which you can use to export data from D365 to external applications. It can be used for Power BI reporting or PowerApps. 

The feature is not without it's issues but it's a better option than the alternatives as we will see later.

Framework Parameters

Path:Module > Data management > Framework parameters.

Newly created entities do not appear by default in entity list. To resolve this we refresh the entity list. Refresh the entity list before Publishing and enabling the change tracking. It takes around 15 to 30 mins to complete the refresh. 

Enable ‘All company export’ to export data for all Legal entities for DataEntities which has DataAreaId key enabled.

Increase the SQL command timeout to 3200 seconds or more so that we do not have timeout issues during export.

Configure entity export to Database

We connect to Azure SQL with the connection string given below. Validate the connection for successful connection.

 Connection string >>

Data Source=<Server name>,1433; Initial Catalog=<Database name>; Integrated Security=False; User ID=<UserId>; Password=<Password>

Publish and Enable change tracking

To create tables in BYOD, select the Entity you want and click on Publish. After a few seconds, Staging table will be created in Azure SQL.

To delete the Staging table, click on Drop Entity. After a few seconds, Staging table will be dropped in Azure SQL.

We have 3 options to enable the change tracking.

Enable Primary Table: Change tracking is enabled for the primary table only.

Enable entire Entity: Change tracking is enabled for all datasources of the Entity.

Enable custom query: Change tracking is enabled for the query datasources defined in enabledCTQuery method of the entity.

Data Export Jobs

It is recommended by Microsoft not to have more than 8 entities in a single Data export Job. CPU Utilization will spike if we export multiple entity data through a narrow time period.

The export job has been configured in such a way that it has a combination of Setup, Master and transaction tables so that load on each export job is similar.

 

The refresh type should be set to Incremental push for all entities for which change tracking can be enabled.

Set export across companies to ‘Yes’ where needed.

Entities exporting across companies take more time to export data as it has to loop through all legal entities.

Batch Jobs

If code deployment is planned, set batch job status to ‘Withhold’ and restart (Waiting status) when code deployment is done. Records may not get pushed if Error occurs during code deployment.

Best Practices and pointers

• Use only fields which are required and remove unnecessary fields from mapping.
• Use Incremental exports and limit and limit the number of columns which needs to be tracked using only primary table change or custom query.
• Don't allow change tracking for all datasources.
• If code deployment is planned, set batch job status to ‘Withhold’ and restart (Waiting status) when code deployment is done. Records may not get pushed if Error occurs during code deployment.
• Maximum limit of entities in Export jobs should not exceed 8. 
• Manage the load on each Job by have a mix of Entities of type Master, Setup and transactions. 
• Manage the load on each Job by have a mix of Entities which has Export across companies and the ones which do not have this option enabled.
• Increase the SQL command timeout to 3200 seconds or more so that we do not have timeout issues during export.

BYOD Issues and limitations

• Lot of manual intervention is needed during code deployment and upgradation. 
• Staging table may not have all necessary fields related to the table. Custom development of Entities is needed in these scenarios.
• If any record is not pushed to BYOD due to any issue, OR new field is added to staging table, there is no simple solution to rectify the data. The records in the staging table needs to be deleted, the existing batch jobs needs to be deleted,  new export job should be created, change tracking should be disabled and enabled again. Then the batch jobs should be exported. 
• Data export is asynchronous. There is a delay in data export depending on your batch recurrence.