Behind every great cup of coffee is a complex supply chain. Every step in the process is critical, from sourcing and roasting to production and delivery. The best coffee companies rely on advanced supply chain analytics to consistently deliver fresh, high quality products to customers.

Data Clymer recently helped a major coffee roaster and retailer build a cross-functional supply chain dashboard that brings all their supply chain data together in one place. The project is a key component in driving the company’s business intelligence and analytics. It allows a variety of stakeholders to monitor key supply chain operations metrics in a single streamlined dashboard.

Supply Chain Dashboard

In this article, we’ll take a closer look at:

  • The coffee company’s supply chain analytics challenges and goals
  • Technical pieces involved in building the supply chain dashboard
  • Key outcomes and next steps for the ongoing initiative

Supply Chain Analytics Challenges

The coffee chain had been struggling with time-consuming and ineffective supply chain analysis. Different departments were all using their own Excel spreadsheets and managing data differently. The result: siloed data and inconsistent results that required manual verification and correlation. Everyone was reporting on the same data, but getting different results.

The company needed a streamlined, efficient way to analyze data so they could understand and take action on what was happening across their supply chain. After the COVID-19 pandemic caused major disruptions in business operations, they’d seen firsthand how critical it was to develop supply chain agility and resilience. Better supply chain analytics would give them the foundation they needed to support long-term business growth.

Supply Chain Analytics Goals

The company turned to Data Clymer to help them create a supply chain dashboard that would bring a number of different data sources into one consistent view.

One of the major goals was to develop a dashboard that would work for multiple different audiences. Executives wanted a high-level overview of major business KPIs like product turnover, while operations needed to drill down and get insights on the entire cycle.

Targeted business outcomes of the supply chain dashboard project included:

  • Faster time-to-insight for key supply chain metrics
  • Increased data-driven decision making across the business
  • Improved data quality, accuracy, and consistency
  • Reduction in the amount of time spent manually processing data

Behind the Build: Supply Chain Analytics Consulting

Architecture

The company’s modern data stack integrates best-in-class tools including:

The diagram below shows how these tools work together to ingest, store, model, and visualize data.
Modern Data Architecture

Supply Chain Dashboard Development

The Data Clymer team focused on five key areas to build the supply chain dashboard with Microsoft PowerBI:

  1. Information gathering: We began with stakeholder sessions, where we gathered information on who does what, when, and where. This information helped us identify which dimensions and facts were involved in the business process to eventually build robust data models and visualizations.
  2. Early feedback and validation: Once the most crucial components of the data models were built, we started working on the data visualization pieces. We knew how important it would be to get feedback from stakeholders and validate the data early in the process.
  3. Direct Query: We validated the data by using “Direct Query” in Power BI instead of “Import mode.” With Direct Query, you get what’s in the database instead of cached data that lives in Power BI. So you won’t need to worry about whether or not you refreshed your model when conducting data validations.
  4. Model migration: Once we determined the data was valid, we migrated the model we built in Direct Query to AAS. This included transferring DAX measures and configurations of the Power BI data model.
  5. Production: After many iterations with the stakeholders on data validation and report design, we finally put the Power BI report into production for use.

Data Modeling and Conversion Logic

One of Data Clymer’s key deliverables for the supply chain dashboard was the implementation of dynamic unit of measure (UOM) conversion logic in the Power BI data model. This would save users a significant amount of time by enabling them to easily switch between different UOMs without needing to check conversion rates.

For example, if an item had the transaction UOM of “Each,” but the user wanted to see it in “Case,” the conversion logic was already built into the model. The only thing the user has to do is to select “Case” from a dropdown option of the UOM slicer/filter in the report.

To accomplish this implementation, we combined data modeling in Power BI with complex Data Analysis Expressions (DAX) calculations, which is the process of creating calculated columns, measures, and custom tables in Power BI. This allows users to switch from the default UOM to another UOM so they can analyze the data further in one report.

Results and Next Steps

Data Clymer completed the first phase of the supply chain analytics dashboard in late 2022. This major milestone brought together a number of different data sources into one consistent supply chain view. It also united business stakeholders from various areas and built out automated processes that are easy to maintain.

Now, a solid foundation is set. Clean, organized data enables stakeholders across the business make faster, more informed business decisions. Stakeholders can access the metrics they need with speed, consistency, and accuracy.

Supply Chain Dashboard: Fill Rate Operations View
Best of all, the “one-stop” supply chain planning dashboard includes multiple views, so different audiences can easily see the metrics that matter most to them.

  • Executive view shows high-level KPIs. Charts built on time-intelligence calculations illustrate performance by specific time periods, such as YTD or by day, week, month, or quarter.
  • Operations view shows the entire cycle behind the high-level KPIs to illustrate components of manufacturing and delivery planning. The director of delivery planning uses this view to understand warehouse capacity and identify opportunities to save storage costs.
  • Details view is easily accessible from higher level bar charts. Team members can quickly drill down and investigate specific trends and activity.

And this is only the beginning. Next, the company will integrate manufacturing and distribution data to gain insights on labor productivity, quality, and safety. They also plan to integrate delivery logistics and customer experience data.

Thanks to Yuki Kakegawa for his contributions to this article.

Supply Chain Analytics Consulting

Maximize supply chain efficiency with reliable, actionable insights. Contact us today for supply chain analytics consulting. Our team has deep expertise in developing powerful supply chain dashboards to drive better decision making, cost savings, and operational efficiency.

You Might Also Like: