Customer Profile
The Company is an oil refining and marketing company in the Philippines. An industry leader and proud partner of the Filipino in nation-building, they supply nearly 40% of the country’s oil requirements. From their globally-certified depots, they bring world-class products to over 2,000 stations—the most extensive in the country and ensure a meaningful customer experience through quality service and exciting rewards (Value Card).
The Challenge
The Company was incurring huge operational costs and was unable to manage the inventory across the stations. Also, they did not have instrumentation for rich reporting in order to analyze the inventory of the stations and to keep a track of their maintenance as well.
As a result, they were looking to implement a BI solution over the cloud infrastructure that could help them overcome these challenges. It wanted a system to collect the data that was residing on the back office server of each station to be moved to a data warehouse on AWS so that they can do analysis and reporting on it. 30% of stations followed the barcode scanning process and the legacy systems that they used were around 8-10 years old which became a bottleneck for the company since the right insights were not available at the right time.
The Solution
BlazeClan conducted an exhaustive study to understand back office server software workflow and found out that-
- These local servers were located at the company’s petrol pumps and stored all the inventory and local data about the refilling station.
- They had no databases and primarily provided flat files as the reports became a big overhead while analysing the data.
- Approximately 12 TB of data per month was generated for all stations.
Once the workflow was understood, BlazeClan team of certified SAs set up the AWS infrastructure along with infrastructure for ETL processes and data warehouse. They created Data marts for Sales managers and convenient store managers and Data models for extracting POS data from Back Office Server and perform analytics like-
- Consolidation of bulk purchase at discounts on behalf of stores
- Understand what products are fast moving by area
- Build a standardised catalogue/inventory for each store
- Carve promotions to encourage sales
BlazeClan solution provided real-time insights for predictive maintenance of their inventory. It also helped:
- All the business stakeholders to explore the performance of each area and territory manager, on their hand-held devices, usually iPad or Tablet.
- Since the Sale In (inventory is given to the dealers) data was being captured in SAP, once the Fuel was sold to the dealer, the company could easily have a visibility on Sale Out (inventory sold out to the consumers) data, (that is, how much Fuel is sold) before ordering new quantity.
The Benefits
- Decision system empowerment: Business stakeholders were able to explore and analyse the performance of each station and its managers easily on their handheld devices.
- Real Time Insights: This helped the company in inventory management at each station.
- Cost Effectiveness: Proper planning of the inventory provided them operational efficiency and decreased their storage costs considerably.
- High Availability: The Company achieved a highly available system that could meet and cater to requests from the business.
- Scalability: They achieved the ability to scale the application as and when required by leveraging auto scaling and load balancing features of AWS.
Tech Stack
- EC2
- Redshift
- S3
- EMR
- SNS
- SES
- Dynamo DB
- Tableau
