• Industry Eyewear and eye care retail
• Scale 150+ stores, 150+ report users, millions of rows of sale-level data
• Engagement Full reporting build plus ongoing platform maintenance, since 2024
• Stack Power BI, Power BI Embedded, Microsoft Fabric, SQL, Azure Runbooks

A franchise network with no shared view of its own performance

A chain of more than 150 stores generates a great deal of operational data and very little visibility, unless someone builds the layer that turns one into the other.

The client needed three different groups to see three different things. Store and regional managers needed to know whether a store was hitting budget. Someone needed to see, before it became a problem, which stores were sitting on ageing stock and which were about to sell out of a fast mover. And operating partners needed to be assessed on how they actually run a practice.

That last one was the hard part. The client does not judge a practice on turnover alone. Their view of a well-run store covers commercial performance, clinical quality and patient outcomes, operational leadership, how the team is developed, and whether the partner behaves like an owner. Turning that into something a report can produce, consistently, across 150+ stores, is a different problem from building a sales dashboard.

The data made it harder still. The business logic is genuinely complex, the data arrives at several different aggregation levels, and the performance figures that matter most sit at the level of the individual sale: millions of rows of transaction-level data. Any reporting layer built on that has to resolve grain correctly or it quietly returns the wrong number.

There was a commercial constraint on top. Reporting for 150+ stores means a lot of concurrent users, and the platform running it had to stay affordable.

What we built

The engagement covers the full reporting estate, from the SQL underneath to the report surface inside the POS, plus ongoing maintenance of the platform it runs on, all part of our Power BI and data engineering services.

Doing the heavy work in SQL

The client's business logic is complex enough that leaving it inside report measures would have made the model slow and hard to maintain. So as much of the data preparation and business logic as possible is pushed upstream into SQL, which keeps the semantic model lean and moves the expensive work off the Power BI load.

This is also what makes the multi-grain problem tractable. Sales data at transaction level, targets at store and period level, and inventory at stock-item level all have to reconcile. Resolving that in SQL with a defined grain per fact table is more reliable than resolving it in DAX after the fact.

Inventory and budget reporting

Inventory tracking. Ageing stock, low stock, and high-selling lines that need restocking, with email alerts going to each store rather than waiting for someone to open a report. It also tracks the stock takes each store performs, including updates and recounts, so inventory accuracy itself is visible.

Revenue against budget. Built for store managers and regional managers to see each store's revenue against its budget, whether it is breaking even, over or under. Users can leave comments and feedback directly against the numbers, so the explanation for a variance sits next to the variance.

The operating partner balanced scorecard

The scorecard assesses every operating partner across five weighted pillars, each carrying equal weight:

1. Financial and commercial performance
2. Clinical excellence and patient outcomes
3. Operational and business leadership
4. People leadership and team culture
5. Strategic fit, ownership mindset and professional conduct

Beneath those sit fifteen sub-sections and more than forty-five individual criteria, from turnover growth and remake rates through to coaching, community engagement and brand representation. Targets are set per store rather than applied uniformly, because no two practices in a 150-store network are alike.

Each measured criterion carries a traffic-light status driven by growth trends rather than single data points, tracked on a rolling and year-to-date basis, so one strong month cannot turn a declining store green.

Ranking answers a different question from status, so each store is also placed against the rest of the network: top 10%, top 25%, middle of the group, or bottom 25%. A store can be hitting its own target and still sit in the bottom quarter, and a regional manager needs both numbers before deciding where to spend the week.

Building it started with a line-by-line pass through every criterion with the client's team, asking three questions of each: can this be measured from data at all, what exactly is the definition, and where does the threshold sit. "Maintains staff costs within policy" is a reasonable thing to want and an ambiguous thing to report on, until someone decides what the policy is and what the number is compared against.

That pass produced the most useful finding in the project. A meaningful share of the criteria cannot be derived from transactional data and never will be. Whether a partner coaches their team well, or represents the practice properly to suppliers, is a judgement a regional manager makes.

So the scorecard was designed to hold both. Roughly two-thirds of the criteria are calculated automatically from data and tracked over time. The rest are captured as structured qualitative input from the people qualified to give it, scored on the same scale and weighted into the same result. The alternative was either a scorecard that only measured what happened to be easy, or a scorecard nobody could complete.

Delivered inside the point of sale, with row-level security

The scorecard is embedded directly into the client's point of sale system, so the people who need it reach it where they already work rather than through a separate login.

It carries heavy row-level security. An operating partner sees their own stores. A manager sees theirs. Access is enforced in the model rather than by convention, which matters more than usual here, because a scorecard that ranks people against each other is only workable if everyone trusts who can see what.

It also resolves below store level. Where a store has more than one optometrist, performance is reported per optometrist against the same criteria, so it is clear who is performing and who needs support.

Keeping 150+ users on a low-tier capacity

The reporting runs on one of the lower Fabric capacity tiers, which sets a hard ceiling on capacity units. Serving 150+ users on that tier only works if the reporting is built for it.

Measures and models are optimised for low CU consumption, which is where the SQL upstreaming described above pays for itself commercially as well as architecturally. Alongside that, Azure Runbooks handle scheduled pause and resume: the capacity runs for two hours in the morning and two in the afternoon, around four hours a day instead of twenty-four.

What changed

• 150+ stores on one reporting estate. Reporting across the full network, from individual store performance up to group level, used by 150+ operating partners, store managers, regional managers and executives.
• 45+ criteria, 5 weighted pillars. Operating partner performance assessed on commercial, clinical, operational, people and conduct measures rather than turnover alone: roughly two-thirds calculated automatically, the rest as structured qualitative input.
• Around 4 hours of capacity a day instead of 24. Scheduled pause and resume on the Fabric capacity, so it runs when the business needs it rather than continuously.
• Ranked across the network. Every store placed against the rest of the group, with traffic-light status on growth trends.

The reporting is used across store managers, regional managers, franchise owners and C-level executives, which means one set of definitions is shared by everyone from the shop floor upward. Performance conversations start from the same numbers rather than from competing spreadsheets.

The scorecard changed what those conversations are about. A partner is no longer assessed on whichever figures were easiest to pull that quarter, and a strong commercial month no longer hides a weak clinical one. Ranking each store against the network added something the budget report never could: a store can hit its own target and still sit in the bottom quarter of the group, which is a different conversation from missing budget and a more useful one. Where a store has more than one optometrist, the same criteria resolve to the individual, so support goes to the person who needs it rather than to the store average.

What the client's team mention most often is speed. Performance that used to be assembled by hand is read at a glance, and the hours that went into pulling figures now go into acting on them. The inventory reporting is where the effect is most visible in the stores themselves: ageing stock gets flagged early and is either sent back to the warehouse or sold at promotional prices, so it stops taking up space on a shelf.

What the client says

"I'm happy to recommend Andrei's firm to any company wanting to build an insightful business reporting suite while staying on plan, within budget and in compliance with data security guidelines."

CTO

If you are reporting across a lot of locations

Multi-site retail has a recognisable shape: performance data at transaction level, targets at store level, stock moving independently of both, and a lot of people who each need to see only their own slice. Tell us what your network looks like and we will tell you what the reporting layer takes to build and what it costs to run. Book a call or see our services.