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

The future of reporting for D365FO (and most other Microsoft applications) involves moving to a data lake. For D365FO, the supported data lake for exporting data is Azure Data Lake Storage (ADLS) Gen2 which is built on Azure Blob storage. It also integrates features that were available in ADLS Gen1 including security and file system structures. ADLS Gen2 allows organizations to store petabytes of information in low-cost storage and still use it as a data source for reporting and analysis.

Microsoft's Open Data Initiative, launched in coordination with Adobe and SAP, is also built on ADLS Gen2 and has the goal of removing data silos through the use of Common Data Model within the data lake architecture.

The D365FO transition to the data lake has already started. Microsoft is shifting Entity Store entities from AX Data Warehouse (a relational SQL Server database within a D365FO environment, better known as AXDW) to the data lake and is set to continue its evolution with the data entities currently available within the Data Management workspace. AXDW is the Entity Store which houses the aggregate entities that replaced the cubes from AX2009 and AX2012. The Data Management workspace is for DIXF (Data Import Export Framework) and is used to manage getting data in and out of D365FO. In the case of our solution it's the way we configure the push to an external SQL database. The shift of the data entities from the relational database to the data lake is called BYOD v2, and refers to the way we extract data from a SQL database. It's intended to help achieve that mission within D365FO.  

The end of the traditional data warehouse?

One of the key benefits of utilizing a data lake as your staging area for BI and reporting, versus a traditional relational database, is that you can store many disparate types of data in a single location without a heavy initial investment in cleansing and transforming the data. Data lakes are considered schema-on-read versus schema-on-write. This is part of the migration to a Modern Data Warehouse architecture. Essentially, we are migrating from the traditional Extract-Transform-Load (ETL) to Extract-Load-Transform (ELT).

The move to the data lake will not totally replace relational databases. There is still a major benefit in a properly cleaned and indexed version for analytical purposes. Our current dimensional data warehouse solution, dataCONNECT, based on the Kimball methodology star schema will be modified to source data from the data lake using PolyBase instead of from a relational operational data store (ODS) using T-SQL. The data lake will allow us to supplement our current offering with additional Azure Data Services including Azure Machine Learning, Azure Databricks, and Azure SQL Data Warehouse, along with foreign key relationships that support Power BI.

There are many advantages to this migration including simplifying the process of getting your data out of D365FO. Microsoft is working towards managing all of this within the system with only a few initial configuration updates in the System Administration area of D365FO. This will allow you to configure a near-real time trickle feed of the data into the ADLS Gen2 storage without the complexity and management presently required.

In the April 2019 release Microsoft enabled the Entity Store on ADLS Gen2 in public preview. They will utilize telemetry to identify dormant SQL Entity Stores and decommission them quickly. The migration of active Entity Store entities from a relational database stored within your D365FO AXDW environment to ADLS Gen2 is targeted for the October 2019 release. Other capabilities such as exporting the data entities available in the Data Management workspace to the data lake have not yet been announced. Based on what I heard at the Microsoft Business Applications Summit, the cost to store 1 TB of data for a month in the new data lake is approximately $20.

The biggest challenge for both customers and partners is keeping up with the product roadmap and knowing exactly where Microsoft is going and when. However, with so many changes afoot, this is an exciting time to be involved in reporting and analytics!

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
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