General

Advanced Metering Infrastructure (AMI) provides high resolution demand data for each customer meter in a water network, such as the volume consumed each hour.This provides the opportunity for a highly accurate spatial and temporal demand distribution in a hydraulic model, especially compared to common estimation techniques. This data can be used for calibration to a particular historical period, analysis of specific historical events, and real-time modeling. It can also be used as the basis for more accurate typical day patterns, monthly or seasonal variations, forecasts, and master planning.

This wiki describes sample queries to create views in a SQL Server database for importing hourly customer AMI demand into a WaterGEMS model through ModelBuilder. Views allow existing data to be organized, filtered, and categorized as needed on-the-fly, and be read just as a normal data table. For more information on views, see Microsoft SQL Server Help.

WaterGEMS ModelBuilder can connect to SQL Server views as well as tables to import data items such as Customer Meter elements, Patterns, and Pattern Points. When importing, ModelBuilder will update existing items if the Label field matches, or create a new item if no matching Label is found. Newly created items will include default values for any field not explicitly mapped. This means, for example, that new patterns do not explicitly need to have weekly and monthly multipliers assigned. These will default to 1.

After the initial connections are defined, updates can be performed by a single Sync-In command from WaterGEMS prior to a simulation run.

The methodology shown here is very flexible, and can be adapted in many ways. The sample queries below make several assumptions, that can be modified by adjusting the queries:

1. The period of interest for modeling is the most recent 24 hours on a rolling basis.
2. Non-revenue water that does not pass through a customer meter will be added to junctions using some other method.

If the AMI data is stored in a different database type than SQL Server (such as a proprietary vendor database), there are several options:

1. You could use the concepts here as a guide for exporting and pre-processing data by other means and storing the results in Excel or similar tables which ModelBuilder can read.
2. You could replicate a portion of the AMI database into a SQL Server or SQL Server Express (free desktop) database with only the date ranges and customers needed for modeling.

Either option could be scripted to occur automatically to support real-time modeling.

Individual vs. Aggregated Strategies

There are two general strategies that can be used when importing AMI demand data to a hydraulic model. For the Individual strategy, every customer gets a specific pattern directly from their usage (resulting in thousands of patterns in the model). For the Aggregated strategy, customers are assigned to groups (such as Residential, Commercial, etc.), and a single normalized pattern is created for each group (resulting in only a few patterns in the model).

The strategies could also be combined, so that small customers are aggregated while large or otherwise distinctive customers are given individual patterns.

There are 6 view queries defined below. Only 3 are needed for each strategy. Three views are used for the Individual strategy, and the other three views are used for the Aggregated strategy.

In either strategy, the total hourly demand in the model is the same. The spatial distribution is more accurate in the Individual strategy. Performance limitations or other factors such as desired model simplicity may make the Aggregated strategy more attractive.

With the Individual strategy, junction demand will typically look irregular, as it is the combination of semi-random pulse demand from nearby customers:

With the Aggregated strategy, junction demand will typically follow a smoother pattern, as the shape of the pattern is averaged from many similar customers:

For more information on Individual vs. Aggregated strategies and their effects on model accuracy, see the Journal AWWA article Using AMI Data for Water Distribution Modeling.

Model Timeframe

These queries are configured for realtime modeling of the previous 24 hours on a rolling basis. It assumes all customer meter data is rapidly available with minimal lag.If the simulation is extended into the past or future, the recent demand will be repeated every 24 hours.

The queries can be adjusted in many ways to handle different use cases, such as:

1. Extending to a period longer than 24 hours
2. Setting fixed historical dates to a calibration period or abnormal event
3. Averaging demand to get typical seasonal demand
4. Forecasting demand based on any desired logic, such as averaging typical demand for the matching day-of-week
   

Note that if the duration is other than 24 hours, some formulas may need to be adjusted and validated to ensure expected results.

Assumed Database Structure

1. CustomerDetails, with columns (CustomerID, Pattern_WaterGEMS)
2. Demand, with columns (CustomerID, Timestamp, Volume)

"Pattern_WaterGEMS" is simply a text field that names what group the customer should be included with for aggregation, such as "Residential" or "Zone 1". It is only required if using the Aggregated Strategy.

Data Cleaning

SQL Server View Queries

In this methodology, the AMI data is prepared for import to WaterGEMS using View queries in SQL Server. For more information on Views, see Microsoft SQL Server Help. This section shows sample queries that can be used to extract data from the AMI source.

Aggregated Strategy

Strategy Goal: Highly accurate temporal demand and fairly accurate spatial demand optimized for model performance with a small number of patterns.

Assign each customer a Base Demand according to their average demand flow rate in the analysis period.
Group similar customers together, and create an average pattern for each group.
Normalize the pattern (so that the average of the demand multiplier points is 1).

Pattern groups can be by customer type (Residential, Commercial, etc.), by pressure zone or District Meter Area, by both type and zone, or any other logical grouping, and are defined using the "Pattern_WaterGEMS" field of the CustomerDetails table.

Download Aggregated Patterns Queries here

• View: Aggregated_Patterns_Points
• View: Aggregated_Patterns
• View: Aggregated_Customer_Meters

Individual Strategy

Strategy Goal: Highly accurate temporal demand and highly accurate spatial demand using a large number of patterns.

Assign each customer a Base Demand of 1 (as the Pattern will contain the complete flow rate).
Assign each customer an individual pattern where the pattern multipliers are the actual demand converted from a volume to flow rate units.

Note: This strategy could be adapted to normalize the patterns and use an average Base Demand. This is mathematically equivalent, and simply a matter of user preference.

Download Individual Patterns Queries here

• View: Individual_Patterns_Points
• View: Individual_Patterns
• View: Individual_Customer_Meters

Initial Model Configuration

ModelBuilder Setup

Assuming the data is in a SQL Server database, set the Data Source type to OLE DB, and press Browse to establish a connection. In the Provider tab, select SQL Server Native Client. In the Connection tab, enter the server name, login credentials, and select the database.

Once the connection is established, select the three tables matching the chosen strategy (Individual or Aggregated).

Simulation Run

To run a new realtime simulation with the most recent 24 hours of demand data, follow these steps:

  1) In ModelBuilder, select the connection and choose Sync-In to Model.

 

 

  2) Set the Scenario calculation start date and time. Note that for a rolling 24 hour realtime simulation, the start time will not always be midnight.

 

 

  3) Validate the model. Check for any issues with the patterns, such as invalid starting multipliers.

  4) Run the simulation.

  5) Verify the results are as expected.

 

One method of validating the overall results is to open the Calculation Summary, copy the table, and paste it into Excel. The Flow Demanded column can be compared to other simulations, and to summaries queried directly from the AMI database.

 

 

As shown below, the Individual and Aggregated total model demands are almost identical for each timestep, and match the total demand queried from the AMI database. The one noteworthy exception is the initial point.

 

WaterGEMS requires that the PatternStartMultiplier be equal to the final multiplier to form a continuous loop. The current queries simply assign the last multiplier as the starting multiplier. This means the first simulation timestep is actually using demands from 24 hours in the future. The query could be improved by identifying the accurate starting multiplier, and adding a dummy timestep 1 minute after the last true multiplier in the pattern points view that repeats the starting value to form a loop.

• Make sure unit conversions are correct. AMI databases are usually stored in volume, while WaterGEMS uses flow rates for demand.The volume needs to be divided across the period it represents (typically 1 hour) to become a flow rate, and then converted into the model flow rate units.
  
• Spot check a few Customer Meters and their patterns. Confirm that when the Base Demand is multiplied by the Pattern Multiplier, it equals the expected demand at that time. (See the above note about the first timestep.)
• For Aggregated patterns, confirm that the average value of all points in the pattern equals 1 so it is properly normalized.
• Confirm that the Daily and Monthly pattern multipliers are all equal to 1 (especially if any active patterns were created prior to the AMI import).
• Confirm that no Demand Adjustments are active.
• Use the Demand Center tools to check whether there are any unexpected demands or unit demands placed directly on junctions, hydrants, tanks, or surge tanks.
  • If extra demands are intentionally placed on junctions, such as to estimate leaks throughout the system, these demands should be subtracted out from the simulation totals when comparing to AMI database totals.
• Check whether any Customer Meters exist in the model that are not in the AMI database. These may be valid as not all customers may have AMI meters installed. These demands should be subtracted out from the simulation totals when comparing to AMI database totals.