Enterprise Information Services (EIS) – Review Ray Chase Manager, EIS

Enterprise Information Services (EIS) - Review Ray Chase Manager, EIS

EIS Vision Provide reliable, innovative, centralized, and comprehensive decision support and business intelligence systems to ERCOT, the Market, and the PUCT.

Why? Protocol Requirements – Market Data Collection and Use (section 17) Collect an Accurate Historical Record of all Source Systems Single System Source Data Integration Opportunity to Offset Source System Load Robust Reporting Environment Intra-day Capture – All Data Auditability Ease of Use Centralized Tools One Version of the Truth Data Modeling Oversight Future ERCOT and Market Decision Support Opportunities

Overview Data Collection Data Archiving/Roll-off Data Integration Data Transparency and Delivery SOA Framework Data Modeling and Analysis Business Intelligence and Reporting Legacy System Transition Current Project Relationships Current Statistics

Data Collection All Operational Source Systems feed into ODS Replication is near real-time All intra-day transactions Nearly all tables (rules) Siebel Lodestar Paper Free TCH TCR lodstar EMMS omsappdev EMMS ercot RSS EMMS his user ODS

Data Archiving/Roll-off Uses a table based, data partitioning strategy Performance Long-term Historical Data Archiving Source System RSS ODS Table A Table A Table A Current Record Record Insert, Update, Update, Update Long-term Tape Record Insert, Update, Update, Update History 200103 Record 200102 Record 200101 Record Insert Insert Insert

Data Integration Multi-source system data integration Removes fragmentation of solutions Single version of the truth Reusability Various 2005 projects with integration 50017 50130 40015 50001 – – – – Collateral Calc ESIID Lookup TCR Reporting BI Foundations Lodestar lodstar EMMS omsappdev TCR Collateral Calculation ODS Business Intelligence

Data Transparency and Delivery Single Design - Multiple Solutions Large Scale Market Participant Extraction Public Data Extraction Market Reporting Tool (2006) Web Service Delivered Data (50024 – SCR740, ESIID) Type-1 – Scheduled Data Delivery Type-2 – Real-time Data Delivery Portal Views CDW Data Extracts Market Web Services RSS ODS Report Tool Business Intelligence

SOA Framework System to System utilizing our SOAP Web Services System to System utilizing our XML REST Web Services ERCOT’s Service Orientated Architecture Framework will our Use an ERCOT supplied Build your Webown Interface interface utilizing MP MP System information and System provide four methods to access ERCOT’s XML REST Web Services services URL HTML URL XSLT URL XML URL XML SOAP Request XML ERCOT Service Orientated Architecture Framework www.amazon.com/gp/browse.html?node 3435361 SOAP Reply

Data Modeling and Analysis Provides a framework for decision support, business intelligence, and data modeling and analysis Legacy Business Logic Lodestar CDW MS Access MS MS Access Access .xls.xls.xls .xls.xls.xls .xls.xls.xls EMMS RSS .xls.xls .xls ODS PC PC PC Business Intelligence .xls .xls .xls PC PC PC .xls .xls .xls .xls .xls .xls .xls .xls .xls Current

Data Modeling and Analysis Data Warehouse Marts LodestarSiebel lodstar TCH EMMS omsappdev ODS EMMS ercot EMMS his user Proposed .xls .xls .xls CDW .xls .xls .xls PC PC PC .xls .xls .xls .xls .xls ETS Data Mart Business Intelligence

Data Modeling and Analysis Potential External Data Source Additions Census Geographic Detailed Weather CDW RSS ODS .xls .xls .xls .xls .xls .xls Business Intelligence PC PC PC .xls .xls .xls .xls .xls

Business Intelligence & Reporting Centralized view foundation Provides standardized querying of all data Secure - Prevents direct access to the ODS database objects Ensures all private data is protected by the ODS security layer In-Bound Application Security In-Bound Application Security API API Operational Data Store (ODS) Conformed Data Warehouse (CDW) API Ref Cursor Views OutBound API Ref Cursor Views OutBound

Business Intelligence & Reporting Web-based tool for universal use Prompts provide a way quickly choosing your selection criteria B.I. tool allows “drilling-thru” to lower levels of granularity Extremely efficient for quickly & visually going from “10,000 foot” view to “extreme close-up”

EIS System Transition EIS Systems & Applications Web Service Based Data Delivery Data Delivery Module (DDM) Real-Time Reporting TCH RSS Paperfree Paperfree RSS Summary Reporting 6 T Report Net D Security Foundations 6 Paperfree ODS 6 6 Standard Views Standard APIs Custom Views Custom APIs & BLAPIs Siebel ODS D TCR Extracts Application Replication & Security Framework TCH ODS Siebel RSS Detailed Reporting 6 Siebel D Data Transparency Business Intelligence Foundation 6 TCH D TCR ODS EMMS EMMS RSS EMMS ODS Lodestar Lodestar RSS Lodestar ODS Source Systems Replicated Source Systems Operational Data Stores Application Administration Conformed Data Warehouse (CDw) 6 Data Mart Data Mart Data Mart ETS 6

Current Project Relationships 2005 EIS Project Relationships P 40015 02 TCR Report Enhancements 30084 CSC Thermal & Volt Limits Calculations / Posting 50004 EDW BI Reporting Tool P 30105 Congestion Management Reports 50017 CO – Collateral Calculation P 50024 Enhancements to SCR727 P 40100 RTCA Archiving 40072 MPCE Correction 40084 Outage Scheduler Enhancements P2 50030 EDW Lodestar & EMMS Transition 50121 03 Market Information Distribution P 40108 Disclosure of Local Congestion 40042 06 EDW Standard LodeStar Extracts P 50005 EDW EMMS Reports 40042 07 EDW EMMS ODS 40098 Performance Monitoring for SCADA / State Estimator 50001 01 Ancillary Services 30183 01 R4 Project Rollover 30026 EPS Meter Data (RID) P 40007 SCR 727 Phase II P Legend P 50001 03 Replacement Reserves Market Ad Hoc Reporting Stand-Alone Projects 50007 Enhancements to Fas Trak Tools 50001 02 Price Spike Analysis MO - 40044 ADAM Integration MO - 40104 Zero Charge Enhancements 50001 04 Ad Hoc Access to QSE Down-Balancing Compliance Report Data P Affected by Parsing Project NOT Started Initiated Project MO Planning Project CO 50002 MOMS – Enhancements to Potomac Software 50023 Potential Load Loss 50003 MOMS – Enhancements to AREVA Study Tools 50025 Enhance ESI ID Look-Up Function Executing Project SO IO RO Completed Project Cancelled or Deferred Project

Proposed 2006 EIS Projects Add' l Source Committee Src Doc Project Proj. No. Impact Area I Priority n f o Ran k Market PUCT IO EDW BI Foundation P2 - Repl Reserves Market Ad-hoc Reporting 50001 03 Monitor ing EDW 1.1a 22 PUCT IO EDW BI Foundation P2 - Ad-hoc Access to QSE Rpt Data 50001 04 Monitor ing EDW 1.1a 23 PUCT IO EIS BI Foundations III New Monitor ing EDW 1.1a 24 PUCT IO EIS Conformed Data Warehouse Foundations New Monitor ing EDW 1.1a 27 PUCT IO EIS CDW Data Marts New Monitor ing EDW 1.1a 28 ERCOT RO EDW Siebel Transition 50059 Infrastructure EDW 1.1b 48 ERCOT RO EDW TCH (Transaction Clearinghouse) Transition 50060 Infrastructure EDW 1.1b 49 ERCOT RO EDW EAI Transition (inc. PaperFree & NAESB) 50061 Infrastructure EDW 1.1b 50 ERCOT RO ETS (ESIID Tracking System) Transition to EDW 50088 Infrastructure EDW 1.1b 51 ERCOT IO EDW Lodestar Decommission 50030 Infrastructure EDW 1.1b 67 ERCOT IO EDW EMMS Decommission 50031 Infrastructure EDW 1.1b 68 ERCOT IO EIS Reporting Tool II New EDW 1.1b 69 ERCOT MO Lodestar Web Services New Market Market Market Market Market Monitor ing Market Impact EDW 1.2 93

Current ODS Statistics Migration - 2 billion historical transactions from Lodestar and EMMS Replication – no lost information to date Daily Average 15 million transactions Total transactions posted Source System Replicated LODSTAR 1,273,023,928 EMMS 890,651,313 HIS USER 181,868,288 2,345,543,529 Developed/Installed Code Base Object INDEX Count Lines 787 PACKAGE 1,501 150,935 PACKAGE BODY 1,500 610,481 PROCEDURE 682 45,400 TABLE 794 TRIGGER 762 69,763 3,535 103,208 9,561 979,787 VIEW

Conclusion Comments? Questions? Thank You!