Capstone Project: Executive Multi-Branch Financial & Operations Dashboard

# Capstone Project: Executive Multi-Branch Financial Dashboard with Dynamic RLS & Profiling **Target Role:** Lead BI Architect / Enterprise Power BI Specialist **Estimated Time:** 280 min (4.7 Hours) **Deliverable Format:** Power BI Project (`.pbip` Git repository with TMDL) or `.pbix` --- ## 1. Business Context & Problem Statement "Apex Global Finance" operates commercial lending across 4 geographical regions (North America, EMEA, APAC, LATAM) with 45 branch locations. The Chief Financial Officer requires an executive monitoring cockpit. Critical Enterprise Mandates: 1. **Hierarchical Dynamic RLS:** Branch Managers see only their branch. Regional VPs see all branches in their assigned region via parent-child reporting hierarchies (`PATHCONTAINS`). Executive Board members (CFO/CEO) see global data. 2. **Performance SLA:** Every visual on the executive summary canvas must render in **under 500 ms** over 5 million loan history rows. 3. **Executive UX:** Modern visual hierarchy (Z-pattern, KPI cards, drill-through operational view, and bookmark state machines). 4. **Automated Quality Gate:** The data model must pass Michael Kovalsky's **Tabular Editor Best Practice Analyzer (BPA)** with **0 Violations**. --- ## 2. Technical Deliverable Specifications 1. **Hierarchical Dynamic RLS:** - Implement an organizational path in `Dim_Employee` using `PATH()` and enforce security via `PATHCONTAINS()`: ```dax VAR CurrentUserPath = LOOKUPVALUE(Dim_Employee[OrgPath], Dim_Employee[Email], USERPRINCIPALNAME()) RETURN PATHCONTAINS(CurrentUserPath, Dim_Employee[EmployeeID]) ``` - Validate security locally in Power BI Desktop via **View as Roles** across 3 test personas (Branch Manager, Regional VP, CFO). 2. **Executive Multi-Page Canvas:** - **Page 1: Executive KPI Cockpit:** Net Interest Margin, Loan Default Rate %, Total Originations, YoY Volume Growth. Format Edit Interactions to **Filter**. - **Page 2: Branch Operational Drill-Through:** Detailed loan delinquency analysis with parameter-driven risk scoring. 3. **Performance Profiling Audit Package (`PERFORMANCE_AUDIT.md`):** - Export **Performance Analyzer** JSON proving visual execution under 500 ms. - Provide a **DAX Studio Server Timings** trace with an embedded watermarked query comment (`-- TOPFOLIO-EVAL: `) proving Storage Engine (SE) $>80\%$ and FE $<20\%$. - Tabular Editor BPA screenshot proving 0 rule violations. --- ## 3. Grading Rubric (100 Points Total) - **Dynamic RLS & Hierarchical Security (25 pts):** Scalable parent-child hierarchy using `PATHCONTAINS()`; passes all 3 persona tests. - **Dimensional Modeling & BPA Compliance (25 pts):** Pristine Star Schema, surrogate keys, 0 Tabular Editor BPA violations. - **DAX Precision & Performance (20 pts):** Proper lazy `VAR` caching, `DIVIDE`, zero context transition loops. - **Executive UI/UX & Visual Storytelling (15 pts):** Z-pattern layout, Edit Interactions set to Filter, clean drill-through and bookmarks. - **Performance Profiling Proof (15 pts):** Performance Analyzer under 500ms, DAX Studio trace with user watermark proving SE $>80\%$.