databricks-aibi-dashboards

# Databricks AI/BI Dashboards ## Overview This skill provides comprehensive patterns for building production-grade Databricks AI/BI (Lakeview) dashboards. These patterns were developed from 100+ production deployments and prevent common visualization errors, deployment failures, and maintenance issues. **Core Philosophy: Self-Service Analytics** AI/BI Lakeview dashboards provide **visual analytics for business users** with **no SQL required**. This skill emphasizes: - ✅ **Visual insights** for non-technical users - ✅ **Consistent metrics** across the organization - ✅ **Self-service analytics** without coding knowledge - ✅ **Professional, branded** appearance - ✅ **Automated deployment** with validation - ✅ **Error prevention** through pre-deployment checks **Key Capabilities:** - Widget-query column alignment validation - Number formatting rules (percentages, currency, plain numbers) - Parameter configuration (time ranges, multi-select, text input) - Monitoring table query patterns (window structs, CASE pivots, custom drift metrics) - Chart configuration (pie, bar, line, area, table, pivot, point map, choropleth, sankey) - **Pivot tables** with hierarchy expand/collapse and non-additive ratio metric support - **Map visualizations** (point maps with lat/lng, choropleth by region) - **disaggregated vs aggregated** query mode patterns for correct metric rollups - Pre-deployment SQL validation (90% reduction in dev loop time) - UPDATE-or-CREATE deployment pattern (preserves URLs and permissions) - Variable substitution (no hardcoded schemas) - Complete JSON templates for all widget types - Phase-by-phase implementation guide - **Production example** - Complete Jobs System Tables Dashboard demonstrating all patterns ## When to Use This Skill Use this skill when: - **Building AI/BI dashboards** - Creating new dashboards with proper widget configurations - **Troubleshooting visualization errors** - Fixing "no fields to visualize", empty charts, or formatting issues - **Deploying dashboards via API** - Automating dashboard deployment with UPDATE-or-CREATE pattern - **Validating dashboard queries** - Pre-deployment SQL validation to catch errors before deployment - **Querying monitoring tables** - Accessing Lakehouse Monitoring profile and drift metrics - **Configuring parameters** - Setting up time ranges, filters, and multi-select parameters - **Planning dashboard projects** - Using templates to gather requirements and plan implementation - **Onboarding new developers** - Teaching AI/BI dashboard best practices with working examples ## Mandatory Skill Dependencies **Before building dashboard datasets, determine which query pattern the task requires:** | Query Pattern | Required Pre-Read | Trigger | |---|---|---| | `MEASURE()` against Metric Views | **MUST READ** `semantic-layer/01-metric-views-patterns/SKILL.md` + [references/metric-view-dashboard-queries.md](references/metric-view-dashboard-queries.md) | User mentions "MEASURE()", "Metric View", or semantic layer queries | | Direct Gold table SQL | No additional skill needed | Querying fact/dimension tables directly | | Monitoring / system table queries | **MUST READ** `monitoring/01-lakehouse-monitoring-comprehensive/SKILL.md` | Dashboard includes monitoring widgets | **Always load these common skills first (per AGENTS.md):** - `skills/databricks-expert-agent/SKILL.md` — "Extract Don't Generate" principle, core SA behavior - `common/naming-tagging-standards/SKILL.md` — naming conventions for dashboards and datasets > **Plan addendum filename:** Dashboards are always planned in `plans/phase1-addendum-1.5-aibi-dashboards.md`. See [`planning/00-project-planning/assets/addendum-numbering.md`](../../planning/00-project-planning/assets/addendum-numbering.md) for the canonical numbering table. The legacy name `phase1-addendum-1.1-dashboards.md` is forbidden — if you see it anywhere, replace it with `1.5-aibi-dashboards.md`. **Complementary installed skills** (check `available_skills` list): - `databricks-lakeview-dashboard` — comprehensive widget JSON patterns for 16+ chart types, **mandatory "TEST EVERY QUERY" validation workflow** - `databricks-lakeview-dashboard-analyzer` — analyzing existing dashboards for patterns ## 🚀 Quick Start (2 Hours) **Goal:** Create visual dashboards with AI-powered insights for business users **What You'll Create:** 1. SQL queries from Metric Views or Gold tables 2. AI/BI Dashboard via UI (drag-and-drop layout) 3. Auto-refresh schedule **Fast Track (UI-Based):** ``` 1. Navigate to: Databricks Workspace → Dashboards → Create AI/BI Dashboard 2. Add Data → Query Metric View or Gold table 3. Add Visualizations: - Counter tiles for KPIs (Total Revenue, Units, Transactions) - Bar charts for comparisons (Revenue by Store) - Line charts for trends (Daily Revenue Trend) - Tables for drill-down (Top Products Detail) 4. Add Filters (Date Range, Store, Category) 5. Configure Layout (Canvas: 1280px wide, tiles sized to fit) 6. Enable Auto-refresh (Hourly/Daily) 7. Share with business users ``` **Query Pattern (Metric Views):** ```sql -- Use MEASURE() function for semantic metrics SELECT store_name, MEASURE(`Total Revenue`) as revenue, MEASURE(`Total Units`) as units, MEASURE(`Transaction Count`) as transactions FROM sales_performance_metrics WHERE transaction_date BETWEEN :start_date AND :end_date ORDER BY revenue DESC LIMIT 10 ``` **Best Practices:** - ✅ **Use Metric Views** (not raw tables) for consistent metrics - ✅ **Add filters** for date range, key dimensions - ✅ **Counter tiles** for top KPIs (large, prominent) - ✅ **Charts** for trends and comparisons - ✅ **Auto-refresh** for near real-time dashboards **Output:** Professional dashboard with AI-powered insights **Time Estimate:** 2-4 hours for complete dashboard **Production Example:** See `references/Jobs System Tables Dashboard.lvdash.json` for a complete working example demonstrating all patterns from this skill. --- ## 📋 Project Planning Template **Use this template to gather requirements before building your dashboard.** ### Dashboard Purpose - **Dashboard Name:** _________________ (e.g., "Sales Performance Dashboard", "Patient Outcomes Dashboard") - **Audience:** _________________ (e.g., "Sales Managers", "Hospital Administrators", "Finance Team") - **Update Frequency:** [ ] Real-time [ ] Hourly [ ] Daily [ ] Weekly - **Primary Goal:** _________________ (e.g., "Track daily KPIs", "Monitor data quality", "Analyze trends") ### Data Sources - **Catalog:** _________________ (e.g., my_catalog) - **Schema:** _________________ (e.g., my_project_gold) - **Primary Data Source:** [ ] Metric View [ ] Gold Fact Table [ ] System Tables - **Table/View Name:** _________________ (e.g., sales_performance_metrics, fact_sales_daily) ### KPIs to Display (3-6 key metrics) | # | KPI Name | Source Field | Format | |---|----------|--------------|--------| | 1 | Total Revenue | SUM(net_revenue) | Currency (USD) | | 2 | _____________ | ______________ | _____________ | | 3 | _____________ | ______________ | _____________ | | 4 | _____________ | ______________ | _____________ | **Example - Retail:** - Total Revenue (Currency), Total Units (Number), Transaction Count (Number) **Example - Healthcare:** - Patient Count (Number), Readmission Rate (Percentage), Avg Length of Stay (Number) **Example - Finance:** - Transaction Volume (Number), Total Amount (Currency), Fraud Rate (Percentage) ### Filters Required | Filter Name | Type | Values Source | |------------|------|---------------| | Date Range | Date Range | start_date, end_date | | __________ | Single Select | Dimension table | | __________ | Multi Select | Dimension table | **Common Filters:** - Date Range (always include) - Location/Store/Facility (dimension) - Category/Type (dimension) - Status/State (dimension) ### Charts to Include (3-5 visualizations) | # | Chart Type | Purpose | Data | |---|-----------|---------|------| | 1 | Line Chart | Revenue Trend | Daily revenue over time | | 2 | Bar Chart | Top 10 by metric | Category comparison | | 3 | _________ | ______________ | __________________ | | 4 | _________ | ______________ | __________________ | **Chart Types Available:** - Line Chart (trends over time) - Bar Chart (category comparisons) - Pie Chart (distribution) - Pivot Table (hierarchical drill-down with expand/collapse) - Table (detailed flat data) - Point Map (lat/lng store-level data) - Choropleth Map (geographic aggregations by region) - Sankey (flow/relationship diagrams) - Counter/KPI (single metric) ### Dashboard Pages | Page Name | Purpose | Widgets | |-----------|---------|---------| | Overview | High-level KPIs | 6 KPIs + 2 charts | | Details | Detailed analysis | 1 table + 2 charts | | Global Filters | Cross-page filters | Date, dimensions | ### Input Required Summary - Gold layer tables or Metric Views - KPI requirements (metrics to display) - Filter requirements (date range, dimensions) - Visualization preferences (charts, tables) --- ## Quick Reference ### Top 10 Critical Rules | Rank | Issue | Prevention | |------|-------|------------| | 1 | **Lakeview JSON Format** | Datasets MUST use `queryLines` (array) + `catalog` + `schema` — NOT `query` (string). Textboxes MUST use `multilineTextboxSpec.lines` — NOT `textbox_spec`. Violating this causes ALL visuals to error. | | 2 | **Dataset `columns` for Hierarchy Pivots** | Deep hierarchy pivots with `cubeGroupingSets` MUST use dataset `columns` array for ratio calculations + `MEASURE()` references in widget query + `cell` encodings (Pattern A). Do NOT use `values` encoding with `cubeGroupingSets`. | | 3 | Widget-Query Column Mismatch | Always use explicit SQL aliases matching widget `fieldName` | | 4 | Incorrect Number Formatting | Return raw numbers, not formatted strings | | 5 | Missing Parameter Definitions | Define ALL parameters in dataset's `parameters` array | | 6 | Ratio Metrics in Pivots | Use `disaggregated: false`. **Always prefer Pattern A** (dataset `columns` + `MEASURE()` + `cell` encoding) — it works in both the UI editor/draft mode and published view. Pattern B (inline expressions + `values` encoding) only renders when published and is invisible in the UI draft editor. Neither pattern uses `transform`. | | 7 | Monitoring Table Schema | Use `CASE` pivots on `column_name`, access `window.start` | | 8 | Map Coordinates Must Be Numeric | CAST lat/lng to DOUBLE upstream; STRING coordinates render blank maps | | 9 | Metric View Column References | Use bare dimension `name` in queries — not `source.col` or `dim.col` prefixes | | 10 | Bundle Deploy vs UI Draft | Bundle deploy updates published version; use API PATCH + publish to overwrite UI draft state | ### Widget-Query Alignment **Rule:** Widget `fieldName` MUST exactly match query output column alias. ```sql -- ✅ CORRECT SELECT COUNT(*) AS total_queries FROM ... -- Widget: "fieldName": "total_queries" -- ❌ WRONG SELECT COUNT(*) AS query_count FROM ... -- Widget: "fieldName": "total_queries" -- MISMATCH! ``` ### Number Formatting | Format Type | Expects | Example | |-------------|---------|---------| | `number-plain` | Raw number | `1234` → `1,234` | | `number-percent` | 0-1 decimal (×100) | `0.85` → `85%` |