Data Lake Storage for Dynamics 365 Finance & Operations

October 22, 2019

The transition of Dynamics 365 Finance and Operations (D365FO) from an on-premise ERP to a cloud-based ERP has created numerous changes, most of them highly advantageous for users, admins and partners. As welcome as these improvements are, it can be challenging to keep pace with Microsoft. That’s where it’s especially useful to have a high-level of engagement with a trusted Dynamics Partner.

Big changes in data storage and access

One of the biggest changes happening with Dynamics 365 right now is in how data is stored and accessed. With the move to cloud-based data applications, comes the move to cloud-based data storage. Microsoft’s data storage strategy has evolved to keep pace with innovations in data storage technology.

  • Database – A database (like SQL Server) is the underlying structure that contains all the transactional data from a business application (like Dynamics 365). The Dynamics 365 Finance and Operations database is designed to make the application work efficiently for processing transactions, not to run analytics.
    Impact on Dynamics: In prior versions of Dynamics (including AX2012), users could query the database with an OLEDB or ODBC connection. Direct access wasn’t recommended for reporting, but it was possible if you were familiar with the database structure.
    This feature was removed when Dynamics moved to the cloud. Instead Microsoft recommended a BYOD or Bring Your Own Database strategy. This allowed administrators to configure their own database and then export Dynamics F&O data into it.
  • Data Warehouse – A data warehouse holds a copy of data from one or more databases and makes it available for analytics and reporting. The data within it is structured into a design that is optimized for reporting. Rules define how the database records are connected. If you have two records of John Smith, the data model rules will determine if this is the same customer or two different customers, and which system record should take priority.
    Impact on Dynamics: With the emphasis now on creating your own database for queries and reporting, D365FO data was housed in the Entity Store, known as the AX Data Warehouse (or AXDW for short). MCA Connect developed the DataCONNECT data warehouse solution that would allow Dynamics users to not just report on D365F&O content but also combine application data from external sources to create “one version of the truth” across the enterprise.
  • Data Lake – A data lake is a storage repository that keeps data in its native format (structured or unstructured) until it is needed. A traditional data warehouse design requires highly structured data. A data lake can store vast volumes of data from IoT sensor devices, machine learning and big data sources. Because the information doesn’t have to be cleaned or indexed up front, there’s minimal expense.
    Impact on Dynamics: Today, Microsoft is shifting its data storage from AXDW to the Azure Data Lake. The goal is to incorporate more data sources, while eliminate data silos by using a Common Data Model within the data lake architecture.

Will the Data Lake make the Data Warehouse obsolete?

No. The data lake will simplify the process of getting data out of D365FO, but most companies will still want / need a data warehouse because they’ll want data that’s been properly cleaned and indexed for reporting.Data Storage Lake for Dynamics 365 Reporting directly against a data lake is inefficient requires defined processes to catalog and secure data. Failure to do so can turn your data lake into a data swamp.

A data lake supplements your data warehouse but doesn’t replace it.

What do Dynamics 365 Administrators need to do?

Be prepared for changes ahead. And stay tuned! We posted a more detailed technical Dynamics 365 Data Lake article on It’s an exciting time to be involved in business analytics. If you have any questions, please contact us. We’d be happy to have a conversation with you.

MCA Connect