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

# 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

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 }
}

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

{
  "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": {}
    }
  }
}

# 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

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

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

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

# 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

# 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

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