Rates Data File Specifications
There are several rates data files that Oracle Utilities Opower requires in order to provide personalized cost insights to customers. These files serve as inputs to the Oracle Utilities Opower Rate Engine, a software computing model that calculates the cost insights based on each customer's rate plans and energy use. Your Delivery Team will work with you to determine which files are applicable to your situation, to construct sample files when necessary, and to test that the files are correct and are being transferred as expected.
On this page:
Overview of Files
The data you provide in each file should be limited to the fields described in each file's specification. Including additional fields will complicate the data transfer process, and the data will not be used. The responsibility of who must provide each file varies, and not every file is required for every type of rate plan.
File |
Responsibility |
---|---|
The Rate Attributes file contains a series of key value pairs that define the account-specific attributes for a customer. When collected together, these attributes can be used to associate a customer account with the appropriate rate plan and rate options, as well as characteristics like budget billing, solar, and peak time rebates. |
Utility |
The Rate Prices file specifies the cost to apply to each component of a rate plan with which a customer is associated. The Rate Prices file includes fields for the rate plan, rate components, charges or credits, start and end dates, and other fields as applicable. |
Utility |
The Holidays and Seasons file defines the different holidays (such as local or national holidays) and seasons (such as summer and winter) throughout the year when the price of energy varies. Your Delivery Team will work with you to determine whether this file is applicable to your situation. |
Utility |
The Rate Tier Definitions file defines the lower and upper bounds of each tier in a tiered rate plan. Your Delivery Team will create the file and work with you to gather the necessary inputs. |
Oracle, with utility input |
The Rate Period Definitions file defines the different "periods" or times of day, season, or year when the cost of energy varies. It is commonly used for time of use (TOU) rate plans. Your Delivery Team will create the file and work with you to gather the necessary inputs. |
Oracle, with utility input |
The Peak Time Rebate files contain information about peak events when weather is more extreme than usual and the cost of energy is higher. These files allow Oracle Utilities to calculate how much money a customer spent during a peak event. |
Utility |
The baselines file contains any energy use baselines that should be used to determine the pricing for tiered and peak time rebate rate plans. You Delivery Team will work with you to determine whether this file is applicable to your situation. |
Utility |
Example Scenarios
The files required for your program will vary slightly depending on the rate plans that must be modeled. Use the example scenarios below to better understand which files you will need to send, and contact your Delivery Team if you have any questions.
Tiered Rate Plan
In a tiered rate plan, customers are charged based on how much energy they use within certain tiers. For example, residential electricity customers could be charged one rate for the first 100 kWhs they consume in a bill period, a higher rate for the next 30 kWhs, and a higher rate for the next 70 kWhs. The graphic below shows the files and data fields involved in a tiered rate plan, along with who must provide each file. The rate_plan_identifier
and rate_component
fields form the common link between most of the files.
1. Rate Attributes File
The Rate Attributes file identifies the account_id
of customers and the rate plan attributes that each account is associated with. For example, as shown below, a key could be RATE_PLAN_CODE
and a value could be E-RES
to associate a customer account with a residential electricity rate plan. Additionally, other key-value pairs can be used to associate a customer's account with different rates based on their geographic location, such as whether they live inside or outside city limits. See Rate Attributes File for more information and additional examples, including for solar customers.
account_id |
key |
value |
start_date |
end_date |
---|---|---|---|---|
902101 | CITY_LOC | IN | 20191208 | |
902101 | RATE_PLAN_CODE | E-RES | 20191208 | |
204011 | CITY_LOC | IN | 20191208 | |
204011 | RATE_PLAN_CODE | E-RES | 20191208 | |
309022 | CITY_LOC | IN | 20191208 | |
309022 | RATE_PLAN_CODE | E-RES | 20191208 | |
309023 | CITY_LOC | OUT | 20200315 | |
309023 | RATE_PLAN_CODE | E-RES | 20200315 | |
204336 | RATE_PLAN_CODE | E-RES | 20200315 | |
204336 | BUDGET_BILLING | 1 | 20200315 |
2. Rate Prices File
The Rate Prices file defines the price of energy for each "rate component" of a rate plan—including the price of each "tier" of the rate plan. A rate component is a line item or charge in the rate plan. In the example below, there are five incrementing charges for each tier in the plan, as well as other charges for components like regulatory fees and power supply adjustments. Note that the price of a tier can start and end on a specific date. This can be done when a utility needs to update the tier and replace it with a different price. See Rate Prices File for more information and additional examples.
rate_plan_identifier |
rate_component |
rate_group |
seasons |
price_type |
tier |
price |
effective_start_date |
effective_end_date |
---|---|---|---|---|---|---|---|---|
E-RES/IN-CITY | CBC-CAP | CHARGE | 0.00144 | 20191101 | ||||
E-RES/IN-CITY | CBC-EEP | CHARGE | 0.00336 | 20191101 | ||||
E-RES/IN-CITY | CBC-STL | CHARGE | 0.00123 | 20191101 | ||||
E-RES/IN-CITY | ENERGY | CHARGE | 1 | 0.02901 | 20180101 | |||
E-RES/IN-CITY | ENERGY | CHARGE | 2 | 0.05833 | 20180101 | |||
E-RES/IN-CITY | ENERGY | CHARGE | 3 | 0.07815 | 20180101 | |||
E-RES/IN-CITY | ENERGY | CHARGE | 4 | 0.09315 | 20180101 | 20200401 | ||
E-RES/IN-CITY | ENERGY | CHARGE | 4 | 0.08315 | 20200401 | |||
E-RES/IN-CITY | ENERGY | CHARGE | 5 | 0.10815 | 20180101 | |||
E-RES/IN-CITY | PSA | WINTER | CHARGE | 0.02894 | 20191201 | |||
E-RES/IN-CITY | PSA | SUMMER | CHARGE | 0.02894 | 20191201 | |||
E-RES/IN-CITY | REG | CHARGE | 0.01343 | 20191201 |
3. Holidays and Seasons File
The Holidays and Seasons file defines the different holidays (such as local or national holidays) and seasons (such as summer and winter) throughout the year when the price of energy varies. In the example below, the winter and summer seasons are defined, along with a national holiday in July. This file may not be applicable to your situation if your utility does not change the cost of energy based on seasons or holidays. See Holidays and Seasons File file for more information.
rate_plan_identifier |
rate_component |
season |
day_type |
period |
ordinal |
resolution |
duration |
start_date |
start_time |
event_date |
effective_start_date |
---|---|---|---|---|---|---|---|---|---|---|---|
E-RES/IN-CITY | ENERGY | WINTER |
|
|
|
MONTH | 8 | 1001 | 0 |
|
20200101 |
E-RES/IN-CITY | ENERGY | SUMMER |
|
|
|
MONTH | 4 | 601 | 0 |
|
20200101 |
E-RES/IN-CITY | ENERGY |
|
HOLIDAY |
|
|
DAY | 1 |
|
0 | 20200703 |
|
4. Rate Tier Definitions File
The Rate Tier Definitions file defines the lower and upper bound of each tier in the plan. In the example below, there are five tiers, and each tier has an upper and lower bound. The price of energy increments when a customer's energy use moves from one tier to the next. See Rate Tier Definitions File for more information and additional examples.
rate_plan_identifier |
rate_component |
tier |
lower_bound |
upper_bound |
effective_start_date |
effective_end_date |
---|---|---|---|---|---|---|
E-RES/IN-CITY | ENERGY | 1 | 0 | 501 | 20200101 | |
E-RES/IN-CITY | ENERGY | 2 | 501 | 1001 | 20200101 | |
E-RES/IN-CITY | ENERGY | 3 | 1001 | 1501 | 20200101 | |
E-RES/IN-CITY | ENERGY | 4 | 1501 | 2501 | 20200101 | |
E-RES/IN-CITY | ENERGY | 5 | 2501 | 20200101 |
5. Baselines File
The baselines file contains any energy use baselines that should be used to determine the pricing for a tiered rate plan. For example, your utility could specify an energy use baseline of 500 kWh for a group of customers, and then charge those customers a specific rate for any energy that is consumed within that baseline. If customers exceed 100% of the baseline, they would then be moved to the next tier and charged a higher rater. The baselines file can specify multiple baselines for different customer demographics or zip codes. For example, one set of baselines could apply to low-income customers, and another set could apply to customers within a specific zip code. This file may not be applicable if your utility does not use energy use baselines to determine tiered rates. See Baselines File for more information.
The asterisk (*) in this example is a wildcard indicating that the usage baseline is applicable to all rate plan identifiers.
account_id |
season |
usage_baseline |
resolution |
rate_plan_identifier |
rate_component |
start_date |
end_date |
premise_id |
---|---|---|---|---|---|---|---|---|
902101 | SUMMER | 0.5900000 | DAY | * | TIERED | 20180401 | 9671204723 | |
204011 | SUMMER | 26.3380000 | DAY | * | TIERED | 20200507 | 3274021769 | |
309022 | SUMMER | 7.7000000 | DAY | * | TIERED | 20190501 | 1422197854 | |
309023 | WINTER | 29.4000000 | DAY | * | TIERED | 20190101 | 1492198469 | |
309024 | WINTER | 1.9200000 | DAY | * | TIERED | 20181101 | 8989765498 | |
309116 | SUMMER | 16.5000000 | DAY | * | TIERED | 20190501 | 13576452167 |
6. Billing and Interval Data
The billing data store includes the fields necessary to associate a customer_id
, account_id
, and service_point_id
with the tiered rate plan. The interval data store provides the service_point_id
field to associate a customer's service point with the tiered rate plan.
Time of Use Rate Plan
In a time of use (TOU) rate plan, customers are charged based on the time of day when energy is used. TOU insights can be shown in various Oracle Utilities Opower products and features such as the High Bill Alert AMI communication and the Behavioral Load Shaping Time of Use 101 module.
For this type of rate plan, most of the same files as in a tiered rate plan are applicable. See the tiered rate plan example above for descriptions and examples of each file. However, a TOU plan has one primary difference: a TOU period definitions file is required instead of a tier definitions file.
The Rate TOU Period Definitions file specifies each time period in the day when the cost of energy varies. For example, as shown in the table below, the file can specify the peak, partial peak, and off-peak time periods for different seasons of the year, days of the week, and even holidays. See Rate Period Definitions File for more information and additional examples.
rate_plan_identifier |
rate_component |
season |
day_type |
period |
ordinal |
resolution |
duration |
start_time |
effective_start_date |
---|---|---|---|---|---|---|---|---|---|
E-RES/IN-CITY | ENERGY | WINTER | WEEKDAY | PEAK | HOUR | 3 | 1500 | 20190101 | |
E-RES/IN-CITY | ENERGY | WINTER | WEEKDAY | PART_PEAK | 1 | HOUR | 8 | 700 | 20190101 |
E-RES/IN-CITY | ENERGY | WINTER | WEEKDAY | PART_PEAK | 2 | HOUR | 4 | 1800 | 20190101 |
E-RES/IN-CITY | ENERGY | WINTER | WEEKDAY | OFF_PEAK | HOUR | 9 | 2200 | 20190101 | |
E-RES/IN-CITY | ENERGY | WINTER | WEEKDAY | OFF_PEAK | HOUR | 24 | 0 | 20190101 | |
E-RES/IN-CITY | ENERGY | WINTER | HOLIDAY | OFF_PEAK | HOUR | 24 | 0 | 20190101 | |
E-RES/IN-CITY | ENERGY | SUMMER | WEEKDAY | PEAK | HOUR | 3 | 1500 | 20190101 | |
E-RES/IN-CITY | ENERGY | SUMMER | WEEKDAY | PART_PEAK | 1 | HOUR | 8 | 700 | 20190101 |
E-RES/IN-CITY | ENERGY | SUMMER | WEEKDAY | PART_PEAK | 2 | HOUR | 4 | 1800 | 20190101 |
E-RES/IN-CITY | ENERGY | SUMMER | WEEKDAY | OFF_PEAK | HOUR | 9 | 2200 | 20190101 | |
E-RES/IN-CITY | ENERGY | SUMMER | WEEKDAY | OFF_PEAK | HOUR | 24 | 0 | 20190101 | |
E-RES/IN-CITY | ENERGY | SUMMER | HOLIDAY | OFF_PEAK | HOUR | 24 | 0 | 20190101 |