FUNCTIONAL REQUIREMENTS DOCUMENT (FRD)
Project: US Energy Market Performance Analytics
|
Version |
Author |
Based On |
Status |
|
1.0 |
Priyanka Chaudhari |
BRD v1.0 |
Approved |
1. DATA SCHEMA, FUNCTIONAL REQUIREMENTS
|
Field |
Type |
Description |
Required |
|
record_id |
TEXT (PK) |
Unique record identifier |
Yes |
|
date |
DATE |
Record date (YYYY-MM-DD) |
Yes |
|
year |
INTEGER |
Calendar year (2023 or 2024) |
Yes |
|
month |
INTEGER |
Calendar month (1-12) |
Yes |
|
quarter |
TEXT |
Fiscal quarter (Q1-Q4) |
Yes |
|
region |
TEXT |
US region (Northeast/Southeast/Midwest/Southwest/West) |
Yes |
|
state |
TEXT |
US state code (2-letter) |
Yes |
|
energy_source |
TEXT |
Generation source (Natural Gas/Coal/Nuclear/Solar/Wind/Hydro) |
Yes |
|
source_type |
TEXT |
Renewable or Non-Renewable classification |
Yes |
|
demand_mwh |
DECIMAL(10,1) |
Energy demand in megawatt-hours |
Yes |
|
generation_mwh |
DECIMAL(10,1) |
Actual generation in MWh |
Yes |
|
capacity_mw |
DECIMAL(10,0) |
Installed capacity in megawatts |
Yes |
|
capacity_utilization_pct |
DECIMAL(5,1) |
Capacity utilization % (demand / capacity*24 * 100) |
Yes |
|
price_per_mwh |
DECIMAL(8,2) |
Market price per MWh in USD |
Yes |
|
total_revenue_k |
DECIMAL(10,2) |
Total revenue in $K (demand * price / 1000) |
Yes |
|
co2_emissions_tons |
DECIMAL(8,3) |
CO2 emissions in metric tons |
Yes |
|
outage_flag |
INTEGER |
1 = outage occurred; 0 = no outage |
Yes |
|
outage_hours |
DECIMAL(6,1) |
Duration of outage in hours (0 if no outage) |
No |
|
outage_cost_k |
DECIMAL(10,2) |
Estimated outage cost in $K |
No |
2. SQL ANALYSIS, FUNCTIONAL REQUIREMENTS
|
Req ID |
Query |
Description |
Key Output Fields |
|
FR-SQL-01 |
Portfolio Overview |
Total demand, revenue, avg price, outage events, CO2 |
8 summary KPIs |
|
FR-SQL-02 |
Revenue by Source |
Demand, price, revenue, utilization, CO2 by energy_source |
Ranked by revenue |
|
FR-SQL-03 |
Renewable Comparison |
Demand share %, revenue, CO2 for Renewable vs Non-Renewable |
2-row comparison |
|
FR-SQL-04 |
Regional Scorecard |
Demand, revenue, price, outages, CO2 by region |
5 regions ranked |
|
FR-SQL-05 |
Seasonal Analysis |
Avg demand, price, outages by quarter |
Q1-Q4 comparison |
|
FR-SQL-06 |
Outage Analysis |
Outage rate %, avg hours, cost by source + region |
Top 15 risky combos |
|
FR-SQL-07 |
Capacity Utilization |
Avg utilization with EFFICIENT/MODERATE/UNDERUTILIZED rating |
All source-region combos |
|
FR-SQL-08 |
CO2 Intensity |
CO2 per GWh and revenue per CO2 ton by source |
Source CO2 ranking |
|
FR-SQL-09 |
Regional Energy Mix View |
CREATE VIEW: demand share by source within each region |
Renewable % per region |
|
FR-SQL-10 |
Renewable Tracker View |
CREATE VIEW: quarterly renewable share and CO2 by year/quarter |
ESG progress tracker |
3. EXCEL WORKBOOK, FUNCTIONAL REQUIREMENTS
|
Req ID |
Tab |
Contents |
Chart |
|
FR-XL-01 |
Executive Summary |
7 KPIs with target/status, 5 strategic findings, 4 recommendations |
No |
|
FR-XL-02 |
Source Analysis |
Revenue, demand, price, CO2, utilization by energy source |
Horizontal bar: revenue by source |
|
FR-XL-03 |
Regional Scorecard |
8 metrics across all 5 regions |
No (data table) |
|
FR-XL-04 |
Seasonal Analysis |
Demand, price, outages, revenue by quarter |
Column chart: avg demand by quarter |
4. POWER BI DASHBOARD, FUNCTIONAL REQUIREMENTS
|
Req ID |
Page |
Visuals |
Slicers |
Key Measures |
|
FR-PBI-01 |
Portfolio Overview |
4 KPI cards, bar by region, donut by source type |
Year, Region |
Revenue $M, Renewable %, Outage Rate |
|
FR-PBI-02 |
Source Performance |
Bar: revenue by source, scatter: price vs CO2 |
Region, Year |
CO2 Intensity, Avg Price/MWh |
|
FR-PBI-03 |
Renewable Transition |
Line: renewable % by quarter, clustered bar: renewable vs non-renewable |
Region |
Renewable Share % |
|
FR-PBI-04 |
Outage Risk Map |
Map: outages by state, bar: outage rate by source, KPI card: total cost |
Source, Region |
Outage Rate %, Outage Cost $K |
5. DATA VALIDATION RULES
|
Field |
Rule |
Action on Failure |
|
demand_mwh |
Must be > 0 |
Reject record |
|
capacity_utilization_pct |
Must be 0-100 |
Cap at 100, flag for review |
|
source_type |
Must be Renewable or Non-Renewable only |
Flag as Unknown, exclude from renewable KPI |
|
outage_flag |
Must be 0 or 1 |
Reject record |
|
co2_emissions_tons |
Must be >= 0 (0 allowed for renewables) |
Reject negative values |
|
region |
Must match approved 5-region list |
Flag as Unknown region |