Alfie Lancaster Logo
Alfie Lancaster Logo
← Back to Projects

SharePoint to Power BI Architecture

Led the redesign of fragmented, Excel-based service reporting into a centralised SharePoint data ecosystem. Engineered a structured relational model and interactive dashboards, replacing manual consolidation with a scalable Source of Truth for management.

View Full Screen
Executive Overview Dashboard

Fig 1.0: Executive Overview tracking £8M+ in financial activity.

View Full Screen
Operational Drill-down Dashboard

Fig 1.1: Operational drill-down for team-level SLA monitoring.

Project Process

Expand Workflow
End-to-end data architecture flow

Fig 2.0: Data Architecture Overview. An end-to-end pipeline integrating Excel-based ETL with SharePoint List storage and Power BI for enterprise-grade reporting.

The Problem

Data Fragmentation: Critical service data was trapped in multiple Excel sheets with inconsistent structures across different teams.

Manual Overhead: Significant time wasted on manual data consolidation to produce management reports.

Lack of Structure: Absence of a relational data model led to inconsistent KPI definitions and restricted historical analysis.

Visibility Gap: Managers had limited, delayed visibility into operational performance.

The Solution

Centralised Data Layer

Designed and deployed standardised SharePoint Lists to replace local Excel files, ensuring multi-user access and data consistency.

View Relationship Model
Power BI Star Schema Model

Fig 3.0: Relational Modeling (Fact/Dimension logic) connecting service data with organisational attributes.

ETL Engineering

Built robust Power Query (M) pipelines to clean, streamline, and merge disparate source data.

Advanced Analytics

Developed a custom Calendar Table and DAX measures to calculate complex KPIs, such as Working-Day Duration (excluding weekends and bank holidays) and processing volumes.

Business Logic EngineeringFull Snippet
Custom DAX Logic Snippet

Deployment & Governance

Rebuilt the final model in Power BI Desktop, creating high-impact visualisations before coordinating the handoff to the Data & Insight team for enterprise-wide publishing.

The Impact

  • 01.

    Standardisation

    Unified KPI definitions across all teams, ensuring management reports are accurate and comparable.

  • 02.

    Operational Insight

    Provided live visibility into processing times and completion rates, enabling data-driven decision-making.

  • 03.

    Future Proofing

    Created a repeatable framework that allowed the Data & Insight team to automate refreshes and scale the solution further.