Power BI & Analytics
Replaced a sprawling, error-prone Excel reporting process across Finance, Accounting, Operations, and HR with a unified Power BI suite — built on a clean STAR schema, advanced DAX logic, and department-level Row Level Security, connected to on-premise systems via gateway.
Client
Large Financial Services Firm (400+ employees)
Key result
Report prep time cut from 3 days to under 2 hours
Reading time
4 min read
A 400+ person financial services firm had reporting scattered across 11 Excel workbooks maintained by different teams. Finance ran their own P&L model. Accounting owned a separate aged-receivables tracker. Operations had a manually updated headcount file. HR tracked leave and headcount in yet another spreadsheet — none of them connected.
Every month, a senior analyst spent three full working days consolidating these files, cross-referencing figures, and formatting board-ready slides. Errors slipped through regularly. By the time reports landed in leadership inboxes, the data was already stale.
There was a further complication: core financial data lived in an on-premise SQL Server — not in the cloud. The existing setup offered no way to surface that data in Power BI without manual exports.
We configured a Power BI On-Premises Data Gateway to bridge the client's local SQL Server with Power BI Service — no manual exports, no CSV hand-offs. Scheduled refreshes run every two hours, keeping every dashboard current without anyone touching a file.
Source data arrived denormalised — wide flat tables with repeated dimensions and no consistent keys. We rebuilt the semantic layer from scratch using a proper STAR schema:
This cut average query time by ~65% compared to the flat-table approach, and made DAX measures readable and maintainable by anyone on the team.
Raw data from SQL, the accounting platform, and HR exports required significant cleaning before it was fit for reporting. We built reusable Power Query transformation pipelines to:
All KPIs were built as certified DAX measures in the shared semantic model rather than report-level calculations — ensuring every team is working from the same definitions:
With 8 departments each needing visibility into their own figures — and leadership needing the full picture — we implemented dynamic Row Level Security using a permissions table maintained by the IT team:
RLS is role-based and driven by Active Directory groups, so access updates automatically when people change roles.
15 reports across four workspaces:
Finance & Accounting: P&L Summary, Budget vs Actual, Aged Receivables, Cash Flow, Intercompany Reconciliation
Operations: Headcount Utilisation, Project Delivery Rate, SLA Compliance, Capacity Planning
HR: Headcount Movement, Leave Liability, Attrition Trends
Executive: Board Scorecard (single-page, PDF-export-ready), CEO Dashboard, Consolidated KPI Summary
Gateway over cloud-only: The on-premise SQL Server wasn't moving to Azure any time soon. The gateway approach gave us live connected reports without forcing a migration the client wasn't ready for.
Shared semantic model over per-report models: Building one certified dataset used across all 15 reports means DAX changes propagate everywhere. No drift between reports showing different numbers for the same metric.
Bookmarks for board mode: One click switches the executive reports into a simplified presentation view — slicers hidden, tooltips suppressed, optimised for projector display. Finance directors don't want to explain what a slicer is mid-meeting.
Want results like this?
Every engagement starts with a free 20-minute discovery call. No pitch, no pressure — just an honest conversation about what would actually help.
