Case study · Retail pharmacy · Supply chain
Improved last-mile supply chain analytics for a pharmacy chain by migrating to AWS.
Retail pharmacy and wellness · 24x7 network
SQL Server and PowerBI could not carry 1.5TB of sales history across 250+ tables. We built the warehouse underneath it.
30 min
Data processing, down from 6 hours
80%
Improvement in decision-making
The opportunity
A catalogue of 100,000+ products, and no single place to analyse it.
This omnichannel pharmacy chain runs a hyperlocal network of 24x7 stores carrying over 100,000 products. Its analytics ran on SQL Server and PowerBI-a combination that was expensive and limited in what it could actually compute.
The data was not consolidated, so large-scale analysis meant working across sources. And the setup was inadequate for the workload: 1.5TB of historical sales data, 1GB arriving incrementally, across more than 250 tables. Sales and inventory decisions were made without the picture they needed.
SQL Server and PowerBI at cost
The incumbent stack was expensive and analytically limited.
Data was not consolidated
No single source meant large-scale analysis worked across systems.
Inadequate for the workload
1.5TB historical, 1GB incremental, 250+ tables exceeded what the setup could carry.
Decisions made without the picture
Sales and inventory choices lacked the analysis to inform them.
The impact
Six hours of processing became thirty minutes.
Redshift as the warehouse
A scalable warehouse designed for complex analytics replaced a database being used as one.
ETL across every source
A pipeline extracts, transforms for consistency and loads from the disparate systems into one place.
PowerBI kept, repointed
The existing reporting tool now queries Redshift, so the team's dashboards did not have to be relearned.
Queries optimised
Workflow and query tuning is what took processing time from six hours to thirty minutes.
The stack
What it runs on.
- Warehouse
- Amazon Redshift
- Pipeline
- ETL across store databases, sheets, CRM and transaction systems
- Reporting
- PowerBI, repointed to Redshift
- Migrated from
- SQL Server, 1.5TB history across 250+ tables
The road ahead
The warehouse is in. Next is what runs on it.
With consolidated data and optimised processing, the same foundation supports ML models and dynamic reporting across sources that previously could not be analysed together.
- 01
ML models on consolidated data
- 02
Store-level inventory analytics
- 03
Wider source coverage
Talk to us
Bring one process. Leave with an outcome architecture.
30 minutes on one function, mapped to the architecture behind it. No slides.