Utility Smart-Meter (AMI) Analytics data model
Kimball warehouse for AMI: the core interval-read telemetry fact (~96 reads/day/meter), daily consumption snapshots, meter events, outages, and billing over conformed meter/service-point/customer/tariff dimensions.
Advanced Metering Infrastructure analytics for an electric/gas/water utility: high-frequency interval reads, daily consumption, meter events and alarms, outages, and billing — a Kimball star schema with conformed meter, service-point, customer, and tariff dimensions supporting load analysis, SAIDI/SAIFI reliability, and TOU billing. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 102
- columns
- 16
- relationships
- 4
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Interval reads separated from daily snapshot
Interval data is enormous (96+ rows/day/meter) and best partitioned by date; a daily snapshot fact serves most BI without scanning intervals.
Dedicated outage fact
Reliability indices (SAIDI/SAIFI) require outage start/end/duration and customers-affected at the service-point grain.
Tables in the Utility Smart-Meter (AMI) Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (5)
fct_interval_read
Grain: one row per meter per interval (5/15/30/60 min)
The core AMI telemetry fact — ~96 rows/day per 15-min meter.
| Column | Type | Keys |
|---|---|---|
| interval_read_key | bigint | PK |
| date_key | bigint | |
| meter_key | bigint | |
| service_point_key | bigint | |
| interval_timestamp | timestamp | |
| interval_minutes | smallint | |
| consumption_kwh | decimal | |
| voltage | decimal | |
| current_amps | decimal | |
| power_factor | decimal | |
| is_estimated | boolean |
fct_daily_consumption
Grain: one row per meter per day (periodic snapshot)
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| meter_key | bigint | |
| service_point_key | bigint | |
| customer_key | bigint | |
| total_kwh | decimal | |
| peak_demand_kw | decimal | |
| min_voltage | decimal | |
| max_voltage | decimal | |
| read_count | smallint | |
| estimated_read_count | smallint |
fct_meter_event
Grain: one row per meter event/alarm
| Column | Type | Keys |
|---|---|---|
| meter_event_key | bigint | PK |
| date_key | bigint | |
| meter_key | bigint | |
| service_point_key | bigint | |
| event_type | varchar | |
| event_timestamp | timestamp | |
| severity | varchar |
fct_outage
Grain: one row per outage at a service point
Supports SAIDI/SAIFI reliability indices.
| Column | Type | Keys |
|---|---|---|
| outage_key | bigint | PK |
| service_point_key | bigint | |
| start_date_key | bigint | |
| end_date_key | bigint | |
| start_timestamp | timestamp | |
| end_timestamp | timestamp | |
| duration_minutes | integer | |
| cause | varchar | |
| customers_affected | integer | |
| is_restored | boolean |
fct_billing
Grain: one row per bill
| Column | Type | Keys |
|---|---|---|
| billing_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| service_point_key | bigint | |
| tariff_key | bigint | |
| billing_period_start_key | bigint | |
| billing_period_end_key | bigint | |
| consumption_kwh | decimal | |
| peak_demand_kw | decimal | |
| energy_charge | decimal | |
| demand_charge | decimal | |
| taxes_and_fees | decimal | |
| total_amount | decimal |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| season | varchar | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean |
dim_meter
Grain: one row per meter per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| meter_key | bigint | PK |
| meter_id | varchar | BK |
| serial_number | varchar | |
| meter_type | varchar | |
| manufacturer | varchar | |
| model | varchar | |
| firmware_version | varchar | |
| interval_minutes | smallint | |
| install_date | date | |
| meter_status | varchar |
dim_service_point
Grain: one row per service point/premise
| Column | Type | Keys |
|---|---|---|
| service_point_key | bigint | PK |
| service_point_id | varchar | BK |
| premise_id | varchar | |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| service_type | varchar | |
| grid_segment | varchar | |
| transformer_id | varchar | |
| substation | varchar | |
| latitude | decimal | |
| longitude | decimal |
dim_customer
Grain: one row per customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| account_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| account_type | varchar | |
| rate_class | varchar | |
| enrollment_date | date | |
| is_active | boolean |
dim_tariff
Grain: one row per tariff/rate plan
| Column | Type | Keys |
|---|---|---|
| tariff_key | bigint | PK |
| tariff_code | varchar | BK |
| tariff_name | varchar | |
| rate_type | varchar | |
| effective_date | date | |
| currency | varchar |
dim_tariff_period
Grain: one row per TOU period within a tariff
| Column | Type | Keys |
|---|---|---|
| tariff_period_key | bigint | PK |
| tariff_key | bigint | |
| period_name | varchar | |
| start_hour | smallint | |
| end_hour | smallint | |
| rate_per_kwh | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dim_tariff_period.tariff_key → dim_tariff (many-to-one)
- fct_interval_read.date_key → dim_date (many-to-one)
- fct_interval_read.meter_key → dim_meter (many-to-one)
- fct_interval_read.service_point_key → dim_service_point (many-to-one)
- fct_daily_consumption.date_key → dim_date (many-to-one)
- fct_daily_consumption.meter_key → dim_meter (many-to-one)
- fct_daily_consumption.service_point_key → dim_service_point (many-to-one)
- fct_daily_consumption.customer_key → dim_customer (many-to-one)
- fct_meter_event.date_key → dim_date (many-to-one)
- fct_meter_event.meter_key → dim_meter (many-to-one)
- fct_meter_event.service_point_key → dim_service_point (many-to-one)
- fct_outage.service_point_key → dim_service_point (many-to-one)
- fct_billing.date_key → dim_date (many-to-one)
- fct_billing.customer_key → dim_customer (many-to-one)
- fct_billing.service_point_key → dim_service_point (many-to-one)
- fct_billing.tariff_key → dim_tariff (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.