CASE STUDY

Cloud data platform: From legacy systems to modern data integration and warehousing

CHALLENGES

Snackbrands Australia required a modern, automated solution to handle data integration, warehousing and reporting to complement their migration of their Dynamics AX ERP system to the Cloud.

With AX 2009 reaching the end of its lifecycle, Snackbrands Australia faced challenges around scalability, security, and operational inefficiency. Snackbrands Australia relied on outdated data processes tied to their on-premises Dynamics AX 2009 system. Several systems were integrated with AX, using multiple methods. For example, several classes in AX produced EDI files, which were stored on an on-prem server, accessed using a mapped SMB fileshare. Several WMS processes accessed this fileshare to ingest the EDI files AX produced.

Similarly, the data orchestration (or ETL – Extract, Transform and Load – processes) for data warehousing was implemented using on-premises SQL Server. AX 2009 which is the primary source was also an on-premises database, and data was exported for multiple entities (tables) by directly connecting to the underlying AX SQL database instance.

These needed to be transitioned to modern, cloud-based systems to deliver the business outcomes that Snackbrands sought to achieve through the migration of their Dynamics AX ERP system to the cloud with Dynamics 365 Finance & Operations (F&O).

 

APPROACH

Hybrid Data Platform

To support the move from AX to Dynamics 365 F&O, these systems and their integration methods were reviewed, and changes required to maintain the existing functionality with alternative data integration and orchestration methods were implemented to form an interim hybrid data platform.

Schema Mapping

Fundamental changes in data schema between AX and Dynamics 365 F&O required careful mapping of legacy data structures to the new format. Walkerscott conducted:

  • A review of Dynamics 365 standard entities to understand compatibility.
  • Manual and automated mapping of entities and fields using tools such as the Data Management Tool (DMT).
  • Documentation of the mapping process for Snackbrands Australia’s reference and future scalability.

SOLUTION

Azure Data Lake Integration

Walkerscott enabled the Export to Data Lake feature in Dynamics 365 Finance and Operations to automate the extraction of data.

  • Azure Data Lake acted as the central repository, where key entities from the ERP system were exported.
  • This setup provided Snackbrands Australia with close to real-time access to their operational data, enabling advanced analytics and reporting.

Modernising the ETL Pipeline

Walkerscott replaced the manual, SQL-driven ETL process with an automated pipeline powered by Azure Data Factory (ADF). This new process extracted data from Azure Data Lake, applied the necessary transformations, and loaded it into Snackbrands Australia’s existing data warehouse schema.

  • The pipeline ensured that only the required data was processed, improving efficiency.
  • Data consistency was maintained by automating updates, deletions, and additions, reducing manual errors.

Integration of Additional Tables

Additional entities required for SSRS and other reporting tools were brought into the data warehouse. Walkerscott ensured these were mapped accurately to Dynamics 365 schema and integrated seamlessly into Snackbrands Australia’s reporting processes.

RESULTS

Outcomes

The successful migration and integration delivered transformative benefits as part of Snackbrands Australia’s move to Dynamics 365 Finance & Operations.

  • Near Real-Time Data Access: Snackbrands Australia gained access to near real-time data through Azure Data Lake, empowering faster and more informed decision-making.
  • Automated Data Integration: Azure Data Factory streamlined the ETL (Extract, Transform and Load) process
  • Improved Reporting: Better data availability enabled seamless reporting, budgeting and forecasting through SSRS, Kepion, and DSX, meeting the organisation’s needs.
  • Scalability and Security: Moving to Azure ensured Snackbrands Australia’s data infrastructure was secure and scalable, capable of supporting future growth.
  • Operational Efficiency: By automating repetitive tasks and reducing errors, Snackbrands Australia’s teams were able to focus on strategic initiatives.

Microsoft Fabric Data Platform

Now, over a year later, Snackbrands Australia is thriving on Dynamics 365 Finance & Operations. Walkerscott has commenced a program of works to deploy Fabric, Microsoft’s all-in-one cloud-based SaaS solution for data and analytics. Supported by Microsoft Fabric’s medallion architecture, this engagement will deliver a full-fledged enterprise data platform by integrating other data sources and the current hybrid data platform can be decommissioned.

“The cloud migration and integration work delivered by Walkerscott have transformed how we manage and access our data, giving us the tools to make smarter, faster decisions while ensuring long-term security and scalability.”

Anna Francis
IT Manager, Snackbrands