Please send questions or feedback to: cedars-team@sound-data.com ------------------------------------------------------------------ Table of Contents ------------------------------------------------------------------ Introduction Specification notes Data file preparation QC Tiers Tier 1 Tier 2 Tier 3 Source of Truth files cet_spec validation_rules warning_rules ------------------------------------------------------------------ Specification notes ------------------------------------------------------------------ CEDARS Cost Effectiveness Tool (CET) Module data requirements are captured in three source of truth files; these files define: 1. The table specifications (table names, field names, data types, primary and foreign keys) 2. The QC rules for the data being submitted to CEDARS. QC rules are broken out into categories: a. Single field QC rules and warnings: found in cet_spec.sql b. QC rules that require evaluation of more than one field. Within this group there are two categories: i. Rules: if a record does not comply with the rule it is rejected: found in validation_rules.csv ii. Warnings: if a record triggers a warning the data are accepted and the warning is provided back to the user for review: found in warning_rules.csv If any data in a CET upload fail QC the entire upload is rejected. Some notes: 1. There is a 'cet_metadata.csv' file that explains the semantics attached to each field in each of the files in a CET upload. 2. The three source of truth files support three tiers of QC; the tiers are additive and evaluated in sequence. 3. Being a primary key constrains a field to be both not null and unique. Additionally, the ProgramCost table has some notable features: 1. The ProgramCost table has a compound primary key. That is, the primary key is made up by two fields instead of one; the ProgramCost table primary key fields are ProgramID and Year. 2. By nature of the primary key, there can only be one ProgramCost record per year per program. 3. For programs that have only program costs and no savings, you must submit a single measure record for the program with zero savings as a placeholder for the CET to correctly calculate the program and portfolio accomplishments and cost effectiveness. The CET needs both program cost and measure data for every program submitted to run correctly. ------------------------------------------------------------------ Data file preparation ------------------------------------------------------------------ CET data submissions consist of two .csv data tables, zipped together and given a file name of user choosing, in a .zip archive. CEDARS requires data files be prepared using the pipe or up-bar character "|" as the delimiter with unquoted text values. Files must contain the correct field names, using the specified casing (e.g. CamelCaps), and fields must occur in the correct order. The maximum precision allowed is 15 digits past the decimal. ------------------------------------------------------------------ QC Tier 1: High level specification compliance ------------------------------------------------------------------ Table level tests 1. Do the files have the right table and field names? 2. Do the data comply with the table primary key requirements? If the answer to any question is no, the upload is refused. Record level tests 1. Do the data have the correct data types? If the answer is no, the upload is refused. 2. Are the data free from line feeds (LF) and carriage returns (CR) between records? If the answer is no, the LF and CR are stripped and the data are accepted. 3. Are the data free from non-ASCII characters? If the answer is no, the whole submission is rejected. ------------------------------------------------------------------ QC Tier 2: Cross table validation ------------------------------------------------------------------ We use the SQL syntax of TableName.FieldName to explicitly specify the table and field being compared. These rules are evaluated at the table level. 1. Do all Measure.PrgID + Measure.left(ClaimYearQuarter, 4) exist in ProgramCost.PrgID + ProgramCost.PrgYear? If the answer is no, reject the upload. 2. Do all ProgramCost.PrgID + ProgramCost.PrgYear exist in Measure.PrgID + Measure.left(ClaimYearQuarter, 4)? If the answer is no, reject the upload. 3. Is the year of all ClaimYearQuarter data equal to or greater than the CET FirstYear chosen for the run? If the answer is no, reject the upload. ------------------------------------------------------------------ QC Tier 3: Field rules ------------------------------------------------------------------ In Tier 3 we perform record-level validation of claims using the CEDARS source of truth files. Tier 3 has two categories of validation: single field and multiple field. Single field validations are in the claim_spec.sql file. Multi-field rules are in the validation_rules.csv and warnings_rules.csv files. Please see the information below on the Source of Truth files. ------------------------------------------------------------------ Source of Truth File: cet_spec.sql ------------------------------------------------------------------ The cet_spec file is written as an enhanced SQL create table script that includes all single field constraints, table definitions, field names, and data types. We are calling our enhanced SQL dialect SQLPlus. Our SQLPlus dialect includes the usual data types (i.e. bit, nvarchar, numeric), plus some extra types and modifiers: CedarsYearQuarter: A six character text field which is validated to confirm that: 1. The given data parses to a quarter of the form 2019Q3 2. The year is in the same as or later than the FirstYear run parameter. CedarsNumRange: Numeric types that are constrained. Square brackets are inclusive [greater/less than or equal to], parenthesis are exclusive (greater/less than). That is: CedarsNumRange (0,1) means greater than zero and less than one CedarsNumRange [0,1] means greater than or equal to zero and less than or equal to one CedarsNumRange (0,1] means greater than zero and less than or equal to one WarningCedarsNumRange: Numeric constraints that raise warnings instead of causing the records to be rejected. Uses the same notation conventions as CedarsNumRange. CedarsValueList: Text type constrained to certain value lists specified by Commission Staff. Please note that, in an effort to reduce validation restrictions for fields that do note impact cost effectiveness, DEER value lists in the CET UI are currently not constrained by year. NotEmpty: means the field is never allowed to be empty. DefaultFalse: used only for boolean flag, usually in final position, it means providing this flag is optional, but if null the field will be autofilled with False. EmptyField: used for fields that are planned for deprecation, but haven't been deleted yet. As the name indicates, this means the field must be left empty for the record to be accepted. ------------------------------------------------------------------ Source of Truth File: validation_rules.csv ------------------------------------------------------------------ The validation_rules file provides are rules which, if violated, cause the CET upload to be rejected. Data are tested against validation rules on record-level basis. Data that fail validation rules cause the entire upload to be rejected. The QC rules in this file require more than one field to evaluate. Some rules are conditionally applied to the data; conditional rules will have one or more set(s) of where clause columns populated in the csv. Validation rules have up to two where clauses. The rules used to specify validation conditions: is null or zero is not null less than less than or equal to greater than greater than or equal to equals does not equal ends with string combo_value_list matches_year_quarter The rules are self-explanatory, except the last two: - a 'combo_value_list' rule means that all the values in the listed fields in a given record must be in an existing combination in the corresponding combo according to the CEDARS specification. - a 'matches_year_quarter' rule is used to ensure that a given ProgramCost's PrgYear matches its ProgramYearQuarter. All rules define the expected state of the data; that is, when data do not comply with the requirement specified, the data are rejected. ------------------------------------------------------------------ Source of Truth File: warnings_rules.csv ------------------------------------------------------------------ The warnings_rules file provides rules that, if violated, return a warning to the user, and the data are accepted. Data are tested against warnings on record-level basis. Some of these rules are conditionally applied to the data; conditional rules will have one or more set(s) of where clause columns populated in the file. Warnings have up to five where clauses. The rules used to specify warning conditions: is null or zero is not null less than less than or equal to greater than greater than or equal to equals does not equal All rules define the expected state of the data; that is, when data do not comply with the requirement specified by the rule, the data are accepted, but a warning is returned to the user.