Data analysis for Dynamics 365 for Finance and Operations: How ADLS Gen2 impacts customers and partners

August 7 2019

Managing huge quantities of data and making sense of it is a challenge for companies across industries. Data warehouses and reporting tools like Power BI play a role, but the field continues to change as the tools and services available to Microsoft business app users evolve.

Kirk Donahoe, consulting manager at MCA Connect, spoke with MSDW about what's involved in managing and analyzing the data from Dynamics systems in the context of data warehouses, data lakes like Azure Data Lake Storage Gen2 (ADLS Gen2), and reporting tools when serving a diverse customer-base.

From data warehouse to data lake

Donahoe splits his time between three main Dynamics 365 Finance and Operations (D365FO) projects currently, working with a charcoal producer, a firearms manufacturer, and a gift company. These organizations are all using V1 of Bring Your Own Database (BYOD) data sources with their Dynamics AX or D365FO systems for exporting data entities. They then write SQL queries and create reports against the data.

His team also works with AX Data Warehouse (AXDW) and they are monitoring Microsoft's moves with Azure Data Lake (ADLS) Gen2.  Microsoft launched a public preview of support for Azure Data Lake Storage Gen2 (ADLS Gen2) as an entity store with the latest builds of D365FO.

ADLS Gen2 can integrate with the AXDW Entity Store and can also support some of the data entities in BYOD V1. However, Donahoe sees migrating BYOD V1 data entities as less feasible, involving significant redevelopment.

With a clearer path for migrations of Entity Store to ADLS Gen2, Donahoe's team started off with exporting entities to Blob storage for non-production scenarios (ADLS Gen2 is built on Azure Blob storage) and they are now are getting ready to migrate a customer's AXDW Entity Store to ADLS Gen2.

[ADLS Gen2] is still in public preview [for D365FO], only since platform update 23. We have some development environments set up. All the customers we've worked with to this point have been doing traditional relational databases [which are] pretty consistent across industries. However, we do work with a computer manufacturer that is one of the largest in the world, using a highly customized AX 2012 system which is still being implemented. This has been going on for some time even though Microsoft no longer sells it. It has 800-900 custom tables [to support lean manufacturing in AX], which is unheard of.

ADLS Gen2 will be able to integrate with analytical workspaces for Power BI by October.

Working with a data lake to analyze data from systems like D365FO represents a new paradigm and it poses challenges for many data staff in existing organizations accustomed to writing queries for SQL Server.

Donahoe's team works with the Data Management workspace to configure data pushes. He explained:

Data Management workspace is currently the only way of exporting data in bulk for a Bring Your Own Database V1 solution. BYOD V2 will be configured through System Administration by linking a Azure Data Lake Store Gen2 then configuring auto-refresh in the Entity Store configuration. Microsoft has it in their roadmap that the data entities currently exported through the Data Management workspace will be pushed to the data lake just like the Entity Store entities.

Microsoft principal program manager, James Rowland-Jones detailed the overall differences between Azure SQL Data Warehouse (ASDW) and ADLS in a post from 2016. ASDW is oriented to structured, relational data and can only handle partially unstructured data while ADLS is a distributed file system, with similarities to Azure Blob Storage but without the scale limitations. Because it's underpinned by the Common Data Model, ADLS Gen2 supports ASDW as well as Power BI (from October onward).  

Getting used to the new realities of D365FO

About Eamon McCarthy Earls

As the assistant editor of MSDynamicsWorld.com, Eamon helps to oversee editorial content on the site and supports site management and strategy. He can be reached at eearls@msdynamicsworld.com.

Before joining MSDynamicsWorld.com, Eamon was editor for SearchNetworking.com at TechTarget, where he covered networking technology, IoT, and cybersecurity. He is also the author of multiple books and previously contributed to publications such as the Boston Globe, Milford Daily News, and DefenceWeb.

More about Eamon McCarthy Earls