# Superset Production Region Cost Trend Dashboard

Updated: 2026-03-09
Status: Active

This guide defines the production region cost trend dashboard design and build procedure based on `mart_cost_summary`.

## 1. Goal And Scope

Goal:
- Track monthly cost trends across all AWS Production accounts by region.
- Support region opening/closing decisions with region-level cost visibility.
- Provide Region → Account → Service drill-down for cost analysis.
- Automatically adapt when accounts are reclassified in `account_mapping.csv`.

Target accounts:
- All AWS accounts where `account_purpose = 'Production'` (currently 7 accounts).

In scope:
- One dashboard: `Production Region Cost Trend` (`slug: prod-region-cost-trend`)
- 6 static charts (region trend + ranking, account×region trend + top services, service trend + detail table)
- N dynamic per-region small multiples charts (one bar chart per production region, auto-discovered via BigQuery)
- Native filters with cascade: Service cascades from Region
- Bootstrap script for dataset/dashboard/chart creation

Out of scope:
- New dbt model/schema changes
- Non-production accounts
- GCP / Alibaba production accounts (future iteration)
- Automated dashboard layout authoring

## 2. Data Semantics

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

Why `mart_cost_summary`:
- Has all needed dimensions (account, region, service, service_category).
- Filters by `account_purpose` column — automatically adapts when seed changes.
- No tag columns needed for this dashboard.

Primary dimensions:
- Time: `billing_period_month`
- Cloud: `cloud_provider`
- Account: `account_id`, `account_display_name`
- Owner: `account_owner`
- Resource: `region`, `region_label` (human-readable, e.g. `us-east-1 | N. Virginia`), `service`, `service_category`

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

## 3. Virtual Dataset

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

Recommended dataset name:
- `vds_prod_region_cost_trend`

SQL template (BigQuery):

```sql
SELECT
  m.billing_period_month,
  m.cloud_provider,
  m.region,
  CONCAT(m.region, ' | ', CASE m.region
    WHEN 'us-east-1'      THEN 'N. Virginia'
    WHEN 'us-east-2'      THEN 'Ohio'
    WHEN 'us-west-1'      THEN 'N. California'
    WHEN 'us-west-2'      THEN 'Oregon'
    WHEN 'ap-east-1'      THEN 'Hong Kong'
    WHEN 'ap-south-1'     THEN 'Mumbai'
    WHEN 'ap-southeast-1' THEN 'Singapore'
    WHEN 'ap-southeast-2' THEN 'Sydney'
    WHEN 'ap-northeast-1' THEN 'Tokyo'
    WHEN 'ap-northeast-2' THEN 'Seoul'
    WHEN 'eu-central-1'   THEN 'Frankfurt'
    WHEN 'eu-west-1'      THEN 'Ireland'
    WHEN 'sa-east-1'      THEN 'São Paulo'
    -- full mapping in bootstrap script
    ELSE m.region
  END) AS region_label,
  m.account_id,
  m.account_display_name,
  CONCAT(m.account_id, ' | ', m.account_display_name) AS account_label,
  m.account_owner,
  m.service,
  m.service_category,
  SUM(m.unblended_cost_usd) AS cost_usd
FROM `__BQ_PROJECT__.__BQ_SCHEMA__.mart_cost_summary` AS m
WHERE m.cloud_provider = 'aws'
  AND m.account_purpose = 'Production'
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
```

Important:
- Filters by `account_purpose = 'Production'` — no hard-coded account IDs.
- Pre-aggregates with `SUM(unblended_cost_usd)` at the grain of all dimension columns.
- Region column contains AWS region codes or `'global'` for non-regional services.

## 4. Native Filter Design

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

1. `billing_period_month` (Time range, default: last 12 complete months)
2. `account_label` (multi-select, no cascade — shows `account_id | account_display_name`)
3. `region` (multi-select, no cascade — **excludes per-region small multiples charts**)
4. `service` (multi-select, cascade from Region)
5. `service_category` (multi-select, no cascade)

Filter behavior rules:
- Keep all filters optional (non-required) by default.
- Default time range should be last 12 complete months (exclude current month).
- Service cascades from Region to show only services available in selected regions.
- Region filter excludes per-region small multiples charts (each has its own hardcoded region filter).

## 5. Chart Specifications (6 Static + N Per-Region)

Use dataset: `vds_prod_region_cost_trend`.

### 5.1 Region Cost Trend (Stacked)

- Name: `Prod Region Cost Trend`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `region_label`
- Metric: `SUM(cost_usd)`
- Row limit: 10000
- Purpose: monthly cost trend stacked by region (human-readable labels)

### 5.2 Region Cost Ranking Table

- Name: `Prod Region Cost Ranking`
- Viz: `table`
- Group by: `region_label`
- Metric: `SUM(cost_usd)`
- Order: descending by cost
- Row limit: 100
- Purpose: region cost ranking to identify most expensive regions

### 5.3 Account × Region Cost Trend

- Name: `Prod Account Region Cost Trend`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `account_display_name`
- Metric: `SUM(cost_usd)`
- Row limit: 10000
- Purpose: monthly cost trend stacked by account for cross-account comparison

### 5.4 Top Services by Region Table

- Name: `Prod Top Services by Region`
- Viz: `table`
- Group by: `region_label`, `service`
- Metric: `SUM(cost_usd)`
- Order: descending by cost
- Row limit: 500
- Purpose: service cost ranking within each region

### 5.5 Service Cost Trend Top 15

- Name: `Prod Service Cost Trend Top 15`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `service`
- Metric: `SUM(cost_usd)`
- Row limit: 15
- Purpose: top 15 services by cost, monthly trend

### 5.6 Region Cost Detail Table

- Name: `Prod Region Cost Detail`
- Viz: `table`
- Group by: `billing_period_month`, `region_label`, `account_id`, `account_display_name`, `service`
- Metric: `SUM(cost_usd)`
- Order: descending by cost
- Row limit: 500
- Purpose: detailed cost breakdown by month, region, account, and service

### 5.7 Per-Region Cost Trend (Small Multiples — Dynamic)

- Name pattern: `Prod: {display_name} ({region_code})` (e.g., `Prod: N. Virginia (us-east-1)`)
- Viz: `echarts_timeseries_bar` (NOT stacked — single series per chart)
- X: `billing_period_month` (P1M)
- Group by: (none)
- Metric: `SUM(cost_usd)` with label `Cost (USD)`
- Adhoc filter: `region == '{region_name}'` (hardcoded per chart)
- Show legend: false
- Row limit: 1000
- Purpose: one bar chart per region for easy visual comparison of individual region trends
- Discovery: regions are queried dynamically via Superset SQL Lab API (regions with cost in the last 6 months), sorted by total cost descending
- Filter exclusion: these charts are excluded from the Region native filter scope (each has its own hardcoded region filter, so the native Region filter would conflict)
- Count: one chart per distinct production region (~18 currently)

## 6. Dashboard Metadata

- Title: `Production Region Cost Trend`
- Slug: `prod-region-cost-trend`
- Label colors: Use default Superset color scheme (many regions benefit from automatic palette assignment)

## 7. Layout (Manual in UI)

Per-region small multiples (4-column grid, sorted by latest month cost descending):

| Row | Col 1 (25%) | Col 2 (25%) | Col 3 (25%) | Col 4 (25%) |
|-----|-------------|-------------|-------------|-------------|
| 1 | Prod: N. Virginia (us-east-1) | Prod: Tokyo (ap-northeast-1) | Prod: Singapore (ap-southeast-1) | Prod: Oregon (us-west-2) |
| 2 | Prod: Hong Kong (ap-east-1) | Prod: Frankfurt (eu-central-1) | ... | ... |
| ... | (continue 4 per row, sorted by cost desc) | | | |

Static charts (2-column layout):

| Row | Left (50%) | Right (50%) |
|-----|------------|-------------|
| N | Prod Region Cost Trend | Prod Region Cost Ranking |
| N+1 | Prod Account Region Cost Trend | Prod Top Services by Region |
| N+2 | Prod Service Cost Trend Top 15 | Prod Region Cost Detail |

## 8. Manual Build Procedure

1. Confirm data access in SQL Lab:
   - `SELECT COUNT(*) FROM \`gcp-rd-research-dev.finops_cloud_billing.mart_cost_summary\` WHERE cloud_provider = 'aws' AND account_purpose = 'Production'`
2. Create virtual dataset `vds_prod_region_cost_trend` from SQL in Section 3.
3. Create dashboard:
   - Title: `Production Region Cost Trend`
   - Slug: `prod-region-cost-trend`
4. Create 6 static charts from Sections 5.1–5.6.
5. Create per-region small multiples charts from Section 5.7 (dynamic, discovered via BigQuery).
6. Add native filters from Section 4.
7. Configure filter scope:
   - Apply all filters to all static charts.
   - Exclude per-region charts from the Region filter scope.
8. Set default filter values:
   - `Time Range = DATEADD(DATETRUNC(DATETIME("today"), month), -12, month) : DATETRUNC(DATETIME("today"), month)`
9. Arrange layout per Section 7 (4-column grid for per-region charts).
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_prod_region_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_summary` and the virtual dataset.
   - All 7 production accounts appear with correct names.

2. Account filtering:
   - Only `account_purpose = 'Production'` accounts appear.
   - Adding/removing production accounts in `account_mapping.csv` automatically reflects in dashboard.

3. Filter linkage:
   - Time/account/region/service/service_category filters update all static charts.
   - Service cascades from Region.
   - Region native filter does NOT affect per-region small multiples charts.

4. Chart correctness:
   - Static trend charts show stacked bars by region/account/service.
   - Tables show cost with proper grouping and descending sort.
   - Per-region small multiples show one bar chart per region with individual region trends.
   - Per-region charts are sorted by latest month cost descending.

5. Business usability:
   - Dashboard can answer:
     - What is the monthly cost trend across production regions?
     - Which individual regions have increasing or decreasing costs?
     - Which regions have very low recent costs (candidates for closing)?
     - Which regions are the most expensive?
     - How does cost distribute across production accounts?
     - What services drive the most cost in each region?
     - Should we consider closing or consolidating any region?

## 10. Known Constraints

1. Month-level only; no day-level trend.
2. AWS only — GCP and Alibaba production accounts are out of scope for this iteration.
3. No tag columns (tenant, service_type) — use tenant cost trend dashboard for tag analysis.
4. Account list is driven by `account_purpose = 'Production'` in seed. Changes require `dbt seed --full-refresh`.
5. Layout must be arranged manually in Superset UI.
6. **Per-region charts do not auto-adapt to new regions**: Per-region small multiples are static charts created at script runtime by querying regions with cost in the last 6 months via Superset SQL Lab API. They do **not** auto-update when new regions appear. Static charts (stacked bar, ranking table, etc.) will automatically include new region data, but per-region small multiples require **re-running the bootstrap script** to generate charts for new regions:
   ```bash
   bash dbt/scripts/create_superset_prod_region_cost_trend.sh
   ```
   The script is idempotent — existing charts are updated, new regions get new charts. Re-run promptly after enabling a new AWS region.