Multi-Branch Financial & Inventory Business Intelligence Engine
Client: Multi-branch retail & commercial hospitality brand with 8 locations across Lebanon
Unified 8 disparate point-of-sale systems and accounting databases into a single real-time executive dashboard with automated daily margin reports and inventory alerts.
The Operational Challenge
Executive management spent up to 3 days at the end of every week consolidating inconsistent Excel files from 8 branch POS systems, food supply vendors, and separate payroll spreadsheets. By the time consolidated reports reached the board, the financial data was outdated, leaving leadership unable to quickly identify inventory waste, theft, or margin collapse at underperforming branches.
The Quantology Tech Solution
Quantology Tech developed an automated data engineering pipeline and custom executive BI dashboard. Utilizing Apache Airflow and PostgreSQL, automated extractors query each branch database at scheduled intervals, clean and normalize multi-currency transactions, and calculate real-time gross margins, shrinkage, labor cost percentages, and vendor cost drift. An executive web dashboard with role-based access allows C-suite leaders and branch managers to monitor live performance on iPads or laptops with drill-downs down to the individual SKU level.
System Architecture & Technical Design
Automated distributed ETL pipeline orchestrated by Apache Airflow running in isolated Docker containers. Secure extractors connect to 8 distinct branch POS databases over private WireGuard tunnels. Extracted records are normalized into a unified PostgreSQL dimensional warehouse. A high-performance FastAPI microservice serves pre-aggregated analytics to a React frontend powered by custom D3.js charts.
Engineering Implementation & Rollout
Delivered over 6 weeks across 3 milestones: Milestone 1 mapped schema differences across legacy POS versions across all 8 branches; Milestone 2 developed the automated ETL extraction and currency reconciliation scripts; Milestone 3 designed the responsive executive dashboard with role-based access controls and automated morning WhatsApp sales notifications.
Performance Benchmarks & Security Hardening
Operational Performance: Complex analytical queries across 1.2 million annual transaction rows compute in under 450 milliseconds. The scheduled morning data synchronization finishes in under 8 minutes across all 8 locations.
Security & Data Governance: Branch databases are queried using strictly read-only credentials over encrypted WireGuard VPN tunnels. The executive dashboard enforces multi-factor authentication and role-based data masking preventing unauthorized branch-to-branch data viewing.
Field Engineering Lessons Learned
POS clock drift on physical branch machines initially created skewed hourly foot-traffic metrics. Introducing automated NTP network time synchronization across local branch POS hardware prior to extraction permanently resolved timestamp discrepancies.
Measurable Outcomes
- Replaced 3 days of manual weekly spreadsheet reconciliation with an automated 10-minute morning dashboard review.
- Identified cross-branch inventory variances within 24 hours, reducing food and stock waste by 32% across all 8 branches.
- Provided leadership with instant multi-currency margin visibility (USD and LBP), protecting profitability during inflation fluctuations.
- Branch managers receive automated evening WhatsApp summaries of daily sales versus targets, driving healthy branch competition.
“Before Quantology Tech, we were driving our business looking through the rearview mirror. Now I wake up every morning with total clarity on exact margins, branch sales, and inventory turns across all 8 locations.”
