EV Charging Network & Session Analytics data model
Kimball warehouse for an EV charging network: a session-grain CDR fact carrying duration, energy delivered, average / peak power, revenue and idle-fee measures with role-playing start/end date keys and roaming flags, plus a periodic-snapshot hourly utilisation fact per station × connector with uptime / in-use / fault minutes and utilisation %. SCD2 station preserves as-was power and ownership; tariff dimension carries OCPI price components; eMSP dimension supports OCPI roaming reconciliation.
Electric-vehicle charging network analytics for a CPO (charge point operator) running OCPP-managed stations and exchanging Sessions, CDRs, Locations and Tariffs over OCPI with eMSP roaming partners. Models charging sessions at OCPI CDR grain plus an hourly station × connector utilisation snapshot, over conformed station (SCD2), connector, driver, tariff, eMSP and session-status dimensions for utilisation, throughput, revenue and roaming-reconciliation reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 104
- columns
- 12
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Session-grain CDR fact
OCPI's Sessions and CDRs modules define the Charge Detail Record as the atomic billable artefact of a charging session; declaring the fact at one-row-per-session preserves duration, energy, power and cost measures and supports drill-anywhere session analytics.
Periodic-snapshot hourly utilisation fact
Operational KPIs like uptime, availability, in-use vs idle vs fault minutes and peak concurrency are naturally additive over fixed time buckets; a station × connector × hour periodic snapshot avoids re-scanning the session fact for every utilisation question.
Dedicated eMSP dimension for OCPI roaming reconciliation
OCPI sessions can be billed via a partner eMSP under roaming; carrying the eMSP party as a conformed dimension lets finance reconcile internal vs roaming CDRs by counterparty and country without re-deriving the OCPI party_id at query time.
SCD2 on station for as-was power and ownership
Station max-power upgrades, owner-segment changes and operator transfers all happen mid-life; type-2 history on dim_station preserves the as-was attributes that drove a session's utilisation and revenue at the moment it was charged.
Tariff dimension over an embedded price
OCPI Tariffs carry time-of-use, parking-time and per-kWh / per-minute components with validity windows; modelling tariffs as a dimension lets the same session fact be re-priced or audited against historical tariff versions without restating the CDR.
Tables in the EV Charging Network & Session 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 (2)
fct_charging_session
Grain: one row per charging session (OCPI Sessions / CDRs module, transaction grain)
Session-grain CDR fact — one row per OCPI session.
| Column | Type | Keys |
|---|---|---|
| charging_session_key | bigint | PK |
| start_date_key | bigint | |
| end_date_key | bigint | |
| station_key | bigint | |
| connector_key | bigint | |
| driver_key | bigint | |
| tariff_key | bigint | |
| emsp_key | bigint | |
| session_status_key | bigint | |
| session_id | varchar | |
| authorization_id | varchar | |
| duration_minutes | decimal | |
| energy_delivered_kwh | decimal | |
| average_power_kw | decimal | |
| peak_power_kw | decimal | |
| gross_revenue | decimal | |
| idle_fee_amount | decimal | |
| kwh_cost | decimal | |
| session_count | integer | |
| currency | varchar | |
| is_roaming | boolean | |
| is_completed | boolean | |
| is_dc_fast | boolean | |
| has_error | boolean |
fct_station_utilization_hourly
Grain: one row per station × connector × hour (periodic snapshot)
Hourly periodic-snapshot utilisation fact per station × connector.
| Column | Type | Keys |
|---|---|---|
| station_utilization_hourly_key | bigint | PK |
| hour_key | bigint | |
| date_key | bigint | |
| station_key | bigint | |
| connector_key | bigint | |
| uptime_minutes | decimal | |
| in_use_minutes | decimal | |
| idle_minutes | decimal | |
| fault_minutes | decimal | |
| session_starts | integer | |
| session_completions | integer | |
| total_energy_kwh | decimal | |
| max_concurrent_sessions | integer | |
| utilization_pct | decimal | |
| availability_pct | decimal |
Dimension tables (8)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean |
dim_hour
Grain: one row per calendar hour
| Column | Type | Keys |
|---|---|---|
| hour_key | bigint | PK |
| hour_datetime | timestamp | BK |
| hour_of_day | smallint | |
| day_part | varchar | |
| is_peak_hour | boolean |
dim_station
Grain: one row per charging station per SCD2 version (OCPI Locations module)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| station_key | bigint | PK |
| station_id | varchar | BK |
| station_name | varchar | |
| operator_cpo | varchar | |
| country | varchar | |
| city | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| station_type | varchar | |
| total_connectors | smallint | |
| max_power_kw | decimal | |
| owner_segment | varchar | |
| is_24_7 | boolean |
dim_connector
Grain: one row per physical connector on an EVSE (OCPI Locations.evses.connectors)
| Column | Type | Keys |
|---|---|---|
| connector_key | bigint | PK |
| connector_id | varchar | BK |
| evse_uid | varchar | |
| connector_standard | varchar | |
| power_type | varchar | |
| max_voltage_v | integer | |
| max_amperage_a | integer | |
| max_power_kw | decimal | |
| is_active | boolean |
dim_driver
Grain: one row per driver / account holder using the network
| Column | Type | Keys |
|---|---|---|
| driver_key | bigint | PK |
| driver_id | varchar | BK |
| driver_name | varchar | PII |
| account_type | varchar | |
| home_country | varchar | |
| registration_date | date | |
| license_plate_masked | varchar | PII |
| account_status | varchar | |
| is_active | boolean |
dim_tariff
Grain: one row per tariff per validity window (OCPI Tariffs module)
| Column | Type | Keys |
|---|---|---|
| tariff_key | bigint | PK |
| tariff_id | varchar | BK |
| tariff_name | varchar | |
| tariff_type | varchar | |
| currency | varchar | |
| price_per_kwh | decimal | |
| price_per_minute | decimal | |
| idle_fee_per_minute | decimal | |
| valid_from | timestamp | |
| valid_to | timestamp |
dim_emsp
Grain: one row per eMSP (e-mobility service provider) — OCPI roaming counterparty
| Column | Type | Keys |
|---|---|---|
| emsp_key | bigint | PK |
| emsp_id | varchar | BK |
| emsp_name | varchar | |
| country | varchar | |
| partner_type | varchar | |
| is_active | boolean |
dim_session_status
Grain: one row per OCPI session status code
| Column | Type | Keys |
|---|---|---|
| session_status_key | bigint | PK |
| session_status | varchar | BK |
| description | varchar | |
| is_billable | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_charging_session.start_date_key → dim_date (many-to-one)
- fct_charging_session.end_date_key → dim_date (many-to-one)
- fct_charging_session.station_key → dim_station (many-to-one)
- fct_charging_session.connector_key → dim_connector (many-to-one)
- fct_charging_session.driver_key → dim_driver (many-to-one)
- fct_charging_session.tariff_key → dim_tariff (many-to-one)
- fct_charging_session.emsp_key → dim_emsp (many-to-one)
- fct_charging_session.session_status_key → dim_session_status (many-to-one)
- fct_station_utilization_hourly.hour_key → dim_hour (many-to-one)
- fct_station_utilization_hourly.date_key → dim_date (many-to-one)
- fct_station_utilization_hourly.station_key → dim_station (many-to-one)
- fct_station_utilization_hourly.connector_key → dim_connector (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.