What Microsoft Dynamics 365 for Finance & Operations users need to know about Azure Data Lake Storage Gen2

August 13 2019

Prior to the introduction of Microosft 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.

Up next: The data lake

About Kirk Donahoe

Kirk Donahoe is a Solution Architect and Consulting Manager for MCA Connect on the Business Analytics team. He has served as Solution Architect on fifteen implementations of MCA Connect’s dataCONNECT solution for Microsoft Dynamics AX/365 Finance and Operations; of which he was the original developer. He has over sixteen years’ experience in technology. Kirk earned his B.B.A. in Business Information Systems from Mississippi State University and his M.S. in Management Information Systems from the University of South Florida. He can be contacted through LinkedIn at https://www.linkedin.com/in/kirkdonahoe/.

MCA Connect has been awarded Dynamics Partner of the Year six times, Microsoft Inner Circle Member eleven times, and most recently recognized as 2019 Microsoft US Partner of the Year Business Applications – Power BI. MCA Connect is a Microsoft Gold Partner in the following competencies: Cloud Business Applications, Cloud Customer Relationship Management, Enterprise Resource Planning, Cloud Platform, Data Analytics, and Data Platform.

More about Kirk Donahoe


Toni ton's picture

Hi, it's good article.
Do you know how to setup these feature then ? start with F&O to Datalake then use it in PowerBI ?
Many thanks in advance