Four years of data, queryable in real time
A chain of 100+ liquor stores, online and physical, ran its analytics directly against a transactional MS SQL database that was never built for it. FireAI migrated four years of GB-scale history into ClickHouse, set up a live sync that keeps it current to within five minutes, and built ten dashboards plus store open/close tracking across every location.
- 100+
- stores in one analytics layer
- 10
- analytics dashboards delivered
- 4 years
- of GB-scale history loaded
- 5 min
- live data sync
Trusted by 200+ orgs to boost business insights.
A liquor retail chain with 100+ stores in India ran analytics directly against a transactional MS SQL database that struggled with years of history across every location. FireAI migrated four years of GB-scale data into ClickHouse, set up a dual sync (every five minutes for recent data, twice daily for the full financial year), and built ten analytics dashboards, a store open/close tracking dashboard, and SKU-velocity analytics across 97 individual stores.
Years of data, a transactional database, and no fast way to analyse it
The chain held several years of transactional history across 100+ stores and an online channel, all in a Microsoft SQL Server database connected to its POS and ERP software. That database was built to record transactions, not to run heavy analytics over millions of rows, so complex aggregations across the full history were slow, and trend analysis across years of data was hard to do without degrading performance.
Keeping dashboards current meant manual data exports, and visibility into day-to-day store operations leaned on manual reporting from individual branches. Leadership needed analytics that ran orders of magnitude faster, stayed current on their own, and gave every business function, sales, inventory, category, customer, finance, one place to look, without anyone exporting data by hand.
- Analytics ran directly against a transactional MS SQL database not built for heavy aggregation
- Complex aggregations across millions of rows and years of history were slow
- Keeping dashboards current meant manual data exports
- Day-to-day store operations relied on manual reporting from individual branches
- No single, fast analytics layer spanning all 100+ stores and the online channel
Why the transactional database fell short
Running analytics on the same database that records transactions held back both speed and freshness, and left store operations dependent on manual reporting.
An OLTP database doing analytics work
The MS SQL database was built to record transactions, so complex aggregations across millions of rows and years of history were slow and risked degrading performance.
Manual exports to stay current
Keeping the numbers up to date depended on manual data exports, so dashboards were only as fresh as the last person to pull the data.
Store operations reported by hand
Visibility into whether branches were opening and closing on schedule relied on manual reporting from individual stores, with no central, automatic view.
A ClickHouse analytics layer that stays current on its own
FireAI migrated the data into a purpose-built analytics database, set up a live sync, and built dashboards across every business function plus store operations.
Four years of history now sits in ClickHouse, kept current automatically, with ten dashboards over it and a dedicated view of store operations. A new store-level phase takes the same analytics down to the individual branch.
MS SQL → ClickHouse migration
Four years of GB-scale transactional history was migrated from MS SQL into ClickHouse, where complex aggregations across millions of rows run in near real time and trend analysis across years of data holds up without degrading.
A dual live sync
Recent data syncs every five minutes for the past seven days, and the full current financial year syncs twice daily, so operational dashboards stay current to within minutes and year-to-date analytics stay accurate, with no manual exports.
Ten analytics dashboards
A suite spanning overview, branch performance across all 100+ locations, category, product and SKU, sales trends with drill-down, customer insights, inventory, credit card details, receivables, and branch-level profit and loss.
Store open/close tracking
A dedicated dashboard records each branch's daily opening and closing times, so operations can see whether stores run on schedule, keep a historical log for compliance, and spot branches with irregular patterns.
Store-level analytics across 97 stores
A new phase brings tailored tracking to 97 individual stores, with store-level inventory visibility, location-specific sales trends, and automated identification of fast- and slow-moving SKUs to sharpen local merchandising and procurement.
Why they chose FireAI
- It moved analytics off the transactional database onto ClickHouse, built for aggregation at GB scale
- The dual sync keeps dashboards current automatically, so no one exports data by hand
- One analytics layer covers every business function, then drills down to the individual store
From a transactional database to a real-time analytics layer
Reported as the capability delivered rather than as financial outcomes.
The chain moved from running analytics on a transactional database to a purpose-built layer where complex aggregations across millions of rows return in near real time. Operational dashboards stay current to within five minutes and year-to-date figures stay accurate with twice-daily refreshes, all without a single manual export. Ten dashboards give every business function, sales, inventory, category, customer, finance, one place to look across all 100+ stores, the store-tracking dashboard gives operations a central, automatic view of branch open and close times, and the new store-level phase takes inventory, sales trends, and fast- and slow-moving SKU detection down to 97 individual locations. End to end, it is a full-stack analytics capability, from raw data migration and live sync to purpose-built dashboards.
How the engagement was delivered
Migration, live sync, then dashboards and a store-level phaseThe build moved from data infrastructure outward: migrate and stabilise the data, keep it synced, then layer the analytics and operational tracking on top, with a store-level phase following.
- 1
Migrate the history
Four years of GB-scale data was migrated from MS SQL into ClickHouse and maintained there, giving the analytics a database built for aggregation rather than transactions.
- 2
Keep it current automatically
A live sync from the POS/ERP-connected MS SQL database was set up, every five minutes for the last seven days and twice daily for the full financial year, removing manual exports.
- 3
Build the dashboards
Ten purpose-built dashboards and the store open/close tracking dashboard were developed and deployed across every business function and all 100+ locations.
- 4
Go store-level
A new phase extended the analytics to 97 individual stores, with store-level inventory, sales trends, and fast- and slow-moving SKU tracking for local merchandising and procurement.
Key takeaways
- Analytics over years of data across 100+ stores is a data-infrastructure problem, not just a dashboarding one
- FireAI migrated four years of GB-scale data from MS SQL into ClickHouse for near-real-time aggregation
- A dual sync keeps dashboards current to within five minutes with no manual exports
- Ten dashboards plus store tracking and a 97-store SKU-velocity phase span every function down to the branch
Who should consider FireAI?
Multi-store retailers running analytics directly against a transactional POS or ERP database, where years of history across many locations make aggregation slow and keeping dashboards current depends on manual exports or branch-level reporting.
Frequently asked questions
Move your analytics off the database that records your transactions
If you run analytics directly against a transactional POS or ERP database and years of history across many stores make it slow, FireAI can migrate it to a purpose-built layer, keep it synced automatically, and build the dashboards on top. Book a demo on your own data.
Want results like Liquor retail?
Book a demo