SWEMA Meter Data Warehouse Benefits

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
ElectSolves 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(CI 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
Outage Mgmt
MV90
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 Warehouse by the hour, day and month.
Track system performance by studying both
interval and monthly meter data so you know when
somethings 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 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).
16
Implementing A Meter Data Warehouse
17

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.)
18
Meter Data Warehouse Cost
19
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.
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. Its difficult for
utilities to identify exact customer KWH usages
by calendar month to compare to actual generation
or wholesale purchases on a calendar basis. Its
even more difficult to identify losses down to
the substation level and to the hour. Using
profilers and load shapes in conjunction with CI
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 CI 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 CI 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 CI 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 with Loss Analysis Applications and
substation CI 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_at_electsolve.com)