SWEMAMeter Data Warehouse Benefits &

ApproachesJune 6, 2005

Presented by: Mark Ponder

President/Chief Technologist

ElectSolve Technology Solutions and Service, Inc.

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.

ElectSolve’s Premise …….

Strong Utility

Experience

In-Depth, Broad-based Technology

Skills

Success for Utilities and

Utility Service Providers

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

ElectSolve Provides . . .

SWEMA Special Subject Session #1

Meter Data Warehouse

What is a Data Warehouse ?

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.

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.

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.

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.

Meter Data Warehouse(Core meter data for analysis and reporting)

Data Access Layer(data requests flow through this layer)

CIS Outage MgmtMV90 IVR Etc…

Staging Database(s)(Extraction, Translation and Loading….)

Operational Database(s)(CIS, AMR, MV90, Outage Mgt, IVR, etc…)

ETL Layer(Meter data loads into Warehouse through this layer)

BI Layer(Business intelligence layer. Data mining & analysis)

Application Layer

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.

Specific Applications & Uses of 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 Warehouse by 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 .

Uses Specific Applications for Meter Data Warehouses …

Customer Data Access: Enable customer access to interval and monthly meter

data managed within the Meter Data Warehouse using 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).

Uses

Implementing A Meter Data Warehouse

Steps to Implement a Meter Data Warehouse ?How

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.)

Meter Data Warehouse Cost

Cost To Implement A Meter Data Warehouse ?Cost

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.

Meter Data Warehouse Use Case

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.

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.

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.

Name System Losses Local Rank U.S.Rank Ranking StatisticsCoop#1 9.710% 1 51 Average 7.654%Coop#2 8.840% 2 88Coop#3 8.730% 3 109Coop#4 8.400% 4 130 Highest 9.710%Coop#5 7.610% 5 204 Lowest 5.170%Coop#6 7.130% 6 257Coop#7 6.680% 7 311 Difference 4.540%Coop#8 6.620% 8 315Coop#9 5.170% 9 477 Target % 7.37000%

Coop#2 Ranking Description

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

Utility Substation Active Customers Loss %Coop#2 Sub1 640 5.23 Sub2 1564 13.55 Take Action Sub3 1903 12.05 Take Action Sub4 1254 7.38 Sub5 1086 7.32 Sub6 1208 9.75 Track Sub7 839 4.89 Sub8 229 6.12 Sub9 532 4.20 Sub10 1874 11.01 Take Action Sub11 1200 9.53 Track Sub12 692 5.60 Sub13 568 7.99 Sub14 1104 10.88 Take Action Sub15 2246 4.88 Sub16 724 7.72 Sub17 979 9.09 Track Sub18 465 10.49 Take Action Sub19 1807 9.52 Track Sub20 1272 10.55 Take Action Sub21 511 15.62 Take Action Sub22 1757 8.56 Sub23 1678 8.95 Sub24 1184 10.00 Take Action Sub25 1669 9.40 Track Sub26 1966 8.59 Sub27 1249 9.01 Track Sub28 407 11.85 Take Action Sub29 610 10.68 Take Action Sub30 329 4.67

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

Conclusion

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?)

This Concludes the SWEMA Special Subject #1

Questions

For Copies of this presentation send request by email To: Mark Ponder (mponder@electsolve.com)