SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsdatabricks-aibi-dashboards

databricks-aibi-dashboards

Data & analytics

View repository

Updated 10-16 09:54

About this skill

AI/BI Dashboard Skill

Create Databricks AI/BI dashboards (formerly Lakeview dashboards). Follow these guidelines strictly.

CRITICAL: MANDATORY VALIDATION WORKFLOW

You MUST follow this workflow exactly. Skipping validation causes broken dashboards.

┌─────────────────────────────────────────────────────────────────────┐
│  STEP 1: Get table schemas via get_table_stats_and_schema(catalog, schema)  │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 2: Write SQL queries for each dataset                        │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 3: TEST EVERY QUERY via execute_sql() ← DO NOT SKIP!         │
│          - If query fails, FIX IT before proceeding                │
│          - Verify column names match what widgets will reference   │
│          - Verify data types are correct (dates, numbers, strings) │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 4: Build dashboard JSON using ONLY verified queries          │
├─────────────────────────────────────────────────────────────────────┤
│  STEP 5: Deploy via manage_dashboard(action="create_or_update")    │
└─────────────────────────────────────────────────────────────────────┘

WARNING: If you deploy without testing queries, widgets WILL show "Invalid widget definition" errors!

Available MCP Tools

Tool Description
get_table_stats_and_schema STEP 1: Get table schemas for designing queries
execute_sql STEP 3: Test SQL queries - MANDATORY before deployment!
manage_warehouse (action="get_best") Get available warehouse ID
manage_dashboard STEP 5: Dashboard lifecycle management (see actions below)

manage_dashboard Actions

Action Description Required Params
create_or_update Deploy dashboard JSON (only after validation!) display_name, parent_path, serialized_dashboard, warehouse_id
get Get dashboard details by ID dashboard_id
list List all dashboards (none)
delete Move dashboard to trash dashboard_id
publish Publish a dashboard dashboard_id, warehouse_id
unpublish Unpublish a dashboard dashboard_id

Example usage:

# Create/update dashboard
manage_dashboard(
    action="create_or_update",
    display_name="Sales Dashboard",
    parent_path="/Workspace/Users/me/dashboards",
    serialized_dashboard=dashboard_json,
    warehouse_id="abc123",
    publish=True  # auto-publish after create
)

# Get dashboard details
manage_dashboard(action="get", dashboard_id="dashboard_123")

# List all dashboards
manage_dashboard(action="list")

Reference Files

What are you building? Reference
Any widget (text, counter, table, chart) 1-widget-specifications.md
Dashboard with filters (global or page-level) 2-filters.md
Need a complete working template to adapt 3-examples.md
Debugging a broken dashboard 4-troubleshooting.md

Implementation Guidelines

1) DATASET ARCHITECTURE

  • One dataset per domain (e.g., orders, customers, products)
  • Exactly ONE valid SQL query per dataset (no multiple queries separated by ;)
  • Always use fully-qualified table names: catalog.schema.table_name
  • SELECT must include all dimensions needed by widgets and all derived columns via AS aliases
  • Put ALL business logic (CASE/WHEN, COALESCE, ratios) into the dataset SELECT with explicit aliases
  • Contract rule: Every widget fieldName must exactly match a dataset column or alias

2) WIDGET FIELD EXPRESSIONS

CRITICAL: Field Name Matching Rule The name in query.fields MUST exactly match the fieldName in encodings. If they don't match, the widget shows "no selected fields to visualize" error!

Correct pattern for aggregations:

// In query.fields:
{"name": "sum(spend)", "expression": "SUM(`spend`)"}

// In encodings (must match!):
{"fieldName": "sum(spend)", "displayName": "Total Spend"}

WRONG - names don't match:

// In query.fields:
{"name": "spend", "expression": "SUM(`spend`)"}  // name is "spend"

// In encodings:
{"fieldName": "sum(spend)", ...}  // ERROR: "sum(spend)" ≠ "spend"

Allowed expressions in widget queries (you CANNOT use CAST or other SQL in expressions):

For numbers:

{"name": "sum(revenue)", "expression": "SUM(`revenue`)"}
{"name": "avg(price)", "expression": "AVG(`price`)"}
{"name": "count(orders)", "expression": "COUNT(`order_id`)"}
{"name": "countdistinct(customers)", "expression": "COUNT(DISTINCT `customer_id`)"}
{"name": "min(date)", "expression": "MIN(`order_date`)"}
{"name": "max(date)", "expression": "MAX(`order_date`)"}

For dates (use daily for timeseries, weekly/monthly for grouped comparisons):

{"name": "daily(date)", "expression": "DATE_TRUNC(\"DAY\", `date`)"}
{"name": "weekly(date)", "expression": "DATE_TRUNC(\"WEEK\", `date`)"}
{"name": "monthly(date)", "expression": "DATE_TRUNC(\"MONTH\", `date`)"}

Simple field reference (for pre-aggregated data):

{"name": "category", "expression": "`category`"}

If you need conditional logic or multi-field formulas, compute a derived column in the dataset SQL first.

3) SPARK SQL PATTERNS

  • Date math: date_sub(current_date(), N) for days, add_months(current_date(), -N) for months
  • Date truncation: DATE_TRUNC('DAY'|'WEEK'|'MONTH'|'QUARTER'|'YEAR', column)
  • AVOID INTERVAL syntax - use functions instead

4) LAYOUT (12-Column Grid, NO GAPS)

Every page must include "layoutVersion": "GRID_V1" alongside pageType.

{
  "name": "overview",
  "displayName": "Overview",
  "pageType": "PAGE_TYPE_CANVAS",
  "layoutVersion": "GRID_V1",
  "layout": [...]
}

Each widget has a position: {"x": 0, "y": 0, "width": 4, "height": 4}

CRITICAL: Each row must fill width=12 exactly. No gaps allowed.

Recommended widget sizes:

Widget Type Width Height Notes
Text header 12 1 Full width; use SEPARATE widgets for title and subtitle
Counter/KPI 4 3-4 NEVER height=2 - too cramped!
Line/Bar chart 6 5-6 Pair side-by-side to fill row
Pie chart 6 5-6 Needs space for legend
Full-width chart 12 5-7 For detailed time series
Table 12 5-8 Full width for readability

Standard dashboard structure:

y=0:  Title (w=12, h=1) - Dashboard title (use separate widget!)
y=1:  Subtitle (w=12, h=1) - Description (use separate widget!)
y=2:  KPIs (w=4 each, h=3) - 3 key metrics side-by-side
y=5:  Section header (w=12, h=1) - "Trends" or similar
y=6:  Charts (w=6 each, h=5) - Two charts side-by-side
y=11: Section header (w=12, h=1) - "Details"
y=12: Table (w=12, h=6) - Detailed data

5) CARDINALITY & READABILITY (CRITICAL)

Dashboard readability depends on limiting distinct values:

Dimension Type Max Values Examples
Chart color/groups 3-8 4 regions, 5 product lines, 3 tiers
Filters 4-10 8 countries, 5 channels
High cardinality Table only customer_id, order_id, SKU

Before creating any chart with color/grouping:

  1. Check column cardinality (use get_table_stats_and_schema to see distinct values)
  2. If >10 distinct values, aggregate to higher level OR use TOP-N + "Other" bucket
  3. For high-cardinality dimensions, use a table widget instead of a chart

6) QUALITY CHECKLIST

Before deploying, verify:

  1. All widget names use only alphanumeric + hyphens + underscores
  2. Every page has "layoutVersion": "GRID_V1"
  3. All rows sum to width=12 with no gaps
  4. KPIs use height 3-4, charts use height 5-6
  5. Chart dimensions have ≤8 distinct values
  6. All widget fieldNames match dataset columns exactly
  7. Field name in query.fields matches fieldName in encodings exactly (e.g., both "sum(spend)")
  8. Counter datasets: use disaggregated: true for 1-row datasets, disaggregated: false with aggregation for multi-row
  9. Percent values are 0-1 (not 0-100)
  10. SQL uses Spark syntax (date_sub, not INTERVAL)
  11. All SQL queries tested via execute_sql and return expected data

Related Skills