Meter reads are conducted once every 6 months. Much has changed by way of process. Historically paper reads were conducted, before a change to iPads and the Horizon database system. Meter readers found the iPads cumbersome, returned data was of very poor quality, and systems did not integrate well / easily. Therefore, this process is back to paper-based.

As of this article, the process is now entirely centralised on OneCouncil with paper reads and manual data entry being the preferred mode of operation.


The process follows some basic steps that must be followed in order. 

Prior to begining the process, two key preparatory requirements must be met:

  1. Rates team and the water team get as up to date as possible on device replacements and property transfers.
  2. A complete stop of all water related BAU is announced. That means no device replacements, no edits to devices, no billing: absolutely everything that is remotely related water has to freeze in place until the worksheet is finalised. No water-related BAU can be done while the worksheet is active - so this process must complete promptly. 

Once these are in place, the process may begin:

  1. A water read worksheet is created and configured.
  2. The read sheets are generated and printed by the water team. 
  3. Provide OneCouncil access to the data entry person.
  4. Training is provided to any contractors / water team members / rates team members on their role in the process.
  5. Reads are conducted, and the data is entered directly onto the worksheet. The dashboard is used to monitor progress and detect errors in data collection / entry.
  6. The worksheet is reviewed and signed off by the rates team.
  7. The worksheet is finalised.
  8. BAU is resumed.

Configuring the Worksheet

What is the worksheet?

The Meter Reading Worksheet is the staging area for all the data being collected by the meter readers. As the readers complete their routes, they hand the paper copies to the person responsible for data entry. The data entry person then enter the data into this worksheet. At the end of the meter reading process this will become the reads on each water meter, which is then billed later in the year. It is important that the following data is entered into the worksheet by the data entry person, on the correct water meter:

  • The reading (reported number on the dials of the water meter)
  • The reading difficulty code (if there were dogs, overgrown bushes etc)
  • Any comments left by the meter reader for reference during future reads.

Refer to attached screenshot for front-end configuration and make a note of the worksheet number.

Once this is complete, ask ICT to execute stored script "AT_MT_RDN_DTE" to insert the standard read dates for each line in the worksheet. 



Note: It is vital that when prompted to select meters based on reader that field be left blank. When you've hit 'finish' to create the worksheet, check that it has actually pulled in the meters.

 

Generating the Read Sheets

The read sheets are the paper booklets upon which the readers enter their data in the field. They can be considered the paper-version of the currently active meter reading worksheet (because it is!). Once complete, they are then passed to the data entry person. The generation of read sheets is up to the business.

Please note: Running the below steps will give you paper read sheets of the latest meter read worksheet. Make sure that you've configured the meter read worksheet, and there aren't any abandoned worksheets. 
To generate these read sheets:

  1. Enterprise search 'Meter Readings - Manual Read Sheets'
  2. Run the report.
  3. Send it to the appropriate water team member.

Read sheet template is called P1SRV_ManualReadSheets_CHRC and are located in the $CRW_US \ Property server folder.


Provide OneCouncil access to the Data Entry Person

This person is to be provided a workstation by the water team. They will use the following credentials to access this device and OneCouncil. From there they can update the meter reading worksheet. You will need to show them how to enter a reading correctly.


Username: [email protected]

Password: Ask IT (Keepass)


From here on out, the process is largely in the hands of the Water and Rates teams. 

Reads are conducted, data is reviewed, and the worksheet is finalised by the business.


Provide continuing support to the water and rates teams (Dashboard etc)

The Dashboard

A key monitoring tool used by both teams is the meter read progress dashboard. This tracks the read entry progress by service area, and route. It also highlights reads with errors that will need rectification prior to read finalisation. NB: This dashboard does not represent a complete analysis of the reading worksheet: rates still need to independently approve the data using their own certification criteria. Just because the errors section is empty does not mean the read sheet is good to go.


Snapshot of how the dashboard looks. Highlighted section show how to search the entire worksheet, and the historical average calculated for a water meter.



It consists of 4 main components: an ETL that can be scheduled to run at specific times while the meter read process is being undertaken. This ETL updates a two warehouse tables with the information stored in the water meter reading worksheet at that time, and in turn, a data model. The dashboard then visualises the data contained within the data model. For copies of the original files refer to latest change request: #CHN-547


There is two modes in which the ETL may be run: an initial 'set up' mode that recalculates the historical averages of a given water meter (highlighted above). The calculation is according to council specification (the last 3 years [6 scheduled reads]). 

This mode needs only be executed once, as historical reads won't change throughout the process, so once you've run it in this set up mode, you can schedule the ETL to run at specific times of the day in the fast, 'non-set up' mode. To switch between modes, simply set the ETL variable appropriately. 


Process view of the ETL


The ETL performs automatic analysis on the read entered and flags each read with a given status. It does this using a basic Switch/Case statement in the main SQL query powering the ETL:



Changes to how the team want the analysis performed are actioned by tweaking / adding cases to the above section of the main query. (Hint: the numbers in each status are the only way to select colours on the dashboard. The order of the cases needs to be the order of status priority / calculation - once a case resolves to 'true', this is the status of the read, regardless if a more accurate status appears later, so order and specify conditional criteria of cases carefully).


Three dashboard components of note are the 'Reads with Errors', 'Last updated', and 'All reads from Worksheet' sections.
In order they: 

  1. list reads in which the calculated status includes the term 'error' 
  2. Show the list of worksheet IDs the dashboard is representing (There should only be 1 worksheet ID, otherwise something has gone horrible wrong). This section also shows the date/time the dashboard's ETL was last run.
  3. This is a complete listing of all reads in the worksheet, with the added benefit of including the analysis completed by the ETL: i.e. Calculated status, and historical meter average.




Common issues with meter reads

  • Backwards reads: these occur when the entered reading is less than the previously recorded reading. These are to be handled by the business. Usually it's the result of a typo / dodgy handwriting or an inaccurate historical read. The process is either to correct the current read, or to excluded from the worksheet and do a re-read after the main meter reads are complete. The worksheet will not accept a new read less than the previous read unless the data entry person ticks the "clockover" checkbox. At this point it will say the consumption is huge. Do not allow such a read to be finalised - if the business is not aware of the previous process they need to be reminded. Such instances are captured in the dashboard in the "read with errors" section. 
  • Meter readers encounter an issue with the meter: The meter read worksheet is configured to use a shipped list of 'difficulty codes'. These denote a discrete issue encountered by a meter reader (meter is missing, has wrong serial number, doggies etc). Only 1 issue may be recorded. Have the data entry person do their best to pick the right difficulty code, and make sure its being used consistently. Ultimately it's up to the water / billing team to determine how these difficulty codes are to be configured. 
  • Missing or unsatisfactory reader notes: the meter readers may decide to add notes for future reference when conducting reads. To add these, simply have the data entry person enter any relevant scribbles into the "reader notes" section of the read.
  • A warning will appear after entering each read if the read date is in the future. (Very likely if you've run the stored script mentioned in the worksheet configuration section). This is just a warning, and not an error. Data entry person can ignore it.
  • This list will expand as issues are identified.