# Data Bi Tools

> Use this skill when asked about BI, dashboard, Metabase, Superset, Looker, Tableau, PowerBI, reporting, data visualization, business intelligence, KPI dashboards, semantic layer, embedded analytics, or LookML. This skill enforces: tool selection based on team size and use case, semantic layer design with business-friendly metrics, dashboard layout patterns (KPI, trend, comparison, funnel), embedded analytics via SDK/iFrame, data source configuration, caching strategy, permissions model with row-level security, and scheduling. Do NOT use for: data warehouse schema design, ETL pipeline configuration, or ad-hoc SQL queries.

- Skill: `j4flmao/data-bi-tools` (Agent Skill, multi-file: 8 files)
- Install (CLI): `npx skillmds@latest add j4flmao/data-bi-tools`
- Raw SKILL.md: https://api.skillmd.com/api/skills/j4flmao/data-bi-tools/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- License: MIT
- Author: j4flmao (https://skillmd.com/u/j4flmao)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/j4flmao/data-bi-tools

---


# Data BI Tools

## Purpose
Select BI tools (Metabase, Superset, Looker, Tableau, PowerBI),
design semantic layers and dashboard layouts
(KPI, trend, comparison, funnel), configure embedded analytics
via iFrame/SDK, set up data source connections with caching,
and build permission models with row-level security.

## Agent Protocol

### Trigger
Exact user phrases: "BI", "dashboard", "Metabase", "Superset",
"Looker", "Tableau", "PowerBI", "reporting", "data visualization",
"business intelligence", "KPI dashboard", "BI tool",
"semantic layer", "embedded analytics", "BI dashboard design",
"LookML", "chart", "analytics dashboard", "BI permissions".

### Input Context
Before activating, verify:
- Team size and technical skill level
- Data warehouse platform
- Number of dashboards and refresh frequency
- Authentication provider (SSO, SAML, OIDC)
- Embedding requirements (customer-facing vs internal)
- Budget range (open-source vs enterprise SaaS)

### Output Artifact
BI strategy with tool comparison, semantic layer design,
dashboard mockup description as YAML and markdown.

### Response Format
```yaml
# Tool comparison matrix
# Semantic layer config
# Dashboard layout spec
# Permission model
# Cache policy
```

No preamble. No postamble. No explanations. No filler/hedging/transitions.
Compress output — why use many token when few do trick.

### Completion Criteria
- [ ] BI tool selected based on evaluation criteria
- [ ] Semantic layer defined with business-friendly metric names
- [ ] Dashboard layout with hierarchy (executive → operational → tactical)
- [ ] Embedded analytics integration approach documented
- [ ] Permission model with row-level security configured
- [ ] Performance optimization for dashboard load times
- [ ] Data source configuration and caching policy defined
- [ ] Dashboard scheduling and alerting configured

### Max Response Length
250 lines of configuration and design.

## Workflow

### Step 1: Tool Selection
Metabase: open-source, no-code query builder.
Best for teams under 50 users.
Quick setup, minimal maintenance.

Superset: open-source, 60+ chart types.
SQL Lab for analyst ad-hoc queries.
Best for 50-500 users with SQL skills.

Looker: enterprise, LookML semantic layer.
Git-backed, version-controlled metrics.
Best for over 100 users needing governance.

Tableau: proprietary, desktop authoring.
Rich visual analytics with VizQL.
Best for design-heavy dashboards.

PowerBI: Microsoft ecosystem, Excel integration.
Tight Teams and SharePoint integration.
Best for Windows-first organizations.

Decision matrix: compare license, hosting,
semantic layer depth, RLS, embedding, pricing.
Match to team size, budget, and capability.

### Step 2: Data Source Configuration
Connections: JDBC or ODBC per warehouse.
Auth: service account with read-only access.
IAM roles for cloud data warehouses.

Schema exposure: analytics schema only.
Marts and views, never raw source tables.
SSL/TLS encryption enforced.

Connection pooling: max 10 per BI tool.
VPN for on-premise via Bastion host.
Validate with sample query during setup.

### Step 3: Semantic Layer Design
Define business metrics in one place:

Revenue: SUM(order_total) WHERE status != cancelled.
Active users: COUNT DISTINCT user_id with event in 30d.
Churn rate: users lost divided by users at start.
NRR: (renewals + upgrades - downgrades) / starting revenue.
CAC: total sales cost / new customers acquired.

Looker: LookML files with dimension, measure, view, explore.
Version controlled in git with CI/CD testing.
Metabase: Models with saved metric definitions.
Superset: virtual datasets with computed columns.

Naming: business-friendly snake_case.
`total_revenue`, `active_users_weekly`, `customer_lifetime_value`.

Documentation: name, description, formula, owner, freshness SLA.
No raw SQL in dashboards — always use semantic layer.

### Step 3a: LookML Examples

```lookml
view: fct_orders {
  sql_table_name: analytics.fct_orders ;;
  dimension: order_id { type: string sql: ${TABLE}.order_id ;; primary_key: yes }
  dimension: status { type: string sql: ${TABLE}.status ;;
    allowed_value: { value: "pending" } { value: "completed" }
    allowed_value: { value: "cancelled" } { value: "refunded" } }
  dimension_group: created { type: time timeframes: [date,week,month] sql: ${TABLE}.created_at ;; }
  measure: total_revenue { type: sum sql: ${TABLE}.total_amount ;; value_format_name: usd }
  measure: order_count { type: count drill_fields: [order_id, total_revenue] }
  measure: avg_order_value { type: average sql: ${TABLE}.total_amount ;; value_format_name: usd }
}

explore: fct_orders {
  join: dim_customers {
    sql_on: ${fct_orders.customer_id} = ${dim_customers.customer_id} ;;
    type: left_outer relationship: many_to_one
  }
  access_filter: { field: dim_customers.region user_attribute: region }
}
```

```lookml
view: customer_metrics {
  derived_table: {
    sql:
      SELECT customer_id, COUNT(DISTINCT order_id) AS lifetime_orders,
             SUM(total_amount) AS lifetime_value
      FROM analytics.fct_orders GROUP BY 1 ;;
    persist_for: "24 hours"
  }
  measure: avg_lifetime_value { type: average sql: ${TABLE}.lifetime_value ;; }
}
```

### Step 4: Dashboard Layout Patterns
Executive dashboard:
Top 5-7 KPIs as cards with sparklines.
Revenue, active users, churn, gross margin, NPS.
Trend charts below (12-month area chart).
Comparison: budget versus actual.
Brief data table for top items.

Operational dashboard:
Daily metrics: orders, avg value, fulfillment rate.
Hourly bar chart: today versus yesterday.
Status breakdown as donut chart.
Recent orders table with 30s auto-refresh.

Tactical dashboard:
Funnel steps with drop-off percentages.
Cohort analysis: retention heatmap.
Geographic breakdown: map and table.
Drill-down to transaction detail.

Design rules: KPI cards on top always visible.
Trend charts below, detail tables at bottom.
Max 10 visualizations per dashboard.
Consistent color scheme.
Filters: date range plus top 3 dimensions.

### Step 4a: Superset and Metabase Examples

```json
{
  "superset_chart_api": {
    "POST /api/v1/chart/": {
      "dashboard_id": 5,
      "datasource_id": 12,
      "datasource_type": "table",
      "slice_name": "Revenue by Month",
      "viz_type": "echarts_timeseries_bar",
      "params": {
        "metrics": ["SUM(total_amount)"],
        "time_range": "LAST_12_MONTHS",
        "groupby": ["status"],
        "row_limit": 10000
      }
    }
  },
  "metabase_question": {
    "POST /api/card": {
      "name": "Monthly Active Users",
      "dataset_query": {
        "database": 3,
        "type": "query",
        "query": {
          "source_table": 47,
          "aggregation": [["count", ["distinct", ["field", "user_id", null]]]],
          "breakout": [["field", "event_month", {"temporal-unit": "month"}]],
          "filter": ["time-interval", ["field", "event_at", null], -30, "day"]
        }
      },
      "display": "line",
      "visualization_settings": {}
    }
  }
}
```

```yaml
# dashboard-as-code YAML spec
dashboard:
  title: Executive Summary
  tags: [executive, revenue, weekly]
  refresh_interval: 3600
  filters:
    - field: date_range
      type: date
      default: LAST_30_DAYS
    - field: region
      type: dropdown
      default: ALL
  charts:
    - title: Total Revenue
      type: kpi
      metric: total_revenue
      sparkline: true
    - title: Revenue Trend
      type: timeseries
      metric: total_revenue
      dimensions: [created_month, region]
      granularity: month
    - title: Orders by Status
      type: donut
      metric: order_count
      dimension: status
    - title: Top Customers
      type: table
      columns: [customer_id, lifetime_value, last_order_date]
      limit: 25
```

### Step 5: Embedded Analytics
Metabase: signed JWT embed with row-level security.
Backend generates token with resource and params.
Frontend renders in iframe.

Superset: guest token with RLS clauses.
CORS restricted to allowed origins.

Looker: SSO embed URL with embed_domain.
Looker API for custom integration.

Tableau: JS API with trusted authentication.
PowerBI: REST API with service principal and Azure AD.

Short-lived embed token (1 hour max).
Cache server-side, refresh on expiry.
Restrict CORS, implement CSP for iframe sources.

### Step 6: Permissions Model
Roles: admin (manage users and settings),
developer (create and edit dashboards),
viewer (view only, no export),
restricted (specific dashboards only).

Row-level security:
Data source filter: WHERE region = current_user_region().
Attribute-based: map JWT claim to data column.

Metabase: data sandboxing (Enterprise).
Superset: data source filters per role.
Looker: access grants with user attributes.

Auth: SSO via SAML or OIDC.
SCIM for user provisioning and deprovisioning.

Audit: dashboard views, query executions, data exports.
Monthly access review, quarterly permission audit.

### Step 7: Caching and Performance
Materialized views for all dashboard source tables.
BI caching: dashboard cache (1 hour), query cache (1 hour),
context cache (24 hours). Invalidate on refresh.

Query limits: 10K rows returned max, 60s timeout max.
Refresh schedule: executive every 4 hours,
operational every 1 hour, tactical on-demand.

Cache warming: pre-load before business hours.
Monitor: load time under 5s, query duration, cache hit rate over 80%.

### Step 8: Scheduling and Alerts
Subscriptions: email (PDF, CSV, charts), Slack (formatted).
Schedule: daily, weekly, monthly with timezone support.

Alerts: metric drops below threshold, anomaly detection.
Delivery by role and department.
Review subscriptions quarterly, remove stale.

### BI Tool Selection

```yaml
bi_tool_comparison:
  tableau:
    strengths: ["Best visualization library", "Strong calculated fields", "Large community"]
    weaknesses: ["Expensive per-user licensing", "Limited self-service data prep", "Tableau Server admin overhead"]
    best_for: "Enterprise dashboards, visual analytics, complex charting"
    licensing: "Creator/Explorer/Viewer tiers, $15-70/user/month"
  
  looker:
    strengths: ["LookML semantic layer (source of truth)", "Git-versioned content", "Embedded analytics"]
    weaknesses: ["LookML learning curve", "Custom visualization limited", "Performance on complex queries"]
    best_for: "Semantic layer governance, embedded analytics, git-versioned metrics"
    licensing: "Standard/Enterprise, per-user or per-instance"
  
  power_bi:
    strengths: ["Low cost", "Office 365 integration", "Power Query (data prep)", "DAX for complex measures"]
    weaknesses: ["Desktop dependency", "Large dataset performance", "Limited Linux/cloud hosting"]
    best_for: "Microsoft ecosystem, self-service analytics, cost-effective BI"
    licensing: "Free (limited), Pro ($10), Premium ($20-5K/user/month)"
  
  metabase:
    strengths: ["Open-source", "Easy setup", "SQL-native", "Self-hosted"]
    weaknesses: ["Basic visualizations", "No scheduling in OSS", "Limited governance"]
    best_for: "Startups, small teams, SQL-heavy analytics, open-source stack"
    licensing: "OSS (free), Enterprise (paid)"
  
  superset:
    strengths: ["Open-source", "Rich SQL editor", "Chart types", "Caching"]
    weaknesses: ["Complex deployment", "Authentication limited", "No mobile"]
    best_for: "Open-source stack, SQL-first teams, custom embedding"
    licensing: "Apache 2.0 (free)"
```

### BI Performance Tuning

```yaml
performance_optimization:
  dashboard_loading:
    - "Use materialized views for all dashboard source data"
    - "Limit rows returned: 10K max per query, 100K max per dashboard"
    - "Pre-aggregate at warehouse level (daily/hourly rollups)"
    - "Set query timeout: 60s max per query"
    - "Use BI caching: dashboard cache (1hr), query cache (1hr)"
    - "Implement cache warming: pre-load dashboards before business hours"
  
  query_optimization:
    - "Avoid cross-joins, unaggregated detail tables in dashboards"
    - "Use incremental refresh for large datasets"
    - "Push filters to warehouse (WHERE clause, not in-memory)"
    - "Limit dashboard tiles: max 10-15 charts per dashboard"
    - "Use summary tables for trend lines over full detail"
  
  monitoring:
    - "Dashboard load time: target < 5s, alert > 10s"
    - "Query duration: target < 2s, alert > 10s"
    - "Cache hit rate: target > 80%"
    - "Concurrent users: track per dashboard, add resources at 90%"
    - "Data freshness: track last refresh time per dashboard"
```

### BI Security and Governance

```yaml
security_model:
  authentication:
    - "SSO (SAML/OIDC) for all BI tools — no local passwords"
    - "SCIM for automated user provisioning/deprovisioning"
    - "MFA required for all admin accounts"
  
  authorization:
    - "Row-level security at data source (warehouse views with WHERE clauses)"
    - "Column-level security via warehouse masking policies"
    - "Dashboard-level permissions by team/role"
    - "Embed tokens: 1 hour max TTL, scoped to specific content"
  
  audit:
    - "Log all dashboard views, query executions, data exports"
    - "Monthly access review, quarterly permission audit"
    - "Alert on: first-time export, bulk export, off-hours access"
  
  data_governance:
    - "No raw SQL in dashboards — use semantic layer"
    - "Certified dashboards only for executive consumption"
    - "Deprecated dashboards removed within 30 days"
    - "Export controls: CSV only, no full-dataset Excel exports"
```

### Decision Tree

#### BI Tool Selection
```
Team and requirements?
├── Enterprise, visual analytics, complex charts → Tableau
├── Semantic layer governance, embedded analytics → Looker
├── Microsoft ecosystem, self-service, cost-sensitive → Power BI
├── Open-source, SQL-first, small team → Metabase or Superset
└── Embedded customer-facing analytics → Looker or Superset

Key question: centralized vs decentralized?
├── Centralized (one semantic layer, governed metrics) → Looker (LookML)
├── Decentralized (teams build own dashboards) → Power BI or Tableau
└── Hybrid (central models, team dashboards) → Any with semantic layer
```

## Rules
- One semantic layer, many dashboards
- Dashboard load under 5 seconds with caching
- Row-level security enforced at data source, not application
- Every dashboard has a purpose, owner, and refresh schedule
- No raw SQL in dashboards — use semantic layer
- Executive dashboards show trends, not raw numbers
- Cache aggressively — stale better than slow
- Export controls prevent data leakage
- Embed tokens short-lived (1 hour max)
- Pre-aggregate at warehouse for dashboard performance
- Monitor dashboard load times and cache hit rates
- Use SSO + SCIM for BI user management
- Automate dashboard generation from semantic layer definitions

## References
  - references/bi-security-governance.md — BI Security and Governance
  - references/bi-tools-architecture.md — BI Tools Architecture
  - references/bi-tools-performance.md — BI Tools Performance Optimization
  - references/dashboard-design.md — Dashboard Design
  - references/embedding-analytics.md — Embedded Analytics
  - references/lookml-examples.md — LookML Examples
  - references/semantic-layer-patterns.md — Semantic Layer Patterns
  - references/tool-selection.md — BI Tool Selection
## Architecture Decision Trees

```
BI Tool Selection
├── Self-service analytics?
│   ├── Yes → Looker (LookML semantic layer)
│   └── No → Power BI / Tableau (managed dashboards)
├── Real-time dashboards (< 5s latency)?
│   ├── Yes → Superset + Druid / Pinot
│   └── No → Traditional OLAP (Snowflake, Redshift)
└── Embedded analytics for customers?
    ├── Yes → ThoughtSpot / Metabase embedding SDK
    └── No → Internal BI tool with SSO
```

**Decision criteria**: Evaluate query concurrency, data freshness requirements, user skill level, and embedding needs before selecting a BI platform.

## Implementation Patterns

### Semantic Layer Pattern
```python
# bi_tools/semantic_layer.py
from pydantic import BaseModel
from typing import Optional

class Metric(BaseModel):
    name: str
    sql_expression: str
    aggregation: str = "SUM"
    description: Optional[str] = None
    dimensions: list[str] = []

class Dimension(BaseModel):
    name: str
    sql_column: str
    description: Optional[str] = None

class SemanticModel(BaseModel):
    metrics: list[Metric]
    dimensions: list[Dimension]

    def generate_lookml(self) -> str:
        parts = [f"view: {name} {{ ... }}" for name in self.dimensions]
        return "\n".join(parts)
```

### Dashboard-as-Code Pattern
```yaml
# bi_tools/dashboard.yml
dashboard:
  name: "Revenue Overview"
  refresh_interval: 3600
  tiles:
    - type: timeseries
      metric: revenue
      dimensions: [date, region]
    - type: kpi
      metric: total_revenue
      format: currency
  access:
    roles: [analyst, finance]
    embed: true
```

## Production Considerations

- **Query governance**: Set query timeout limits (default 5 min) and cost caps per dashboard to prevent runaway queries.
- **Caching strategy**: Use Redis (TTL 5 min) or BI-native caching to reduce warehouse load by 40-60%.
- **Scheduled refreshes**: Align refresh schedules with source ETL completion; use status webhooks.
- **Row-level security (RLS)**: Define RLS policies in the semantic layer, not per-dashboard.
- **Version control**: Store dashboard definitions (LookML, dbt metrics) in Git with CI/CD validation.
- **Usage analytics**: Track dashboard popularity, load times, and query patterns to optimize.

## Anti-Patterns

| Anti-Pattern | Consequence | Solution |
|---|---|---|
| Hard-coded filters per user | Maintenance nightmare | RLS in semantic layer |
| Dashboard per business question | Exploding dashboard count | Parameterized dashboards |
| Direct DB queries from BI tool | Security risk, no governance | Always use semantic layer |
| One BI tool for all use cases | Poor fit for embedded vs internal | Tiered BI strategy |

## Performance Optimization

- **Pre-aggregation**: Create materialized views at the warehouse level for BI query patterns.
- **Query reduction**: Implement dashboard caching and avoid live queries for historical data (> 30 days).
- **Concurrent user scaling**: Use query queuing and read replicas for high concurrency (> 50 users).
- **Slow query analysis**: Profile BI queries with `EXPLAIN ANALYZE` and optimize table design (sort keys, partitioning).
- **BI server sizing**: For Superset/Tableau Server, allocate 4 vCPU + 16 GB RAM per 10 concurrent users.

## Security Considerations

- **Authentication**: Enforce SSO (SAML/OIDC) for all BI access; disable local auth.
- **Authorization**: Implement RLS at the semantic layer filtering by user role/region.
- **Data masking**: Mask PII columns (email, SSN) in shared dashboards.
- **Audit logging**: Log all query executions, dashboard views, and exports to SIEM.
- **Network security**: Deploy BI tools in private subnets with reverse proxy (Nginx/Caddy) and WAF.
- **Token management**: Rotate embed tokens hourly; never expose API keys in dashboards.

## Handoff
`data-data-warehouse` for optimizing warehouse for BI queries
`data-data-quality` for validating dashboard data accuracy