PPECB

Perishable Products Export Control Board

PPECB has an expansive legacy data landscape, including several data sources, on-premises servers doing the processing and internal and external stakeholders to report to. To ensure that the reporting can be done as efficiently as possible and with new cloud sources added to their environment, Mint was tasked with helping PPECB design and implement a modern data warehouse ready for the cloud and enabling any new sources to be added and unlock new ways to extract value from their data.

The Challenges

New ERP D365 CE and F&O implementation data had to be integrated. A new ERP and finance implementation was happening, and we needed to ensure that the data from the new systems could be seamlessly integrated into their existing data landscape. 

Database transformation will be ready for future migration to Azure Synapse Data Warehouse. During a POC early in the project, we discovered that several designs on the legacy data warehouse make it incompatible with a lift-and-shift approach to moving to the cloud. With the redesign happening, we focused on making sure the move to the cloud in the future would be as seamless as possible. 

The current data warehouse solution utilised an on-premises SQL server and SSIS to transform and move most of its data. Due to the presence of inexperienced internal developers, many bad coding and modelling practices were present.  

The Solutions

Azure Synapse Data Warehouse POC.

Azure Synapse Analytics was implemented to get data from D365 to a BI landscape. It utilised Synapse Link to Dataverse and Synapse pipelines to move the required data from D365 to the company’s current data warehouse.

We assisted in designing and implementing a new data ingestion and transformation methodology to add source control, minimise maintenance, and speed up development. This was done with a critical focus on upskilling and knowledge transfer to the business’s in-house team and architecting the solutions to reduce the learning curve as far as possible.

This new design was also made with the plan to migrate to a cloud data warehouse once the legacy codebase has been migrated to the new methodology.

Results

  • Migration from legacy to D365 source completed.
  • A New Database and development methodology were implemented to streamline development and source control.
  • Designed a roadmap to move from legacy to new methodology and implemented it on the first source system.
  • Mint has laid the foundation for PPECB’s cloud adaptation journey for its analytics workloads.

Lessons Learnt

End-to-end data landscape design and implementation. Investigating and detangling a large legacy data platform to understand how everything fits together and what and how it can be made more efficient is a challenging process. Thankfully, the client’s strong involvement and inputs from key stakeholders, was the key to success in this process.

Cloud-ready design considerations. Making sure that any new designs are ready for future data platforms, or minimizes future refactoring as much as possible is an exciting design challenge because it brings all key aspects of People, Process and Tools, in the short, medium and long term into focus.

Working with less experienced developers and ensuring best practices are easy to adhere to for future development. A methodology is only as good as its adherence.

Working in a highly regulated environment, we must ensure all requirements are met and considered from the start to the go-live.

Next Steps

Completion of migration from legacy codebase to new methodology. A new methodology and plan are in place to migrate the rest of the legacy codebase to ensure minimal effort is needed to maintain and add new sources in the future. 

New HR system data to be integrated into the data landscape. Tooling and methodology are now available to ensure it is as painless as possible. 

 

The Team

Barry Barkley – Data Delivery Team Lead

Rickus du Plooy – Data Engineer

Ravin Yellayah – Data Engineer

Donna Hutton – Data Engineer

Pano Kappos – Specialist Developer

 

Additional Notes

While working towards the bigger goals for the project, PPECB had some staff turnover. In these times, the teams assisted with the BAU requests, supplementing the teams while still working towards the bigger picture of modernising the data landscape.