Transforming ERP from a system of registration to a system of analytics

Introduction

Rapid business modernization facilitated by cloud-based Enterprise Resource Planning (ERP) solutions such as Microsoft D365 Finance & Operations (D365 F&O) enable organizations to collect and store vast amounts of data. These ERP systems allow companies to have a good overview of their core business processes and track their resources. However, current data analytics, business intelligence, and reporting capabilities that come with standard out-of-the-box ERP systems are limited in scope, inflexible, and mainly utilize descriptive analytics. For example, Microsoft D365 F&O out-of-the-box tools include SSRS reporting (mainly used for printable documents such as invoice and packing slip), which only displays descriptive data and is not customizable by the end user without the help of a report developer.

Companies today need to extract valuable insights from the data coming from ERP systems to develop their business strategies. In the past, ERP systems have been designed to ensure consistency in their business processes to achieve certain KPIs, such as to maximize production. Nowadays, in order to remain competitive in the current business landscape, companies must be flexible and be able to quickly respond to changing customer demands. Thankfully, many data & analytics tools such as machine learning are becoming widely available and can aid in more efficient and accurate analysis of company data.

There are many ways in which data & analytics tools such as machine learning can improve company performance by utilizing data from ERP systems. Machine learning algorithms can help predict which suppliers are likely to provide excellent quality raw materials (and also the poorest quality) based on past delivery performance. Another way is to reduce the frequency of equipment breakdowns by utilizing machine-learning algorithms on predictive maintenance based on the data stream from equipment sensors. Self-learning algorithms can also use incident reports in production to help predict production problems on assembly lines ([Colu19]).

Utilizing data & analytics tools on ERP data is not a straightforward task. ERP generates transactional data and has an online transactional processing (OLTP) structure. This enables ERP systems to rapidly process large numbers of transactions by multiple people. On the other hand, Complex data analysis is best performed on data that has an online analytical processing (OLAP) structure. OLAP is optimized for business intelligence, data mining, and other decision-making applications. In this article, we will focus on how Microsoft is enabling its data analytics tools, such as Azure Machine Learning and PowerBI, to be used with the ERP data in D365 F&O through integration with Azure Data Lake and Synapse Analytics ([Sinh21]).

In this article, we will first present the building blocks of an integrated system of analytics, i.e. Microsoft Dynamics 365 F&O, Azure Data Lake, and Azure Synapse Analytics, explain their added value, and how they integrate together, and finally we will present a business case where this integration will add value for the user.

A system of registration – Microsoft Dynamics 365 F&O

A transition to an ERP system such as D365 F&O is part of the digital transformation journey of many companies. According to [Rozn21], some of its advantages are:

• Higher management performance by linking different aspects of enterprise activities
• Better information accuracy and availability
• Improved coordination between branches or departments
• Easily adapts to expansion or reduction of the company
• Cost reduction due to decrease of repetitive manual tasks and speeds up activities that involve various requests and approvals

Although the advantages of an ERP system are clear, the potential benefits of an ERP system such as D365 F&O can be further maximized. For the case of Microsoft, it is important to note that the Microsoft Technology Stack also includes data analytics and business intelligence tools such as Power BI and Azure Machine learning. To utilize the capabilities of these tools and analyze D365 F&O data, it is imperative to first understand an important piece of the puzzle: data storage through Azure Data Lake.

Azure Data Lake

Although the Data Lake has gained popularity only recently in business literature, the technology is not new. James Dixon, former CTO at Pentaho, introduced the term in 2011 ([Wood11]). Simply put, a Data Lake is a repository similar to the Documents folder on your laptop. It is folder-based data storage which can store all kinds of files on-premises or in the cloud (e.g., Azure Data Lake). These files contain data which are divided into three categories: structured, semi-structured and unstructured. Structured data is defined as such as it contains a predefined structure in the shape of tables that contain one or more columns. The tables and columns enforce particular characteristics from the data, e.g., data type (text, number, etc.), data length and compliance with business rules (e.g., customer account value is unique per system). Semi-structured data is still structured data in the sense of a predefined format in which it stored (like CSV, XML, etc.), yet there is none or minimal enforcement of data characteristics. For example, a column in a CSV can neither enforce a particular data type nor can it check on business rules as part of CRUD operations (CRUD = Create, Update, Delete records). Whereas semi-structured data still has some form of structure, unstructured data does not have it at all. For example, a fixed format of the data does not exist in cases of emails, images, and Office documents.

The Data Lake is distinguished from a Database by the fact that a database only accepts structured data, while the Data Lake does not have this limitation. So, the logical follow-up question that we receive often from our clients regarding this topic is: why does my company need a Data Lake instead of a Database? The answer to this question is very straightforward: business decisions are increasingly depending on data which keeps growing in size and variety within companies. Database storage is costly in comparison to a Data Lake and only accepts structured data.

Understanding Azure Data Lake and its added value, we will now take a look at how it integrates with Dynamics 365 F&O.

New Microsoft D365 F&O functionality: Azure Data Lake integration

In August and September 2020, Microsoft made new functionality available for public preview in the area of data integration ([Micr21b]). This functionality enables Dynamics 365 F&O (Microsoft ERP) customers to synchronize their ERP data to an Azure Data Lake in a near real-time fashion (every 10 minutes). So, why is this functionality so valuable?

1. Access to essential data. For the purpose of conducting data analysis, it is important to have access to essential data from your ERP system.
2. Connection to data analytics tools. Accessible data means being able to extract this data into a storage which can be connected to a Data Analysis tool like Microsoft Power BI or Azure Machine Learning Studio.
3. Previous limitations with Azure SQL database. The extraction of data was previously limited to only have Azure SQL database as a target storage, which brought the following problems with it:
   1. limited in terms of data size
   2. low data refresh frequency
   3. prone to performance issues
   4. increasingly costly.
4. Optimized performance. The Azure Data Lake is optimized for large amounts of data, the refresh frequency is optimized to a maximum of 10 minutes, performance issues are avoided through incremental refresh and the costs of Azure Data Lake storage are much less compared to Azure SQL database storage.
5. Better performance with analytics. The introduction of an optimization of Azure Data Lake performance (Gen 2 Data Lake) promises even better Analytics performance with Azure Synapse Analytics.

Once Azure Data Lake is integrated with D365 F&O, the next step is to ensure that the information flows from the data lake to the data analytics tools, and this is enabled via Azure Synapse Analytics.

Azure Synapse Analytics

Azure Synapse Analytics is a modern platform for managing all technical artefacts of the Data & Analytics function within a company. The introduction of it logically follows the introduction of the Azure Data Lake integration in D365 platform. Microsoft has been working on this solution for quite some time now. It started with Azure SQL Data Warehouse in 2016 and after some modifications, it was rebranded as Azure Synapse Analytics in 2019.

In simple terms, Azure Synapse is a service provided by Microsoft that combines enterprise data warehousing and Big Data analytics with dedicated resources (for better performance and security) or serverless at scale. Azure Synapse allows users to query and work on their data using SQL or Apache Spark, build pipelines for data integration and Extract, Load, and Transformation (ETL). On top of this, Synapse fully integrates with Other Microsoft tools such as Azure Machine Learning and Power BI that add an analytical power as well. In this way, it is possible to create pipelines from extracting the data, modifying it, analyzing it, to publishing a report containing the generated insights from the data all in one solution without the problem of managing different multiple environments and difficulties connecting them. The bedrock of Azure Synapse is Azure Data Lake Storage Gen2 ([Micr21d]). See Figure 1 for an overview of this structure.

Synapse SQL have streaming and machine learning capabilities. Apache Spark for Azure Synapse can be used for data preparation, ETL, and machine learning. Moreover, Azure Synapse allows effortless mix use of Spark or SQL by removing technology barriers between them, where you can explore and analyze different data types stored in the data lake. Synapse also provides the same data integration experience as Azure Data Factory to create a complete full package. Azure also supports a number of languages used for data analytics such as Python, Java, and R. Nonetheless, the low code development environment of Azure Synapse lowers the difficulty of starting to work with such a sophisticated tool ([Micr21d]).

In Figure 1, you can see different data sources available for import. For example, by streaming data you can achieve real-time analysis ([Micr21a]). One can also ingest data from different Software as a Services (SaaS) using the connectors which Azure Synapse Analytics provides. Aside from Azure Machine Learning or Power BI to provide insights from the data, Azure Purview integration with Azure Synapse Analytics allows customers to have unified data governance on their data ([Micr21c]).

Figure 1. Azure Synapse Analytics structure ([Micr21d]). [Click on the image for a larger image]

Furthermore, Azure Synapse has advanced security and privacy features which includes Azure Active Directory, automated threat detection, as well as Row-level, Column-level, and object-level security. It also supports network-level securities by the means of virtual networks and firewalls ([Meht20]).

Now that we know the different building blocks of a modern data analytics system, we need to see how they all work together.

A system of analytics – Integration of D365 F&O, Azure Data Lake, and Synapse Analytics

There is an extremely valuable synergy between the concepts of D365F&O, Azure Data Lake and Synapse Analytics. It brings customers into a system of analytics, where it moves towards the higher purpose of a full-blown ERP system: understanding and mastering their business through Data Analysis. This is achieved by creating a platform in which Data Analytics function within an organization.

Azure Data Lake is at the core, acting as the central storage of all business-related data coming from various Microsoft Business Applications, yet it will also be a possible repository of unstructured data from IoT devices, social media, etc.

The data in the lake is still in its raw format, however. So, on top of the Azure Data Lake lays Azure Synapse Analytics is the professional playground of the Data Engineer, Data Analyst and Data Scientist. It is the system which is accessible in the cloud and brings all Data Analytics experts together. It enables the experts to clean the raw data, combine it and create business insights in a controlled IT environment. Azure Synapse will enable the Data Analytics team to:

1. access the data from the Azure Data Lake;
2. combine the data from unintegrated systems, and if required, enrich with even more data sources;
3. perform algorithmic data analysis using the data from step 1 and/or step 2;
4. utilize Power BI to present the new insights and enable improved decision making processes;
5. perform their job in a completely scalable solution which is ready for Big Data ingestion and algorithmic data analysis on top of it!

Business case

Let’s illustrate how the synergy works in practice using an example based on a case inspired by an actual client. The company Quality Bikes is a fast-growing organization that manufactures and sells popular bicycles. To facilitate their rapid growth and enable business process standardization across the globe, they have decided to have D365FO as their ERP system covering the back-office processes and the manufacturing processes. Next to D365FO, the client is using D365CE as its CRM system which integrates with D365FO by linking the opportunity to sales quotes. Quality Bikes has a very strong focus on Customer Service as the CEO believes that keeping their existing customers satisfied is the key to their success. Therefore, they developed a unique customer service process, where the management team has decided to hire a third party to build a proprietary application which integrates with D365FO and CE. To comply with their Cloud strategy, the proprietary application is being hosted in Azure.

Once the applications were live, Quality Bikes understood that it was time to reap the benefits from all the data being registered across the new applications. To do so, the data had to be brought into one data storage. This was the perfect moment to turn on the Azure Data Lake integration in F&O and CE. Therefore, the most essential records from D365FO, D365CE and the proprietary service application were synchronized to the data lake in a near real-time fashion.

On top of the Azure Data Lake, Quality Bikes decided to make use of Azure Synapse Analytics. Their Data Engineers are cleaning the data using the Azure Data Bricks integration on the Synapse platform and creating a harmonized cross-system data model which now contains data that is ready for analysis. This is the point where the quantitative data analysis skills of the Data Scientists of Quality Bikes come into play. Using fundamental techniques in algorithmic data analysis, a set of intelligent solutions has been built into Synapse with integrated Machine Learning tools that Microsoft is providing on the platform.

The COO of the company claims that his company has truly managed to turn data into assets which help the business to thrive even more, and their journey in turning data into value has just started. The resulting better management is due to the intelligent solutions built in Synapse, which were able to execute improved control over manufacturing machinery outages through a set of predictive maintenance models. Moreover, by connecting their CE data with external data sources like Twitter and Facebook and utilizing the power of Multiple Regression Analysis in Azure Synapse, they are now able to forecast sales with extremely high precision. The commercial director stated: I know with quite a level of certainty how our sales funnel will evolve in the coming months. Finally, Quality Bikes Data Scientists have spent a tremendous amount of time in the analysis of service data. Using multiple statistical techniques using Python in Synapse, they were able to uncover the key drivers of customer satisfaction. These key drivers are now an integral part of upper management dashboards and incorporated in their performance management system.

Conclusion

Recent developments within the Microsoft Technology Stack allowed the transformation of D365 F&O from a system of registration to a system of analytics. The integration of D365 F&O, Azure Data Lake, and Azure Synapse Analytics creates a synergy that transcends the traditional benefits of an ERP system. It opens the possibilities for customers that are currently using D365 F&O to take advantage of the data analytics tools that the Microsoft Technology Stack has to offer. Moreover, this integration is not only limited to D365F&O, rather, it can be expanded to other data sources such as Microsoft Dynamics CE and even sources of unstructured data. This allows companies to make holistic strategic decisions, which is illustrated by the business case presented.