BYOD v Export to Data Lake

OmniData-BYOD-V-Export-to_Data-Lake

Extracting Data from D365 F&SCM F&O for Data Warehousing and Analytics

 

Overview

When designing and building a typical data warehousing and analytics environment, it is generally preferred to make use of the rawest data structures from which to build the relevant transformations and logic to support the business requirements. From a practical perspective, this means that, you would want to use the raw table structures as opposed to a series of views to build the logic from.

Translating this basic principle to the process of extracting data from D365 F&SCM F&O is however, not that simple. D365 F&SCM F&O is a tier 1, cloud-based, Enterprise Resource Planning (ERP) system, delivered through a Software as a Service (SaaS) model. As a result, accessing the “back-end database” to get data, is simply not possible. 

Because the need for data and analytical solutions is critical to most customers, Microsoft released a process, commonly referred to as BYOD (Bring Your Own Database), to help customers extract data from their D365 F&SCM F&O environment.

The BYOD process had some limitations and in Q4 2021, Microsoft released the Extract to Data Lake functionality for D365 F&SCM F&O into Generally Available (GA) status to help customers be successful with data. 

Let's explore some of the key differences between these technologies.

BYOD v Extract to Data Lake

Once the features are enabled and working, the first significant difference when comparing the BYOD and Export to Data Lake lies in the way entities and tables are handled.

As mentioned, a key principle of building a data & analytics environment is to use the lowest level data structures (tables) from the sources system. 

BYOD

When following a BYOD approach, native table level access does not exist in D365 F&SCM F&O. To achieve functionality akin to this, a set of custom entities, “replicating” the raw table structure is typically built to mimic the underlaying structures in the D365 F&SCM F&O database.

The custom entities are built in C# and require specialized skills to build and maintain. Complicating matters further, the custom entities then need to be deployed through the D365 F&SCM F&O development lifecycle (development, Tier 1,2 etc.) to the production environment.

  • The typical ERP development lifecycle is a slow and deliberate process to ensure stability and robustness of the system. As a result, the process of making any changes to the data environment can be painstakingly slow, impacting the data and analytics teams’ ability to deliver updates and changes to the business.
  • As a result, the BYOD method can hamper the data and analytics team’ speed of response, system enhancements and any additional development to the data warehouse, downstream semantic models, and reports. This is because any new fields or tables, needed for the enhancement, can take weeks or longer to become available due to the dependency on code promotion in the ERP development lifecycle. 

Once the BYOD entities are published, data is then “synchronized” on a scheduled basis to an Azure SQL database, which forms the typical input point for the data warehousing process. While this seems like a simple process, it is filled with several technical and process related challenges. 

Export to Data Lake

By contrast, the Export to Data Lake functionality is designed and built with robustness and simplicity in mind. The Export to Data Lake functionality allows you to select raw tables, to be exported to the data lake, directly in the D365 F&SCM F&O front-end application. This greatly simplifies the process of adding to and enhancing the downstream data warehouse and analytics environment. 

At the core of the process is the Change Data Capture (CDC) process that constantly synchronizes data between the D365 F&SCM F&O environment and a predetermined Azure Data Lake folder/directory structure.

In the Azure Data Lake, data is stored in a .CSV file structure, per table and changed data is constantly being fed in a similar structure to the data lake.

Using the data in the Azure Data Lake as the starting point for your typical data warehouse process allows you the following:

  • Align the architecture of your analytical environment with the recognized best practices for a Modern Data Architecture through the use of Azure Data Lake and Azure Synapse Analytics.
  • Get access to data in a much more timely manner to improve accurate decision making and insights into operational analytics and reporting.
  • Provide internal data customers with near real-time access to data.
  • Create a robust and reliable, enterprise grade method of extracting data from your ERP system, thereby minimizing unplanned downtime and improving how data is made available to internal customers.

Conclusion

With the Export to Data Lake functionality going into General Availability status in November 2021, Microsoft has modernized the tools for extracting data from D365 F&SCM F&O. Export to Data Lake gives data and analytics teams a mechanism to dramatically increase their ability to serve the data and reporting needs of internal customers and represents the future of how data is made available from D365 F&SCM F&O.

 

Need help with implementing export to data lake? Contact us. 

Need help with your D365 F&SCM based data warehouse? Schedule a Consultation. 

 

Posted in Data Warehouse, Microsoft, Technical