• Outcome

    This comprehensive solution enabled Supply Chain department to efficiently monitor and manage their stock, significantly reducing the risk of product wastage. The Power BI reports provided clear, actionable insights, helping the planning department make informed decisions and optimize their supply chain operations.

    This project not only demonstrated our technical prowess but also our ability to understand and solve complex business challenges through data and analytics.

  • Objective

    Supply chain faced the critical challenge of managing products with Best Before Dates (BBD). Each product had a specific timeframe before it could no longer be sold to third parties. To avoid potential losses, it was imperative to track the "Sell by Maximum Time" (SMT) date, the latest date a product could be sold. Our goal was to develop an algorithm that identifies the count of products at risk of exceeding their SMT date based on current stock and sales forecasts. This calculation had to consider various factors including product batches, storage locations, and stock types.

  • Solution

    We developed a sophisticated, automated pipeline to address this challenge. The solution included the following components:

    1. Algorithm Development: Created an algorithm to iteratively calculate the number of products at risk, accounting for sequential sales by batches, locations, and stock types.

    2. Automated Data Collection: Implemented a process to automatically gather stock and forecast data (daily and weekly) from various sources including customer ERP and users xlsx files.

    3. Data Processing and Enrichment: Used Informatica Intelligent Cloud Solution to process and enrich the data with risk information, before storing it in Snowflake DB.

    4. Power BI Reporting: Translated the processed data into comprehensive Power BI reports that included:

      • Risk values for each product and batch / Heads-up alerts for business users
      • Key dates (BBD, SMT, Production Date), locations, and statuses.
      • Forecast values, stock age, and potential losses
    5. Multi-Unit Measure Display: Ensured all data related to stock, risk, losses, and forecasts were displayed in four units of measure: pieces, tons, kilograms, and pallets.

    6. Frequent Updates: Set the process to automatically recalculate risk every three hours, ensuring up-to-date information.

  • Technology

    The solution is built on the use of the main platforms of the customer:

    Data processing:

         Informatica ICS

    DWH: Snowflake

    Front End: PowerBI

How does it work?

1
Data Sources
  • cloud databases
  • on-premise database
  • Excel files with "pretty" formatting
  • csv files
2
Python Script
  • processing Excel files with formatting
  • conversion to *.csv
3
Linux Pipeline
  • Data filtering
4
Staging
  • Staging schema data load
5
Aggragation / MDS
  • Data aggregation at the month level
  • Populating Intermediate Fact Tables
  • Loading MD datamarts
  • Data transfer to MDS
6
MDS
  • MD Enrichment byuser
  • Enter MD required for calculations: courses, units. conversion reates.
  • Launch dataflow continuation
7
DWH Loading
  • Calculation and loading of data marts from fact tables and MDS user data
  • Recording the download log and the errors that occurred with the reasons
8
PowerBI
  • PowerBI dataset refresh