CASE STUDY · AUTO GLASS INDUSTRY · CANADA
Complex reporting to structured reporting system 1,100 users use every day
An auto glass platform was capturing every job, part and invoice in its own CRM — and none of it was answering business questions. I designed and built the analytics layer that now sits inside their customer portal, measuring the full chain from vehicle lookup to paid invoice.
1,100+
end users on the client portal
20+
reports on one shared data model
300
tables modelled into one structure
3 years
of history kept current daily
~2×
faster refresh after model tuning
Built with Azure SQL · SQL ETL and views · Power BI semantic model · Power BI Service · Power BI Embedded · Dynamic row-level security · Power Automate · Incremental refresh · ALM Toolkit
Role Solution architect and delivery lead — requirements, architecture, build, client communication, and leading the developer and ETL engineer.
PROJECT AT A GLANCE
Business problem I solved
Technology stack
Industry
Auto glass industry — CRM platform used across the network
Location
Canada
Project Type
Embedded analytics layer inside the client’s customer portal (Power BI Embedded)
My Role
Solution architect and delivery lead — requirements, architecture, build and client communication, leading the developer and ETL engineer.
Technology
Azure SQL · SQL ETL and views · Power BI semantic model · Power BI Service · Power BI Embedded · Dynamic row-level security · Power Automate · Incremental refresh · ALM Toolkit
Scale
~300 tables modelled into one Power BI semantic model · 20+ reports
Users
1,100+ end users — branch and franchise owners, operations managers, finance, head office
SECTION 1 · WHAT BUSINESS PROBLEM I SOLVED
The business was running well. It just could not see itself.
Everything needed to answer a management question was already being captured — it simply had no way out of the system that captured it. The company operates its own CRM platform for the auto glass industry. Every vehicle lookup, glass part search, quote, booking, job, customer message, invoice and refund passes through it. That system was built to run the business day to day, which it did properly. It was never built to explain the business, and that gap had started to cost real money.
Data was being collected, not used
Years of operational history sat in the platform database. Getting an answer out of it meant asking someone to write a query or export a file, so most questions were never asked at all.
Reporting depended on people, not systems
Numbers were assembled manually in spreadsheets. That work consumed skilled staff every month, it could not scale as the business grew, and it stopped entirely whenever the person who did it was away.
The same number meant different things
Because each report carried its own logic, two people could answer the same question differently and both be defensible. Management meetings were spent reconciling figures instead of deciding what to do about them.
Information arrived too late to act on
By the time a monthly figure was circulated, the period was closed. Problems were confirmed rather than caught — the cost had already been incurred before anyone saw it.
Customers were asking for reporting the platform could not give them
The people using the CRM wanted their own performance visible inside the tool they already worked in. Sending them a file was not an answer, and building a separate reporting product for each customer was not affordable.
What I was actually asked to deliver
Not a dashboard. A reporting capability that becomes part of the product: every customer signs in to the portal they already use, sees only their own business, and gets numbers that are current, consistent and trusted — with no separate login, no export, and no analyst in the middle.
SECTION 2 · WHICH TECHNOLOGY I USED
Every component was chosen for a business reason
Not because it was new. The test each one had to pass: does it still work when there are thousands of users and more customers next year than this year?
| Layer | Technology | Why it is there |
|---|---|---|
| Source | In-built CRM platform | The system of record. Jobs, customers, parts, technicians and invoices are captured here as work happens. |
| Storage | Azure SQL Database | All CRM data lands in structured tenant tables, designed from day one so additional customers can be added without redesign. |
| Preparation | SQL ETL, stored logic | Raw operational data is cleaned and reshaped into reporting-ready tables. Business rules live here once, where they can be tested. |
| Reporting layer | SQL views | A stable, controlled surface between the database and the reports. Underlying tables can change without breaking anything downstream. |
| Data model | Power BI, import mode | Around 300 tables modelled into one governed structure with agreed calculations. One definition of every number, used by every report. |
| Security | Row-level and dynamic RLS | Each user sees only their own data, enforced inside the data itself rather than hidden in the interface. |
| Publishing | Power BI Service | Central home for the model and reports, with controlled promotion from development to production. |
| Delivery | Power BI Embedded, service principal | Reports appear inside the client’s own portal. End users need no Power BI licence and never leave the product they know. |
| Freshness | Incremental refresh, Power Automate | A rolling three-year window is kept current daily, using modification dates so only changed records are reprocessed. |
| Quality | Dedicated validation report | An internal report that checks daily values across every published report, so errors are found before users find them. |
| Change control | ALM Toolkit | Model changes are compared and deployed deliberately, not by republishing and hoping. |
| Delivery process | Trello | Requests, build work and releases tracked visibly so the client always knows what is in progress. |
| Next platform | Microsoft Fabric, OneLake | Reporting is being migrated onto OneLake to consolidate storage, reduce duplication and support larger data volumes. |
SECTION 3 · HOW I BUILT IT — THE METHOD
Removing the three ways reporting projects fail, in order
Reporting projects fail for predictable reasons: the wrong questions get answered, the numbers do not tie to reality, or nobody trusts the result. The method below exists to remove those three risks in order.
The approach
I start from the decision, not the data. Before any table is touched, I establish what someone is going todo differently once they can see a number. That single discipline is what stops a project from producing twenty reports that look impressive and change nothing.
How the data travels
Data moves through six deliberate stages between the CRM and the screen. Each stage exists so that a failure in one place does not become a failure everywhere, and so that a change at the source does not break what users see.
What the reports actually measure
Reports are only worth building if they map to how the business makes money. In auto glass that chain is unusually traceable: someone looks up a vehicle, that becomes a search for a specific glass part, that becomes a quote, a booking, a fitted job, an invoice — and sometimes a return, a refund or a warranty claim. Almost every commercial question lives somewhere on that chain, so that is how the reporting was organised.
Vehicle and part lookups
What is measured
Vehicle lookup volume by make, model and year; glass part lookup volume; lookup value; searches that returned nothing
What it tells the business
Where real demand is, before it becomes a sale. Failed lookups are the most useful number here — they are demand the business could not serve, and they point straight at catalogue gaps and stock decisions.
Sales funnel
What is measured
Lookup → quote → booking → job completed → invoiced, with drop-off at every stage
What it tells the business
Exactly where revenue is being lost. A business can be busy at the top of the funnel and still be leaking money at one specific step.
Sales category
What is measured
Revenue and volume split by category — windscreen, side and rear glass, repair versus replacement, calibration, accessories
What it tells the business
Which work is genuinely worth taking. Volume and margin rarely sit in the same category, and the split is invisible without this view.
Upsell
What is measured
Upsell attach rate, upsell revenue per job, attach rate by branch and by staff member
What it tells the business
Where added-value selling is working and where it is being forgotten. The gap between the best and worst performer is usually the fastest money in the business.
Returns and refunds
What is measured
Return rate by part, supplier and branch; refund value and reason; credit notes as a share of revenue; breakage and rework
What it tells the business
What is quietly eroding margin after the sale is recorded. Grouping by reason separates a supplier quality issue from an ordering mistake from a fitting problem.
Customer messaging
What is measured
Message volume by category — quotes, booking confirmations, reminders, follow-ups; response rates and timing
What it tells the business
Which communication actually converts and which prevents no-shows. Often the cheapest fix available to a branch.
Risk and theft detection
What is measured
Parts consumed without a matching job; jobs completed without recorded parts; invoices edited or voided after completion; unusual discount and refund patterns by user; stock variance by branch
What it tells the business
Losses that never appear on any sales report. Exceptions are surfaced automatically so they are investigated as they happen rather than discovered at stocktake.
Operations
What is measured
Job volume and cycle time, technician utilisation, first-time completion, rework and warranty rate, mobile versus in-shop mix
What it tells the business
Whether capacity is being used well, and which work is coming back at the company’s own cost.
Making one system serve everybody
The hardest requirement was not the reports. It was that more than a thousand people needed to use the same reports and each see a different slice of the business — without building a separate system for each of them. I solved this with dynamic security. Instead of creating a report per customer, there is one model and one set of reports. When a user opens the portal, their identity is passed through, and the data filters itself to what that person is allowed to see before anything is displayed. Adding a new customer or branch is a data entry task, not a development project.
Keeping the data current, and proving it is right
Two things destroy confidence in a reporting system: stale data, and one wrong number. Both are handled automatically rather than by someone remembering to check. A three-year rolling window of history is kept live. Each day, an automated process identifies only the records that changed — using their modification date — and refreshes just those. Reloading three years of history every night would be slow and would put unnecessary load on the source system; reloading only what moved takes a fraction of the time. Alongside the business reports sits a validation report that checks daily figures across every published report, so a discrepancy is found by the system rather than by a customer.
Where this is heading
The platform is now moving onto Microsoft Fabric and OneLake. The reason is practical rather than fashionable: as the number of customers grows, holding one copy of the data that every report reads from is cheaper and simpler than maintaining several. The reporting layer is being migrated so that storage is consolidated, duplication is removed, and the same platform can carry substantially larger volumes without a rebuild.
SECTION 4 · WHAT THE BUSINESS GOT OUT OF IT
Not how many reports it produces — how many decisions get made faster
Decisions made on evidence instead of instinct
Managers can see which work, which sites and which customers actually perform — and act on it while the period is still open. Discussion moved from arguing about whose figure was right to deciding what to do about it.
Outcome · faster, better-founded decisions
Reporting became a system, not a job
The monthly assembly of spreadsheets stopped. Reports are produced by the platform, on schedule, in the same format every time. Skilled staff moved off report production and onto work that uses the reports.
Outcome · recurring manual effort removed
Numbers users can rely on
Every figure comes from one model with one agreed definition, and is checked daily by an automated validation report before anyone sees it. Trust is what makes a reporting system get used; without it, people quietly go back to their own spreadsheets.
Outcome · one version of the truth, verified daily
Data current enough to act on
A rolling three-year history refreshed daily means users are looking at the business as it is now, not as it was at the end of last month. Problems surface while they can still be corrected.
Outcome · near-current operational visibility
Hidden opportunities became visible
Once lookups, sales, messaging and financial data sat in the same model, patterns appeared that no single screen could have shown. Vehicle and part searches that returned nothing turned out to be measurable lost demand, pointing straight at catalogue and stock gaps. Upsell attach rates varied widely between branches and staff, which made the gap closable. Sales category analysis separated the work that carried volume from the work that carried margin. Funnel drop-off identified the exact step where quotes were going cold.
Outcome · unserved demand, upsell gaps and margin leakage found in existing data
Losses that no sales report would ever show
Returns, refunds and credit notes are analysed by reason, part, supplier and branch, which separates a supplier quality problem from an ordering error from a fitting fault. Alongside that, exception metrics flag parts consumed without a matching job, invoices edited after completion, and unusual discount or refund patterns by user. Issues surface as they happen rather than at stocktake.
Outcome · shrinkage, rework and refund leakage brought into view
Analytics became part of the product
Reporting now sits inside the client’s own portal for 1,100+ users. It is something the platform offers its customers rather than something it apologises for lacking — and it scales to new customers without new development.
Outcome · a capability that grows with the business
Faster and cheaper to run
Model and query tuning roughly halved refresh time, and incremental refresh means the system reprocesses only what changed. The platform carries more users and more data without a matching rise in running cost or effort.
Outcome · ~2× faster refresh, lower ongoing load
SECTION 5 · SUMMARY
In business terms, and in technical terms
In business terms
- A CRM that recorded everything and explained nothing now drives daily management decisions.
- Manual monthly spreadsheet reporting was replaced by a system that produces itself.
- 1,100+ users across branches, regions and head office each see their own business, inside the portal they already use.
- Every number carries one agreed definition, checked automatically before anyone reads it.
- Operational and financial data were joined for the first time, exposing margin and efficiency opportunities that neither system could show alone.
- Reporting follows the full commercial chain — vehicle and part lookups, funnel conversion, sales category, upsell, returns and refunds, customer messaging, and exception metrics for loss and theft detection.
- New customers and branches are onboarded as a configuration change, not a development project.
In technical terms
- CRM data landed in Azure SQL tenant tables, structured for a multi-customer future.
- SQL ETL into clean tables, then purpose-built reporting tables, exposed through views.
- ~300 tables modelled into one Power BI import-mode semantic model with shared calculations.
- 20+ reports on that single model, covering lookups, funnel conversion, sales and category performance, returns and refunds, messaging, operations and risk exceptions — published through Power BI Service.
- Dynamic row-level security enforcing per-user data access at scale.
- Power BI Embedded with service principal authentication, delivered inside the client portal.
- Incremental refresh over a rolling three-year window, orchestrated by Power Automate using modification dates.
- Automated validation report covering daily values across all reports.
- ALM Toolkit for controlled model deployment; Trello for delivery tracking.
- Migration in progress to Microsoft Fabric and OneLake.
My role. I built the initial platform and then led its growth — owning requirements, solution architecture, the data model and client communication end to end, while leading an internal Power BI developer and a dedicated ETL engineer.
What this project demonstrates. Not that I can build reports. That I can take a system built to run a business, turn it into something that explains the business, and deliver it to a thousand people in a form they will actually use — securely, accurately, and without the cost growing every time a customer is added.
NEXT PROJECT
Financial Reporting Platform
Germany · Financial Services