Prior to the introduction of Microsoft Dynamics 365 for Finance and Operations (D365FO), AX 2012 users were able to directly access the transactional database using traditional OLEDB or ODBC connections. For reporting needs, it was best practice to query against a secondary or tertiary replica specifically meant for that purpose. But when needed, connectivity to the transactional database was straightforward if you were familiar with the underlying tables.
With the release of D365FO that all changed. We lost the ability to directly query the database beyond a development tier. This meant that when our clients needed data, they needed a new strategy and new data management architecture. Microsoft partners and customers have already invested heavily in solutions to accommodate the restrictions of D365FO, and the latest updates to the product will require further changes to accommodate data lakes.
Investing in BYOD
With D365FO, Microsoft has advocated the use of a Bring Your Own Database (BYOD) solution. Our Business Analytics team at MCA Connect has invested in this approach using the data management framework.
The work required to support BYOD can be extensive. In our case, we developed a custom solution to programmatically generate hundreds of custom entities that we then manipulate further. We clean up any columns that can't be exported due to unsupported data types and flag others as obsolete. These entities are published into the Application Object Tree (AOT) and data export jobs are created through the Data Management workspace. The export jobs are configured to incrementally push the data into a SQL Server database. Microsoft officially claims support for Azure SQL Database, however our default installation for DataCONNECT is to an Azure VM running SQL Server. We have also been able to push into an on-prem SQL Server and SQL Managed Instance. Recurring exports are scheduled through batch jobs as frequently as possible, being careful not to impact the performance of the transactional system. All of this must be monitored closely.