Equipment Manufacturer

Overview

A large manufacturing company was hoping to use their business intelligence and analytics tool, Power BI, to find data insights for optimizing their supply chain. The company was struggling to get the insights they needed from source systems like JD Edwards (JDE) due to its confusing data structure.

This company turned to 2nd Watch’s manufacturing data and analytics consultants to apply their experience ingesting, transforming, and preparing JDE analytics using a modern cloud environment.

The Challenge

Talk to any manufacturer who’s tried to analyze JDE data and you’ll likely hear the same frustrations: JDE’s cryptic back end with non-standard table names makes analyzing the data nearly impossible. The problem gets worse when combined with the desire to centralize data into a modern data warehouse like Snowflake and/or use business intelligence (BI) tools like Power BI, Looker, or Tableau.

This was the problem for a Pennsylvania equipment manufacturer transforming their inventory tracking to become more data-driven. They were well on their way with investments in leading technologies, including Snowflake and Power BI, but hit a major roadblock when it came to integrating and analyzing JDE data.

Their goal was to find a solution that would enable their team to access and analyze all relevant data from source systems across their organization, including JDE data from their ERP, in user-friendly Power BI dashboards.

The Solution

This manufacturer turned to 2nd Watch after learning about our experience making JDE data analytics-ready in a modern cloud environment using Snowflake and Power BI.

Our solution was broken down into four key parts:

1. Ingest JDE data into a data mart within the data warehouse.
2. Transform the JDE data in the data mart to have a consistent format with data from other sources. JDE data was transformed from names like “ABAN8” to a more descriptive name like “Address.”
3. Design user-friendly Power BI dashboards that pull the correct datasets from the data warehouse.
4. Document best practices for support and future expansion of the new Snowflake business layer.

2nd Watch used our proven methodology for loading a business layer and connecting Power BI by generating an initial data mart focused on JDE inventory. We developed orchestrated jobs that transform JDE tables into an architecture that is high-performing, friendly for report users, and scalable for future development. This included:

• Job scheduling for Snowflake/JDE business layer (using Matillion)
• Deploying physical models and SQL scripts for loading an inventory data mart with about four dimensions and one fact table
• Referencing a Power BI composite model, building crosstabs and visualizations to verify data is functional for generating reports in the context of inventory health

Once the technical solution was in place, 2nd Watch trained our client’s team and provided documentation they could reference as their organization expands and reporting needs evolve.

The Outcome

Upon completion of this JDE data transformation project, our client is now able to easily access and analyze data from all sources, including JD Edwards. User-friendly Power BI dashboards with consistent data allow them to find insights such as:

• Manufacturing output versus goals
• Inventory outflows forecast
• Potential out of stock
• On-hand quantity
• Quantity in transit
• Quantity on orders
• Available stock
• Demand
• Inflow quantity
• Product count
• Weeks of supply
• Production plan forecast (actual vs. target)

The solution was also designed to be scalable to account for future needs and company growth. The client’s users and IT team received training and documentation to enable continued success with the solution.

If you’re struggling to incorporate JDE or other data types into your reports or BI tool, 2nd Watch’s manufacturing data and analytics consulting team can help. Contact us for a no-cost, risk-free manufacturing data strategy session. These sessions will help you build a roadmap to your future state, including time, costs, and technologies involved.