How John Soules Foods Improved Its Financial Reporting with Amazon Redshift and Datavail

John Soules Foods, established in 1975, is a leading national producer of ready-to-eat and ready-to-cook chicken and beef products, including being the #1 producer of chicken and beef fajitas in the U.S. With 20%+ sales growth annually over the past three years, this innovative food manufacturer turned its attention to improving its operational and financial reporting.

Challenges and Goals

John Soules Foods needed to provide insights into the financial and operational aspects of the business. A lack of financial reporting to drive business insights was caused by several challenges:

• Data governance was not in place for the existing Tableau reporting environment.
• There was no reporting strategy or framework.
• Reporting was needed across different programs.

The primary data source is Infor M3, the company’s enterprise resource planning (ERP) system, which holds 95% of the data needed for reporting. This ERP was implemented in 2019. Data was extracted from Infor M3 with over 100 extracts processed for reporting. Tableau was the existing reporting toolset and primarily generated tabular reports for the users.

John Soules Foods needed to report across different programs, and financial reporting required immediate attention. The company began exploring a financial reporting solution that would leverage a new AWS Redshift data mart to ingest, transform, and consolidate its Infor M3 data sources into a central data repository. From there, the solution would provide self-service access to appropriate data segments.

Ultimately, John Soules Foods wanted to:

• Improve the overall reporting process to minimize ongoing data extracts.
• Create a financial data mart for reporting with on-demand data needs.
• Gain accurate data for reporting and analytics in a timely manner via a structured and governed process.
• Establish consistent reporting capability across the enterprise.
• Have a scalable solution to meet the future needs of the organization.
• Develop a single version of the truth.

Solution

John Soules Foods wanted to start small with a financial data mart for reporting, enabling it to build its data warehouse over time. The organization partnered with Datavail to design, build, test, and verify the Enterprise Data Warehouse.

Datavail used the following approach for this project, with a proposed 12-week schedule spread over 4 sprints:

• Defined key metrics and business requirements.
• Established a financial data mart with facts and dimensions.
• Standardized Tableau reporting for financial data.
• Established support and monitoring processes.

The majority of the data comes from the organization’s Infor M3 ERP. For this initial project, John Soules Foods focused on accounts receivable, accounts payable, fixed assets, and general ledger data. Up to four fact tables per focus area were created, with up to six dimensions per fact table.

This data went on the following journey:

1. The data was staged on an Amazon S3 bucket with incremental data.
2. Amazon Glue extracted, transformed, and loaded the data into the Amazon Redshift data mart.
3. This financial data mart powered analysis through Tableau.
4. The data governance process used Audit/Balance/Controls (ABC) as part of the data ingestion process to ensure data quality and accuracy.

As part of this engagement, Datavail also developed 10 reports of varying complexity to support the business’s existing reporting needs, including dashboards, tabular, and visual graphic layouts. The team also developed Runbooks for Amazon Glue Extract, Transform, Load (ETL) and data ingestion procedures, and performed thorough testing of the financial data mart and reports.

Results

Datavail helped John Soules Foods establish data governance, create a consistent reporting environment with data sourced from the organization’s ERP, and generate an incremental future state solution for reporting and analytics while building out the financial data mart.

John Soules Foods new AWS Redshift-based solution addressed existing and future business requirements for data integration, and is highly scalable and extensible. By implementing a single source of the truth, the organization now enjoys improved data reliability and accuracy.