fabric-optimization-review

# Fabric Optimization Review — Reference Analyzes Microsoft Fabric lakehouse tables, data models, and semantic models to detect performance issues, anti-patterns, and optimization opportunities. Produces actionable recommendations with severity, effort, and expected impact. ## Review Categories The agent performs three independent review tracks. The user can request one, several, or all: | Track | Target | What It Checks | |-------|--------|----------------| | **Data Storage** | Lakehouse Delta tables | File sizes, compaction, partitioning, V-Order, Z-Order, small files, schema evolution | | **Data Model** | Lakehouse table relationships | Normalization, star-schema compliance, redundant columns, key integrity, naming conventions | | **Semantic Model** | Power BI semantic models (TMDL) | DAX measure quality, relationship cardinality, hidden columns, implicit measures, format strings, role-playing dimensions | | **AI & Agentic Readiness** | Semantic models + lakehouse tables | Descriptions, synonyms, linguistic schema, display folders, naming conventions, pre-computed measures, column visibility for AI/NLQ/Copilot | | **Workspace Audit** | Full workspace | Fabric best practices, dimensional modeling compliance, Direct Lake readiness, item organization, capacity usage, pipeline health | ## Data Storage Optimization Rules ### Small File Detection Delta tables with many small files degrade read performance. Fabric uses V-Order by default but files still fragment from frequent small appends. | Metric | Threshold | Severity | Recommendation | |--------|-----------|----------|----------------| | Avg file size < 32 MB | < 32 MB | High | Run `OPTIMIZE` to compact files | | File count > 1000 per partition | > 1000 | High | Run `OPTIMIZE` with bin-packing | | Avg file size < 8 MB | < 8 MB | Critical | Immediate compaction required; rethink ingestion batch size | ### Partitioning Assessment | Pattern | Severity | Recommendation | |---------|----------|----------------| | Table > 1 GB with no partitioning | Medium | Consider partitioning by date or high-cardinality filter column | | Partition column with > 10,000 distinct values | High | Over-partitioned — creates too many small folders. Choose a coarser grain (year-month vs date) | | Partition column with < 5 distinct values | Low | Under-partitioned — minimal benefit. Consider removing partitioning | | Partition column not used in common filters | Medium | Partitioning on non-filter columns wastes metadata. Change to a commonly filtered column | ### V-Order and Z-Order | Check | Detection | Recommendation | |-------|-----------|----------------| | V-Order not enabled | Table properties lack `delta.parquet.vorder.enabled = true` | Enable V-Order: `ALTER TABLE {table} SET TBLPROPERTIES ('delta.parquet.vorder.enabled' = 'true')` | | Z-Order not configured on query-heavy columns | No `ZORDER BY` in OPTIMIZE history | Apply `OPTIMIZE {table} ZORDER BY ({columns})` for frequently filtered/joined columns | | Z-Order on > 4 columns | OPTIMIZE with > 4 ZORDER columns | Z-Order effectiveness degrades beyond 3-4 columns. Prioritize the most selective ones | ### Table Maintenance | Check | Detection | Recommendation | |-------|-----------|----------------| | No OPTIMIZE in last 7 days | Delta log has no OPTIMIZE commits recently | Schedule regular `OPTIMIZE` runs | | Vacuum not run / retention > 7 days | `delta.deletedFileRetentionDuration` > 7 days | Run `VACUUM {table} RETAIN 168 HOURS` to reclaim storage | | Stats collection stale | Column-level stats absent or outdated | Run `ANALYZE TABLE {table} COMPUTE STATISTICS FOR ALL COLUMNS` | | Too many Delta log entries | > 1000 log files without checkpoint | Force checkpoint: table properties `delta.checkpointInterval` | ### Schema & Data Type Optimization | Pattern | Severity | Recommendation | |---------|----------|----------------| | String columns storing only integers | Medium | Cast to `INT`/`LONG` — smaller footprint, faster scans | | String columns storing only dates | Medium | Cast to `DATE`/`TIMESTAMP` — enables predicate pushdown | | `DOUBLE` for currency/money values | Low | Use `DECIMAL(18,2)` for exact arithmetic | | Wide tables with > 50 columns | Low | Consider vertical partitioning (split into related tables) | | Unused columns (null > 95%) | Medium | Consider dropping or archiving — reduces scan I/O | ## Data Model Review Rules ### Star Schema Compliance | Anti-Pattern | Detection | Severity | Recommendation | |-------------|-----------|----------|----------------| | Snowflake dimensions | Dimension table has FK to another dimension | Medium | Flatten into single dimension for better query performance | | Flat/denormalized mega-table | Single table with > 30 columns mixing measures and attributes | High | Normalize into fact + dimension tables | | Missing date dimension | Fact table has date columns but no dedicated date/calendar table | High | Create a date dimension for time intelligence | | Fact-to-fact relationships | Two fact tables joined directly | High | Introduce shared dimensions (conformed dimensions) | | Bridge tables without clear purpose | Many-to-many junction tables | Medium | Verify necessity; simplify if possible | ### Key & Relationship Integrity | Check | Detection | Recommendation | |-------|-----------|----------------| | No defined primary key | Dimension table has no unique column identified | Define a PK to enable relationship validation | | Composite keys | Joins require multiple columns | Consider surrogate integer keys for simpler joins | | Orphan foreign keys | FK values not found in dimension PK | Data quality issue — clean orphans or add "Unknown" dimension member | | Circular relationships | A → B → C → A | Redesign to eliminate cycles; causes ambiguous query paths | | Bidirectional relationships | Cross-filter in both directions | Use single direction unless absolutely required (performance + ambiguity risk) | ### Naming Convention Review | Pattern | Severity | Recommendation | |---------|----------|----------------| | Inconsistent casing (mix of camelCase, snake_case, PascalCase) | Low | Standardize to one convention (prefer `snake_case` for Spark/lakehouse) | | Table names without prefix (`fact_`, `dim_`) | Low | Add prefixes for clarity in large models | | Ambiguous column names (`id`, `name`, `value`) | Medium | Qualify with table context (`customer_id`, `product_name`) | | Reserved word column names (`date`, `order`, `table`) | Low | Rename to avoid query issues (`order_date`, `order_number`) | ## Semantic Model Review Rules ### DAX Measures | Anti-Pattern | Detection | Severity | Recommendation | |-------------|-----------|----------|----------------| | Implicit measures | `discourageImplicitMeasures` is false or missing | High | Set `discourageImplicitMeasures` in model.tmdl and create explicit DAX measures | | SUM on non-additive columns | `SUM()` on ratio, percentage, or average columns | High | Use `AVERAGE()`, `DIVIDE()`, or weighted calculations | | Duplicate measures | Two measures with identical DAX expressions | Medium | Consolidate to a single measure | | Missing COUNTROWS | Fact table without a row-count measure | Low | Add `COUNTROWS('{table}')` as base metric | | Unformatted measures | Measures without `formatString` | Medium | Add format strings (`#,##0`, `#,##0.00`, `0.0%`) | | Time intelligence without DATEADD/SAMEPERIODLASTYEAR | Manual date calculations instead of DAX time functions | Medium | Use `DATEADD`, `SAMEPERIODLASTYEAR`, `TOTALYTD` for correct time intelligence | | CALCULATE without explicit filter removal | Nested CALCULATE without REMOVEFILTERS | Low | Review filter context — may produce unexpected results | ### Relationship Configuration | Issue | Detection | Severity | Recommendation | |-------|-----------|----------|----------------| | Missing relationships | Matching key columns with no defined relationship | High | Add the relationship (many-to-one, single direction) | | Wrong cardinality | Many-to-many where many-to-one is correct | High | Fix cardinality — many-to-many bypasses engine optimizations | | Both-direction cross-filter | `crossFilteringBehavior: bothDirections` without a bridge table | Medium | Use single direction; both-direction causes ambiguity and performance issues | | Role-playing dimensions | Same dimension used for multiple relationships (e.g., date table for order_date and ship_date) | Medium | Use `USERELATIONSHIP()` in measures for inactive relationships; document clearly | | Inactive relationships not used | Inactive relationship exists but no measure uses `USERELATIONSHIP` | Low | Either activate or remove the unused relationship | ### Column Configuration | Issue | Detection | Severity | Recommendation | |-------|-----------|----------|----------------| | ID/key columns visible | `isHidden: false` on FK/PK columns | Medium | Hide technical columns from report view | | `summarizeBy` not set to `none` | Non-measure columns with default aggregation | Medium | Set `summarizeBy: none` to prevent implicit measures | | Missing `isKey: true` | Dimension PK not marked as key | Low | Mark PK columns as `isKey: true` for VertiPaq optimization | | Data type mismatch | Relationship columns with different TMDL types | High | Align types — mismatches cause conversion overhead | ### Locale & Formatting | Issue | Detection | Severity | Recommendation | |-------|-----------|----------|----------------| | Missing culture | No `culture` in model.tmdl | Medium | Add `culture: es-ES` (or target locale) | | Wrong format strings for locale | Using `.` as decimal in Spanish locale | Low | Use `#.##0,00` for es-ES (dot = thousands, comma = decimals) | | Missing `sourceQueryCulture` | Not set in model.tmdl | Low | Add `sourceQueryCulture: es-ES` to match data source locale | ## AI & Agentic Readiness Rules These rules evaluate how well a semantic model (and its underlying data) is prepared for consumption by AI agents, Copilot, natural language queries (NLQ), and other agentic interfaces. A model optimized for human report builders is NOT automatically optimized for AI — AI needs richer metadata, unambiguous naming, and pre-computed measures to generate correct answers. ### 1. Table Descriptions Every table must have a `description` in TMDL that explains its purpose, grain (what one row represents), and key relationships. | Issue | Detection | Severity | Recommendation | |-------|-----------|----------|----------------| | Table without description | `description` property missing or empty in table TMDL | High | Add description explaining purpose, grain, and relationships. Example: `"Fact table of individual customer support calls. One row = one call. Joins to dim_agent, dim_customer, dim_date via FK columns."` | | Description too short (< 20 chars) | Description exists but is trivially short | Medium | Expand with grain, relationships, and business context | | Description is technical jargon | Description uses only internal/database terms | Medium | Rewrite in business language that an AI can interpret for end users | TMDL example: ```tmdl table 'fact_calls' description: "Fact table recording individual customer support calls. One row represents a single call. Grain: call_id. Joins to dim_agent (agent handling the call), dim_customer (caller), dim_date (call date), dim_product (product discussed)." ``` ### 2. Measure Descriptions Every DAX measure must have a `description` explaining what it calculates, its units, when to use it, and any caveats. | Issue | Detection | Severity | Recommendation | |-------|-----------|----------|----------------| | Measure without description | `description` missing on measure | High | Add description with calculation logic, units, and usage guidance | | Description missing units | Description doesn't mention currency, %, seconds, count, etc. | Medium | Specify units explicitly: "in euros (€)", "as percentage (0-100)", "in seconds" | | Complex measure without usage guidance | CALCULATE/FILTER/time-intelligence measure with no context | Medium | Explain when to use vs. similar measures. Example: "Use for YTD comparison. For month-only, use [Revenue Monthly]." | TMDL example: ```tmdl measure 'Average Handle Time' = AVERAGE('fact_calls'[duration_seconds]) description: "Average duration of customer calls in seconds. Includes hold time. Use for operational efficiency analysis. For calls without hold time, use [Avg Talk Time]." formatString: #,##0.0 ``` ### 3. Column Descriptions At minimum, describe all foreign key columns, amount/currency columns, coded/enumerated columns, and any column whose name is ambiguous. | Issue | Detection | Severity | Recommendation | |-------|-----------|----------|----------------| | FK column without description | Column ending in `_id`/`_key` with no description | High | Describe what it references: "Foreign key to dim_agent. Join on agent_id to get agent name and team." | | Amount/currency column without description | Numeric column with `amount`, `total`, `price`, `importe`, `precio` in name, no description | Medium | Specify currency, tax inclusion, sign convention: "Net revenue in EUR, excluding VAT. Positive = income, negative = refund." | | Coded/enum column without description | Column with low cardinality (< 20 distinct values) and no description | Medium | List possible values and meanings: "Call outcome code: 'R' = Resolved, 'E' = Escalated, 'A' = Abandoned, 'C' = Callback scheduled." |