Multi-Entity Operational Data Consolidation & KPI Engineering

Company: Blue Sapphire Tech Ltd. (Sheela Palace Group), Ireland Role: Data Analyst — Designed, built, and delivered end-to-end Stack: PostgreSQL · pgAdmin · Excel · dbdiagram.io

---

🔴 Business Problem

A multi-site hospitality group operating two Irish branches (Lucan and Liffey) was maintaining revenue, staffing, and footfall data in siloed Excel files across locations. This created significant operational and financial reporting challenges:

• No consolidated cross-entity view for management reporting
• Revenue leakage going undetected (zero-revenue days with labour costs logged)
• Labour cost inefficiencies on high-demand days
• No mechanism to identify and replicate high-performing revenue events across branches

Objective: Design and implement a structured PostgreSQL data pipeline to consolidate multi-entity operational data, engineer financial KPIs, and deliver a management information layer for daily decision-making.

---

📊 Data Governance & Collection

• Designed the data collection framework from scratch — defined Excel input structure, data entry rules, and quality standards enforced across both sites over a 6-month period
• Personally supervised daily data entry by operational staff to ensure completeness and accuracy
• Dataset includes: daily revenue per entity, footfall, staffing by shift, labour hours, and operational context (events, weekends, external factors)
• Structured dataset was then migrated into a PostgreSQL pipeline for analysis

This mirrors real-world financial data governance processes — defining upstream data standards before downstream analytics is built.

---

🔧 Technical Architecture

Schema Design — 3-Layer Data Warehouse Model

staging   →   ref (dimensions)   →   core (facts)

• staging — Raw CSV ingestion layer (master daily records, staffing data)
• ref — Reference/dimension layer (dim_branch — branch master data)
• core — Consolidated fact layer (fact_daily — single source of truth across entities)

This mirrors standard financial data warehouse architecture used in fund administration, management reporting, and BI environments.

ETL Pipeline

• Imported raw source data into staging tables
• Applied join logic against dimension tables to produce clean, enriched fact records
• Built v_fact_daily_enriched — enriched view with core financial efficiency metrics

Key KPI Engineered: Sales Per Staff Hour (SPSH)

SPSH = Daily Revenue / Total Labour Hours Logged

This is an operational efficiency metric equivalent to revenue per unit cost — a standard KPI in financial performance analysis.

---

🚨 Anomaly Detection — Financial Controls Layer

Built a CTE-based anomaly detection framework to flag financial and operational irregularities — equivalent to financial controls and variance analysis in a reporting environment:

Anomaly Type	Finance Equivalent	Result
Zero revenue with labour hours logged	Revenue leakage / cost with no income	1 day flagged → POS error uncovered
High footfall, below-average revenue	Conversion efficiency variance	7+ instances identified
Sales outliers >3σ from mean	Statistical variance flagging	3 festival spikes at Liffey, 1 at Lucan
High labour hours + low SPSH	Cost efficiency breach	Highlighted recurring inefficiency days

---

📈 Management Information Layer

Built v_ops_daily — a clean, manager-facing daily reporting view consolidating revenue, footfall, labour, and efficiency KPIs into a single table, sorted chronologically.

This is the equivalent of a daily MIS (Management Information System) report — the same structure used in bank operations, fund administration, and financial reporting teams to give senior stakeholders a single source of truth.

---

📊 Results & Business Impact

• Data governance: Established a structured 6-month operational dataset from scratch across two entities — zero prior data infrastructure existed
• Revenue leakage detection: Identified 1 confirmed POS error (74 staff hours logged, zero revenue recorded) — corrected before period-end close
• Labour cost optimisation: Identified recurring high-cost, low-efficiency days — informed staffing schedule changes
• Revenue growth: Detected 4 event-driven revenue spikes — recommended and supported campaign replication across branches
• Cross-entity benchmarking: Established that Lucan outperforms on Friday/Saturday (late dining), Liffey outperforms on Sundays (family dining) — informing entity-level resource allocation

---

📂 Repository Structure

restaurant-sql-consolidation/
├─ sql/
│  ├─ 00_create_schemas.sql          # 3-layer schema: staging, ref, core
│  ├─ 01_create_staging_tables.sql   # Raw ingestion layer
│  ├─ 02_load_data_instructions.md   # CSV import documentation
│  ├─ 03_build_core_tables.sql       # dim_branch + fact_daily + loads
│  ├─ 04_views_kpis.sql              # v_fact_daily_enriched + v_ops_daily
│  ├─ 05_cte_anomalies.sql           # Variance detection & entity comparison
│  └─ 99_drop_all.sql                # Environment cleanup
├─ erd/
│  ├─ tech_schema.png                # Full warehouse schema (staging/ref/core)
│  └─ manager_schema.png             # Simplified MIS layer (dim + fact + view)
├─ results/
│  ├─ zero_sales_staff_present.png
│  ├─ outliers_3sigma.png
│  ├─ busy_underperforming.csv
│  └─ branch_head_to_head.png
└─ README.md

---

⚙️ Tech Stack

• PostgreSQL — Schema design, ETL, CTE-based anomaly detection, KPI views
• pgAdmin — SQL development environment
• dbdiagram.io — Data warehouse ERD documentation
• Excel — Source data governance and upstream collection framework

---

🏷️ Context

Delivered during my role at Blue Sapphire Tech Ltd., supporting the Sheela Palace Group (Ireland). Project initiated as a strategic data infrastructure initiative — designed, owned, and delivered independently from data collection framework through to management reporting layer.

---

💼 Finance Industry Applications

The architecture, methods, and analytical patterns used in this project directly mirror workflows used daily in financial services environments. Below is an explicit mapping of this project's components to real finance use cases.

---

1. 🏦 Fund NAV Reconciliation — Fund Administration

What NAV Reconciliation is: Fund administrators (Northern Trust, Citi, State Street) reconcile Net Asset Value daily across multiple fund entities — flagging discrepancies, zero-price securities, and calculation breaks before they reach investors.

How this project mirrors it:

This Project	Fund NAV Reconciliation
Two branches (Lucan & Liffey) as separate entities	Multiple funds / sub-funds as separate entities
Daily revenue consolidated into fact_daily	Daily NAV calculated and consolidated per fund
Zero-sales + staff present anomaly detection	Zero-price or missing security flagging
v_ops_daily — single daily view for managers	Daily NAV report delivered to fund managers
POS error caught via anomaly detection	NAV break identified before investor reporting

SQL pattern used in this project (directly transferable):

-- Flags entity-days where costs are logged but revenue is zero
-- In fund admin: flags fund-days where expenses post but NAV is missing
SELECT
    branch_name,
    date,
    total_labour_hours,
    daily_sales
FROM v_fact_daily_enriched
WHERE daily_sales = 0
  AND total_labour_hours > 0;

---

2. 📊 Branch / Business Unit P&L Reporting — FP&A & Management Accounting

What Branch P&L Reporting is: Financial Planning & Analysis (FP&A) teams in banks, insurance companies, and corporates produce monthly P&L reports per business unit, cost centre, or region — comparing actuals vs budget, flagging variances, and identifying cost inefficiencies.

How this project mirrors it:

This Project	Branch P&L Reporting
Lucan vs Liffey head-to-head comparison	Business Unit A vs Business Unit B P&L
SPSH (Sales Per Staff Hour) KPI	Revenue per FTE / Cost efficiency ratio
Day-of-week performance breakdown	Period-over-period variance analysis
High labour hours + low SPSH flagged	Cost centre overspend vs budget flagged
Manager-facing v_ops_daily view	CFO/Management monthly reporting pack

SQL pattern used in this project (directly transferable):

-- Branch head-to-head efficiency comparison
-- In FP&A: business unit cost efficiency vs company average
SELECT
    b.branch_name,
    AVG(f.spsh)            AS avg_revenue_per_cost_unit,
    SUM(f.daily_sales)     AS total_revenue,
    SUM(f.total_labour_hours) AS total_cost_hours
FROM fact_daily f
JOIN dim_branch b ON f.branch_id = b.branch_id
GROUP BY b.branch_name
ORDER BY avg_revenue_per_cost_unit DESC;

---

3. 🚨 Operational Risk Flagging — Risk & Compliance

What Operational Risk Flagging is: Risk teams in banks and financial institutions monitor operational processes daily for control failures, process breaks, and statistical outliers — escalating issues before they become regulatory or financial problems. This is a core function under Basel III operational risk frameworks.

How this project mirrors it:

This Project	Operational Risk Flagging
3σ outlier detection on daily sales	Statistical threshold breach alerting
Zero-revenue + active cost anomaly	Control failure — cost with no corresponding income
Busy days with below-average conversion	Process inefficiency flag — high input, low output
CTE-based anomaly detection framework	Automated controls monitoring layer
Escalation to managers via v_ops_daily	Risk escalation report to senior management

SQL pattern used in this project (directly transferable):

-- Flags statistical outliers using 3σ threshold
-- In risk: flags transactions or positions breaching control limits
WITH stats AS (
    SELECT
        AVG(daily_sales)    AS mean_sales,
        STDDEV(daily_sales) AS stddev_sales
    FROM fact_daily
)
SELECT
    f.date,
    f.branch_id,
    f.daily_sales,
    s.mean_sales,
    s.mean_sales + (3 * s.stddev_sales) AS upper_control_limit
FROM fact_daily f
CROSS JOIN stats s
WHERE f.daily_sales > s.mean_sales + (3 * s.stddev_sales)
   OR f.daily_sales < s.mean_sales - (3 * s.stddev_sales);

---

4. 🏢 Management Information Systems (MIS) — Banking Operations

What MIS Reporting is: Every bank, credit union, and financial institution produces a daily MIS report — a clean, consolidated view of the day's key metrics delivered to branch managers and senior operations staff. This is a core deliverable in retail banking, fund operations, and insurance.

How this project mirrors it:

This Project	Banking MIS
v_ops_daily view	Daily MIS report / operations dashboard
Revenue + footfall + labour in one view	Balances + transactions + headcount in one view
Sorted chronologically, manager-ready	Distributed daily to branch/ops managers
Built on top of consolidated fact table	Built on top of core banking data warehouse

---

🎯 Summary — Why This Matters for Finance Roles

This project was built in a hospitality context but is architected using the same foundational patterns as financial data infrastructure:

• ✅ Multi-entity data consolidation (fund admin, retail banking, FP&A)
• ✅ 3-layer data warehouse design (staging → reference → core)
• ✅ Statistical anomaly detection (operational risk, financial controls)
• ✅ KPI engineering on top of fact tables (performance reporting)
• ✅ Management-facing reporting layer (MIS, executive dashboards)
• ✅ Data governance from source (upstream data quality ownership)

The domain is restaurants. The skills are finance.