# Superset AWS Tenant Cost Trend Dashboard

Updated: 2026-03-03
Status: Active

This guide defines the tenant cost trend dashboard design and build procedure based on `mart_cost_monthly`.

## 1. Goal And Scope

Goal:
- Track monthly cost trends by tenant (`tag_tenant`) across 4 production accounts.
- Group accounts into 3 products: Dedicated, Premium, Essential & Starter.
- Provide product → tenant → service drill-down for cost visibility.
- Give Cloud Team actionable visibility into per-tenant spend patterns.

Target accounts and products:
- **Dedicated**: `730335266318` (dbaas-prod-dataplane-001) + `143458967504` (dbaas-prod-dataplane-000)
- **Premium**: `632330501419` (dbaas-prod-dataplane-nextgen)
- **Essential & Starter**: `380838443567` (pingcap-dbaas)

In scope:
- One dashboard: `AWS Tenant Cost Trend` (`slug: aws-tenant-cost-trend`)
- 6 charts (product overview, per-product tenant trends, service breakdown, detail table)
- Native filters with cascade: Product → Account / Tenant
- Bootstrap script for dataset/dashboard/chart creation

Out of scope:
- New dbt model/schema changes
- Non-production accounts
- Automated dashboard layout authoring

## 2. Data Semantics

Source table:
- `gcp-rd-research-dev.finops_cloud_billing.mart_cost_monthly`

Why `mart_cost_monthly`:
- Has 17 dimensions including `tag_tenant`.
- Contains all 4 production accounts with usage-only rows.

Primary dimensions:
- Time: `billing_period_month`
- Account: `account_id`, `account_display_name`
- Product: `product` (derived from account_id)
- Tenant: `tenant` (COALESCE of tag_tenant, '(untagged)')
- Resource: `region`, `service`, `service_category`

Primary metrics (from mart):
- `unblended_cost_usd` — matches AWS Cost Explorer default view

Product derivation:
- `account_id IN ('730335266318', '143458967504')` → Dedicated
- `account_id = '632330501419'` → Premium
- `account_id = '380838443567'` → Essential & Starter

## 3. Virtual Dataset

Create one Superset virtual dataset from SQL Lab, and use it for all dashboard charts.

Recommended dataset name:
- `vds_aws_tenant_cost_trend`

SQL template (BigQuery):

```sql
SELECT
  m.billing_period_month,
  m.account_id,
  m.account_display_name,
  CONCAT(m.account_id, ' | ', m.account_display_name) AS account_label,
  CASE
    WHEN m.account_id IN ('730335266318', '143458967504') THEN 'Dedicated'
    WHEN m.account_id = '632330501419' THEN 'Premium'
    WHEN m.account_id = '380838443567' THEN 'Essential & Starter'
  END AS product,
  COALESCE(m.tag_tenant, '(untagged)') AS tenant,
  m.tag_service_type,
  COALESCE(m.tag_usedby, '(shared)') AS tag_usedby,
  m.region,
  m.service,
  m.service_category,
  m.unblended_cost_usd
FROM `__BQ_PROJECT__.__BQ_SCHEMA__.mart_cost_monthly` AS m
WHERE m.account_id IN ('380838443567', '730335266318', '143458967504', '632330501419')
```

Important:
- Hard-coded filter on 4 production accounts. No other accounts appear in the dataset.
- `product` is derived from `account_id` via CASE expression.
- `tenant` uses COALESCE to replace NULL `tag_tenant` with `(untagged)`.

## 4. Native Filter Design

Apply these filters to all charts in the dashboard (6 filters):

1. `billing_period_month` (Time range, default: last 6 complete months)
2. `product` (Dedicated / Premium / Essential & Starter)
3. `account_label` (cascade from Product)
4. `tenant` (cascade from Product)
5. `service`
6. `tag_service_type` (serverless / devtier / etc.)

Filter behavior rules:
- Keep all filters optional (non-required) by default.
- Default time range should be last 6 complete months (exclude current month).
- Account cascades from Product; Tenant cascades from Product.

## 5. Chart Specifications (6 Charts)

Use dataset: `vds_aws_tenant_cost_trend`.

### 5.1 Monthly Cost by Product

- Name: `Monthly Cost by Product`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `product`
- Metric: `SUM(unblended_cost_usd)`
- Purpose: three products cost overview (Dedicated/Premium/Essential & Starter)

### 5.2 Dedicated - Monthly Cost by Tenant

- Name: `Dedicated - Monthly Cost by Tenant`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `tenant`
- Metric: `SUM(unblended_cost_usd)`
- Adhoc filter: `product = 'Dedicated'`
- Row limit: 30
- Purpose: Dedicated product per-tenant cost trend

### 5.3 Premium - Monthly Cost by Tenant

- Name: `Premium - Monthly Cost by Tenant`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `tenant`
- Metric: `SUM(unblended_cost_usd)`
- Adhoc filter: `product = 'Premium'`
- Row limit: 30
- Purpose: Premium product per-tenant cost trend

### 5.4 Essential & Starter - Monthly Cost by Cluster

- Name: `Essential & Starter - Monthly Cost by Cluster`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `tag_usedby`
- Metric: `SUM(unblended_cost_usd)`
- Adhoc filters: `product = 'Essential & Starter'` AND `tag_service_type IN ('serverless', 'devtier')`
- Row limit: 30
- Purpose: Essential & Starter product per-cluster cost trend (E&S uses `tag_usedby` for cluster identification)

### 5.5 Cost by Service Top 15

- Name: `Cost by Service Top 15`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `service`
- Metric: `SUM(unblended_cost_usd)`
- Row limit: 15
- Purpose: service-level cost breakdown across all products

### 5.6 Tenant Cost Detail Table

- Name: `Tenant Cost Detail Table`
- Viz: `table`
- Group by: `billing_period_month`, `product`, `account_display_name`, `tenant`, `service`, `region`
- Metric: `SUM(unblended_cost_usd)`
- Row limit: 500
- Purpose: exportable detail table for reconciliation

## 6. Dashboard Metadata

- Title: `AWS Tenant Cost Trend`
- Slug: `aws-tenant-cost-trend`
- Label colors:
  - Dedicated: `#1FA8C9` (teal)
  - Premium: `#FF5A5F` (red)
  - Essential & Starter: `#FFC107` (amber)
  - (untagged): `#999999` (gray)

## 7. Layout (Manual in UI)

| Row | Charts | Width |
|-----|--------|-------|
| 1 | Monthly Cost by Product | 100% |
| 2 | Dedicated - Monthly Cost by Tenant | 100% |
| 3 | Premium - Monthly Cost by Tenant / Essential & Starter - Monthly Cost by Cluster | 50/50 |
| 4 | Cost by Service Top 15 | 100% |
| 5 | Tenant Cost Detail Table | 100% |

## 8. Manual Build Procedure

1. Confirm data access in SQL Lab:
   - `SELECT COUNT(*) FROM \`gcp-rd-research-dev.finops_cloud_billing.mart_cost_monthly\` WHERE account_id IN ('380838443567','143458967504','730335266318','632330501419')`
2. Create virtual dataset `vds_aws_tenant_cost_trend` from SQL in Section 3.
3. Create dashboard:
   - Title: `AWS Tenant Cost Trend`
   - Slug: `aws-tenant-cost-trend`
4. Create 6 charts from Section 5.
5. Add native filters from Section 4.
6. Configure filter scope:
   - Apply all filters to all 6 charts.
7. Set default filter values:
   - `Time Range = DATEADD(DATETRUNC(DATETIME("today"), month), -6, month) : DATETRUNC(DATETIME("today"), month)`
8. Set label colors: Dedicated=#1FA8C9, Premium=#FF5A5F, Essential & Starter=#FFC107, (untagged)=#999999.
9. Arrange layout per Section 7.
10. Save and publish dashboard.

## 8.1 Bootstrap Script (Local)

You can bootstrap dataset/dashboard/charts locally with:

```bash
export SUPERSET_URL="http://127.0.0.1:8088"
export SUPERSET_USER="admin"
export SUPERSET_PASS="admin"
export DB_NAME_IN_SUPERSET="FinOPSGoogleBigQuery"
export BQ_PROJECT="gcp-rd-research-dev"
export BQ_SCHEMA="finops_cloud_billing"
bash dbt/scripts/create_superset_tenant_cost_trend.sh
```

Notes:
- Script is idempotent (safe to rerun).
- Dashboard layout still needs manual arrangement in UI.

## 9. Acceptance Checklist

1. Data connectivity:
   - SQL Lab can query `mart_cost_monthly` and the virtual dataset.
   - All 4 production accounts appear with correct product mapping.

2. Product mapping:
   - Dedicated = accounts 730335266318 + 143458967504
   - Premium = account 632330501419
   - Essential & Starter = account 380838443567

3. Filter linkage:
   - Time/product/account/tenant/service filters update all charts.
   - Account cascades from Product; Tenant cascades from Product.

4. Per-product charts:
   - Charts 2-4 only show tenants for their respective product via adhoc filters.
   - Untagged resources appear as `(untagged)` in tenant dimension.

5. Business usability:
   - Dashboard can answer:
     - What is the monthly cost trend for each product?
     - Which tenants drive the most cost in each product?
     - How much cost is untagged (no tenant tag)?
     - What services are the biggest cost drivers?
     - Detailed drill-down for per-tenant cost reconciliation.

## 10. Known Constraints

1. Month-level only; no day-level trend.
2. Only `tag_tenant` is tracked. Other tags are out of scope.
3. Hard-coded to 4 production accounts. Add accounts by updating VDS SQL.
4. Product mapping is derived from account_id in VDS SQL. Changes require VDS update.
5. Layout must be arranged manually in Superset UI.