DNV Meter Device Implementation Plan

Overview

This document tracks the implementation progress for integrating water meter device data from two sources:

1. Timeseries CSV - Dynamic consumption data
2. Shapefile - Static device information

Data Sources

Timeseries CSV Fields

Field	Original Name	Data Type	Sample Value	Model Coverage	Status
Account	ACCOUNT	string	10001	✅ Device.AccountNumber	Complete
Route Category	ROUTE_CATEGORY	string	METERED - MONTHLY	✅ DeviceGroup.Name	Complete
Billing Cycle	BILLING_CYCLE	string	METERED - MONTHLY	✅ DeviceGroup.BillingCycle	Complete
Serial Number	REG_SERIAL	string	70093784A	✅ Device.SerialNumber	Complete
Reading Year	READING_YEAR	int	2014	✅ DeviceTagDatum.ReadingYear	Complete
Reading Period	READING_PERIOD	int	12	✅ DeviceTagDatum.ReadingPeriod	Complete
Consumption	CONSUMPTION	double	1112500	✅ DeviceTagDatum.Value	Complete

Shapefile Fields

High Priority Fields (Essential for Operations)

Field	Original Name	Data Type	Sample Value	Model Coverage	Status
Asset ID	Asset_ID	string	WTRMTR00001	✅ Device.AssetId	Complete
Meter Serial	MeterSeria	string	51846999A	✅ Device.SerialNumber	Complete
Address	Address	string	1583 GARDEN AVE	✅ Device.Address	Complete
Location	Location	string	In Manhole At Curb In Backlane	✅ Device.Location	Complete
Meter Type	MeterType	string	T10	✅ Device.MeterType	Complete
Owner	AM_Owner	string	DNV	✅ Device.Owner	Complete
Department	AM_Dept	string	UTILS_DEPT	✅ Device.Department	Complete
Contact Name	ContactNam	string	-	✅ Device.ContactName	Complete
Contact Number	ContactNum	string	-	✅ Device.ContactNumber	Complete
X Coordinate	POINT_X	double	-	✅ Device.XCoord	Complete
Y Coordinate	POINT_Y	double	-	✅ Device.YCoord	Complete

Medium Priority Fields (Operational Support)

Field	Original Name	Data Type	Sample Value	Model Coverage	Status
High Flow Radio ID	HF_RadioID	long	1540560424	❌ Not Implemented	Pending
Low Flow Radio ID	LF_RadioID	long	-	❌ Not Implemented	Pending
High Flow Reading	HF_Reading	double	-	❌ Not Implemented	Pending
Low Flow Reading	LF_Reading	double	27396	❌ Not Implemented	Pending
Bypass Required	BypassReq	string	No	❌ Not Implemented	Pending
Comments	Comments	string	-	❌ Not Implemented	Pending
Asset Size	AM_Size	int	2	❌ Not Implemented	Pending
Size Comment	SizeCommen	string	-	❌ Not Implemented	Pending

Low Priority Fields (Administrative/Historical)

Field	Original Name	Data Type	Sample Value	Model Coverage	Status
Inspector	Inspector	string	Jordie	❌ DeviceInstallation.Inspector	Planned
Data Gatherer	Met_Gather	string	-	❌ DeviceMetadata.MetGather	Planned
Data Format	Met_Format	string	-	❌ DeviceMetadata.MetFormat	Planned
Data Donor	Met_Donor	string	-	❌ DeviceMetadata.MetDonor	Planned
Data Input	Met_Input	string	-	❌ DeviceMetadata.MetInput	Planned
Data Technology	Met_Tech	string	-	❌ DeviceMetadata.MetTech	Planned
Data Method	Met_Method	string	-	❌ DeviceMetadata.MetMethod	Planned
Override By	MET_Ovr_By	string	-	❌ DeviceMetadata.MetOvrBy	Planned
Override Type	MET_Ovr_Ty	string	-	❌ DeviceMetadata.MetOvrTy	Planned
Created By	created_us	string	water9@dnv.org	❌ DeviceAudit.CreatedBy	Planned
Created Date	created_da	DateTime	2024-04-03 0:00:00	❌ DeviceAudit.CreatedDate	Planned
Last Edited By	last_edite	string	SDE_STORMK	❌ DeviceAudit.LastEditedBy	Planned
Last Edited Date	last_edi_1	DateTime	2022-05-05 0:00:00	❌ DeviceAudit.LastEditedDate	Planned
Global ID	GlobalID	string	{D0EA84C8-6B2A-4AB2-9311-09BA7F0DB646}	❌ DeviceAudit.GlobalId	Planned

Implementation Progress

✅ Completed (Phase 1)

• Core Device model structure
• DeviceType model with Description and Unit fields
• DeviceGroup model with BillingCycle and Frequency fields
• DeviceTag model with TagType and Unit fields
• DeviceTagDatum model with composite key and time handling
• Navigation properties for all models
• All timeseries CSV field mappings
• High priority shapefile field mappings
• Coordinate fields (XCoord, YCoord)

🚧 In Progress (Phase 2)

• Medium priority shapefile fields implementation
• Additional Device model fields for operational support

📋 Planned (Phase 3)

• DeviceInstallation model for installation tracking
• DeviceMetadata model for technical specifications
• DeviceAudit model for data governance
• Low priority shapefile field mappings

Data Integration Strategy

Critical Join Key

• Timeseries CSV.REG_SERIAL ↔ Shapefile.MeterSeria ↔ Device.SerialNumber

Data Transformation Pipeline

1. Parse Shapefile → Create/Update Device records with static information
2. Parse Timeseries CSV → Create DeviceTagDatum records with consumption data
3. Join Operations → Link consumption data to devices via SerialNumber
4. Validation → Ensure data integrity and completeness

Next Steps

Immediate (Phase 2)

1. Add medium priority fields to Device model:
   • HighFlowRadioId, LowFlowRadioId
   • HighFlowReading, LowFlowReading
   • BypassRequired, Comments
   • Size, SizeComment

Future (Phase 3)

1. Create DeviceInstallation model
2. Create DeviceMetadata model
3. Create DeviceAudit model
4. Implement remaining low priority fields

Model Relationships

Device (1) ←→ (M) DeviceTag
Device (M) ←→ (1) DeviceType
Device (M) ←→ (1) DeviceGroup
DeviceTag (1) ←→ (M) DeviceTagDatum
Device (1) ←→ (M) DeviceInstallation [Planned]
Device (1) ←→ (1) DeviceMetadata [Planned]
Device (1) ←→ (M) DeviceAudit [Planned]

Success Criteria

• All timeseries CSV data can be stored and queried
• All high priority shapefile data can be stored and queried
• Data can be joined between sources via SerialNumber
• All medium priority operational fields are implemented
• Full audit trail and metadata tracking is available
• Performance is optimized for large datasets
• Data validation rules are implemented