Valuable insights from Microsoft Dynamics 365

Although we needed to gain insights from ‘just’ one data source, the situation was quite complex because:

• The data is season-based and the data varies depending on the harvesting time.
• They process potatoes with different structures and sizes, so their article structures are flexible.
• During the harvest, volumes and stocks need to be estimated.
• The prices are determined at the end of the year, when everything is sold.
• The volumes invoiced differ from the actual volumes delivered because the weight of the packaging is subtracted from the total weight.

Approach

First, we engaged with various stakeholders and assessed the current and desired situation. To improve data quality, we implemented a new data infrastructure.

The main requirements for the infrastructure were: “Be able to ingest data from Dynamics365, the solution must be scalable and easy to apply business rules to generate a data model that will later serve PowerBI dashboards”.

As part of the Agrico data team, we handled requests from end-users within the organization. Stakeholders created tickets, after which we discussed the scope and definitions with them. We checked in Dynamics 365 to see if the data was available and created an initial design.

We ingested the raw data to Databricks via Microsoft Azure. Using dbt, we performed various data quality checks, transformed the data, and documented the entire process. We created various joints between tables, filtered data from large tables, and created subsets for use in specific reports. Based on the final tables in dbt, we visualized the data in PowerBI.

This was an iterative process in which we worked closely with stakeholders to optimise the reports and underlying models.

Result

Various stakeholders within Agrico now have PowerBI reports that provide a comprehensive view of their business processes. They can filter information very specifically. For example, they can see the sales of potatoes for consumption per growing season.

With clear visualisations of their ERP data, they can monitor their agreements with growers much more effectively. This allows them to make sales forecasts for multiple growing seasons and adjust their operational planning accordingly.

Future

As part of Agrico's data team, we are still developing new reports in collaboration with stakeholders. We closely monitor new features within Microsoft Dynamics 365 and adapt our process and associated documentation as needed.

Receive data insights, use cases and behind-the-scenes peeks once a month?

Sign up for our email list and stay 'up to data':