Please send questions or feedback to: cedars-help@sound-data.com

-------------------------------------------------------------------------------
Specification notes
-------------------------------------------------------------------------------

CEDARS Monthly Report Module (MR) data requirements are captured in one source
of truth file named MR_spec.sql; that file defines:

    1. The table specification (table name, field names, data types, and
       primary key)
    2. The QC rules for monthly report data being submitted to CEDARS. The
       monthly report has only single field QC rules that can be found in
       MR_spec.sql.

If any data in a MR upload fail QC the entire upload is rejected.

Each MR upload is a stand alone submission; i.e. if data for a previously
submitted YearMonth are resubmitted, the prior submission data will be deleted
entirely and the new submission will become the data of record.

Some notes:

    1. There is a ‘MR_metadata.csv' file that explains the semantics attached
       to each field in each of the files in a MR upload.
    2. Being a primary key constrains a field to be both not null and unique.
    3. When MR data are uploaded, and the new upload passes QC, CEDARS will
       overwrite any prior submitted data for that year with the new upload.
    4. SQL files can be opened and read in any text editor.

-------------------------------------------------------------------------------
Data file preparation
-------------------------------------------------------------------------------

MR data submissions consist of one .csv data table, zipped and given a file
name of PA choosing, in a .zip archive.

A Readme.txt may optionally be included in the .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.

-------------------------------------------------------------------------------
QC
-------------------------------------------------------------------------------

Table level tests

    1. Does the file 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 non-ASCII characters are stripped.

    4. Is YearMonth the same as the current submission YearMonth for all
       records?
       If the answer is no, the upload is refused.


Single field validations are in the MR_spec.sql file.

-------------------------------------------------------------------------------
Source of Truth File: MR_spec.sql
-------------------------------------------------------------------------------

The MR_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 ones:

    1. CEDARSYearMonth: A six character text field which is validated to
       confirm that the given data parses to a month of the form 201701,
       where 01 = January

    2. CEDARSValueList: Text types that are constrained to certain value lists
       as specified by Commission Staff.

    3. NotEmpty: usually in final position, it means the field is never
       allowed to be empty.

-------------------------------------------------------------------------------
Data change management
-------------------------------------------------------------------------------

CEDARS has a queue for suggested changes to the specification and QC
requirements for consideration at the next annual specification update.

Please email change suggestions to cedars-help@sound-data.com; you will receive a
response with a link that will allow you to continue to correspond with the
CEDARS team and monitor work on your suggestion via the CEDARS ticketing
system.