SWEMA Meter Data Warehouse Benefits & Approaches

1. SWEMAMeter Data Warehouse Benefits & Approaches June 6, 2005 Presented by: Mark Ponder President/Chief Technologist ElectSolve Technology Solutions and Service, Inc.

2. Who is ElectSolve….. ElectSolve Technology Solutions and Services, Inc. is an I/T systems professional services company. Primary Focus is utility I/T systems development, support and services. Customers include: Electric Cooperatives, Electric Utility G & T Cooperatives, Municipal Utilities, Electric Utility Service Providers and Deregulated Electric Market Participants.

3. ElectSolve’s Premise ……. Strong Utility Experience Success for Utilities and Utility Service Providers + = In-Depth, Broad-based Technology Skills

4. ElectSolve Provides . . . • Utility Meter Data Warehousing Design, Development and Implementation Services • Profile Metering Solutions, VEE, Data Settlement & Shadow Settlement Services • Advanced Utility Data Services • Utility Technology Assessments • Utility Systems Architecture, Design, Development and Implementation Services • AMR Meter Data Processing and Improvement Services • AMR Outsourced Operations. AMR Data Integration and Billing Data Preparation • SCADA/EMS, Telecom, Network, LAN, WAN, and Network Infrastructure Services • Computer Server Support and Services • Utility I/T Project Management • I/T Resource Staffing Services • Support and Implementation of Loss Analysis and Reporting Systems

5. SWEMA Special Subject Session #1 Meter Data Warehouse

6. What is a Data Warehouse ?

7. What is a Data Warehouse ? Defined A Data Warehouse is not a single database product. Rather, it is an overall strategy, or process, for building decision support systems and environments that support both everyday tactical decision-making and long-term business strategy. A Data Warehouse is designed to manage historical data that does not get updated once it is processed into the model. A Meter Data Warehouse(MDW) is a Data Warehouse primarily designed for storing and managing vast amounts of historical interval meter data(C&I 5 minute, 15 minute , etc), monthly cycle meter read data and monthly profiled meter data along with the required ancillary information needed to effectively “mine” and report useful information.

8. What is a Meter Data Warehouse ? Defined A Meter Data Warehouse implementation positions a utility to utilize an enterprise-wide meter data store to link information from diverse sources and make the information accessible for a variety of user purposes such as monitoring system performance, settlement, loss analysis and historical operational reporting. The primary objective of a Meter Data Warehouse is to bring together meter read data from disparate sources(AMR, C& I meters, monthly cycle reads, etc) and put the information into a format that is conducive to making business decisions. This objective necessitates a set of activities that are far more complex than just collecting meter data and reporting against it and requires both business and technical expertise to implement.

9. What is a Meter Data Warehouse ? Defined All data in a Meter Data Warehouse is accurate as of some moment in time, providing a historical perspective. This differs from the operational environment in which data is manipulated and changed by operational applications on an ongoing basis. The data in the Meter Data Warehouse is, in effect, a series of snapshots. Once the data is loaded into the enterprise data store and data marts, it is not intended for further update. It is refreshed on a periodic basis, as determined by the business need.

10. Components of a Meter Data Warehouse A Meter Data Warehouse typically includes the following: Operational Database Layer: An operational database is used to manage data that is changed frequently for normal daily business operations. Ex. CIS, Outage Mgmt… Staging Database Layer: A staging database is used to modify operational data to the design of the Meter Data Warehouse. Since these databases have different access patterns and different purposes each will have a different design. Core Data Warehouse Layer: The primary location of historical meter data(both interval and monthly along with all supporting ancillary data pulled in from Operational databases). ETL(extraction, translation and loading) Layer: This layer is responsible for the methods used for loading data into the warehouse. It can be purchased or custom developed depending on the range of needs. BI(Business Intelligence) Layer: This layer is where data is mined, extracted for analysis and reviewed by users. This layer can be purchased or custom developed. Application Layer: This layer includes all legacy and custom analysis applications that utilize data residing in any of the three database layers within the warehouse. Data Access Layer: Many utilities will create a dynamic data access interface so users will not directly access tables in any of the layers of the warehouse.

11. Application Layer CIS MV90 Outage Mgmt IVR Etc… Data Access Layer(data requests flow through this layer) ETL Layer(Meter data loads into Warehouse through this layer) BI Layer(Business intelligence layer. Data mining & analysis) Operational Database(s) (CIS, AMR, MV90, Outage Mgt, IVR, etc…) Staging Database(s) (Extraction, Translation and Loading….) Meter Data Warehouse (Core meter data for analysis and reporting)

12. Is All of This Necessary ? Defined While each of the described Layers of a Meter Data Warehouse has a specific purpose, depending on your goals, you might still achieve your objectives and not implement all of these layers. A minimum set of Layers would include; ETL Layer, Staging Database Layer and the Core Data Warehouse . Each is essential to implement a minimum set of functional capabilities along with an application to report analytical information from the Meter Data Warehouse.

13. Specific Applications & Uses of Meter Data Warehouses?

14. Uses Specific Applications for Meter Data Warehouses … System Loss Analysis: System Losses can be tracked and analyzed by Loss Analysis applications using meter data processed into the Meter Data Warehouseby the hour, day and month. Track system performance by studying both interval and monthly meter data so you know when something’s wrong with the metering accuracy before revenue is lost. Maintain low loss margins consistently over time using interval data analysis enabled by the data stored and managed within the Meter Data Warehouse. Meter Data Archival: Metering data (interval and monthly meter reads) is consolidated and accessed from a single location for Network Analysis, Load Studies, System Planning, Marketing Research, Performance Reporting and for Internal Engineering. Historical Loss Reporting: Accurately report Hourly, Daily, Weekly, Monthly and Annual Losses by calendar dates using interval and profiled hourly data stored in the Meter Data Warehouse. Generate reports by substation or your entire system by the Hour, Day, Month or Year by running reports using data stored in the Meter Data Warehouse. Power Delivery Analysis: Generate reports on Tie-Point power deliveries to reconcile bulk power purchases and for strategic decision support. Shadow Settlement: Perform “Shadow Settlements” using delivery point data stored within the Meter Data Warehouse.

15. Uses Customer Data Access: Enable customer access to interval and monthly meter data managed within the Meter Data Warehouseusing Internet enabled applications. Energy Management: Enable large commercial/industrial customers to access interval meter data and load history for energy management programs. (Fee based) Operational Systems Integration: Enable data sharing and interface options using a common data store. Deregulated Market Participation: Manage vast amounts of both interval and monthly meter data for reporting in a deregulated market place(I.e. Texas Deregulated market participation and ERCOT reporting).

16. Implementing A Meter Data Warehouse

17. How Steps to Implement a Meter Data Warehouse ? Develop a Project Plan Identify Functional Roles and Responsibilities(internalize or outsource) Identify Requirements Analyze Data Source(s) Design and Develop the Data Warehouse Architecture and Databases Design and Develop the Extract Transform Load Data Routines Develop the data Cleansing Routines Design and Develop any new Data Warehouse Applications Design and Develop the Business Intelligence Reporting Implement the warehouse Test Ongoing Database maintenance (Backups, Archiving, Performance Tuning,etc.)

18. Meter Data Warehouse Cost

19. Cost Cost To Implement A Meter Data Warehouse ? Software and Tools: RDBMS licensing, Data translation- extracting and loading tools(ETL) and Data mining tools(BI) are recommended. ETL and BI can be custom developed if the scope of these efforts are minimal. Hardware: Database servers are required to deploy the databases required for Meter Data Warehouses. Adequate disk space, memory, network connectivity, backup and monitoring are required. Support and Management: Hardware and software systems require support and management. In-sourcing is difficult unless adequate technical resources are available. Out-sourcing allows specific selection of the required technical skills to support, manage and expand the Meter Data Warehouse. Professional Services: To implement an effective solution requires knowledgeable resources capable of designing the schema, data population methods and interface capabilities to enable ease of use and manageability. The designer and implementer are usually excellent candidates to provide long term support and expansion of the Meter Data Warehouse.

20. Meter Data Warehouse Use Case

21. Use Case for Meter Data Warehouses Distribution Line Loss Analysis. Power Losses resulting from normal line resistance during transmission and distribution, meter error, “no load losses”, theft, meter sizing errors, etc all contribute to what is commonly referred to “Line Losses”. Utilities generally monitor losses on a rolling monthly basis. While power generation or wholesale purchases are easily reconciled to the calendar month, residential cycle meter reads are random due to cycle schedules. It’s difficult for utilities to identify exact customer KWH usages by calendar month to compare to actual generation or wholesale purchases on a calendar basis. It’s even more difficult to identify losses down to the substation level and to the hour. Using profilers and load shapes in conjunction with C&I interval meter data collection at substations, analysis tools can be used to reduce the “noise” and produce more accurate analysis of losses on a calendar basis resulting in improved control of losses. The data generated from these processes, read profiling and interval data collection, is massive even for small utilities. The Meter Data Warehouse facilitates the data storage, query and retrieval requirements necessary for detailed loss analysis.

22. Use Case for Meter Data Warehouses-continued Monthly cycle based meter reads are profiled to create hourly interval values and C&I 15 minute interval data is aggregated into hourly intervals and both data types are stored and managed within the Meter Data Warehouse for analysis and reporting. For a small 30,000 member cooperative electric or municipal electric, these process would produce approximately 30-35 million records each month to be managed within the data warehouse. Loss Analysis Applications utilize this detail data to determine loss percentages down to the substation level. Without the use of the Meter Data Warehouse and Loss Analysis Applications, a “rolling” monthly loss reporting method is a common approach for tracking losses with limited insight into where the higher losses are occurring within the system. Leveraging a Meter Data Warehouse and Loss Analysis Applications along with C&I metering in place within each substation, a utility is now able to report losses (within certain margins of error) by the hour down to the substation level after all cycle data has been processed from the current cycle billing month.

23. Use Case for Meter Data Warehouses-continued This level of loss analysis reveals greater insight for utilities with higher than normal losses. For instance, a utility with an 8.5% loss level typically has no insight into where their losses are occurring. By leveraging the Meter Data Warehouse, Loss Analysis Applications and improved C&I metering, utilities are better able to determine which areas have higher or lower losses by reporting the data back to the distribution substation level. Efforts can now be focused on areas with the highest losses while other areas may need little or no action. Below is an example of 9 Texas Cooperatives and their loss percentages for 1 recent year.

24. System Loss Use Case(*utility names have been masked for privacy) * There is no detailed insight to help determine where this 8.84% Loss is being generated

25. Leveraging the Meter Data Warehouse in conjunction withLoss Analysis Applications and substation C&I Metering results in better strategic information as illustrated in this example.

26. Conclusion

27. Meter Data Warehouse Considerations • Value (Does this solution provide real business value) • Return on Investment (Can this be measured ? Ex. Loss reductions) • In-Source or Out-Source (Combination usually works best.) • Cost • Supportability (Do you in-source or out-source day to day support?)

28. This Concludes the SWEMA Special Subject #1 Questions For Copies of this presentation send request by email To: Mark Ponder (mponder@electsolve.com)