Capstone Project: Executive Multi-Branch Financial & Operations Dashboard

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():

     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: <user_slug>) 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%$.