BI 可视化工具深度对比：Tableau、Power BI、Superset 与 Metabase

# Tableau Desktop macOS 静默安装
sudo installer -pkg /Volumes/TableauDesktop/TableauDesktop.pkg -target /
echo "YOUR_LICENSE_KEY" | /Applications/Tableau\ Desktop.app/Contents/MacOS/Tableau -activate

# Power BI Desktop 安装
winget install Microsoft.PowerBI

# Superset Docker 启动
git clone https://github.com/apache/superset.git
cd superset
docker compose -f docker-compose-non-dev.yml up -d
docker exec -it superset_app superset fab create-admin \
  --username admin --firstname Admin --lastname User \
  --email admin@example.com --password admin
docker exec -it superset_app superset load-examples

# Metabase Docker 启动
docker run -d -p 3000:3000 --name metabase \
  -v ~/.metabase-data:/metabase-data \
  -e "MB_DB_FILE=/metabase-data/metabase.db" \
  --restart always metabase/metabase:latest

-- Tableau 自定义 SQL 示例
SELECT o.order_id, o.order_date, c.customer_name, c.segment,
       p.product_name, p.category, oi.quantity, oi.sales, oi.profit
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')

{ FIXED [Customer ID] : MIN([Order Date]) }
{ EXCLUDE [Category] : SUM([Sales]) }
{ INCLUDE [Order ID] : SUM([Profit]) }

import pandas as pd
from statsmodels.tsa.holtwinters import ExponentialSmoothing

dates = pd.to_datetime(_arg1)
values = pd.Series(_arg2, index=dates).sort_index().resample('M').sum().ffill()
model = ExponentialSmoothing(values, trend='add', seasonal='add', seasonal_periods=12).fit()
return list(model.forecast(6).values)

import tableauserverclient as TSC

tableau_auth = TSC.PersonalAccessTokenAuth('TOKEN_NAME', 'TOKEN_SECRET')
server = TSC.Server('https://tableau.company.com', use_server_version=True)

with server.auth.sign_in(tableau_auth):
    req_option = TSC.RequestOptions()
    req_option.filter.add(
        TSC.Filter(TSC.RequestOptions.Field.Name,
                   TSC.RequestOptions.Operator.Equals, 'Sales Dashboard')
    )
    workbooks, _ = server.workbooks.get(req_option)
    for wb in workbooks:
        server.workbooks.refresh(wb.id)

let
    BaseUrl = "https://api.company.com/v1/orders",
    GetPage = (page as number) as table =>
        let Url = BaseUrl & "?page=" & Number.ToText(page) & "&size=100",
            Source = Json.Document(Web.Contents(Url))
        in Table.FromRecords(Source[data]),
    Combined = Table.Combine(List.Transform({1..10}, each GetPage(_)))
in Combined

Rolling 12M Sales =
CALCULATE([Total Sales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))

Customer LTV Segment =
SWITCH(TRUE(), [Customer LTV]>=10000, "High Value",
       [Customer LTV]>=5000, "Medium Value", [Customer LTV]>=1000, "Low Value", "Churn Risk")

Optimized Margin % =
VAR TotalRevenue = SUM('Sales'[Revenue])
VAR TotalCost = SUM('Sales'[Cost])
RETURN DIVIDE(TotalRevenue - TotalCost, TotalRevenue, 0)

using Microsoft.PowerBI.Api;
using Microsoft.Rest;

public async Task<EmbedToken> GetEmbedTokenAsync(string reportId)
{
    var credential = new ClientSecretCredential(_tenantId, _clientId, _clientSecret);
    var token = await credential.GetTokenAsync(
        new TokenRequestContext(new[] { "https://analysis.windows.net/powerbi/api/.default" }));
    using var client = new PowerBIClient(new Uri("https://api.powerbi.com/"),
                                          new TokenCredentials(token.Token));
    var report = await client.Reports.GetReportInGroupAsync(
        Guid.Parse(_workspaceId), Guid.Parse(reportId));
    var req = new GenerateTokenRequest(accessLevel: "view", datasetId: report.DatasetId);
    return await client.Reports.GenerateTokenAsync(
        Guid.Parse(_workspaceId), Guid.Parse(reportId), req);
}

import requests

BASE = "http://localhost:8088"
H = {"Authorization": "Bearer TOKEN", "Content-Type": "application/json"}

db = requests.post(f"{BASE}/api/v1/database/", headers=H, json={
    "engine": "postgresql", "name": "Production DB",
    "sqlalchemy_uri": "postgresql://user:pass@postgres:5432/analytics"
}).json()

ds = requests.post(f"{BASE}/api/v1/dataset/", headers=H, json={
    "database": db["id"], "schema": "public", "table_name": "sales_orders"
}).json()
print(f"Dataset ID: {ds['id']}")

WITH monthly_stats AS (
  SELECT DATE_TRUNC('month', order_date) AS month, region,
         COUNT(DISTINCT customer_id) AS unique_customers,
         SUM(sales_amount) AS total_sales,
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sales_amount) AS median_order
  FROM sales_orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY 1, 2
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY month ORDER BY total_sales DESC) AS sales_rank
  FROM monthly_stats
)
SELECT * FROM ranked WHERE sales_rank <= 5 ORDER BY month DESC, sales_rank;

requests.post(f"{BASE}/api/v1/rowlevelsecurity/", headers=H, json={
    "clause": "tenant_id = {{ current_username() }}",
    "dataset_id": 15, "roles": [3, 4], "group_key": "tenant_isolation"
})

# superset_config.py
from cachelib.redis import RedisCache
CACHE_CONFIG = {
    'CACHE_TYPE': 'RedisCache', 'CACHE_DEFAULT_TIMEOUT': 3600,
    'CACHE_KEY_PREFIX': 'superset_results', 'CACHE_REDIS_URL': 'redis://localhost:6379/1'
}

curl -X POST http://localhost:3000/api/card \
  -H "Content-Type: application/json" \
  -H "X-Metabase-Session: TOKEN" \
  -d '{"name":"每日订单量","dataset_query":{"type":"query","query":{
    "source-table":4,"aggregation":[["count"]],
    "breakout":[["field",25,{"temporal-unit":"day"}]],
    "filter":["time-interval",["field",25,null],-30,"day"]
  },"database":2},"display":"line"}'

SELECT order_date, region, product_category,
       COUNT(*) AS order_count, SUM(total_amount) AS revenue
FROM orders
WHERE order_date BETWEEN {{start_date}} AND {{end_date}}
  AND region = {{region_filter}}
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 5 DESC
LIMIT 1000

import jwt
from flask import Flask, render_template

app = Flask(__name__)

@app.route("/dashboard/sales")
def embed():
    token = jwt.encode({
        "resource": {"dashboard": 25},
        "params": {"region": ["North America", "Europe"]},
        "exp": int(time.time()) + 600
    }, "embed-secret", algorithm="HS256")
    url = f"https://metabase.company.com/embed/dashboard/{token}#bordered=true&titled=false"
    return render_template("embed.html", iframe_url=url)

<iframe src="{{ iframe_url }}" style="position:absolute;top:0;left:0;width:100%;height:100%;border:none;"
  allowtransparency sandbox="allow-scripts allow-same-origin"></iframe>

import { t } from '@superset-ui/core';
import transformProps from './transformProps';

export default {
  key: 'my_custom_chart', name: t('我的自定义图表'),
  description: t('基于 ECharts 的定制化图表'),
  controlPanel, transformProps,
  queryObject: { metrics: 'METRIC', groupby: 'GROUPBY' }
};

import { EChartsCoreOption } from 'echarts';
export default function transformProps({ formData, queriesData }: ChartProps): EChartsCoreOption {
  const data = queriesData[0].data;
  return {
    tooltip: { trigger: 'axis' },
    xAxis: { type: 'category', data: data.map(d => d[formData.x_axis]) },
    yAxis: { type: 'value' },
    series: [{
      name: formData.metric_name, type: 'bar',
      data: data.map(d => d[formData.metric_name]),
      itemStyle: { color: (p: any) => p.value > 1000 ? '#5470c6' : '#91cc75' }
    }]
  };
}

# dbt_project.yml
metrics:
  - name: revenue
    label: 营业收入
    model: ref('fct_orders')
    calculation_method: sum
    expression: sales_amount
    timestamp: order_date
    time_grains: [day, week, month, quarter, year]
    dimensions: [region, product_category, customer_segment]

# 通过 Superset REST API 批量注册指标
import requests

BASE = "https://superset.company.com"
HEADERS = {"Authorization": "Bearer <token>", "Content-Type": "application/json"}

metrics_payload = {
    "expression": "SUM(sales_amount - discount_amount)",
    "metric_name": "net_revenue",
    "description": "净营收，扣除折扣",
    "extra_json": '{"certification": {"details": "Finance approved"}}'
}

resp = requests.post(
    f"{BASE}/api/v1/dataset/42/metric/",
    headers=HEADERS,
    json=metrics_payload
)
print(resp.json())

# 数据集虚拟表 SQL（启用 "TEMPLATE PARAMETERS"）
"""
SELECT *
FROM sales_orders
WHERE 1=1
  {% if filter_values('region') %}
  AND region IN {{ filter_values('region') | where_in }}
  {% endif %}
  {% if from_dttm and to_dttm %}
  AND order_date BETWEEN '{{ from_dttm }}' AND '{{ to_dttm }}'
  {% endif %}
  {% if current_username() == 'admin' %}
  -- admin 可查看全量数据，不追加额外过滤
  {% else %}
  AND tenant_id = '{{ current_username() }}'
  {% endif %}
"""

# superset_config.py 关键配置
FEATURE_FLAGS = {
    "ROW_LEVEL_SECURITY": True,
    "EMBEDDED_SUPERSET": True,
    "ALERT_REPORTS": True,
}

from flask_appbuilder.security.manager import AUTH_DB
AUTH_TYPE = AUTH_DB

# 通过 API 创建 RLS 规则示例（管理员执行）
"""
curl -X POST https://superset.company.com/api/v1/rowlevelsecurity/ \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "tenant_isolation",
    "description": "多租户数据隔离",
    "filter_type": "Regular",
    "tables": [42],
    "roles": [5, 6, 7],
    "group_key": "tenant_rls",
    "clause": "tenant_id = '\''{{ current_username() }}'\''"
  }'
"""

# docker-compose 中 celery 调度器环境变量
 superset-worker-beat:
   environment:
     - CELERY_BEAT_SCHEDULE_FILENAME=/app/superset_beat.schedule
     - SUPERSET_ALERT_REPORTS_NOTIFICATION_DRY_RUN=false
     - WEBDRIVER_TYPE=chrome
     - WEBDRIVER_BASEURL=http://superset-app:8088

# 通过 API 创建图表级定时报告
report_payload = {
    "type": "Report",
    "name": "每日 GMV 日报",
    "description": "发送 GMV Top10 图表截图至企业微信",
    "active": True,
    "crontab": "0 9 * * *",
    "chart": 128,
    "owners": [1],
    "recipients": [
        {"type": "email", "recipient_config_json": {"target": "ops@company.com"}},
        {"type": "slack", "recipient_config_json": {"target": "#data-alerts"}}
    ]
}
requests.post(f"{BASE}/api/v1/report/", headers=HEADERS, json=report_payload)

// src/MyCustomChart.ts — 插件注册入口
import { t, ChartMetadata, ChartPlugin } from '@superset-ui/core';
import transformProps from './transformProps';
import buildQuery from './buildQuery';
import controlPanel from './controlPanel';

const metadata = new ChartMetadata({
  name: t('漏斗转化图'),
  description: t('自定义多层漏斗，支持动态颜色阈值'),
  thumbnail: require('./images/thumbnail.png'),
});

export default class MyCustomChart extends ChartPlugin {
  constructor() {
    super({
      metadata,
      buildQuery,
      loadChart: () => import('./MyFunnelEchart'),
      transformProps,
      controlPanel,
    });
  }
}

// src/transformProps.ts
import { EChartsCoreOption } from 'echarts';

interface ChartProps {
  formData: Record<string, any>;
  queriesData: Array<{ data: any[] }>;
}

export default function transformProps({ formData, queriesData }: ChartProps): EChartsCoreOption {
  const data = queriesData[0]?.data || [];
  return {
    tooltip: { trigger: 'item', formatter: '{b}: {c} ({d}%)' },
    series: [{
      name: formData.metric_name || 'value',
      type: 'funnel',
      left: '10%', top: 60, bottom: 60, width: '80%',
      min: 0,
      max: Math.max(...data.map(d => d[formData.metric_name])) * 1.2,
      data: data.map(d => ({ value: d[formData.metric_name], name: d[formData.groupby] })),
    }],
  };
}

// Metabase API — 创建 Model（类型为 question + dataset = true）
{
  "name": "月度用户留存模型",
  "dataset": true,
  "display": "table",
  "dataset_query": {
    "type": "native",
    "native": {
      "query": "SELECT cohort_month, retention_month, retained_users, total_users FROM dbt.cohort_retention"
    },
    "database": 3
  },
  "visualization_settings": {
    "table.pivot_column": "retention_month",
    "table.cell_column": "retained_users"
  }
}

// 集合权限矩阵（PUT /api/permissions/graph）
{
  "revision": 12,
  "groups": {
    "3": {
      "root": "none",
      "13": "write",   // Finance Collection
      "14": "read"    // Product Collection
    },
    "4": {
      "root": "read",
      "13": "none",
      "14": "write"
    }
  }
}

import requests, json

BASE = "https://metabase.company.com"
HEADERS = {"X-Metabase-Session": "<session-token>", "Content-Type": "application/json"}

pulse = {
    "name": "每周 GMV 脉冲",
    "cards": [{"id": 88, "include_csv": True, "include_xls": False}],
    "channels": [
        {
            "channel_type": "email",
            "recipients": [{"email": "manager@company.com"}],
            "details": {},
            "schedule_type": "weekly",
            "schedule_day": "mon",
            "schedule_hour": 9
        },
        {
            "channel_type": "slack",
            "details": {"channel": "#weekly-gmv"},
            "schedule_type": "weekly",
            "schedule_day": "mon",
            "schedule_hour": 9
        }
    ],
    "skip_if_empty": True
}

r = requests.post(f"{BASE}/api/pulse", headers=HEADERS, json=pulse)
print(r.status_code, r.json().get("id"))

// React 项目中使用 Metabase Embedding SDK
import { MetabaseProvider, InteractiveQuestion } from '@metabase/embedding-sdk-react';

function SalesDashboard() {
  return (
    <MetabaseProvider
      authConfig={{
        metabaseInstanceUrl: 'https://metabase.company.com',
        authProviderUri: '/api/metabase-auth',
      }}
    >
      <InteractiveQuestion
        questionId={128}
        height={600}
        withTitle
        withResetButton
      />
    </MetabaseProvider>
  );
}

// 后端 /api/metabase-auth 返回 JWT（对应 Metabase enterprise SSO）
// app.get('/api/metabase-auth', (req, res) => {
//   const token = jwt.sign({ id: req.user.id, email: req.user.email }, METABASE_SECRET, { expiresIn: '10m' });
//   res.json({ id: req.user.id, token });
// });

// src/datasource.ts — 最小数据源插件
import { DataSourceInstanceSettings, DataQueryRequest, DataFrame } from '@grafana/data';
import { DataSourceWithBackend } from '@grafana/runtime';

interface CustomQuery extends DataQuery {
  rawQuery?: string;
}

export class CustomDataSource extends DataSourceWithBackend<CustomQuery, MyDataSourceOptions> {
  constructor(instanceSettings: DataSourceInstanceSettings<MyDataSourceOptions>) {
    super(instanceSettings);
  }

  applyTemplateVariables(query: CustomQuery, scopedVars: ScopedVars): CustomQuery {
    return {
      ...query,
      rawQuery: getTemplateSrv().replace(query.rawQuery, scopedVars),
    };
  }
}

# provisioning/alerting/rules.yaml
apiVersion: 1
groups:
  - orgId: 1
    name: infrastructure
    folder: SRE
    interval: 60s
    rules:
      - uid: high-memory-alert
        title: 节点内存使用率告警
        condition: B
        data:
          - refId: A
            relativeTimeRange:
              from: 300
              to: 0
            datasourceUid: prometheus
            model:
              expr: |
                (
                  sum(node_memory_MemTotal_bytes - node_memory_MemAvailable_bytes) by (instance)
                  /
                  sum(node_memory_MemTotal_bytes) by (instance)
                ) * 100 > 85
              legendFormat: '{{ instance }}'
          - refId: B
            relativeTimeRange:
              from: 0
              to: 0
            datasourceUid: __expr__
            model:
              type: threshold
              expression: A
              conditions:
                - evaluator:
                    type: gt
                    params: [85]
        noDataState: NoData
        execErrState: Error
        for: 5m
        annotations:
          summary: "节点 {{ $labels.instance }} 内存使用率超过 85%"
        labels:
          severity: p1

// 通过 Grafana HTTP API 创建 Annotation
{
  "dashboardUID": "sre-infra-dash",
  "panelId": 12,
  "time": 1725206400000,
  "timeEnd": 1725207000000,
  "tags": ["deploy", "api-gateway"],
  "text": "v2.3.1 灰度发布",
  "data": {
    "commit_sha": "a1b2c3d",
    "triggered_by": "ci-cd-pipeline"
  }
}

# 值班表配置（通过 Terraform 或 UI 导出）
schedules:
  - name: "SRE 周一轮班"
    type: "ical"
    ical_url: "https://calendar.company.com/sre/oncall.ics"
    time_zone: "Asia/Shanghai"
    shifts:
      - name: "primary"
        start: "09:00"
        duration: "24h"
        rotation: "weekly"
        users:
          - user_id: "U123"
          - user_id: "U456"
      - name: "shadow"
        start: "09:00"
        duration: "24h"
        rotation: "weekly"
        users:
          - user_id: "U789"

# Python — 生成带 RLS 参数的嵌入 Token
import jwt
import time

EMBED_SECRET = "metabase-embed-secret"

def generate_embed_token(user_id: str, tenant_id: str, dashboard_id: int):
    return jwt.encode(
        {
            "resource": {"dashboard": dashboard_id},
            "params": {
                "tenant_id": [tenant_id],   # RLS 字段通过 params 传入
                "user_region": ["APAC"]     # 附加业务过滤
            },
            "exp": int(time.time()) + 600
        },
        EMBED_SECRET,
        algorithm="HS256"
    )

# Superset Guest Token（支持 RLS + 细粒度仪表盘资源）
def generate_superset_guest_token(
    username: str,
    rls_clauses: list[dict],
    dashboard_id: int
):
    from superset.utils.machine_auth import machine_auth_provider_factory
    # 实际生产中通过 Superset Security Manager 签发
    return {
        "user": {"username": username, "first_name": username},
        "resources": [{"type": "dashboard", "id": str(dashboard_id)}],
        "rls": rls_clauses,
        "exp": int(time.time()) + 300
    }

# Python — 生成色盲安全的调色板（基于 Wong 2011 推荐）
import matplotlib.pyplot as plt

COLORBLIND_SAFE = {
    "black": "#000000",
    "orange": "#E69F00",
    "sky_blue": "#56B4E9",
    "bluish_green": "#009E73",
    "yellow": "#F0E442",
    "blue": "#0072B2",
    "vermillion": "#D55E00",
    "reddish_purple": "#CC79A7",
}

def apply_accessible_theme(fig):
    for ax in fig.axes:
        for spine in ax.spines.values():
            spine.set_color("#333333")
        ax.tick_params(colors="#333333")
        ax.set_title(ax.get_title(), color="#111111", fontsize=14, fontweight="bold")
    return fig

# 验证对比度
from colorsys import rgb_to_yiq

def contrast_ratio(hex1: str, hex2: str) -> float:
    def lum(h):
        r, g, b = tuple(int(h[i:i+2], 16) / 255 for i in (1, 3, 5))
        srgb = [c / 12.92 if c <= 0.03928 else ((c + 0.055) / 1.055) ** 2.4 for c in (r, g, b)]
        return 0.2126 * srgb[0] + 0.7152 * srgb[1] + 0.0722 * srgb[2]
    l1, l2 = lum(hex1), lum(hex2)
    lighter = max(l1, l2)
    darker = min(l1, l2)
    return (lighter + 0.05) / (darker + 0.05)

print(contrast_ratio("#000000", "#FFFFFF"))  # 21.0 : 1（极高对比）

// 基于 ECharts 的响应式仪表盘适配
const chartDom = document.getElementById('main');
const myChart = echarts.init(chartDom);

function resizeChart() {
  const width = window.innerWidth;
  const option = myChart.getOption();
  if (width < 768) {
    option.legend[0].orient = 'horizontal';
    option.legend[0].bottom = 0;
    option.grid[0].bottom = 60;
    option.dataZoom = [{ type: 'inside', start: 0, end: 30 }];
  } else {
    option.legend[0].orient = 'vertical';
    option.legend[0].right = 10;
    option.legend[0].top = 'center';
    option.grid[0].right = 120;
    option.dataZoom = [{ type: 'inside', start: 0, end: 100 }];
  }
  myChart.setOption(option);
}

window.addEventListener('resize', resizeChart);
resizeChart();

# models/metrics/schema.yml
custom_metrics:
  - name: net_revenue
    label: Net Revenue
    model: ref('fct_orders')
    calculation_method: sum
    expression: gross_amt - discount_amt - refund_amt
    timestamp: ordered_at
    time_grains: [day, week, month, quarter, year]
    dimensions:
      - region
      - product_category
      - customer_segment
    filters:
      - field: status
        operator: '!='
        value: "'cancelled'"

# Python — 通过 dbt Semantic Layer API 查询指标
import requests

r = requests.post(
    "https://semantic-layer.cloud.getdbt.com/api/graphql",
    headers={"Authorization": "Bearer <dbt-sl-token>"},
    json={
        "query": """
        query {
          metric(name: "net_revenue") {
            query(grain: MONTH, dimensions: ["region"]) {
              sql
              error
            }
          }
        }
        """
    }
)
print(r.json()["data"]["metric"]["query"]["sql"])

// cube.js — 数据模型定义
cube('Orders', {
  sql: 'SELECT * FROM orders',

  measures: {
    count: {
      type: 'count',
      drillMembers: [id, createdAt],
    },
    totalRevenue: {
      sql: 'revenue',
      type: 'sum',
    },
    avgOrderValue: {
      sql: `${totalRevenue} / ${count}`,
      type: 'number',
    },
  },

  dimensions: {
    id: {
      sql: 'id',
      type: 'number',
      primaryKey: true,
    },
    status: {
      sql: 'status',
      type: 'string',
    },
    createdAt: {
      sql: 'created_at',
      type: 'time',
    },
  },

  segments: {
    highValue: {
      sql: `${totalRevenue} > 10000`,
    },
  },
});

// 前端实时数据推送（基于 ECharts + WebSocket）
const ws = new WebSocket('wss://stream.company.com/metrics');

ws.onmessage = (event) => {
  const payload = JSON.parse(event.data);
  const { metric, timestamp, value, tags } = payload;

  // 仅更新目标 series 的最新数据点
  chartData[metric].shift();
  chartData[metric].push({ name: timestamp, value: [timestamp, value] });

  myChart.setOption({
    series: Object.keys(chartData).map(key => ({
      name: key,
      data: chartData[key],
    })),
  });
};

ws.onerror = (err) => console.error('WebSocket error:', err);
ws.onclose = () => setTimeout(() => location.reload(), 5000);

// ECharts 大屏优化配置
{
  "animation": false,
  "renderer": "canvas",
  "progressive": 2000,
  "progressiveThreshold": 5000,
  "series": [
    {
      "type": "bar",
      "large": true,
      "largeThreshold": 500,
      "data": []
    }
  ]
}