Data Modeling and the Star Schema

💡 Key concepts

• Facts = events and measurements (EVM by period, obligations by month, milestone records, task records). Dimensions = things you filter by (Program, Date, Contract, WBS, Appropriation, Vendor).
• Grain: State the grain of each fact, e.g., "one row per program × control account × month". Mixed grain causes double counting.
• Relationships: One-to-many, from dimension to fact. Single-direction filtering by default. Avoid bi-directional unless you know why you need it.
• Date table: One continuous date table, marked as the Date Table, related to every fact's date column. This fixes the "timeline only filters one table" problem from Module 1.5.
• Role-playing dates: Milestones have Baseline, Forecast, and Actual dates. Options: one active relationship plus inactive ones activated with USERELATIONSHIP in DAX, or separate date tables per role.
• Snowflake vs. star: Flatten small lookup chains (Portfolio → Program) into one dimension.
• Hygiene: Hide foreign keys in facts, set default summarization to "Don't summarize" for IDs, sort MonthName by MonthNum, use display folders, add descriptions.

Target model for PEO-FS:

                dim_Date
                   │
dim_Program ── fact_EVM ── dim_WBS
     │      ╲
     │       fact_Budget ── dim_Appropriation
     │
     ├── fact_Milestones (BaselineDate active, ForecastDate/ActualDate inactive → dim_Date)
     ├── fact_Tasks
     ├── fact_Risks
     └── dim_Contract ── (relates to fact_EVM by ContractNumber)

🛠 Hands-on exercise: Build the PEO-FS Star

Data: all D1–D7 datasets plus dim_Date from 2.2.

1. Create dim_Appropriation (RDT&E, Procurement, O&M) and dim_WBS (distinct ProgramID+WBS with a composite key ProgramWBSKey) in Power Query.
2. Create relationships as in the diagram. Mark dim_Date as the date table.
3. Relate fact_Milestones[BaselineDate] (active) and [ForecastDate], [ActualDate] (inactive) to dim_Date[Date].
4. Hide all FK columns in fact tables. Sort MonthName by MonthNum and FYLabel by FiscalYear.
5. Test: one matrix with dim_Program[Portfolio] and dim_Date[FYLabel] showing sums from fact_EVM and fact_Budget. Both should filter correctly.

📦 Mini-project: Model Design Document

A 1–2 page model doc: diagram (screenshot), grain statement for each fact, relationship list (from/to, cardinality, direction, active?), and the reasoning for any inactive or bi-directional relationship. This becomes documentation for your capstone.