Analytics Engineering & Governance: From Fragmented Spreadsheets to a Governed Semantic Layer
The Demonstration: I audited and rebuilt a fragmented data environment, standardizing metric definitions across Finance, Sales, and Marketing, and delivered a fully documented executive reporting suite with a structured handoff. (Note: This lab uses synthetic retail data designed to mirror real-world enterprise engagements.)
The Situation
The business was experiencing rapid YoY revenue growth, but leadership lacked visibility into regional profitability. The issue was not a lack of tooling—they had SQL Server, modern CRMs, and ad platforms. The issue was that company-wide metrics were being manually built by operations managers in isolated Excel spreadsheets instead of a centralized data warehouse.
Because the analytics environment lacked a governed definition layer, the Finance and Marketing teams were consistently arriving at completely different numbers for the exact same metrics. They needed a seasoned Analytics Engineer to intervene, migrate the manual reports, create interoperable tables, and establish a single source of truth.
Why this engagement mattered
Inflexible reporting is a common byproduct of databases and dashboards that grow without centralized ownership. When calculations accumulate across ad-hoc queries without a unifying standard, the problem is rarely the data itself—it is the absence of a canonical definition layer.
Resolving this requires more than just writing SQL. It requires stakeholder alignment on what metrics actually mean, disciplined upstream implementation in the ETL pipeline, and creating a concrete artifact that teams can align around. My approach prioritizes definition alignment before model development, ensuring that what gets built reflects what the business actually agreed on.
My Approach
I worked across three distinct workstreams to stabilize and automate the environment:
- Metric Standardization & Governance: Rebuilt the transformation logic. Calculations previously distributed across individual analysts’ spreadsheets were moved upstream into SQL Server views, versioned, and documented.
- Semantic Layer Deployment: Engineered a governed Power BI Semantic Model using DAX. This handled the complex time-intelligence and currency formatting centrally, ensuring metric definitions were not replicated at the dashboard layer.
- Executive Dashboard Buildout: Developed a highly interactive, 3-page reporting suite covering macro trends, root-cause anomaly detection (Decomposition Trees), and What-If scenario modeling.
The Deployed Asset
The Outcome
After this rollout, metric definitions were unified in a single governed Power BI Semantic Model, with Finance, Sales, and Marketing pulling from the exact same source. At handoff, the internal team took full ownership of a maintainable, automated codebase.
| Metric Trust & Ownership | Before Intervention | After Rollout |
|---|---|---|
| Metric Definitions | Distributed across messy dashboards and offline spreadsheets. | Unified centrally in a governed DAX semantic model. |
| Cross-Team Consistency | Finance and Sales often produced wildly different numbers for the same metric. | A singular, verifiable source of truth across all reporting surfaces. |
| Documentation | Sparse, relying on the institutional knowledge of a single employee. | Inline SQL comments and a standalone, hand-off metrics catalog. |
| Reporting Cadence | Highly manual, taking 72+ hours to compile weekly reports. | Automated daily refreshes requiring zero human intervention. |
Three Values in Action
Curiosity
I audit the existing architecture before proposing changes, taking the time to understand the original intent behind the legacy models before deciding what to modify.
Integrity
Where metric definitions are ambiguous, I surface the friction. I work with business stakeholders to reach a documented resolution rather than making unilateral technical decisions.
Sustainability
Every architectural decision accounts for long-term maintainability. The goal is to build an ecosystem that the internal team can operate independently after the handover.
Architectural Decision Record (ADR)
Strategic Rationale: Power BI Semantic Models vs. SSAS Tabular
When designing the semantic layer for this rollout, I actively chose to bypass traditional SQL Server Analysis Services (SSAS) in favor of a cloud-native Power BI Semantic Model.
Under the hood, both architectures rely on the exact same VertiPaq engine. However, Microsoft's strategic focus has shifted heavily toward Power BI and Microsoft Fabric. By bypassing on-premises SSAS, this architecture supports immediate Git integration, dynamic Calculation Groups, Entra ID Row-Level Security, and is immediately scalable for future DirectLake adoptions.
Want to explore more of my builds?
Check out my case studies, read my writing on AI automation and clean data, or get in touch directly.