Example: PL/SQL procedure to load table from the “staging” area into the “work” area 10
What to know about the data 11
Dimensional Data Table Contents 11
Dimensional Data Table Add times 11
ESIID Level Data Table Contents 12
ESIID Level Data Table Add times 12
“Delete” Table Processing 12
Handling Exceptions 14
Appendix A: Table Order for Daily Loading 14
Appendix B: Data Field Descriptions By Table 15
Dimensional Tables- 15
Transactional Tables- 18
Appendix C: SCR727 Lodestar Database Schema 22
This document describes the data contained within and the environment and strategies for data loading the SCR 727 ESIID Service History & Usage Extract data. Explanation on how the extract is built and the data that is contained on a daily basis is provided. The technical examples contained in this document use Oracle architecture. The concepts, however, can be applied to any relational database system (RDBS) with adjustments.
This document is intended for both business and technical audiences. This guide is not intended as a complete guide for scheduled data extracts. Supplemental information regarding the SCR 727 ESIID Service History & Usage Extract will be communicated to the market from ERCOT on an as needed basis. Please ensure that these communications are passed within your organization to the appropriate parties responsible for the business and technical aspects of processing ESIID Service History & Usage Extract data.
When translating the logic within this document to your own systems, please be aware that the examples may need modifications in order to accommodate your unique environment. Thorough testing is strongly advised.
Provides transparency to Market Participants for ESIID level data that ERCOT utilizes in market settlement
Provides data for Market Participants to perform comparisons for identifying ESIID relationships, characteristics and usage and generation variances between their internal systems and ERCOT systems
Provides necessary data for LSEs to shadow settle their load volume
Provides the level of transparency to expedite resolution of ESIID level data anomalies between ERCOT and Market Participant systems, which should result in greater accuracy of settlement statements
Is triggered by data inserts/updates/deletes occurring within the data load time window
Who receives this extract?
Extract is scheduled by Market Participants
Market Data Transparency Web Services (ESIID)
A new set of Market Data Transparency Web Services will be delivered to the market with the implementation of SCR 740 (SCR 727 Phase II). Market Participants will be able to use these Web Services to perform ad-hoc data research on ESIID information used during the data aggregation and settlements processes. Market Participants will also be able to request a database refresh for their SCR 727 ESIID Service History & Usage Extract databases. The refresh is requested based on a provided point in time. Once the refresh data is loaded in the Market Participant database, the subsequent day’s daily SCR 727 ESIID Service History & Usage Extract should be applied while continuing to load daily files to keep the data current.
Data Extract Transition Process
This section describes the transition process that will be used when the SCR 727 ESIID Service History & Usage extract is moved from production out of the Data Archive to the Lodestar ODS. A diagram explaining the SCR 727 transition process is displayed below along with a list of significant information of changes to the extract.
NOTE: This transition plan only applies to recipients of the extract at the time of the transition.
Extract data load window is changing from receiving an extract file, including the net change of data for trunc(SYSDATE – 1) 04:00:00 – trunc(SYSDATE) 04:00:00, to receiving an extract file including the net change of data for trunc(SYSDATE -3) batch date. This data will be representative of the data available for use in the data aggregation and settlements process for trunc(SYSDATE – 3) batch date.
This transition does not require any changes to the DDL.
ESIID Usage & Service History daily extracts will transition by using a two day window to synchronize the production of the extract with the batch loading cycle as shown in the diagram above.
The transition process will not incur a data gap as the result of transitioning to the new method of production.
A single day of the extract (batch day 1/19/2007 in the example) will have a shortened load window to align with the new schedule and possibly contain fewer rows of transactional data.
At the time of the transition, all current recipients of the SCR 727 extract will continue to receive the extract moving from the old schedule to the new schedule. This will not require actions by the Market Participants.
Market Participants scheduled to receive the extract will have the capability to unschedule themselves from receiving the daily SCR 727 data extracts once this transition is complete.
Any Market Participants who are not in ERCOT systems at the time of the transition will not be scheduled to receive extracts on a daily basis.
Market Participants not opted-in, or those that have unscheduled data extracts, will need to request an initial data refresh to baseline their SCR 727 ESIID Service History & Usage Extract database in addition to submitting an opt-in to receive daily extracts. The request for an initial data refresh should be for a point in time of sysdate-1, while the request for the daily extracts should occur on the same day as the request is made for the database refresh. These requests should be performed simultaneously so that there are no lapses in data delivery.
TDSPs will no longer receive separate TDSP and MRE extracts. One extract per Market Participant DUNs number will be created going forward. TDSPs that are only MREs for certain ESIIDs will now receive a combined data file which includes ESIIDs for which they are the TDSP and/or MRE.