Business Intelligence at Scale use case

The use case: Analyzing the impact of dynamic weather conditions on static inventory management for perishable goods

Broger Foods is a mega grocery retail chain, with multiple outlets across the country, selling 100+ perishable goods. Each year, Broger Foods suffers losses ranging between 30 and 45 million dollars due to inventory shrinkage. Damages and spoilage of perishable items in warehouses has been identified as a key contributor to the shrinkage. The challenge Broger faces is the relatively static inventory management system for perishable goods. Stocks of fresh produce and food rot or expire in the warehouses due to intermittent demand and over stocking. Understocking is not a viable option due to the competitive nature of the industry, leading to customers turning to Hole Foods, a rival retail chain gaining popularity.

The CFO of Broger Foods, Roger Walters has tasked their VP of Analytics, Richard, to look into this issue and help solve Broger's supply chain management problem.

Key business questions

• How can Broger Foods optimize its inventory management system to reduce revenue loss and prevent food wastage while not loosing customers to its competitors?
• How can the BI team at Broger Foods deliver insights at scale?

Solving the problem

Richard formulates two hypotheses to analyze the sales trends. His first hypothesis is based on population density of a location and the second one is based on the weather conditions. Upon delving deeper using ad-hoc analytics, Richard figured out that there is a strong correlation between demand for perishable items in each location and weather conditions in that location. To continuously improve Broger’s inventory management process, Richard plans to use the location-wise sales information present in Broger's CDP Public Cloud environment along with real-time weather information from a weather data provider.

To deliver the insights at scale, Richard calls for a meeting with Syd (Streaming App Developer), David (Data Engineer), and Nick (Data Analyst) to explain his model and what he needs to implement this new inventory forecasting model.

To build a streaming pipeline, Richard asks Syd to capture live weather data from OpenWeather and ingest it into CDP. Syd sets up a data flow that ingests data into CDP in real time.

He then asks David to build an unified data pipeline that aggregates data from each stream into two separate tables:

• Current weather data. This table captures temperature, humidity, precipitation, and pressure.
• Historical data for 5 previous days.

David’s pipeline will be triggered to process and aggregate the freshly ingested data every hour and update the Optimized Row Columnar (ORC) or Parquet tables in CDW every hour.

Now, Nick is tasked to write queries that averages out the weather data from both the weather tables and combine it with the location-based sales data for additional analysis. He also has to build a dashboard that showcases the variance between item sales and temperature at a given store location on a daily basis. The dashboard also shows a 7 day forecast of sales. The sales forecast is done on Monday and Friday every week.

Key personas in this use case

• Ingest Developer – Develops and explores the data ingest streams
• Data Engineer – Prepares the data pipelines, optimizes and aggregrates data for use by Analysts
• Data Analyst – Analyzes data using queries and by building reports and dashboards
• Business stakeholders – Consume analyzed data and visualizations

Success criteria

Rapidly develop and build data streams to deliver business insights at scale by enabling self-service DataFlow development, Data Engineering, Data Warehousing, and Business Intelligence reporting and dashboarding capabilities.