Most BI projects fail for the same reason: the dashboards get built, nobody uses them, and six months later the company is back to emailing spreadsheets.
This project was different. A consulting firm needed centralized KPI reporting that stakeholders would actually adopt — not just admire in a demo. Here’s how we built it.
The Problem
The firm had data in multiple systems — CRM, project management, finance, HR. Each department tracked KPIs differently. The finance team used Excel pivot tables. Project managers used their own Notion databases. Leadership got monthly PDF reports that were already stale by the time they arrived.
The result: nobody trusted the numbers because everyone had different numbers.
Why Microsoft Fabric
We evaluated three options: a traditional data warehouse on Snowflake, a lakehouse on Databricks, and Microsoft Fabric.
Fabric won for three reasons:
The firm was already on Microsoft 365. Everyone had Teams, Outlook, SharePoint. Adding Fabric meant no new login, no new vendor relationship, no new security audit. The data stayed within the Microsoft tenant.
OneLake eliminates data movement. In a traditional architecture, you ETL data from source systems into a warehouse, then connect BI tools to the warehouse. Fabric’s OneLake acts as a single data lake that all Fabric workloads share. Data lands once and is accessible everywhere — no copying between layers.
Power BI is native. The reporting layer isn’t an integration — it’s built in. Semantic models, DAX measures, row-level security, and scheduled refresh all live in the same platform. For a team already familiar with Excel, the learning curve is minimal.
The Architecture
The pipeline had three layers:
Ingestion — Fabric Dataflows pulled data from the CRM API, the project management tool’s webhook, and flat files from the finance system. Each source landed in OneLake as Delta tables with incremental refresh.
Semantic Layer — This is where the real work happened. We built a star schema with DAX measures for every KPI: revenue per consultant, project utilization rate, client retention rate, average deal cycle time, and margin by service line. The semantic model enforced one definition for each metric — no more “my numbers are different from yours.”
Reporting — Power BI reports connected directly to the semantic model. We built three tiers: an executive summary (6 KPIs on one page), departmental deep-dives (filterable by team, client, and time period), and self-service exploration pages where users could drag and drop dimensions.
Making Self-Service Work
The hardest part wasn’t the technology. It was getting people to stop asking the data team for reports and start exploring data themselves.
Three things made self-service adoption work:
Bookmarks, not pages. Instead of building 20 separate report pages, we built 5 pages with bookmarks that pre-filtered to specific views. The VP of Sales had a bookmark that showed their pipeline KPIs. The HR director had one for headcount and attrition. Same report, different lenses.
Natural language Q&A. Power BI’s Q&A feature lets users type questions like “revenue by client this quarter” and get a chart. We trained the Q&A model with synonyms — “income” maps to revenue, “churn” maps to client attrition rate. This turned skeptics into daily users.
Scheduled subscriptions. Every Monday at 8AM, stakeholders received a PDF snapshot of their KPIs in their inbox. They didn’t have to open Power BI. But the PDF included a deep-link to the live report — and within a month, most users were clicking through to explore the interactive version.
DAX: The Underrated Skill
DAX (Data Analysis Expressions) is what separates a pretty dashboard from a useful one. Three patterns were critical in this project:
Time intelligence — Year-over-year comparisons, rolling 12-month averages, and quarter-to-date calculations. DAX has built-in time intelligence functions (SAMEPERIODLASTYEAR, TOTALYTD, DATESINPERIOD) that eliminate the need for complex date table joins.
Semi-additive measures — Headcount can’t be summed across time (10 employees in January + 10 in February ≠ 20 employees). DAX’s LASTNONBLANK and CALCULATE with MAX date filters handle this correctly.
Row-level security — Each regional manager sees only their region’s data. DAX expressions in the security role filter the fact tables dynamically based on the logged-in user’s identity.
Results
Within three months of launch:
- Report adoption: 78% of stakeholders accessed Power BI at least weekly (up from 0%)
- Time to insight: Monthly reporting cycle compressed from 5 business days to real-time
- Data trust: Single semantic model eliminated metric discrepancies across departments
- Self-service ratio: 60% of new report requests were handled by business users directly, without involving the data team
The Stack
| Layer | Tool | Why |
|---|---|---|
| Data Lake | Microsoft Fabric OneLake | Single storage, no data movement |
| Ingestion | Fabric Dataflows | Low-code, incremental refresh |
| Semantic Model | Power BI / DAX | One definition per metric, RLS |
| Reporting | Power BI Service | Self-service, Q&A, subscriptions |
| Orchestration | Fabric Pipelines | Scheduled refresh, dependency management |
Simba Hu helps companies make better decisions with data and AI — from strategy to implementation. Based in Tokyo, serving clients globally. Book a strategy call or visit simbahu.com.