Standardized Program Tracking Database SPTdb

1
Standardized Program Tracking Database (SPTdb)

• Eric Merkt
• Summit Blue Consulting

2
Overview

• Background
• IOU Program Tracking Data
• E3 Calculators
• Standardized Program Tracking Database (SPTdb)
• Requirements for SPTdb
• SPTdb Creation Process
• Next Steps

3
BackgroundIOU Program Tracking Data

• IOU EE database systems track EE projects
• Primarily fed by EE Program Implementer
  project-level data
• Site information (i.e., address, building type,
  climate zone)
• Incentive information (i.e., rebate issue amount
  and date)
• EE Measure information (i.e. EE measure
  name/type, baseline)
• IOU Program Tracking Data is a product of an
  IOU query of the tracking database system
• Submitted quarterly to the Energy Division (ED)
• Data fields submitted to CPUC are a subset of the
  total fields in the IOU EE database system
• Data could receive levels of aggregation/disaggreg
  ation of implementer data

4
Background (continued)

• Complications
• Each IOU has own methodology for reporting
  tracking data
• EE projects reported at various levels of
  aggregation
• One line item could represent an entire EE
  project involving several different measures, or
• One line item could represent a single CFL
• Varies from IOU-to-IOU and within IOUs and within
  single EE programs
• Different database structures, reported fields,
  and data types
• Varies from IOU-to-IOU and within IOUs
• Varies from quarter-to-quarter
• 2,200 unique fields across 100 data tables in
  various formats (Excel, Access, etc.)
• Program tracking databases are a moving target
• EMV studies and IOUs EE projects are concurrent
  throughout the evaluation cycle are ongoing
  through the evaluation cycle
• Frequently, a given line of IOU program tracking
  data can change, be deleted, or zeroed out
  without traceabilitylack of primary keys
• Introduces sampling issues
• Bottom line Contractors have used several
  iterations of IOU tracking data to perform EMV
  studies, establish study designs, and develop
  sample populations, BUT
• All EMV study results must be tied back to the
  final
• Q4 2008 IOU Program Tracking Database Claim (Mar
  09 submittal)

5
BackgroundE3 Calculators

• IOU-submitted quarterly EE program
  accomplishments summary
• Publicly available through the EEGA website
• Gives CPUC program cost effectiveness insight
  (TRC test)
• Avoided costs and net benefits
• Provides additional data fields not present in
  IOU program tracking data (ex-ante load shapes,
  EUL, NTGR, etc.)
• One submittal (MS Excel) per program or program
  element 177 total submittals
• Aggregates IOU program tracking data for
  reporting purposes 4.5 million program
  tracking records 21,000 E3 calculator records

6
BackgroundE3 Calculators (contd)

• Complications
• Each IOU has own methodology for E3 reporting
• All submittals generally follow the same
  guidelines specified by CPUC
• Fields used for aggregation of IOU program
  tracking data into E3 reporting lines vary by
  IOU, program, program element, and quarter
• E3 file structure also varies between E3 filings
• It is sometimes unclear how/whether IOU ex-ante
  realization rates are applied to reported E3
  numbers
• Result It is sometimes difficult to match IOU
  program tracking data to E3 filing.

7
E3 Solution Standardized E3 Table

• ERT team developed tool to bring all E3
  calculators into a standardized format
• 177 MS Excel files into a single data table with
  well-defined fields
• Maintains traceability by storing the data source
  of record and assigning primary key
• Earlier versions used in the 1st (2/5/2009) and
  2nd (10/15/2009) verification reports
• Enables a systematic, centralized mapping of IOU
  program tracking data to E3 claim lines

E 3 Calculator 2
E 3 Calculator 3
E 3 Calculator 1
E 3 Calculator n
Standardized E3 Table
8
Standardized Program Tracking Database (SPTdb)

• Requirements
• Standard format
• Need for a standardized data format that brings
  together all IOU program tracking data
• Enables global queries of measures and programs
  that reach across IOUs
• Primary key that allows for tracing of a line
  item of tracking data throughout the entire
  evaluation process
• Version control
• Need for a single source of IOU-claimed data to
  ensure all contractors are using the same source
  of officially claimed data (2006-2008 period),
  specified by ED
• Ensures all IOU-provided lookup tables are the
  latest version and are globally applied
• Individual EMV contractors and ERT team has
  requested data from IOUs and could have
• Single source of supplemental IOU data necessary
  to ensure consistency
• Allows for matching of contractor studies to the
  final, official program tracking claim
• Application of Ex-Post EMV results
• All claimed lines of IOU program tracking data
  will have EMV results applied
• All ex-post realized savings estimates must be
  applied in a traceable fashion

9
EMV Study Lead Data Request
Contractor Response 2

• All EMV study leads were sent a request to
  identify the IOU data they require from the
  various data sources available to perform EMV
  studies
• Response was used to populate an exhaustive
  unique list of fields required in the SPT DB

Contractor Response 1
Contractor Response 3
Exhaustive List of Required Fields
Contractor Response n
10
Consolidating IOU Data to a Single Dataset Per IOU

• Depending on IOU, one or many IOU program
  tracking datasets and lookup tables (not present
  in program tracking data) per IOU need to be
  consolidated into one database per IOU
• ED has data added fields to assist the ERT
  process (i.e., EDUpstreamFlag)
• ED data operations include logic that selects the
  official claimed data from the IOU data
• One unique dataset per IOU was developed

Additional Lookup Tables
ED Data operations
IOU Tracking Dataset 1
One Tracking Dataset per IOU
IOU Tracking Dataset 2
IOU Tracking Dataset n
IOU Tracking Dataset 3
11
Mapping IOU Program Tracking Data to SPTdb
Standardized E3 Table

• The standardized E3 table was merged with the IOU
  tracking dataset via various algorithms developed
  by contractors, ED, or IOUs
• Using the contractor field list reponses, the
  SPTdb standard field list was iteratively
  developed through contractor data reviews
• The SPTdb standard field list was used to develop
  a data dictionary that maps IOU program tracking
  fields to SPTdb fields
• Using the data dictionary, tracking data with an
  E3 foreign key is mapped to the SPTdb

SPTdb
Tracking Data Merged With E3 Data
Data Dictionary
One Tracking Dataset per IOU
12
SPTdb Creation Process
E 3 Calculator 2
E 3 Calculator 3
Contractor Response 2
Contractor Response 3
Contractor Response 1
E 3 Calculator 1
Contractor Response n
Standardized E3 Table
E 3 Calculator n
Exhaustive List of Required Fields
Tracking Data Merged With E3 Data
SPTdb
Data Dictionary
Additional IOU X Lookup Tables
One Tracking Dataset per IOU
IOU X Tracking Dataset 1
Proposed standardized field list, nomenclature
ED-Developed Lookup Tables
IOU X Tracking Dataset 2
IOU X Tracking Dataset n
IOU X Tracking Dataset 3
13
Current State of SPTdb

• First cross-IOU view of all 2006-2008 IOU program
  tracking data
• Common format, allowing multi-IOU querying
• Provides flexibility required for ERT
  calculations
• Majority of tracking data line items have been
  merged with E3 calculatorsongoing with IOU
  assistance

14
Next Steps

• IOU review of SPTdb
• Review of tracking data values to ensure correct
  version
• Review of ED-assigned primary keys/key assignment
  methodology
• Review of data dictionary to verify appropriate
  field mapping to SPTdb and data type conversions
• Review of sum of ex ante savings (gross kW, kWh,
  Therms) by program, program element, and measure
• Data request will be issued mid November ED
  will establish timeline for review
• Respond to IOU comments and produce new version

15
Questions?