User Guide
Current Claim Year: 2023
Last Updated: 2023-07-19
Quarterly Claims > Specification > User Guide
CEDARS Cost Equations
PII Data
Introduction
What are claims?
Energy efficiency program claim data clearly and precisely document program activities undertaken including the energy savings and expenditures that resulted from those activities. Claims generally describe where, when, and how an energy efficient measure was delivered to a program participant, alongside details on the measure, site, customer, and other key details required by CPUC.
The CPUC runs submitted claim data through the Cost Effectiveness Tool (CET) to calculate the total system benefit, total resource cost, and other cost effectiveness measures used to evaluate program performance.
Claim data submit to CEDARS may *not* include any personally identifiable or confidential data.
What is a submission?
A submission refers to a quarterly or yearly period when a program administrator uploads savings and/or cost data to CEDARS that describe their program activities for the time period. A submission can consist of one or multiple uploads.
Submission Frequency
In a typical year, each PA submission is confirmed by a PA administrator five times. They confirm once per quarter and then one final time when the data have been reviewed and finalized.
Each quarter (Q1, Q2, Q3, and Q4) PAs report new activities since the last submission and updates to claims submit in prior quarters as needed.
At the end of the year, after Q4, PAs have until May 1 to finalize all of their data for prior calendar year and ensure savings and expenditures match at the program-level between their claims and monthly reports. When the yearly submission (YR) is confirmed the claim data are considered final. The date of the final confirmation may be changed in specific years by ALJ decision.
A quarterly submission is comprised of one or more uploads. Final confirmation does not require an upload but may have one or more uploads.
What is an upload?
An upload is a single file used to report program activities to CEDARS. Claim uploads include five data tables, as defined in the specification.
File Preparation
Requirements
When preparing your claim uploads, you will be compiling five data tables: ProgramCost, Measure, Claim, Site, and ContactClaim. In order to avoid any errors, it is important to adhere to the claims specification which defines all the necessary fields and data rules.
Your files must contain all tables and the correct field names as headers. Field names are not required to match the same case as the specification. The fields may occur in any order in the files. You are free to choose the file name as per your preference.
CEDARS requires CSV data files to be prepared using the pipe or up-bar character "|" as the delimiter with unquoted text values.
When it comes to precision, numeric fields can have a maximum of 15 digits past the decimal.
By following these guidelines, you can ensure that your files are ready for uploading without any issues.
Supported file formats
ZIP - Each table should be included as a .csv file, the five .csv files are then zipped together
XLSX - Each table should be a worksheet/tab in the Microsoft Excel XLSX file.
What character encoding does CEDARS use?
CEDARS uses UTF-8 character encoding, as do 98% of websites today. Uninterpretable characters in uploaded data will result in a submission being rejected.
Claims Submissions
How to upload your claim
Before uploading your claim data, ensure it conforms to the claim specification. Depending on whether it is a PA local or statewide claim, select either the 'Upload Claim' or 'Statewide Upload Claim' button in the left-hand navigation.
On the Upload Claim page, you can either drag & drop or browse and select your claim ZIP or XLSX file from your computer. Click 'Upload' to complete the process.
How CEDARS handles your claim upload
If there are no issues with your file it will have its tables and records undergo QC and you will receive a summary of the accepted and rejected records.
If you are uploading data for a ClaimID that already exists in CEDARS, and the new data for that ClaimID passes QC, the previously submitted data for that ClaimID will be overwritten. Additionally, any ProgramCost data submitted for a program and year will overwrite previously submitted ProgramCost data for that same program and year.
Upload status and feedback
The upload page will provide feedback if there are any file errors, and your data will not be saved to CEDARS.
Upon successful upload, you will be directed to the Upload History page, where you can find a list of your uploads.
The status column will indicate the following:
- Processing - CEDARS is still processing your file.
- Server error - Something unexpected happened on our end. CEDARS would not have processed your upload.
- Table errors found - CEDARS accepted no records.
- Record errors found - Records have been rejected.
- Warnings found - Records were accepted, but recommended data rules were broken.
- All QC passed - All records accepted. There is no feedback.
From the Upload History page you can click to view a detailed summary of the uploads processed records, savings and QC feedback.
Correct submitted data
To correct a previously submitted record, you can upload a corrected record with the same primary key to overwrite the previous one.
Review claim savings
- Upload summary - You can access your summary from the upload history page. The summary will show how many records were processed, re-uploaded, rejected and accepted, and you can also view the accepted record first year and lifecycle savings and any QC feedback.
- Active dashboard - The Active dashboard is where you can review the savings of all your submission uploads for the open submission period. Use this dashboard to verify the submission is ready to be confirmed. The summaries on this page include all submitted claims for the claim year, including current quarter submissions that still need to be confirmed.
- Confirmed dashboard - The dashboard displays summaries of only the confirmed claims. It does not include uploads from the active claim submission period.
- All confirmed dashboard - The summaries on this page include all program administrators and statewide program claims that are confirmed for the year to date.
Run CET on your claims
From the active dashboard, you can run claim data through the cost effectiveness tool (CET) to review the metrics calculated for the claim data before confirming the claim submission. The CET can only be run after there are accepted records uploaded.
The number of records in the claim data will determine how long it takes for the CET to return outputs. Small record sets can be expected to return results in a few minutes; large record sets can take an hour or longer to complete processing.
Upload a readme file
If you choose to include additional notes and information about your claim, you can upload a readme file from the Active dashboard. Uploading a readme file is optional, but if you choose to upload one, be aware that the file will be available for CPUC and DEER users on the confirmed dashboards.
The accepted file type for the readme is PDF, with a maximum size of 50 MB.
CEDARS will rename your file to PA_readme.pdf, for instance, PGE_readme.pdf.
You can have one readme file per claim year.
For statewide claims, there is one readme per PA per claim year.
If you need to change or edit your readme, delete the file from the active dashboard before uploading a new one. Any changes you make to the readme file will be applied once the current submission period has been confirmed.
Confirmation of a submission period
Program administrator admins can confirm claims for a submission period (quarterly or yearly). The confirmation of a claim submission requires certain conditions:
- The CET needs to be run on all uploaded data before you can confirm your claim submission
- A quarterly submission can only be confirmed if it has at least one completed (validated and accepted) upload for the present quarter.
- A yearly submission (YR) can be confirmed even if no uploads were made in the yearly submission period. This can happen if your review confirms that uploads for Q1 through Q4 were correct and contain the best available data.
- A quarterly submission can't be confirmed if it contains ProgramCosts for a future quarter. This can arise when there were uploads under the current quarter and then the previous quarter is re-opened. Example: the confirmation button for the 2023Q3 submission won't be available if CEDARS contains ProgramCost records for 2023Q4. Should this arise, re-upload the ProgramCosts for the re-opened quarter and they will overwrite the future quarter program costs and then you will be able to confirm.
Reopening claim submissions
Program administrators may discover issues with their confirmed submissions that require attention. Located at the top-right of the PA active dashboard is a button that allows them to unconfirm a submission before the submission period deadline or request a reopening with the CPUC if it is after the deadline.
To request a reopening, a reason must be provided. This reason and the request will be submitted to the CPUC for approval or rejection.
If a statewide submission needs to be unconfirmed or reopened, the program administrators must first unconfirm or reopen their local claims. All statewide program administrators will be notified.
It is only possible to reopen one submission period before the active submission, not two or more. For instance, if the active submission period is Q4, CEDARS will permit the reopening of Q3 but not Q2
Data Rules
File
File validation occurs when you upload a file on the Upload Claim or Statewide Upload Claim page. If the file does not meet the requirements, it won't be saved in CEDARS, and you will be shown an error message under the upload form.
File validation checks include:
- File format
- File size (Under 50MB)
- All required tables are in the file
- All required headers are in the table
- There are no extra headers in the table
If the file validation is successful, the file data will go through table validation.
Table
You can see if your upload has any table errors on the Upload History page and in more detail on the Upload Summary.
Table validation checks that a primary key is present for each table and that they are unique regardless of letter case it also checks the relational structure between tables.
-
Example of table relations:
- Measure.PrgID must match with a ProgramCost.PrgID
- Measure.CEInputID must match with a Claim.ClaimID
- ContactClaim.Claim ID must match with a Claim.ClaimID
- Claim.SiteID must match with a Site.SiteID
If the table validation is successful, the data will go through record validation.
CEDARS will reject the file entirely if a table error exists.
Record
You can see if your upload has record errors on the Upload History page and in more detail on the Upload Summary.
Record validation checks the following:
- Data type: Is the data in each record the correct data type? For example, a value that should be a number isn't a text string.
- Data rules: Is the data in each field following the required and conditional data rules? For example, a field's value must always be less than 2.
The full list of the record level data rules is listed in claims specification data rules.
CEDARS accepts and saves any successful records into the database.
When a record has an error, CEDARS rejects the record and it is not saved into the database.
Warnings
You can see if your upload has warnings on the Upload History page and in more detail on the Upload Summary.
When a record has a warning, CEDARS will accept the record, but the data breaks recommended rules as defined in the claims specification.
CEDARS Cost Equations
Cost equations
Claim.TotalGrossIncentive = (Measure.UnitEndUserRebate + Measure.UnitIncentiveToOthers + Measure.UnitDirectInstallLab + Measure.UnitDirectInstallMat) * Measure.NumUnits
Claim.TotalGrossMeasureCost = Measure.UnitMeaCost1stBaseline * Measure.NumUnits
Claim.TotalGrossMeasureCost_ER = Measure.UnitMeaCost2ndBaseline * Measure.NumUnits
CEDARS Savings Equations
First Year Gross
sum (
case when Measure.RUL_Yrs >= 1 then 1
when Measure.RUL_Yrs > 0 and Measure.RUL_Yrs < 1 then Measure.RUL_Yrs
when Measure.RUL_Yrs = 0 and Measure.EUL_Yrs > 1 then 1
else Measure.EUL_Yrs end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkW else 1 end
* Measure.NumUnits
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW1stBaseline
+
case when Measure.RUL_Yrs > 0 and Measure.RUL_Yrs < 1 and Measure.EUL_Yrs >= 1 then 1 - Measure.RUL_Yrs
when Measure.RUL_Yrs > 0 and Measure.RUL_Yrs < 1 and Measure.EUL_Yrs < 1 then Measure.EUL_Yrs - Measure.RUL_Yrs
else 0 end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkW else 1 end
* Measure.NumUnits
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW2ndBaseline
) as Claim.TotalFirstYearGrosskW,
sum (
case when Measure.RUL_YRS >= 1 then 1
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 then Measure.RUL_YRS
when Measure.RUL_YRS = 0 and Measure.EUL_YRS > 1 then 1
else Measure.EUL_YRS end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkWh else 1 end
* Measure.NumUnits
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh1stBaseline + Measure.UnitkWhIOUWater1stBaseline)
+
case when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS >= 1 then 1 - Measure.RUL_YRS
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS < 1 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkWh else 1 end
* Measure.NumUnits
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh2ndBaseline + Measure.UnitkWhIOUWater2ndBaseline)
) as Claim.TotalFirstYearGrosskWh,
sum (
case when Measure.RUL_YRS >= 1 then 1
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 then Measure.RUL_YRS
when Measure.RUL_YRS = 0 and Measure.EUL_YRS > 1 then 1
else Measure.EUL_YRS end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRTherm else 1 end
* Measure.NumUnits
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm1stBaseline
+
case when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS >= 1 then 1 - Measure.RUL_YRS
hen Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS < 1 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRTherm else 1 end
* Measure.NumUnits
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm2ndBaseline
) as Claim.TotalFirstYearGrossTherm,
First Year Net
sum (
case when Measure.RUL_YRS >= 1 then 1
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 then Measure.RUL_YRS
when Measure.RUL_YRS = 0 and Measure.EUL_YRS > 1 then 1
else Measure.EUL_YRS end
* Measure.NumUnits
* Measure.NTGRkW
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW1stBaseline
+
case when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS >= 1 then 1 - Measure.RUL_YRS
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS < 1 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* Measure.NumUnits
* Measure.NTGRkW
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW2ndBaseline
) as Claim.TotalFirstYearNetkW,
sum (
case when Measure.RUL_YRS >= 1 then 1
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 then Measure.RUL_YRS
when Measure.RUL_YRS = 0 and Measure.EUL_YRS > 1 then 1
else Measure.EUL_YRS end
* Measure.NumUnits
* Measure.NTGRkWh
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh1stBaseline + Measure.UnitkWhIOUWater1stBaseline)
+
case when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS >= 1 then 1 - Measure.RUL_YRS
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS < 1 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* Measure.NumUnits
* Measure.NTGRkWh
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh2ndBaseline + Measure.UnitkWhIOUWater2ndBaseline)
) as Claim.TotalFirstYearNetkWh,
sum (
case when Measure.RUL_YRS >= 1 then 1
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 then Measure.RUL_YRS
when Measure.RUL_YRS = 0 and Measure.EUL_YRS > 1 then 1
else Measure.EUL_YRS end
* Measure.NumUnits
* Measure.NTGRTherm
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm1stBaseline
+
case when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS >= 1 then 1 - Measure.RUL_YRS
when Measure.RUL_YRS > 0 and Measure.RUL_YRS < 1 and Measure.EUL_YRS < 1 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* Measure.NumUnits
* Measure.NTGRTherm
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm2ndBaseline
) as Claim.TotalFirstYearNetTherm,
Lifecycle Gross
sum (
case when Measure.RUL_YRS > 0 then Measure.RUL_YRS
else Measure.EUL_YRS end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkW else 1 end
* Measure.NumUnits
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW1stBaseline
+
case when Measure.RUL_YRS > 0 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkW else 1 end
* Measure.NumUnits
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW2ndBaseline
) as Claim.TotalLifecycleGrosskW,
sum (
case when Measure.RUL_YRS > 0 then Measure.RUL_YRS
else Measure.EUL_YRS end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkWh else 1 end
* Measure.NumUnits
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh1stBaseline + Measure.UnitkWhIOUWater1stBaseline)
+
case when Measure.RUL_YRS > 0 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRkWh else 1 end
* Measure.NumUnits
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh2ndBaseline + Measure.UnitkWhIOUWater2ndBaseline)
) as Claim.TotalLifecycleGrosskWh,
sum (
case when Measure.RUL_YRS > 0 then Measure.RUL_YRS
else Measure.EUL_YRS end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRTherm else 1 end
* Measure.NumUnits
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm1stBaseline
+
case when Measure.RUL_YRS > 0 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* case when Measure.DeliveryType = 'C&S' then Measure.NTGRTherm else 1 end
* Measure.NumUnits
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm2ndBaseline
) as Claim.TotalLifecycleGrossTherm,
Lifecycle Net
sum (
case when Measure.RUL_YRS > 0 then Measure.RUL_YRS
else Measure.EUL_YRS end
* Measure.NumUnits
* Measure.NTGRkW
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW1stBaseline
+
case when Measure.RUL_YRS > 0 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* Measure.NumUnits
* Measure.NTGRkW
* Measure.InstallationRatekW
* Measure.RealizationRatekW
* Measure.UnitkW2ndBaseline
) as Claim.TotalLifecycleNetkW,
sum (
case when Measure.RUL_YRS > 0 then Measure.RUL_YRS
else Measure.EUL_YRS end
* Measure.NumUnits
* Measure.NTGRkWh
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh1stBaseline + Measure.UnitkWhIOUWater1stBaseline)
+
case when Measure.RUL_YRS > 0 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* Measure.NumUnits
* Measure.NTGRkWh
* Measure.InstallationRatekWh
* Measure.RealizationRatekWh
* (Measure.UnitkWh2ndBaseline + Measure.UnitkWhIOUWater2ndBaseline)
) as Claim.TotalLifecycleNetkWh,
sum (
case when Measure.RUL_YRS > 0 then Measure.RUL_YRS
else Measure.EUL_YRS end
* Measure.NumUnits
* Measure.NTGRTherm
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm1stBaseline
+
case when Measure.RUL_YRS > 0 then Measure.EUL_YRS - Measure.RUL_YRS
else 0 end
* Measure.NumUnits
* Measure.NTGRTherm
* Measure.InstallationRateTherm
* Measure.RealizationRateTherm
* Measure.UnitTherm2ndBaseline
) as Claim.TotalLifecycleNetTherm,
PII Data
What is PII data?
PII data describe the confidential and personal details about the people and sites that participated in the energy efficiency programs. PII data reporting is done outside of Cedars (using SFTP tool called Kiteworks) at the same time as the Cedars claim submissions and is required to align with that year's cumulative current Cedars claim data. That is, every SiteID and ContactID submit to CEDARS must be described in the PII data submission.
To minimize movement of confidential data, we recommend only submitting incremental PII additions and corrections each quarter, but PAs may resubmit the entire PII data set if they wish. New data are appended to the PII data table; any PII IDs that had been previously submit overwrite the previously submit record.
Preparing your Claims PII file
Your Claims PII upload consists of two specific data tables:
PII_Contact
PII_Site
Your file must contain all tables and correct field names as headers as defined in the Claims PII specification; fields must be in the table in the order shown in the specification. The data in the tables must adhere to the rules specified in the specification, including the primary key rules for the PII tables. That is, the PII data submission should not include duplicate records, and each primary key should appear in the PII data only once. Primary key values are case-insensitive, which means that Customer123 is the same ID as CUSTOMER123.
Create a CSV file for each table and zip them together. Name each CSV file using your PA code and table name (e.g. SCE-Site.csv). The zip file should be named using your PA code and claim year quarter (e.g. SCE2023Q4.zip). Prepare the CSV using the pip or up-bar character as the delimiter.
Programs and measures exempt from PII data reporting
The following types of programs and measures are not required to submit PII data:
- Programs in the Market Education Outreach and Energy Savings Assistance categories
- Measures where delivery type is Codes and Standards (C&S)
- Measures where impact type is population-level custom NMEC (Cust-NMEC-Pop)
FAQ
Where should I input rebates and incentives?
All rebates and incentives are in the Measure table (measure.csv file). The incentive fields are multiplied by the quantity input in the NumUnits field to produce the total cost. The field NormUnit describes the type of normalizing units, or basis of NumUnits or quantity used by the cost effectiveness calculation (e.g. 'Lamp', 'hp', 'tons', 'facility', etc.). The measure table contains the following rebate and incentive fields:
- UnitDirectInstallLab
- UnitDirectInstallMat
- UnitEndUserRebate
- UnitIncentiveToOthers
- UnitMeaCost1stBaseline
- UnitMeaCost1stBaseline
Should the input kW be coincident, daily coincident, or non-coincident?
The kW savings must be calculated consistently with the DEER coincident peak definition. The DEER coincident peak definition is described on page A-12 in DEER Resolution E-5152.