When the Numbers Don't Tie

Finance couldn't trust the reports. Operations couldn't act on the data. Two consecutive vendor engagements had produced something that looked like business intelligence but didn't work — numbers that didn't reconcile, corrections that took weeks, and a reporting environment that had quietly become a liability instead of an asset.

TL;DR

• When two vendor engagements left a growing eyecare network with reports that didn't reconcile, we rebuilt the analytics platform from the ground up — a Microsoft Fabric lakehouse, a restructured 48+ table semantic model, and 30+ production reports delivered in two months — and engineered Python automation that eliminated $26K/year in licensing.
• More than 70 clinic managers now have real-time visibility into budgets, appointment flow, revenue cycle, and patient recall — working off numbers that finally tie.
• Three siloed source systems (a clinical database, a CRM, and a SharePoint layer) were converged into one governed reporting layer that refreshes hourly with an overnight full reload.
• The client owns the whole platform — version-controlled in Azure DevOps, promoted through governed Dev → Test → Prod pipelines, and built to be operated by their own team.

---

Table of Contents

• Overview
• The Challenge
• Data Foundation
• Reporting Suite
• Automation and Engineering
• Governance
• Outcomes
• What They Said

Overview

Finance couldn't trust the reports. Operations couldn't act on the data. By the time this client came to us, they weren't looking for a technology conversation. They had a direct and urgent ask: build something that actually works.

This is what it looks like when a growing organization outpaces its data infrastructure. More locations, more complexity, more decisions that matter — and a BI platform that couldn't keep up.

Over a multi-year engagement spanning 2023–2026, we rebuilt the entire analytics platform from the ground up. We architected a Microsoft Fabric data lakehouse, overhauled a 48+ table semantic model, and delivered 30+ production reports in two months — a scope most implementations would size as a multi-quarter initiative. We engineered Python automation that eliminated $26K in annual licensing costs, built a data extraction pipeline for a platform the vendor claimed couldn't be automated, and connected three previously siloed source systems into a single unified reporting layer. Today, more than 70 clinic managers across the network have real-time visibility into their budgets, appointment flow, revenue cycle, and patient recall programs.

The Challenge

The existing analytics environment had accumulated years of technical debt from two failed vendor engagements. The problems were structural — the kind that compound quietly until a month-end close, a board question, or a missed operational signal makes them impossible to ignore.

A semantic model in disarray. Roughly 30 auto-generated time tables had proliferated unchecked. Dozens of calculated columns performed transformations that belonged in the ETL layer. Relationship ambiguity made cross-filtering unpredictable. The model hadn't been built to a standard — it had grown organically until it broke.

Data that didn't reconcile. Discrepancies between reports and source systems had eroded trust across the operations team. Numbers that don't tie don't just create technical problems — they create organizational friction. When leadership can't verify a figure, they stop relying on it.

Three disconnected source systems. A clinical application database, a CRM, and a SharePoint-based business integration layer each held different pieces of the picture, with no unified layer connecting them. Cross-system reporting was either manual or nonexistent.

A meaningful adoption gap. Much of the organization's end-user population had limited prior exposure to interactive data visualization. Deploying a full Power BI dashboard environment to users who had never navigated one would have undermined adoption before it started.

This wasn't a case that called for incremental improvement. The foundation needed to be rebuilt.

Data Foundation

Before a single report could be trusted, the data architecture needed to change.

We designed and deployed a Microsoft Fabric Lakehouse from scratch, implementing a Bronze-Silver-Gold medallion architecture integrating all three source systems:

• Bronze — raw ingestion from the clinical application database, CRM, and SharePoint integration layer via full-load and CDC (Change Data Capture) incremental pipelines
• Silver — cleansed and deduplicated records, standardized types, and business-rule transformations executed in PySpark notebooks with primary key management
• Gold / Semantic Layer — consumption-ready data structured per Kimball dimensional modeling principles, optimized for Power BI

The semantic model overhaul ran concurrently:

• Eliminated ~30 auto-generated time tables and replaced them with a properly structured date dimension
• Removed dozens of calculated columns and relocated the logic to its appropriate ETL layer
• Restructured relationships across a 48+ table model — clarifying cardinality, enforcing referential integrity, and eliminating ambiguous joins
• Rebuilt the full DAX measure library with consistent naming, documented calculation logic, and no reliance on implicit context assumptions
• Implemented a parameterized query architecture and TMDL-based semantic modeling for long-term maintainability and peer-reviewable deployments

Data refreshes on the hour throughout the business day, with a full overnight reload every evening.

Reporting Suite

We delivered 30+ production reports in two months — a scope most implementations would size as a multi-quarter initiative, spanning five functional areas across the full doctor → location → region → market hierarchy.

Reports were organized across five functional areas:

Doctor Utilization & Compensation. Provider productivity, scheduling efficiency, utilization rates, and practitioner-level compensation analytics — designed for both individual doctor review and cross-location benchmarking.

Revenue Cycle. Billing adjustments, carrier payment tracking and reconciliation, claim analysis by payer, and transaction detail with full payment type and insurance breakdowns. Gave finance the visibility needed to manage billing operations with confidence.

Appointment Flow & Patient Recall. Appointment tracking and clinical throughput — plus a purpose-built Recall Dashboard that merged historical recall data, current recall data, and appointment records from three separate source systems into a single unified fact table. The prior platform vendor could not produce this view. We built it from the ground up, unlocking recall metrics the organization had never had access to before.

Retail Capture. Optical retail attachment rates, item-level performance, and conversion analysis by location.

Operations & Executive Dashboards. Multi-level rollup views for regional and executive leadership with consistent KPI definitions and drill-down to clinic level across the full organizational hierarchy.

Adoption-first deployment. Because a meaningful portion of the end-user base was new to interactive BI tools, we deliberately began with paginated (tabulated) reports — familiar territory for users accustomed to spreadsheet outputs — before layering in interactive Power BI dashboards as comfort grew. This staged approach compressed the adoption curve without overwhelming end users. Today, more than 70 clinic managers across the network log in to see exactly how they are tracking to their budgets.

Automation and Engineering

PDF Report Distribution Engine

A recurring operational need — distributing individualized doctor performance reports to managers — had no scalable solution. Provisioning per-user Power BI premium licenses for report-only consumers would have cost the organization an additional $26K annually.

We engineered a fully automated Python distribution engine using the Power BI REST API:

• Employee-level dynamic report filtering per export cycle
• Async batch export with exponential backoff retry logic
• OAuth2 dual-authentication (ADAL + MSAL) with token caching for secure, unattended access
• Automated distribution via Office 365 SMTP
• SharePoint archival upload with modern MSAL-based authentication
• Watchdog process monitoring for unattended scheduled execution

The system runs on a schedule without manual intervention.

For more on this technique, see Python: Dynamic Power BI PDFs with the REST API.

4PatientCare Data Extraction

The organization's patient recall and reminder platform was a data island. When the platform's own developers stated that automated data extraction was not possible, we built a Selenium-based extraction tool that:

• Navigates the full multi-step authentication and session management workflow
• Executes the monthly extract programmatically without manual input
• Exports structured results to CSV and uploads automatically to SharePoint for downstream consumption

Zoom Call History Integration

We integrated the Zoom API to capture phone call history — caller and callee identity, department, call duration, and routing path — giving operations teams structured data visibility into patient contact patterns that previously had no representation in the reporting environment.

Governance

A platform is only as valuable as its ability to be maintained, extended, and trusted over time — and a platform you can't operate without external help isn't an asset, it's a dependency. Everything delivered was designed to be owned and run by the client's own team. Not locked to any vendor. Not requiring ongoing external management to stay functional.

Every deliverable was built to enterprise governance standards:

• Azure DevOps source control — all Power BI reports, semantic models, and automation scripts version-controlled in Git with a structured branching and release workflow
• Deployment pipelines — governed Dev → Test → Prod promotion across managed workspaces, with documented acceptance validation per business process
• TMDL semantic modeling — text-based model definitions enabling diff tracking, peer review, and repeatable deployments at scale
• Role-based security — scoped access per reporting tier, with audience segmentation aligned to the organizational hierarchy
• Automated refresh schedules — all semantic models on governed cadences with monitoring

The platform belongs to the client. It was designed to be operated, extended, and trusted by their internal team — long after the engagement closes.

Outcomes

70+ clinic managers have real-time budget visibility. Finance has clean month-end data. Leadership can drill from market to clinic in seconds. The platform that once produced numbers nobody trusted now runs the organization's reporting from the ground up — and it cost $26K less per year to operate than the alternative.

What They Said

Two senior leaders from the organization provided written recommendations independently — unsolicited, after a year and a half of working together. Their words get closer to the actual experience than any summary could.

Frequently Asked Questions

What do you do when BI reports and source systems don't reconcile?

You rebuild the foundation, not the surface. Numbers stop tying when the semantic model has grown organically — auto-generated tables, calculated columns doing ETL work, ambiguous relationships. We restructured a 48+ table model, moved logic to the correct layer, and enforced referential integrity so every figure traced back cleanly.

How long does a Power BI platform rebuild take?

This one delivered 30+ production reports in two months — a scope most implementations size as a multi-quarter initiative. The speed came from architecting a Microsoft Fabric lakehouse with a Bronze-Silver-Gold medallion design first, so the reporting layer sat on data that was already clean, deduplicated, and dimensionally modeled.

How did the platform eliminate $26K a year in licensing?

A Python distribution engine on the Power BI REST API replaced per-user premium licenses for report-only consumers. It filters reports per employee, exports on a schedule, and emails and archives them unattended — a 90% licensing cost reduction that runs without human intervention.

When the numbers stop tying, it's the architecture

If your numbers don't tie, your team has lost confidence in the data, or you've been through a vendor engagement that didn't deliver, that's not a people problem — it's an architecture problem, and it's fixable. Tell us what's breaking and we'll tell you exactly what we'd do differently.

---

About the author — William Rodriguez is the founder of Analytical Ants, the delivery arm of Analytical Solutions. He holds an MBA from Emory University's Goizueta Business School and dual undergraduate degrees in Finance and Managerial Science, and spent roughly a decade architecting enterprise BI and data platforms for operations running $10M–$60B in revenue — the credibility floor beneath the agentic delivery work Analytical Ants does today. More about Analytical Ants.