logical-model

# Skill: Logical Data Model Design (Kimball Methodology) ## Prerequisites - Reference `.github/skills/logical-model/references/relationship-patterns.md` for advanced relationship patterns. - Reference `.github/references/naming-conventions.md` for naming rules. - If (and ONLY if) RLS is required by the specs, reference `.github/references/security-rls-best-practices.md` for recommended patterns (dynamic RLS, least privilege, governance). ## Input / Output | | | |---|---| | **Input** | `<ProjectName>/spec/requirements_summary.md` | | **Output** | `<ProjectName>/spec/er_diagram.md` | ### Critical Clarification Carry-Forward Gate (MANDATORY) Before designing Step 2, verify Step 1 critical clarifications are resolved or explicitly approved as assumptions in `workflow_state.json`: - Time/period semantics used by calculations. - Classification threshold semantics for status-style KPIs. - Grain reconciliation policy across compared datasets. Rules: - Do NOT introduce implicit defaults in Step 2. - If a critical clarification is unresolved and not explicitly assumption-approved, STOP and request a targeted answer. - If Step 2 proceeds under approved assumptions, list those assumptions explicitly in `er_diagram.md` and state that they are temporary until final confirmation. ## Design Rules When designing the logical data model, strictly adhere to Kimball dimensional modeling principles: ### Star Schema - Design a **pure Star Schema**. Avoid Snowflaking unless explicitly requested by the user for a highly specific, justified reason. - Every Fact table sits at the center, surrounded by Dimension tables. - No direct Fact-to-Fact joins (use conformed dimensions instead — Kimball multipass SQL rule). ### Naming Conventions - **Fact tables**: `Fact_<BusinessProcess>` (e.g., `Fact_Sales`, `Fact_Budget`) - **Dimension tables**: `Dim_<Entity>` (e.g., `Dim_Customer`, `Dim_Date`, `Dim_Area`) - **Measures table**: `_Measures` (disconnected, prefixed with underscore) - See `.github/references/naming-conventions.md` for full column-level naming rules. ### Surrogate Keys - ALL dimensions MUST use **integer-based Surrogate Keys** (e.g., `DateKey`, `CustomerKey`) as their Primary Key. - Fact tables reference dimensions via **Foreign Keys** matching the surrogate keys. - **Never use natural business keys** for relationships (store them as descriptive attributes). - Exception: `Dim_Date` may use `DateKey` in `YYYYMMDD` integer format for readability. ### Conformed Dimensions - Dimensions shared across multiple fact tables MUST be **conformed**: identical attributes and domain values. - Budget and Sales facts sharing Area and Date dimensions must use the SAME dimension tables. ### Slowly Changing Dimensions (SCD) - If historical tracking is implied in the specifications, default to **SCD Type 2** for those dimensions. - SCD Type 2 requires: `ValidFrom` (date), `ValidTo` (date), `IsCurrent` (boolean) columns. - If not implied, use **SCD Type 1** (overwrite) as default. ### Date Dimension - ALWAYS include a `Dim_Date` table (calendar dimension). - It MUST be marked as the **Date Table** in the semantic model. - Include fiscal year/month columns if the specs reference fiscal periods. - Include: `DateKey`, `Date`, `Year`, `Quarter`, `Month`, `MonthName`, `DayOfWeek`, `DayName`, `IsWeekend`, `FiscalYear`, `FiscalMonth`, `FiscalQuarter`. ### Degenerate Dimensions - Transaction identifiers (e.g., `SalesID`, `InvoiceNumber`) that don't warrant a separate dimension table should be kept directly in the Fact table as **degenerate dimensions**. ### Security & RLS Modeling (if applicable) - If requirements include **dynamic RLS**, plan the required security mapping entities in the logical model (e.g., `Dim_UserSecurity`, `Bridge_UserTerritory`, `Bridge_UserCustomer`). - Prefer modeling RLS filters on **Dimensions** (with relationship propagation to Facts) rather than filtering Facts directly. - Keep security entities consistent with the overall star design (avoid introducing ambiguous paths or many-to-many unless explicitly required). ### ⛔ CRITICAL: Ambiguous Path Detection **Ambiguous paths** occur when there are MULTIPLE active relationship paths between the same two tables. This causes Power BI to fail with error: ``` There are ambiguous paths between '<FactTable>' and '<DimensionTable>': '<FactTable>'->'<IntermediateDim>'->'<DimensionTable>' and '<FactTable>'->'<DimensionTable>' ``` **Common Cause**: Redundant Foreign Keys in Fact tables that create both direct and indirect paths. **Example of INCORRECT design** (creates ambiguity): - `Fact_Sales` has FK: `CustomerKey`, `CountryKey`, `AreaKey`, `IndustryKey` - `Dim_Customer` has FK: `CountryKey`, `IndustryKey` - `Dim_Country` has FK: `AreaKey` This creates THREE ambiguous paths: 1. `Fact_Sales → Dim_Country` (direct) vs `Fact_Sales → Dim_Customer → Dim_Country` (indirect) 2. `Fact_Sales → Dim_Area` (direct) vs `Fact_Sales → Dim_Customer → Dim_Country → Dim_Area` (indirect) 3. `Fact_Sales → Dim_Industry` (direct) vs `Fact_Sales → Dim_Customer → Dim_Industry` (indirect) **CORRECT Design Rules**: 1. **Remove redundant FKs**: If a dimension is reachable through another dimension, do NOT create a direct FK in the fact table 2. **Choose ONE path**: Either direct FK OR through intermediate dimension, NEVER both 3. **Snowflake when necessary**: For normalized hierarchies (Customer → Country → Area), keep only the Customer FK in the fact **Corrected Example**: - `Fact_Sales` has FK: `CustomerKey`, `ProductKey`, `DateKey`, `SalespersonKey` ONLY - `Dim_Customer` has FK: `CountryKey`, `IndustryKey` - `Dim_Country` has FK: `AreaKey` - Paths are now unambiguous: `Fact_Sales → Dim_Customer → Dim_Country → Dim_Area` **When to Use Inactive Relationships**: If you genuinely need multiple paths (e.g., role-playing dimensions like OrderDate/ShipDate), make all but ONE relationship `isActive: false` and use `USERELATIONSHIP()` in DAX. ## Output Format Output the proposed logical model using **Mermaid.js Entity-Relationship diagram** syntax: ### Mermaid Compatibility Rules (MANDATORY) To prevent parser failures across different Mermaid runtimes (VS Code, GitHub UI, docs renderers): - Use only standard key markers in attributes: `PK`, `FK`, `UK`. - Do NOT use custom markers like `DD` (degenerate dimensions must be described in prose, not as Mermaid key tags). - Do NOT model `_Measures` as an ER entity in Mermaid diagrams (it is a semantic-model utility table, not a relational entity). - Prefer unquoted relationship labels (for example `: DateKey`, not `: "DateKey"`). - Keep entity names alphanumeric with underscores, starting with a letter (`Dim_*`, `Fact_*`). If a parser error is reported, first sanitize the diagram with the rules above before changing the logical model design. ```mermaid erDiagram Dim_Date { int DateKey PK date Date string Year string FiscalYear string FiscalMonth } Dim_Customer { int CustomerKey PK string CustomerName string Country string Industry } Fact_Sales { int SalesKey PK int DateKey FK int CustomerKey FK decimal SalesAmountLC decimal AdjustedProfitLC } Dim_Date ||--o{ Fact_Sales : DateKey Dim_Customer ||--o{ Fact_Sales : CustomerKey ``` ### Checklist Before Presenting - [ ] All fact tables have a clear grain documented - [ ] All dimensions use integer surrogate keys - [ ] `Dim_Date` is included with fiscal period columns - [ ] All relationships are 1:N (Dim to Fact) - [ ] Conformed dimensions are identified - [ ] Naming follows conventions - [ ] Mermaid compatibility rules validated (no custom key tags, no `_Measures` entity, unquoted relationship labels) - [ ] **NO ambiguous paths**: Verify that between any two tables there is ONLY ONE active relationship path (no redundant FKs in fact tables) - [ ] If snowflaking is used, ensure fact tables connect only to the lowest-grain dimension in the hierarchy Save primary artifact to `<ProjectName>/spec/er_diagram.md`.