Texas Nodal CDR Project

Texas Nodal CDR Project Conceptual System Design ERCOT Public

CDR

Conceptual System Design

v1.0

February 07, 2008

Texas Nodal CDR Project Conceptual System Design ERCOT Public

Document Revisions

Date / Version / Description / Author(s)
21-Dec-07 / 0.10 / First draft / Ganesh Narayan
24-Dec-07 / 0.11 / Changes incorporated as per Michael D’s suggestion / Ganesh Narayan
04-Jan-08 / 0.12 / Incorporated changes suggested by Sanjay / Ganesh Narayan
07-Jan-08 / 0.13 / Incorporated changes suggested by EA / Ganesh Narayan
08-Jan-08 / 0.14 / Added Data flow view / Ganesh Narayan
08-Jan-08 / 0.15 / Updated Figure 2 / Ganesh Narayan
09-Jan-08 / 0.16 / Cosmetic changes / Ganesh Narayan
10-Jan-08 / 0.17 / Grammatical and format changes only / Amy Apodaca
11-Jan-08 / 0.18 / Accepted Amy’s changes / Ganesh Narayan
14-Jan-08 / 0.19 / Formatting changes / Ganesh Narayan
31-Jan-08 / 0.20 / Changes to reflect TPTF comments / Ganesh Narayan
7-Feb-08 / 1.0 / Made changes as per TPTF comments. Reflects TPTF approval on 6-Feb-08 / Ganesh Narayan

Document Approvals

Date / Approved By / Approval Documented In (select)
Brian Cook
Enterprise Architecture / ___ Approval email on file
___ Signature

Texas Nodal CDR Project Conceptual System Design ERCOT Public

Table of Contents

1. Introduction 4

1.1. Purpose 4

1.2. Scope 4

1.3. Assumptions 4

1.4. Definitions, Acronyms, and Abbreviations 4

1.5. References 6

2. Overview 6

2.1. Design Goals 6

2.2. Design Approach 7

2.2.1 System Functional Capabilities 7

2.2.2 CDR White Box View 9

2.2.3 CDR Black Box View 9

3. Functional Specification 11

3.1. Function: Data Retrieval / Data Aggregation 11

3.1.1 Traceability to Requirements 11

3.1.2 Introduction 11

3.1.3 Inputs and Sources 11

3.1.4 Processing 11

3.1.5 Outputs and Targets 11

3.2. Function: Report Generation 11

3.2.1 Traceability to Requirements 11

3.2.2 Introduction 12

3.2.3 Inputs and Sources 12

3.2.4 Processing 12

3.2.5 Outputs and Targets 12

3.3. Function: Publish Reports 12

3.3.1 Traceability to the Requirements 12

3.3.2 Introduction 12

3.3.3 Inputs and Sources 12

3.3.4 Processing 12

3.3.5 Outputs and Targets 13

3.4. Function: Retrieve Reports 13

3.4.1 Traceability to Requirements 13

3.4.2 Introduction 13

3.4.3 Inputs and Sources 13

3.4.4 Processing 13

3.4.5 Outputs and Targets 13

4. System Dependencies and Design Constraints 13

4.1. Hardware 13

4.2. Software Interfaces 13

4.3. Services Interfaces 14

4.4. Database Interfaces 14

4.5. Licensing Requirements 14

5. Architectural View 15

6. Data Flow View 16

7. Supplementary Specifications 17

7.1. Performance 17

7.2. Legal and Regulatory 18

7.3. System and Communication 18

7.4. System Security 18

7.5. Data Confidentiality 18

7.6. Back up and Recovery 18

7.7. Availability and Redundancy 18

7.8. Maintainability 19

7.9. Usability 19

Texas Nodal CDR Project Conceptual System Design ERCOT Public

1. Introduction

The Current Day Reports (CDR) Conceptual System Design (CSD) document is intended to explain how NODAL current day reports will be delivered to Market Participants, as required by the NODAL protocols. As stated in the project charter, Current Day reports are defined as Market Information System (MIS) Content Inventory items with a latency, frequency, and/or performance requirements that cannot be met by the Enterprise Data Warehouse (EDW) environment. Market Participants will utilize the MIS portal application or Web Services to access these reports.

This project will also deliver a NODAL report content management solution that supports report archival needs of the current day reports project. This solution will be separate from the existing Zonal report content management implementation.

The requirements for the Current Day reports originate from the Texas NODAL Protocols.

1.1. Purpose

The purpose of this Conceptual System Design document is to provide a conceptual overview of the high level architecture that will be in place to support NODAL CDR deliverables.

1.2. Scope

This document is limited to CDR requirements including real-time and graphical views of ERCOT market data. Replication of source system data to the EDW Operational Data Store (ODS) and Replication Source System (RSS) are out of scope for this project and will be covered by the EDW CSD. The generation of dashboard portlets is out of scope for the CDR project. Alerts and Notifications are out of scope. The enhancements to MID/MIR to support CDR are a part of the scope of this project.

The reports delivered by CDR are either in the form of pre-defined, scheduled reports or reports that are generated on-demand and as data end points to External Web Services (EWS.) These deliverables have been identified via the NODAL Data Services Master List (NDSML) and have been mapped directly to specific NODAL Protocol sections.

1.3. Assumptions

The CDR project follows these assumptions

· Replication of source systems of record are out of scope for CDR project, it is in scope of the EDW project

· Dashboard portlets are out of scope for CDR, it is in scope of MIS project

· Ad-Hoc and on-demand reports will be entire sets of data. These sets of data will not be filtered (there will be methods to sort and filter on fields like date and timestamp).

· CDR will go against the source system staging tables for current day data and the source staging table RSS in case of source system staging table outages or load balancing.

1.4. Definitions, Acronyms, and Abbreviations

In order to clarify the acronyms associated with the ERCOT Enterprise Information Services (EIS) environment, the conceptual definition for the systems and tools used in the development and deployment of NODAL CDR deliverables are described below. These definitions and acronyms are specific to ERCOT and may not reflect industry definitions for the same conceptual components.

Current Day Reports (CDR) – The CDR system, rather than EDW, will handle real-time or near-real-time reports not produced by source systems. CDR will utilize EDW interfaces and other shared EIS features. CDR is a project separate from EDW.

Conformed Data Warehouse (CDW) – Data warehouse solution for the reporting and querying of enterprise information that requires data from a variety of source systems and ensures all dimensional data conforms to a single set of enterprise-approved characteristics.

DeMilitarized Zone (DMZ) - This is the zone between an organization's trusted internal network and an untrusted, external network such as the Internet.

Enterprise Data Warehouse (EDW) – Data warehouse solution for the replication of enterprise information from a variety of source systems to ensure all dimensional data conforms to a single set of enterprise-approved characteristics. EDW data will be used for Business Intelligence purposes.

Enterprise Information Services (EIS) – Organization that handles management and provides services of Enterprise wide data in a company.

Information LifeCycle Management (ILM) - refers to a wide-ranging set of strategies for administering storage systems on computing devices

Information Services Master Database (ISM) - ISM is the central EIS database consisting of the ODS and the CDW

Market Information Distribution (MID) - Set of services required to drop reports and extracts into the MIR, as well as retrieve them on demand. These are exposed as web services internal to ERCOT.

Market Information Repository (MIR) – The report content management database containing all reports and data extracts generated to be available to Market Participants. Market Participants retrieve reports from MIR via MIS.

Market Information System (MIS) – The primary NODAL Market Participant interface providing both GUI and web service interfaces. The MIS is presented here only as the means by which Market Participants access reports generated by CDR or EDW. MIS is a separate project outside the scope of the CDR and EDW projects.

NODAL Data Services Master List (NDSML) – Identifies reports and extracts specified by the protocol, specifies the source system, and the system responsible for producing the report or extract.

Operational Data Store (ODS) – Provides point-in-time selectivity (PITS) history of each source system allowing for accurate historical reporting of source system data.

Replication – Technology that copies data from one data source to another whenever data is inserted, updated, or deleted. Depending on the needs, systems may be replicated in a master-slave (one-way) or master-master (bi-directional) manner. Please refer to EDW CSD for more details

Replicated Source System (RSS) – Standby database configured to match a given source system with high-speed replication (master-slave). Independent of the ISM (The central EIS database consisting of the ODS and the CDW) database, used to reduce risk to certain source systems sensitive to performance degradation.

Source System (Source System of Record) – One of the live systems making up the Texas NODAL Program. This may also include the source systems RSS and its replicated ODS. Source systems include EMS, MMS, NMMS, CRR, and COMS.

1.5. References

The following sources provided input to this conceptual design document.

Artifact / Definition
MIS - Nodal Data Services Master List v5.7: 01/25/2008 / The NODAL Data Services Master List
EDW Conceptual System Design v1.0 / Texas NODAL EDW Conceptual System Design document
MIS Portal Conceptual System Design v1.0 / Texas MIS Portal Conceptual System Design document
EIP - External Interfaces Specification v1.07 / Texas NODAL EIP External Web Services interfaces list
CDR - Extract and Report Specifications v0.12 / Texas NODAL CDR Report and Extract specification. This currently reflects a subset of all the reports and extracts to be completed by the CDR project. The document will evolve to include additional specifications as they become available.

2. Overview

The design goals and approach of the NODAL CDR project encompass leveraging existing infrastructure like common services for logging, audit trail and exception handling, with required enhancements, efficiently using tools from ERCOT’s Technology stack and leveraging ERCOT standards to generate current day reports.

2.1. Design Goals

The design of CDR leverages the use of existing ERCOT implementations and develops new technologies to supplement them to meet and maintain report latencies as per the NODAL protocols.

The primary design goal of CDR development responsibility is to design a reporting system that generates market reports for the MIS Portal, MIR, and External Web Services in near real time per the NODAL protocols. The scope of the CDR project includes the design and implementation of the changes to the existing MID/MIR Solution.

Additional CDR design goals are:

· Conform to ERCOT corporate policies, including security and regulatory constraints.

· Conform to Enterprise architecture standards, such as technology standards.

· Coexist with current state and planned systems within which the Texas NODAL Systems operates.

2.2. Design Approach

The most important feature of a real time reporting engine is the ability to produce reports almost in near real time without adversely affecting the source system databases. The product also allows reusable components to plug-in to its architecture to account for any business logic or functional calculations. The generated data must also be wrapped in the required delivery file format. All these functionalities must happen without violating the report service level agreement. To reduce the overhead of time, a reporting engine can go directly against the source database, but this might affect the performance of the source system. A reporting engine can also be handled in a typical Extract Transform and Load (ETL) basis, but this technology is better suited for batch runs or scheduled data synchronization, as performing ETL on a very short time frame (e.g., five minutes) can consume high resources. The CDR solution has been designed for meeting stringent report timelines by using source staging databases, calculation engine, and a light weight reporting engine.

The NODAL CDR design approach is to utilize existing enhanced NODAL source system staging tables. CDR will also develop loosely coupled systems in the form of an aggregation engine and reporting engine and incorporate additional systems where necessary. The design will meet all time latency periods, delivery formats, and archiving requirements dictated by MIR repository standards and NODAL protocol requirements

2.2.1 System Functional Capabilities

The basic functionality of the CDR project is to provide reports to the market participants according to the Service Level Agreements (SLA) described by the NODAL Protocols. This is achieved by performing the following four methods:

§ Data Retrieval and Data Aggregation – data will be retrieved by issuing a query against the corresponding Source System of Record and can be further aggregated for reporting purposes.

§ Generate Report – the engine can perform calculations and computations and generate outputs as defined by report templates.

§ Publish Report – the engine will be able to post the report to MIR via the MID web services as well as make available the same data for graphs in User Interfaces and for External Web Services.

§ Retrieve Report – will have the ability to fetch ad-hoc reports generated by the Source System.

In addition, the CDR project will also be functionally responsible for:

§ MID/MIR enhancements to create reports

§ Reports conforming to the ILM (Information Lifecycle Management) specified in the individual report spec

§ Reproduction of reports at a future point in time

Figure 1 CDR White-Box

2.2.2 CDR White Box View

§ Source Systems of Record (Staging tables of Source systems, RSS and ODS):

The Source Systems of record are the Staging tables of the operational systems or RSS or ODS based on the time criticality, latency, and load specified in the individual Report Specification. Current day information stays in the EMS staging tables for 3 days typically for most data and in the MMS Staging tables for 7 days. During this time, data is also replicated to the RSS for the same amount of time specified above. Data is then replicated from the RSS to the ODS and is retained in the ODS for 7 years that includes storage and archival.

§ Market Information Repository (MIR):

Reports are published to MIR for the sake of retrieval by Market Participants and External Web Services. The Information Lifecycle Management (ILM) for each report will be determined as per the individual report specification (e.g., CDR and EDW.) Note: CDR and EDW do not present reports directly to users but place produced documents into MIR; user accesses MIR via the MIS web portal or a web service.