Raw pet food manufacturer
This privately held company is the most tenured raw pet food producer in the nation, as well as one of the leading private label providers of raw pet food.
Overview
THE CHALLENGE
This raw pet food manufacturer needed to give non-technical sales and marketing teams access to actionable data analytics to influence their decision-making and, ultimately, drive operational improvements.
THE SOLUTION
The company struggled to efficiently extract the data required for high-quality and accessible reporting from their Epicor enterprise resource planning (ERP) system.
THE OUTCOME
Ollion created a data warehouse and designed an analytics solution that transforms raw Epicor data into a user-friendly Power BI dataset and reports based on the requirements of end users. Now, non-technical teams can apply insights in self-service reporting to impact business outcomes.
The challenge
Our client wanted to generate analytics for their profit and loss (P&L) statement across a variety of dimensions, including time, customer, product and location. However, they struggled to both extract the required data out of their Epicor ERP, as well as deliver it in a user-friendly report. In order for non-technical teams to apply the analytics, reporting needed to be sliceable, drillable and scalable.
The company and their end users outlined various requirements:
- Reports including filters to accommodate different data frames based on who needed what
- Reporting displaying a range of data from a high-level context, down to a detailed view to pinpoint intervention opportunities and track outcomes
- Reports facilitating the addition of new data to underlying models with minimal effort as one-off reporting limits applicability and demands additional support for next steps
Not only did the company need to see their income statement, for example, but they also required visibility into their income statement for beef-based products. Then, they needed to be able to drill down into revenue accounts and see an ordered list of beef customers by sales in the last quarter.
Beyond case-by-case data visibility, this Ollion client wanted to empower users to generate any other permutation of the report to identify potential efficiencies in a data-driven way. Of course, the ability to produce these reports needed to be replicable month-after-month with minimal requirements from focused IT teams. Overall, the intent was to enable non-technical teams to gain Epicor insights and useable analytics independently, without having to distract busy IT teams.
The solution
The Ollion Data Insights team created a data warehouse to translate raw Epicor data into Power BI datasets for reporting.
First, our development team landed a copy of relevant ERP tables into a separate SQL Server on an Amazon Relational Database Service (RDS) machine. With Amazon RDS serving as the analytics backend, we were able to remove any contention in the ERP’s production environment.
Next, Ollion engineers used SQL scripts to connect the operational tables (e.g., InvcDtl, PartTran, LaborDtl, etc.) to the primary general ledger (GL) table (tranGLC). By doing this, we gained the benefit of being able to apply additional business logic outside of Epicor. For example, the raw pet food company wanted to allocate any unmapped cost of goods sold (COGS) and overhead expense balances back to active customers based proportionally on sales. This practice allows for a more accurate profit outcome when slicing by customer. This is just one of the dozens of transformations we applied while engineering the data warehouse to best fit the company’s requirements.
From there, our architect designed the new flattened tables into a star schema warehouse. This is an important step because it provides an easy method for connecting Power BI to the new information. It also allows developers to identify and create the relationships between data domains.
Finally, Ollion reporting developers published a variety of key performance indicators (KPIs) and other attributes to use in downstream reporting. The design of the Power BI data set was based on requirements set forth by our client’s end users during the planning stages of our partnership for this project. In addition to loading the data warehouse, the Ollion team created, showcased and provided training on a suite of reports for the company’s users to harness and build off of.
The outcome
This manufacturer went from using a limited number of canned reports in Epicor – which were both expensive and time-consuming to build – to having unlimited, self-service reporting access, powered by a modern data platform. The primary benefit of the solution is that the company fulfilled their business objective. The sales and marketing teams, as well as other non-technical teams, can make data-driven decisions to efficiently improve business outcomes.
Now, the company can adjust pricing based on historic labor, material and burden variances. They can identify the metric most effective for increasing earnings. And they can adjust prices based on increases in raw material costs and inflation. With analytic answers to inductive questions like these, as well as easy-to-manipulate data sets and reports, the client can drive operational efficiency across the organization.
Additionally, enterprise teams now have the ability to discover, communicate and justify new processes in sales, marketing and operations based on the Epicor ERP – without relying on expensive and slow third parties to serve up the inputs. Moving forward, the raw pet food manufacturer will continue to build on the data foundation provided by Ollion.
